SQL 2016–Novedades en tablas in-memory

Standard

En el ultimo evento Ignite de 2015 se presentaron algunas de las novedades de SQL Server 2016 entre ellas las mejoras en tablas in-memory.

El siguiente cuadro compara la primer versión de SQL2014 y lo que tendrá SQL 2016

Feature/Limit SQL Server 2014 SQL Server 2016
Maximum size of durable table 256 GB 2 TB
LOB (varbinary(max), [n]varchar(max)) Not supported Supported*
Transparent Data Encryption (TDE) Not supported Supported
Offline Checkpoint Threads 1 1 per container
ALTER PROCEDURE / sp_recompile Not supported Supported (fully online)
Nested native procedure calls Not supported Supported
Natively-compiled scalar UDFs Not supported Supported
ALTER TABLE Not supported / (DROP / re-CREATE) Partially supported / (offline – details below)
DML triggers Not supported Partially supported / (AFTER, natively compiled)
Indexes on NULLable columns Not supported Supported
Non-BIN2 collations in index key columns Not supported Supported
Non-Latin codepages for [var]char columns Not supported Supported
Non-BIN2 comparison / sorting in native modules Not supported Supported
Foreign Keys Not supported Supported
Check/Unique Constraints Not supported Supported
Parallelism Not supported Supported
OUTER JOIN, OR, NOT, UNION [ALL], DISTINCT, EXISTS, IN Not supported Supported
Multiple Active Result Sets (MARS) / (Means better Entity Framework support.) Not supported Supported
SSMS Table Designer Not supported Supported

Tipos de datos Unicode e impacto en la performance

Standard

Los tipos de datos UNICODE que podemos usar con el NCHAR() o NVARCHAR() en muchos casos son útiles si deseamos guardar caracteres de este tipo. Ahora bien, cual es el problema si diseñamos una base con este tipo de caracteres y no los tenemos? en principio uno podría decir que nada ya que si no están el tipo de dato guardara los caracteres normales, pero….  para poder guardar los Unicode necesita el SQL un byte mas por carácter por ello el máximo de un varchar o char común es de 8000 y el de un NChar o Nvarchar es de 4000.

Lo que haremos en este post es observar que sucede con la performance si usamos uno u otro tipo de dato

Primer paso:

Lo primero que haremos es crear dos tablas iguales solo que una esta definida como Unicode y la otra no. Luego a cada una de ellas le insertaremos 1Millon de registros iguales

CREATE TABLE TNOUNICODE (C1 VARCHAR(255),

                         C2 VARCHAR(255),

                         C3 VARCHAR(255)

                                      )

 

CREATE TABLE TUNICODE (C1 NVARCHAR(255),

                       C2 NVARCHAR(255),

                       C3 NVARCHAR(255)

                                    )

GO

 

DECLARE @MAXVALUE INT

SET @MAXVALUE = 1000000 –1 MILLION

 

INSERT INTO TNOUNICODE WITH (TABLOCK) (C1, C2, C3)

SELECT TOP (@MAXVALUE)

‘HOLA MUNDO 12345′,

‘HOLA MUNDO 234567890000000′,

‘HOLA MUNDO XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX’

FROM MASTER.DBO.SYSCOLUMNS S1,

MASTER.DBO.SYSCOLUMNS  S2

 

–DECLARE @MAXVALUE INT

SET @MAXVALUE = 1000000 –1 MILLION

 

INSERT INTO TUNICODE WITH (TABLOCK) (C1, C2, C3)

SELECT TOP (@MAXVALUE)

‘HOLA MUNDO 12345′,

‘HOLA MUNDO 234567890000000′,

‘HOLA MUNDO XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX’

FROM MASTER.DBO.SYSCOLUMNS S1,

MASTER.DBO.SYSCOLUMNS  S2

 

Segundo paso:

Observaremos el espacio ocupado por cada una de estas tablas usando SP_SPACEUSED

EXEC SP_SPACEUSED ‘TNOUNICODE’

EXEC SP_SPACEUSED ‘TUNICODE’

 

image

 

Como podemos observar en los resultados la tabla con Unicode pesa casi el doble que la otra tabla.

Prueba de performance:

Ahora lo que vamos a hacer es un SELECT de ambas tablas las cuales harán el mismo plan de ejecución (Query Plan) pero al ser mas pesado uno que el otro lo veremos representado en los costos de cada QP

SELECT * FROM TNOUNICODE

SELECT * FROM TUNICODE

 

image

 

Conclusiones:

Como siempre decimos hay que ser muy cuidadoso con los tipos de datos a elegir porque el peso juega en la performance, siempre hay que recordar que el costo de un plan de ejecución toma en cuenta el costo de CPU y el de I/O.

