Blog SQL Server

Maximiliano Accotto MVP en SQL Server y owner en Triggerdb.com

Cursores de SQL Server y performance

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

Como leer el contenido del transaction log de SQL Server

En muchas oportunidades me preguntan como se puede leer el contenido del transaction log de SQL Server, en este simple post les mostrare el uso de la función no documentada:

sys.fn_dblog la cual nos permitirá leer el transaction log.

 

Primero debemos estar posicionados sobre la base de datos que deseamos analizar el Log, por ejemplo AdventureWorks2012 y luego podemos simplemente correr el siguiente query

 

SELECT * FROM sys.fn_dblog(NULL, NULL)

 

 

El siguiente ejemplo solo busca las operaciones de modificación

 

SELECT * FROM sys.fn_dblog(NULL, NULL)

WHERE Operation IN

   ('LOP_INSERT_ROWS','LOP_MODIFY_ROW',

    'LOP_DELETE_ROWS','LOP_BEGIN_XACT','LOP_COMMIT_XACT') 

 

Leerlog

Índices redundantes y el impacto en la performance

En muchos lugares se habla de los índices redundantes o similares, estos básicamente son índices que están cubiertos por otros dentro de la misma tabla.

Supongamos que tenemos una tabla con 5 campos (C1,C2,C3,C4,C5) y luego creamos índices con este estilo:

Índice 1 = C1

Índice 2 = C1,C2

Índice 3 = C1,C2,C3,C4

El índice 3 cubre a los otros 2 (recordar que se leen y son útiles de izquierda a derecha) ya que índice 3 va a servir para las consultas de C1=Valor y C1 and C2 = Valor.

Que sucede si tenemos estos 3 índices en una tabla? bueno estaríamos castigando las operaciones de modificación (INSERT , UPDATE, DELETE)  ya que tiene que actualizar 3 índices cuando si borro Indice1 e Indice2 seria menor el costo en estas operaciones.

En principio entonces estaríamos diciendo que deberíamos eliminar el índice 1 y 2 para solo quedarnos con el 3, pero en este post veremos que no es tan así la cosa y antes de hacer esto hay que analizar otras cuestiones Sonrisa

Vamos a ir por partes entonces, en primer lugar crearemos una tabla que luego la llenaremos con una cantidad de registros.

USE TEMPDB

GO

 

CREATE TABLE TR (C1 INT,

                          C2 DATETIME,

                              C3 CHAR(500),

                              C4 CHAR(100),

                              C5 CHAR(300),

                              C6 BIT

                              )

GO

 

WITH C

AS

(                    

SELECT TOP 10000000 ROW_NUMBER() OVER( ORDER BY  C1.OBJECT_ID) AS ID  FROM

SYS.COLUMNS C1,

SYS.COLUMNS C2

)

INSERT INTO TR (

                        C1,

                        C2,

                        C3,

                        C4,

                        C5,

                        C6

)

SELECT C.ID AS C1,

DATEADD(MI,C.ID,GETDATE()) AS C2,

 REPLICATE ('A', 500-LEN(C.ID)) + CONVERT(VARCHAR(200),C.ID),

 REPLICATE ('B', 100-LEN(C.ID)) + CONVERT(VARCHAR(200),C.ID),

 REPLICATE ('C', 300-LEN(C.ID)) + CONVERT(VARCHAR(200),C.ID),

 0 AS C6

 FROM C

Ahora que tenemos la tabla creada y sus datos en ella haremos algunas pruebas.

 

Update sin índices:

 

Haremos un UPDATE sobre la tabla sin índices creados para medir el consumo de Reads, para ello usaremos el siguiente código:

 

SET STATISTICS IO ON

 UPDATE TR SET C6=1

SET STATISTICS IO OFF

Como resultado obtenemos las siguientes estadísticas

 

Table 'TR'. Scan count 1, logical reads 246045, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Update con el primer índice:

