Blog Maximiliano Accotto (MVP)

SQL Server , consultoría y DBA at Triggerdb.com

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.

 

Como crear un linked Server hacia otro SQL con seguridad integrada customización del nombre.

Este ejemplo de código genera un nuevo linked server hacia un SQL Server pero donde podemos customizar el nombre del mismo como así también indicarle que la conexión se hará por medio de autentificación integrada.

EXEC    sp_addlinkedserver   

@server='NOMBRE (ALIAS)',

@srvproduct='',

@provider='SQLOLEDB',

@datasrc='EL NOMBRE DE SU SERVER SQL E INSTANCIA'

 

EXECUTE master.dbo.sp_addlinkedsrvlogin

@rmtsrvname = ' NOMBRE (ALIAS)',

@useself = N'False',

@locallogin = N'LoginLocal',

@rmtuser = N'dominio\loginremoto',

@rmtpassword = 'Password'

 

 

EXEC    sp_addlinkedserver   

@server='REPORTES',

@srvproduct='',

@provider='SQLOLEDB',

@datasrc='SQLRPT'

 

EXECUTE master.dbo.sp_addlinkedsrvlogin

@rmtsrvname = 'REPORTES',

@useself = N'False',

@locallogin = N'SA',

@rmtuser = N'mda\maccotto',

@rmtpassword = 'Wrt123'

 

Nuevos permisos en SQL 2014 (CONNECT Any Database / Select All User )

En la nueva versión de SQL Server hay varias funciones nuevas. En este articulo veremos dos nuevos permisos que podemos usar como DBA para tener un control mas fino como así también mejorar la administración diaria.

En SQL 2014 tenemos como novedad los permisos de CONNECT ANY DATABASE y SELECT ALL USER SECURABLES .

Básicamente el primer Grant nos permite darle acceso a conexión a un login para todas las bases de datos que existan y las que vendrán en el futuro (en versiones anteriores solo los Sysadmin podían hacer esto).

El segundo permitirá a un login la opción de hacer SELECT para todas las bases.

Ahora vamos a hacer un ejemplo de estas nuevas funciones.

Paso 1: Creamos un login simple

USE [master]

GO

CREATE LOGIN [DEMO14] WITH PASSWORD=N'123',

DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF,

CHECK_POLICY=OFF

GO

 

Paso 2: Probamos de conectarnos con este login a la base AdventureWorks2012

EXECUTE AS  LOGIN = 'DEMO14' 

--SELECT SUSER_SNAME()

USE AdventureWorks2012  

REVERT

 

Lo cual como es lógico nos dará un error de seguridad

Msg 916, Level 14, State 1, Line 12
The server principal "DEMO14" is not able to access the database "AdventureWorks2012" under the current security context.

Paso 3: Usamos los nuevos permisos

GRANT CONNECT ANY DATABASE TO DEMO14

 

Paso 4: Volvemos a probar el acceso y observamos que no da errores

 

Ahora tenemos acceso de connect a todas las bases pero si queremos hacer un SELECT por ejemplo a la tabla Sales.Customer nos dará un error de permisos

 

Msg 916, Level 14, State 1, Line 22
The server principal "DEMO14" is not able to access the database "AdventureWorks2012" under the current security context.
Msg 208, Level 16, State 1, Line 25
Invalid object name 'SALES.Customer'.

Paso 5: Si deseamos darle permisos de lectura a todas las bases usamos “SELECT ALL USER SECURABLES”

GRANT SELECT ALL USER SECURABLES  TO DEMO14

 

Paso 6: Volvemos a probar con el login DEMO14 y observamos que puede hacer SELECT a todas las bases