Con los datos Unicode recomiendo al igual que los otros tipos de datos analizarlos con cuidado y solo aplicarlos donde se considere será necesario.

Como calcular un acumulado de forma eficiente con TSQL

Standard

En algunas oportunidades necesitamos hacer alguna consulta que nos genere una acumulado de valores, por ejemplo si tenemos una tabla con transacciones poder ir viendo la evolución del saldo para un articulo transacción por transacción.

Este tipo de consultas suelen ser un tanto complejas de resolver y además por lo general poco eficientes.

Por suerte a partir de SQL Server 2012 tenemos la posibilidad de usar las Windows Function las cuales son muy poderosas.

Lo que haremos en este articulo es poder evaluar sobre nuestra base de datos AdventiureWorks2012 o 2014 las diferentes opciones que tenemos para poder calcular un acumulado comparándolos en performance a cada uno de ellos.

Básicamente lo que deseamos obtener es una consulta donde su resultado sea como la siguiente figura donde la columna balance muestra el acumulado de las cantidades para un producto dado transacción por transacción.

image

 Cursores:

Podríamos utilizar un curso para esta consulta el cual su código seria algo así como:

DECLARE @Result AS TABLE

(

  ProductID   INT,

  TransactionID  INT,

  Quantity     int,

  balance int

);

DECLARE

  @productid    AS INT,

  @prvactid AS INT,

  @tranid   AS INT,

  @quantity     AS int,

  @balance  AS int;

 

DECLARE C CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR

  SELECT t.ProductID  , t.TransactionID , t.Quantity

  FROM [Production].[TransactionHistory] t

  ORDER BY t.ProductID , t.TransactionID ;

OPEN C

 

FETCH NEXT FROM C INTO @productid, @tranid, @quantity;

SELECT @prvactid = @productid, @balance = 0;

WHILE @@fetch_status = 0

BEGIN

  IF @productid  <> @prvactid

    SELECT @prvactid = @productid , @balance = 0;

       SET @balance = @balance + @quantity;

       INSERT INTO @Result VALUES(@productid , @tranid, @quantity, @balance);

  FETCH NEXT FROM C INTO @productid, @tranid, @quantity;

END

CLOSE C;

DEALLOCATE C;

 

SELECT * FROM @Result;

GO

 

Sub Consultas

Otra alternativa podría ser la utilización de subconsultas

SELECT T1.ProductID , T1.TransactionID , T1.Quantity ,

  SUM(T2.Quantity ) AS balance

FROM [Production].[TransactionHistory]  AS T1

  JOIN [Production].[TransactionHistory] AS T2

    ON T2.ProductID = T1.ProductID

   AND T2.TransactionID  <= T1.TransactionID

GROUP BY T1.ProductID , T1.TransactionID , T1.Quantity

ORDER BY T1.PRODUCTID,T1.TRANSACTIONID

 

Window Function

Como comente al principio estas funciones se las puede usar a partir de SQL 2012 y lo que resuelven básicamente es la utilización de OVER en las funciones de agregación como el SUM, MIN, MAX, etc.

Veamos como se haría la misma consulta pero usando estas nuevas opciones.

SELECT T.TransactionID,

       T.TransactionDate,

       T.ProductID,

       T.Quantity,

       SUM(T.QUANTITY) OVER (PARTITION BY PRODUCTID ORDER BY TransactionID

                                ROWS UNBOUNDED PRECEDING) AS balance 

   FROM

[Production].[TransactionHistory] T

ORDER BY ProductID,TransactionID 

 

Comparativas en performance y uso de recursos

Lo ultimo que nos quedaría hacer es poder medir los impactos en performance de cada solución y los recursos consumidos, para ello utilice profiler y exponer los resultados en los siguientes gráficos:

Solución

Tiempo ms

Reads

CPU

Sub consulta

24290

804644

69938

Cursores

5948

573391

4140

Window Function

978

876

451

 

image

image

image

 

Conclusiones

Los números hablan por si solos, la diferencia de usar Window Function es insuperable tanto en los tiempos de proceso como en los recursos consumidos.

PowerQuery sigue creciendo

Standard

Esta excelente herramienta de BI sigue creciendo día a día, en su versión de marzo 2015 incorpora

  • Conector para CRM Online.
  • Mejoras en performance.
  • Nuevas transformaciones.

Sin duda que esta herramienta esta día a día creciendo para mejorar el trabajo en BI de los usuarios, yo soy un usuario frecuente de la misma y la verdad que cada día me gusta mas.

Les dejo el link de descarga.