Lo que haremos ahora es crear un primer índice para el campo C2 y volver a correr el UPDATE anterior para poder así obtener las estadísticas.

CREATE INDEX I1 ON TR (C2)

SET STATISTICS IO ON

 UPDATE TR SET C6=1

SET STATISTICS IO OFF

Como resultado obtenemos las siguientes estadísticas

 

Table 'TR'. Scan count 1, logical reads 1002733, physical reads 0, read-ahead reads 16, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

 

Como se puede observar hemos pasado de 246045 lecturas a 1002733 debido a que ahora la operación es mas costosa por el índice.

 

 

Update con dos índices:

 

Lo que haremos ahora es crear un segundo índice por varios campos pero el primero será para C2 y volveremos a correr el UPDATE

 

 

CREATE INDEX I2 ON TR (C2,C3,C4) INCLUDE (C5,C6)

SET STATISTICS IO ON

 UPDATE TR SET C6=1

SET STATISTICS IO OFF

Como resultado obtenemos las siguientes estadísticas

 

Table 'TR'. Scan count 1, logical reads 1135426, physical reads 0, read-ahead reads 411, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

Como se puede observar también ahora es mas caro hacer el UPDATE  porque tenemos mas índices.

 

Índices redundantes:

 

Observando hasta acá podríamos decir que el primer índice I1 por la columna C2 es redundante ya que el I2 lo contiene en su primera columna, por lo cual podría ser una buena idea eliminar el índice 1 I1 para que las operaciones de modificación sean mas eficientes.

Vamos a hacer algunas pruebas primero con consultas y observar que pasa.

 

Si vemos el query plan del siguiente código observaremos que usa el índice 1 (I1)

 

SELECT * FROM TR

WHERE C2 >= GETDATE()

AND C2 <= GETDATE() + 40

Redundante1

 

 

Con un costo de QP de 146

 

Rediundante2

 

 

Como el primer índice es redundante con I2 vamos a eliminarlo y volver a correr la misma consulta.

 

DROP INDEX I1 ON TR

 

SELECT * FROM TR

WHERE C2 >= GETDATE()

AND C2 <= GETDATE() + 40

Vamos a observar el Query plan ahora

 

Redundante3

Redundante4

 

Como podemos observar se sigue haciendo un Index Seek (ahora por I2) pero el costo es mayor, pasamos de 146 a 151, y esto se debe a que I2 al ser mas pesado que I1 cuesta mas, recordar que el Costo de un Query Plan es = Costo CPU + Costo I/O de una query, como aquí el I/o es superior la consulta pesa mas y tarda mas.

 

Conclusiones:

No es para nada bueno tener índices redundantes, muchas veces (por no decir la mayoría) son por errores en el diseño, pero hay que tener cuidado con consultas especificas como las que vimos,en nuestro ejemplo si borramos el índice redundante las operaciones de UPDATE serán mas rápidas pero en la del Select será mas lenta, dependiendo de cuan critica sea una y otra es como deberíamos actuar.

Como instalar un parche de SQL Server sobre Windows Core

Actualmente hay cada vez mas instalaciones que usan Windows Core (la cual recomiendo 100% para lo SQL). En este simple post voy a enumerar cuales serian los pasos para instalar algún parche ya sea Service Pack u Hotfix.

Paso 1: Extraer el contenido del parche

c:\> SQLServer2012SP2-KB2958429-x64-ENU /x:c:\tmp\servicepack

Paso 2: Ejecutar el setup con interface de Windows Core

c:\tmp\servicepack> setup.exe /UIMODE=EnableUIOnServerCore

Consumo de lecturas vs escrituras por base

Como Dba´s poder saber cual es el porcentaje de escrituras vs lecturas en nuestras bases de datos es algo de vital importancia.

Con esta información podríamos comprender en que tipo de LUN o array poner los archivos.

Para poder saber esta información usaremos la vista sys.dm_io_virtual_file_stats

Recordar que esta información tiene los datos desde la ultima vez que se inicio el servicio de SQL Server.

La siguiente query sacara entonces para todas nuestras bases de datos el porcentaje de lecturas y escrituras.

SELECT

DB_NAME(F.database_id) Base,

CAST(SUM(num_of_bytes_read) AS DECIMAL)

/ ( CAST(SUM(num_of_bytes_written) AS DECIMAL)

+ CAST(SUM(num_of_bytes_read) AS DECIMAL) ) * 100.0 AS RatioReads ,

CAST(SUM(num_of_bytes_written) AS DECIMAL)

/ ( CAST(SUM(num_of_bytes_written) AS DECIMAL)

+ CAST(SUM(num_of_bytes_read) AS DECIMAL) ) * 100.0 AS RatioWrites ,

SUM(num_of_bytes_read) AS TotalBytesRead ,

SUM(num_of_bytes_written) AS TotalBytesWritten

FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS f

group by F.database_id

Iops, latencia y performance

El otro día dando una conferencia me sorprendí porque algunas cosas que uno asume como conocidas no lo son tanto Sad smile , por tal motivo me decidí a escribir este post Smile

Sin ninguna duda que para nosotros los Dba’s el subsistema de disco es nuestro principal cuello de botella en lo que respecta a performance. Por lo tanto es bueno tratar de entender como se mide y funciona.

Sabemos que nuestro motor SQL Server lee y escribe sobre los archivos, esto lo hace con distintos patrones (Random, Secuencial) y con distintos tamaños de bloque (8k, 64k, etc).

Latencia

Nuestro sistema se puede empezar a poner lento si los discos responden lento, esto que quiere decir: Yo SQL Server te envió operaciones de I/O y vos disco tardas mas de lo que yo espero con lo cual empiezo a sentir algunos problemas de performance.

A este fenómeno lo llamamos latencia, que en otras palabras es: La unidad de medida en tiempo que tardara el disco desde el momento que se crea una solicitud de IO hasta el momento que esa solicitud se completa , este tiempo se lo suele medir en ms (milisegundos).

Para poder medir la latencia de nuestro servidor, deberíamos usar alguno de los siguientes contadores de performance (logical disk o del physical disk)

Average Disk sec / read: Latencia en ms de lectura

Average Disk sec / write: Latencia en ms de escritura.

El siguiente cuadro muestra los valores deseados para una base de datos OLTP

1 – 5 ms: para el transaction log (idealmente 1ms)

5 – 20ms: para el Data (Idealmente 10ms).

Arquitectura de discos en el SO

El siguiente dibujo nos muestra como es la arquitectura de las capas en el SO

0508_image_thumb_3A473A55

 

IOPS: (Entradas y salidas por segundo)

Esta es la unidad de medida principal sobre la performance que nos dan los fabricantes de los discos. La misma es una métrica que representa el promedio de la cantidad de operaciones de entrada y salida por segundo que el disco puede hacer.

Como se calculan los iops?

Iops = 1 / (Average latency + Average Seek Time)

Veamos un ejemplo:

Disco X:

Average Latency : 2ms

Average Seek Time: 3ms

Iops = 1 / (0,002 + 0,003) = 200

Calcular los IOPS:

Es muy importante poder calcular los IOPS que nuestro sistema va a necesitar, si por ejemplo calculamos un sistema para 1000 iops y luego le enviamos 2000 lo que sucederá es que el sistema de discos se saturara y empezara a encolar operaciones, con lo cual veremos que los tiempos de latencia aumentaran.

Para poder hacer un calculo es bueno poder medir lo que tenemos , para ello podemos usar los contadores:

Disk Reads / Second: Iops de lectura

Disk Writes / Second: Iops de escritura

Capturando estos valores y luego haciendo alguna formula estadística (ver picos, por ejemplo) podremos usar alguna calculadora que nos ayude a dimensionar cuantos discos necesitaríamos, aquí les paso una de las mas populares

Herramientas para hacer stress test:

Hay distintas herramientas que nos ayudaran a hacer un stress test del disco y obtener resultados de IOPS, Latencias, mb/s etc.

Microsoft para SQL Server tiene estas dos:

SQLIO

SQLSIM

Libro gratis sobre Microsoft Azure HDInsight

2251_9780735685512f_thumb_782340FF

Sin ninguna duda que Big Data llego para quedarse, como muchos sabrán HDInsight es la implementación de Microsoft sobre HADOOP.

Este es un libro gratuito (ingles) sobre esta plataforma. Imperdible para todos aquellos que estamos trabajando en Big Data.

Download the PDF (6.37 MB; 130 pages) 

 http://aka.ms/IntroHDInsight/PDF

Download the EPUB (8.46 MB) 

http://aka.ms/IntroHDInsight/EPUB

Download the MOBI (12.8 MB) 

http://aka.ms/IntroHDInsight/MOBI

SQL 2014 y la posibilidad de extender el Buffer Pool

Esta nueva funcionalidad de SQL Server 2014 es una de las mas interesantes a nivel performance según mi juicio. Todos conocemos que los datos se leen del disco y se almacenan en la famosa cache siendo uno de los componentes claves de la base de datos para poder obtener buenos resultados en performance.

Hasta versiones anteriores a 2014 no se podía controlar el buffer pool para indicarle tamaño, básicamente el mismo era una porción de la memoria RAM instalada.

A partir de SQL 2014 podemos hacer una extensión de este buffer para ampliarlo! obviamente que hay algunas recomendaciones y que no todo es trivial Smile.

Lo primero que debemos tener en cuenta es que la extensión se debe hacer sobre discos de estado solido ya que si lo hacemos sobe otra tecnología corremos serios riesgos.

También destacar que esta funcionalidad esta mas pensada para instalaciones donde la memoria no es muy grande (por ejemplo 32GB de RAM). Si bien se puede aplicar a cualquier entorno es muy probable que con un servidor de 128GB de RAM no sea necesario extender ningún buffer Smile.

El siguiente grafico ilustra como es la arquitectura del buffer pool extension

bufferextension2014

 

Se deben tomar estas consideraciones:

  • El tamaño de la extensión debe ser mayor que la memoria máxima asignada al SQL Server, si por ejemplo tenemos 12GB el archivo de extensión debe ser mayor a 12GB.
  • Se puede alcanzar un máximo de hasta 32 veces el tamaño de max_server_memory pero es recomendado una proporción no mayor a 1:16
  • Antes de implementar esta funcionalidad haga distintas pruebas.
  • Cuando lo tenga implementado en producción evite hacer cambios o desactivarlo.

Recuerde que esta funcionalidad no esta disponible en todas las ediciones de SQL Server.

Ahora veamos unos ejemplos:

ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION ON

(FILENAME = 'E:\BUFFER.BPE', SIZE = 20GB)

GO

 

-- Disable Buffer Pool Extension

ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF

GO

 

Mejoras en los índices ColumnStore de SQL 2014

Los índices ColumnStore fueron una novedad en SQL 2012, los mismos fueron pensados y optimizados para los sistemas DW donde con los mismos se podían lograr mejoras en performance significantes (10x por ejemplo).

Pero como toda nueva funcionalidad tenia algunas limitaciones , los que los hemos usados nos habremos topado con el problema de no poder actualizar la tabla que tenia un índice ColumnStore entonces no nos quedaba otra alternativa que aplicar un proceso de disable y luego Rebuild (post insert o actualización de datos) o bien trabajar con particiones y el Swith de las mismas.

SQL 2014 viene con algunas mejoras en relación a este tipo de índices, ahora si podremos insertar y además usarlos como clustered que era otra de las limitaciones de SQL 2012.

En este post haremos un análisis de estas nuevas funcionalidades.

Paso 1: Armado de tabla demo con datos para luego usar ColumnStore Index

SELECT *