Power Query Update 2

Optimizando los tiempos de creación, ampliación y restore de Base de Datos.

Standard

SQL Server en su edición Enterprise y desde la versión 2005 dispone de la funcionalidad “Instant File Initialization” la cual permite optimizar de forma significativa las operaciones que se realizan sobre los archivos de datos (creación, ampliación y restore básicamente).

Esta funcionalidad no es algo que se deba activar en el SQL Server sino que al Sistema operativo hay que indicarle que le de permisos a la cuenta de SQL Server para poder usarlo, por defecto los administradores del equipo tienen permisos solo para ello, con lo cual si la cuenta de servicio no es administrador del equipo no usara la funcionalidad.

Habilitar o verificar que este el permiso en el SO para usar Instant File.

Paso 1: Desde el menú inicio haga clic en ejecutar (o desde la consola) y escriba: secpol.msc para abrir la Local Security Policy

Paso 2: Del menú de la izquierda busque : Local Policy – > User Rights Assignment

Paso 3: Busque la política : Perfmon volume Maintenance task

Paso 4: Agregue la cuenta de servicio a la política

Paso 5: Reiniciar el servicio de SQL Server

image

image

Pruebas de performance:

Lo que haremos son unas simples pruebas sin tener los permisos habilitados en el Sistema Operativo y luego habilitándolo para poder así usar dicha funcionalidad.

Test 1: Crear una base de datos vacía de 10GB

CREATE DATABASE [DEMOFILEINIT]

 ON  PRIMARY

( NAME = N’DEMOFILEINIT’, FILENAME = N’D:\TMP\DEMOFILEINIT.mdf’ , SIZE = 10240000KB , FILEGROWTH = 1024KB )

 LOG ON

( NAME = N’DEMOFILEINIT_log’, FILENAME = N’D:\TMP\DEMOFILEINIT_log.ldf’ , SIZE = 1024KB , FILEGROWTH = 10%)

GO

 

Test 2: Hacer un restore de la base de datos vacia:

 

RESTORE DATABASE [DEMOFILEINIT] FROM  DISK = N’D:\TMP\DBKP.BAK’ WITH  FILE = 1,  NOUNLOAD

 

Resultados de los test:

 

image

 

NewID() Vs NewsequentialID() en claves primarias

Standard

En algunas situaciones nos puede tocar utilizar alguna clave artificial del tipo GUID como PK (Primary Key). Si bien esto en principio no es ningún problema, lo que sucede es que por defecto (a menos que nosotros digamos otra cosa) toda PK es el índice Clustered de la tabla y aquí si ya empezamos a tener problemas con este tipo de datos.

Dependiendo de la función que usemos podemos obtener mejores en la performance general de la tabla o bien condenarla a que si la PK es el clustered la misma pierda performance en las operaciones de Insert y además quede fragmentada.

Del lado de SQL Server existen dos funciones para poder hacer GUID las cuales son:   NEWID() y NEWSEQUENTIALID()

La primera genera GUID de forma aleatoria con lo cual el problema que vamos a tener que no son secuenciales y si esto lo ponemos en un índice Clustered vamos a tener una tabla mas fragmentada y con perdida de performance.

Ahora haremos algunas pruebas con código para poder comparar ambas funciones:

Creamos tablas:

Vamos a crear dos tablas con el tipo de dato Uniqueidentifier donde usaremos en un caso NEWID() y en otro NEWSEQUENTIALID().

CREATE TABLE T_NEWSEQUENTIALID

(ID UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() PRIMARY KEY,

 FECHA DATETIME,

 C1 CHAR(200),

 C2 CHAR(200),

 C3 CHAR(200),

 C4 CHAR(200),

 NRO INT IDENTITY

)

GO

CREATE TABLE T_NEWID

(ID UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,

 FECHA DATETIME,

 C1 CHAR(200),

 C2 CHAR(200),

 C3 CHAR(200),

 C4 CHAR(200),

 NRO INT IDENTITY

)

GO

 

Insertamos 300K registros a cada tabla:

 

Luego de hacer un INSERT de 300.000 registros a cada tabla haremos la siguiente consulta en cada tabla para observar que sucedió con el orden de las inserciones.

 

SELECT TOP 5 ID,NRO AS ORDENINSERT FROM T_NEWSEQUENTIALID

ORDER BY ID

SELECT TOP 5 ID,NRO AS ORDENINSERT FROM T_NEWID

ORDER BY ID

 

image

 

Aquí podemos observar que NEWID() (el segundo cuadro) fueron insertados de forma totalmente aleatoria impactando en la performance de esta tabla ya que sus PK son clustered.

 