INTO DBO.FACTPRODUCTINVENTORYBIG

FROM DBO.FACTPRODUCTINVENTORY

 

ALTER TABLE DBO.FACTPRODUCTINVENTORYBIG

DROP COLUMN MOVEMENTDATE;

GO

INSERT INTO [DBO].[FACTPRODUCTINVENTORYBIG]

           ([PRODUCTKEY]

           ,[DATEKEY]

           ,[UNITCOST]

           ,[UNITSIN]

           ,[UNITSOUT]

           ,[UNITSBALANCE])

SELECT       [PRODUCTKEY]

           ,[DATEKEY]

           ,[UNITCOST]

           ,[UNITSIN]

           ,[UNITSOUT]

           ,[UNITSBALANCE]

FROM [DBO].[FACTPRODUCTINVENTORY];

GO

INSERT INTO [DBO].[FACTPRODUCTINVENTORYBIG]

           ([PRODUCTKEY]

           ,[DATEKEY]

           ,[UNITCOST]

           ,[UNITSIN]

           ,[UNITSOUT]

           ,[UNITSBALANCE])

SELECT       [PRODUCTKEY]

           ,[DATEKEY]

           ,[UNITCOST]

           ,[UNITSIN]

           ,[UNITSOUT]

           ,[UNITSBALANCE]

FROM [DBO].[FACTPRODUCTINVENTORYBIG];

GO 4

 

Paso 2: Creamos un ColumnStore Index clustered

 

CREATE CLUSTERED COLUMNSTORE INDEX ix_cs_MyDWTable_ON dbo.FactProductInventoryBig;

 

Paso 3: Hacemos un insert a la tabla que tiene el ColumnStore Index Smile

 

INSERT INTO [dbo].[FactProductInventoryBig]

           ([ProductKey]

           ,[DateKey]

           ,[UnitCost]

           ,[UnitsIn]

           ,[UnitsOut]

           ,[UnitsBalance])

SELECT top 1        [ProductKey]

           ,[DateKey]

           ,[UnitCost]

           ,[UnitsIn]

           ,[UnitsOut]

           ,[UnitsBalance]

FROM [dbo].[FactProductInventoryBig];

 

Paso 4: Ejecutamos una consulta para observar en el Query Plan (plan de ejecución) el uso del ColumnStoreIndex

 

SELECT  p.EnglishProductName,

             AVG(fpi.UnitCost),

             SUM(fpi.UnitsOut)

FROM   dbo.FactProductInventoryBig as fpi

INNER JOIN dbo.DimProduct as p ON

       fpi.ProductKey = p.ProductKey

INNER JOIN dbo.DimDate as d ON

       fpi.DateKey = d.DateKey

GROUP BY p.EnglishProductName,

             d.WeekNumberOfYear,

             d.CalendarYear

ORDER BY p.EnglishProductName

 

columnStore1

 

Notas adicionales:

Recuerde primero que estos índices fueron pensados para los sistemas DW, luego la mejora de SQL2014 en el uso de los ColumnStoreIndex es muy importante, pero se debe considerar que para que esto funcione se lo debe crear como Clustered (no se le indican columnas) y los Non Clustered quedaron como solo lectura, o sea: si bien se pueden crear (siempre y cuando y exista un Column Store Index Clustered) los mismos son de solo lectura y quedara la tabla imposibilitada de hacer insert, update o delete. Por ejemplo si deseamos crear el siguiente índice (columnsStore Non Clustered) sobre el ejemplo anterior nos dará un error.

            

CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_1]

ON dbo.FactProductInventoryBig

(

             ProductKey,

             DateKey,

             UnitCost,

             UnitsIn,

             UnitsOut,

             UnitsBalance

);

 

Msg 35303, Level 16, State 1, Line 131 CREATE INDEX statement failed because a nonclustered index cannot be created on a table that has a clustered columnstore index. Consider replacing the clustered columnstore index with a nonclustered columnstore index.