Tiempos de inserción:

 

El siguiente grafico muestra el tiempo insumido en hacer los 300k Insert en cada caso.

 

image

 

Análisis de fragmentación:

Lo que analizaremos ahora es en que nivel de fragmentación han quedado cada una de las tablas y cual seria su impacto.

Para ello usaremos el siguiente query:

SELECT OBJECT_NAME(OBJECT_ID) AS TABLA ,

INDEX_TYPE_DESC as INDICE,

AVG_FRAGMENTATION_IN_PERCENT AS [FRAG %]

FROM

SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(‘TESTID’), NULL, NULL, NULL , NULL);

 

image

Aquí podemos observar que la tabla que ha usado NEWID tiene un 99% de fragmentación Triste.

Como podemos medir que impacto podría tener esto? para ello vamos a correr estas dos consultas y ver su consumo de reads.

SET STATISTICS IO ON

SELECT TOP 20000 * FROM T_NEWSEQUENTIALID

SELECT TOP 20000 * FROM T_NEWID

SET STATISTICS IO OFF

 

Table ‘T_NEWSEQUENTIALID’. Scan count 1, logical reads 2266, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘T_NEWID’. Scan count 1, logical reads 3342, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

Resumen:

Si necesitamos utilizar los campos GUID como PK lo ideal seria que no sean del tipo Clustered pero de serlo es ideal usar la función NEWSEQUENTIALID().

Debemos además mencionar que esta función tiene algoritmos mas performantes para poder calcular el GUID comparada contra  NEWID().

SELECT INTO y paralelismo en SQL 2014

Standard

Muchas veces utilizamos la instrucción SELECT INTO para poder insertar registros en alguna tabla.

A partir de SQL 2014 usando su modo de compatibilidad de base de datos 120, esta instrucción de forma automática hace paralelismo utilizando mas cores y así pudiendo mejorar los tiempos de respuesta en todas estas operaciones.

Es muy importante destacar que la base de datos debe estar en modo 120 (SQL 2014) de lo contrario el SELECT INTO funcionara como lo venía haciendo hasta el momento (sin paralelismo).

Ahora vamos a hacer algunas pruebas de performance y comparar los distintos planes de ejecución.

Usando paralelismo de SELECT INTO:

Lo que haremos primero es poner la base en modo 120 si es que no lo está:

ALTER DATABASE [AdventureWorksDW2014] SET COMPATIBILITY_LEVEL = 120

Ahora haremos un SELCT INTO y observaremos el plan de ejecución:

SELECT [ProductKey]
,[DateKey]
,[MovementDate]
,[UnitCost]
,[UnitsIn]
,[UnitsOut]
,[UnitsBalance]
INTO #TEMP1
FROM DBO.INSERTPARALELO



Aquí podemos observar la aparición de “Parallelism” en el INSERT.

Sin usar paralelismo de SELECT INTO:

La siguiente prueba es ejecutar el mismo SELECT INTO pero que no use paralelismo (versión de base de datos menor que 120)
ALTER DATABASE [AdventureWorksDW2014] SET COMPATIBILITY_LEVEL = 110

SELECT [ProductKey]
,[DateKey]
,[MovementDate]
,[UnitCost]
,[UnitsIn]
,[UnitsOut]
,[UnitsBalance]
INTO #TEMP1
FROM DBO.INSERTPARALELO


Aquí ya podemos observar que no aparece la operación de paralelismo como en el caso anterior.

Comparativas de performance:

En el siguiente cuadro se ilustran algunos números relacionados a la performance cuando se ejecuta de una u otra manera

Tipo Insert Costo QP Duración ms Cpu Time
Paralelismo (SQL 2014)

326

8094

22172

Normal (Modo 110)

576

9510

9484

Como se puede observar en la tabla de resultados es mas performante utilizar el modo de SQL 2014 a costa de mas consumo de CPU y en donde los costos de Query Plan han bajado de forma considerable al igual que los tiempos

Conclusiones:

SQL 2014 en su modo 120 de base de datos tiene entre sus tantas mejoras de performance la posibilidad de utilizar SELECT INTO con algoritmos de paralelismo mejorando así los tiempos de respuesta en estos procesos.

 

Como determinar cuál nodo del clúster es el activo

Standard

Muchas veces sufrimos un failover de un clúster de SQL Server o simplemente necesitamos determinar cuál de los nodos es el activo.

A partir de SQL Server 2012 se agregó a la DMV de sistema SYS.DM_OS_CLUSTER_NODES una nueva columna llamada Is_Current_Owner la cual es un bit e indica de todos los nodos cual está activo (o sea es el principal).Entonces si deseamos saber cuál esta como owner simplemente debemos ejecutar una consulta como la siguiente:

SELECT FROM SYS.DM_OS_CLUSTER_NODES WHERE is_current_owner = 1

Cursores de SQL Server y performance

Standard

Como ya todos sabemos el uso de esta técnica de programación tienen impactos negativos en la performance. Pero hay veces que no tenemos otra opción y si necesitamos usar algún que otro cursor Sad smile

En este post veremos como optimizar a los cursores ya que hay diferentes formas de definirlos y según como lo hagamos podemos tener distintos tiempos de respuesta.

Para nuestras pruebas usaremos la base de datos AdventurweWorks2012 en donde haremos distintos cursores probando los tiempos de respuesta, básicamente la diferencia entre ellos es el tipo de cursor pero no así la consulta o proceso que hagan internamente el cual siempre es el mismo.

Cursor Default:

La primer prueba es usar los cursores por defecto o sea sin indicarle ningún atributo.

DECLARE c CURSOR

FOR

  SELECT O.[object_id]

    FROM sys.objects AS O

    CROSS JOIN (SELECT TOP 1000 name FROM sys.objects) AS O2

    ORDER BY O.[object_id];

OPEN c;

FETCH c INTO @i;

WHILE (@@FETCH_STATUS = 0)

BEGIN

  SET @i += 1;

  FETCH c INTO @i;

END

CLOSE c;

DEALLOCATE c;

Cursor Local:

La segunda prueba es definir al cursor como local y correr el mismo proceso

DECLARE c CURSOR

LOCAL

FOR

  SELECT O.[object_id]

    FROM sys.objects AS O

    CROSS JOIN (SELECT TOP 1000 name FROM sys.objects) AS O2

    ORDER BY O.[object_id];

OPEN c;

FETCH c INTO @i;

WHILE (@@FETCH_STATUS = 0)

BEGIN

  SET @i += 1;

  FETCH c INTO @i;

END

CLOSE c;

DEALLOCATE c;

Cursor Local y estático:

Ahora haremos la prueba definiéndolo como local y a su vez estático

DECLARE c CURSOR

LOCAL STATIC

FOR

  SELECT O.[object_id]

    FROM sys.objects AS O

    CROSS JOIN (SELECT TOP 1000 name FROM sys.objects) AS O2

    ORDER BY O.[object_id];

OPEN c;

FETCH c INTO @i;

WHILE (@@FETCH_STATUS = 0)

BEGIN

  SET @i += 1;

  FETCH c INTO @i;

END

CLOSE c;

DEALLOCATE c;

Cursor Local y FAST_FORWARD:

La siguiente prueba es configurarlo como Local y FAST_FORWARD (Rápido y solo hacia adelante)

DECLARE c CURSOR

LOCAL FAST_FORWARD

FOR

  SELECT O.[object_id]

    FROM sys.objects AS O

    CROSS JOIN (SELECT TOP 1000 name FROM sys.objects) AS O2

    ORDER BY O.[object_id];

OPEN c;

FETCH c INTO @i;

WHILE (@@FETCH_STATUS = 0)

BEGIN

  SET @i += 1;

  FETCH c INTO @i;

END

CLOSE c;

DEALLOCATE c;

Cursor Local , Static Read_Only y Forward_Only:

Nuestra ultima prueba es configurarlo con todas estas opciones.

DECLARE c CURSOR

LOCAL STATIC READ_ONLY FORWARD_ONLY

FOR

  SELECT O.[object_id]

    FROM sys.objects AS O

    CROSS JOIN (SELECT TOP 1000 name FROM sys.objects) AS O2

    ORDER BY O.[object_id];

OPEN c;

FETCH c INTO @i;

WHILE (@@FETCH_STATUS = 0)

BEGIN

  SET @i += 1;

  FETCH c INTO @i;

END

CLOSE c;

DEALLOCATE c;

Comparación de performance en tiempo:

Al correr todos los distintos procesos hemos tomado los tiempos donde arrojaron los siguientes resultados:

TEST TIEMPO (Seg)
CURSOR DEFAULT 22
LOCAL 20
LOCAL STATIC 5
LOCAL FAST_FORWARD 4
LOCAL STATIC READ_ONLY FORWARD_ONLY  4

image

 

Conclusiones:

Si bien el uso de los cursores es una técnica poco recomendada por performance, es bueno considerar si tenemos que usarlos cuales son las mejores opciones de configuración para poder obtener los mejores tiempos de respuesta dentro de un cursor. Como se puede observar en los test hay una diferencia significativa entre los Cursores Default o locales y el resto