sql server 2014

SolidQ Global S.L.

Juan Carlos I, 88, 4C

03340 Albatera (Alicante)

Spain

solidq.com

+34.914.148.950 office

+34.966.007.008 fax

[email protected]

Contenido Introduccin .................................................................................................................................. 3

Documentacin Oficial de las Novedades ..................................................................................... 4

El Equipo de SolidQ Relacional en Espaa.................................................................................. 5

Publicaciones Realizadas de SQL Server 2014............................................................................... 6

Archivos de Datos como Blobs en Containers de Windows Azure ........................................... 6

Introduccin .......................................................................................................................... 6

Escenarios Donde Puede Resultar til .................................................................................. 6

Puesta en Marcha.................................................................................................................. 7

Conclusiones.......................................................................................................................... 8

Buffer Pool Extension ................................................................................................................ 9

Introduccin .......................................................................................................................... 9

Detalles de la Funcionalidad ................................................................................................. 9

Alternativas de Uso Eficiente de SSD .................................................................................. 10

El Escenario que Podra ser Ideal ........................................................................................ 10

Anlisis de Ventajas en Escenarios de Pruebas Estndar de la Industria ............................ 10

Conclusiones........................................................................................................................ 12

Conectividad SQL Server a Cloud (Windows Azure)................................................................ 13

Introduccin ........................................................................................................................ 13

Un Ejemplo y Pruebas de Impacto ...................................................................................... 13

Herramientas de Medicin.................................................................................................. 14

Elementos a Medir y Probar ................................................................................................ 14

Conclusiones........................................................................................................................ 14

Durabilidad Diferida de Transacciones ................................................................................... 15

Introduccin ........................................................................................................................ 15

Configuracin a Nivel de Base de Datos .............................................................................. 15

Definicin de las Pruebas .................................................................................................... 15

Ejecucin de las Pruebas y Anlisis de Resultados .............................................................. 16

Conclusiones........................................................................................................................ 19

Eficiencia CPU en In-Memory .................................................................................................. 20

Introduccin ........................................................................................................................ 20

Escenario de Mejora Presentado en PASS y Anlisis .......................................................... 20

Diseo de Arquitecturas basadas en ROI ............................................................................ 23

Conclusin ........................................................................................................................... 24

In-Memory Databases con SQL Server 2014 ........................................................................... 25
mailto:[email protected]

SolidQ Global S.L.



Spain

solidq.com

+34.914.148.950 office

+34.966.007.008 fax

[email protected]

Introduccin ........................................................................................................................ 25

El Caso de Uso que Probamos ............................................................................................. 25

Conclusin ........................................................................................................................... 28

IOPS bajo Control con Resource Governor ............................................................................. 29

Introduccin ........................................................................................................................ 29

Las Pruebas Realizadas ........................................................................................................ 29

Conclusin ........................................................................................................................... 33

Reconstruccin de ndices Particionados en SQL Server 2014 ............................................... 34

Introduccin ........................................................................................................................ 34

Instruccin para Reconstruir ONLINE una Particin ........................................................... 34

Nuevas Opciones para Reconstruir ndices ......................................................................... 35

Frenos que Detectamos en Clientes de Cara a Implementar Particionado ........................ 35

SQL Server In-Memory OLTP Engine ....................................................................................... 36

Introduccin ........................................................................................................................ 36

Arquitectura a Alto Nivel y Comunicacin entre los Engines .............................................. 36

La Motivacin ...................................................................................................................... 36

Optimizacin de Estructuras en Memoria RAM .................................................................. 37

Eliminar Latches y Bloqueos ................................................................................................ 38

Compilacin de Cdigo Nativo Mquina ............................................................................. 38

Componentes Clave ............................................................................................................ 39

Comparacin de Rendimiento ............................................................................................ 40

Bibliografa y Documentacin de Referencia ...................................................................... 41

Tablas Temporales vs Tablas in-memory ................................................................................ 42

Introduccin ........................................................................................................................ 42

Escenario de Prueba ............................................................................................................ 42

Pruebas y Anlisis ................................................................................................................ 42

Conclusiones........................................................................................................................ 46

SolidQ Global S.L.



Spain

solidq.com

+34.914.148.950 office

+34.966.007.008 fax

[email protected]

Introduccin La salida de SQL Server 20144 al mercado es inminente, y en los blogs de SolidQ hemos

realizado publicaciones peridicas de funcionalidades que consideramos interesantes para

nuestros clientes. Cuando se incorporan novedades a los productos el ciclo de adopcin que

solemos seguir se aproxima al siguiente:

1. Nos familiarizamos con la nueva caracterstica

2. Evaluamos los casos de uso propuestos por el fabricante

3. Enlazamos/asociamos los casos de uso del fabricante con las necesidades que

detectamos en clientes

4. Preparamos pruebas funcionales de la caracterstica

5. Realizamos pruebas de rendimiento

6. Sacamos conclusiones y proponemos al siguiente nivel (decisores tcnicos o decisores

de negocio dependiendo de la orientacin de la funcionalidad)

7. Realizamos pruebas pseudo-reales de la novedad generalmente PoC (Proof of

Concept)

8. Implementamos la funcionalidad en produccin

Dependiendo de la complejidad de la funcionalidad, el tiempo necesario puede variar mucho;

pondremos dos ejemplos:

1. Implementar las mejoras relacionadas con backup/restore se puede realizar en

menos de 2 das debido a la facilidad de las pruebas a realizar.

2. Implementar una - en memoria puede llevar 1

semana de trabajo perfectamente porque se necesita:

o Analizar el modelo en cuestin

o Realizar cambios necesarios para soportar la funcionalidad

o Probar funcionalmente los cambios

o Capturar y comparar la actividad antes y despus del cambio

o Evaluar el ROI retorno/mejoras que trae el cambio

En el caso de SolidQ, para la mayora de los aspectos nos encontramos entre el 4) y el 7); por

cliente, y por ot -

los que ya estamos realizando pruebas de concepto.

En definitiva, SQL Server 2014 incorpora novedades muy tiles que estamos evaluando para

nuestros clientes; el objetivo de este ebook es compartir con la comunidad y nuestros clientes

las averiguaciones/investigaciones y pruebas que hemos hecho de cara al lanzamiento de SQL

Server 2014.

Finalmente, queremos indicar que las pruebas y anlisis realizados de rendimiento son con

propsito de evaluacin sobre SQL Server 2014 CTP2 y no se pueden utilizar para realizar

comparativas oficiales; dicho de otra forma, estas pruebas no violan la clasula Dewitt.

Esperamos que disfrutis de este documento al igual que lo hemos hecho nosotros evaluando

primero y compartiendo despus el conocimiento adquirido.
mailto:[email protected]://en.wikipedia.org/wiki/David_DeWitt

SolidQ Global S.L.



Spain

solidq.com

+34.914.148.950 office

+34.966.007.008 fax

[email protected]

Documentacin Oficial de las Novedades El motor de SQL Server 2014 incluye las siguientes novedades:

http://msdn.microsoft.com/en-us/library/bb510411(v=sql.120).aspx

Database Engine Feature Enhancements

Memory-optimized Tables SQL Server Data Files in Windows Azure Host a SQL Server Database in a Windows Azure Virtual Machine Backup and Restore Enhancements New Design for Cardinality Estimation Delayed Durability AlwaysOn Enhancements Partition Switching and Indexing Managing the Lock Priority of Online Operations Columnstore Indexes Buffer Pool Extension Incremental Statistics Resource Governor Enhancements for Physical IO Control Online Index Operation Event Class

Que podran desglosarse en las siguientes categoras - :

Alto Rendimiento

Alta Disponibilidad y Escalabilidad

Integracin y aproximacin a Windows Azure (IaaS y PaaS)
mailto:[email protected]://msdn.microsoft.com/en-us/library/bb510411(v=sql.120).aspxhttp://msdn.microsoft.com/en-us/library/bb510411(v=sql.120).aspx#Featurehttp://msdn.microsoft.com/en-us/library/bb510411(v=sql.120).aspx#MemoryOpthttp://msdn.microsoft.com/en-us/library/bb510411(v=sql.120).aspx#DataFileshttp://msdn.microsoft.com/en-us/library/bb510411(v=sql.120).aspx#AzureVMhttp://msdn.microsoft.com/en-us/library/bb510411(v=sql.120).aspx#Backuphttp://msdn.microsoft.com/en-us/library/bb510411(v=sql.120).aspx#CEhttp://msdn.microsoft.com/en-us/library/bb510411(v=sql.120).aspx#Durabilityhttp://msdn.microsoft.com/en-us/library/bb510411(v=sql.120).aspx#AlwaysOnhttp://msdn.microsoft.com/en-us/library/bb510411(v=sql.120).aspx#OIRhttp://msdn.microsoft.com/en-us/library/bb510411(v=sql.120).aspx#Lockhttp://msdn.microsoft.com/en-us/library/bb510411(v=sql.120).aspx#CCIhttp://msdn.microsoft.com/en-us/library/bb510411(v=sql.120).aspx#Bufferhttp://msdn.microsoft.com/en-us/library/bb510411(v=sql.120).aspx#Statshttp://msdn.microsoft.com/en-us/library/bb510411(v=sql.120).aspx#RGhttp://msdn.microsoft.com/en-us/library/bb510411(v=sql.120).aspx#OnlineEvent

SolidQ Global S.L.



Spain

solidq.com

+34.914.148.950 office

+34.966.007.008 fax

[email protected]

El Equipo de SolidQ Relacional en Espaa

Eladio Rincn

Rubn Garrigs

Enrique Catal

Luis J. Morn

Rubn Serna
mailto:[email protected]://es.linkedin.com/in/erincon/file:///C:/Users/rocio_000/Documents/es.linkedin.com/in/enriquecatalafile:///C:/Users/rocio_000/Documents/es.linkedin.com/pub/lus-morn-cuenca/29/406/a34file:///C:/Users/rocio_000/Documents/es.linkedin.com/pub/rubn-serna-glvez/4b/489/474

SolidQ Global S.L.



Spain

solidq.com

+34.914.148.950 office

+34.966.007.008 fax

[email protected]

Publicaciones Realizadas de SQL Server 2014

Archivos de Datos como Blobs en Containers de Windows Azure

Introduccin SQL Server 2014 trae la posibilidad de utilizar/crear archivos de datos directamente en

Windows Azure. Para detalles oficiales de esta nueva caractersticas le recomiendo visitar la

documentacin del producto (SQL Server Data Files in Windows Azure).

De esta forma se puede utilizar archivos de datos (o log) en Azure y la mquina que

proporciona el servicio (capacidad de clculo) en tu CPD/hosting. Esta configuracin formara

parte de uno de los escenarios hbridos de despliegues de SQL Server 2014.

Escenarios Donde Puede Resultar til Elaboramos los escenarios en los que se puede usar y sus consideraciones asociadas:

Capacidad de cmputo controlado: hay muchas dudas infundadas la mayora de

ellas respecto a la capacidad de cmputo de las VMs de SQL Server en Azure (IaaS).

Tambin existen dudas ms sostenidas en cuanto a la capacidad de PaaS. Con esta

configuracin, puedes tener un escenario en el que la capacidad de computo la tienes

Azure.

Trasiego de informacin entre IaaS Azure y tu LAN. Al trabajar en WAN, las latencias

entre el cliente y el servidor de BBDD resulta muchas veces inviable. Un ejemplo tpico

es Dynamics AX: si el cliente de AX y el servidor de BBDD estn en WAN, como AX es

una aplicacin con mucho trasiego entre cliente y servidor el rendimiento decae

notablemente. No queremos criticar AX sino mencionar un ejemplo tpico. De hecho,

Microsoft tiene un proyecto para ofrecer Dynamics AX en Windows Azure para Abril

2014

distribuya los datos a lao clientes de su LAN, pero el servidor de BBDD necesitar

alimentarse de los archivos de datos que estn en Windows Azure. Dicho de otra

forma: si SQL Server necesita leer datos que no estn en memoria (acceso fsico a

datos a procesar y enviar a la aplicacin cliente.

Capacidad transaccional. No parece buena idea que los archivos de transacciones

estn alojados en Windows Azure porque cada transaccin realizada (COMMIT) antes

de confirmar a aplicacin cliente, debe asegurarse del estado ACID (Durabilidad). Es

decir, habra que valorar el ndice de transacciones que tiene la aplicacin antes de

mover el archivo de log (registro de transacciones) a Windows Azure.

Capacidad Analtica. Si la aplicacin realiza eminentemente lecturas, y el trasiego de

datos por memoria es alto, no es buena idea montar los archivos de datos en Windows

Azure, porque con mucha frecuencia estars solicitando datos a Windows Azure con la

consecuente latencia WAN.

Capacidad de almacenamiento. El precio del almacenamiento en Windows Azure es

muy bajo comparando con el coste que puede suponer cada TB en un CPD (muy

inferior a 1EUR / TB / Mes).

Alta Disponibilidad y Recuperacin de Desastres. Se ofrecer como una solucin rpida

para poder montar IaaS en Windows Azure directamente en caso de desastre; aunque

el ofrecimiento es vlido, soy ms partidario de utilizar backups en Windows Azure

que esta lnea de argumento. Es difcil por los requerimientos de un OLTP subir el
mailto:[email protected]://msdn.microsoft.com/en-us/library/dn385720(v=sql.120).aspxhttp://www.zdnet.com/microsoft-to-bring-dynamics-ax-erp-to-the-cloud-by-april-2014-7000022750/http://www.zdnet.com/microsoft-to-bring-dynamics-ax-erp-to-the-cloud-by-april-2014-7000022750/http://www.windowsazure.com/es-es/pricing/details/storage/pricing-changes/http://www.windowsazure.com/es-es/pricing/details/storage/pricing-changes/

SolidQ Global S.L.



Spain

solidq.com

+34.914.148.950 office

+34.966.007.008 fax

[email protected]

registro de transacciones a Windows Azure, por lo que asegurarnos que el archivo est

disponible en Windows Azure, no parece suficiente. Sin embargo, si el SLA del cliente

permite por ejemplo tener aseguradas copias de seguridad cada 15 minutos, SQL

analizaremos en prxima publicacin).

Puesta en Marcha Los pasos para crear la BBDD con archivos en Windows Azure son muy sencillos; necesitas lo

siguiente:

1. Cuenta de Windows Azure Storage donde tengas un contenedor de archivos blob.

2. Crear una policy sobre el contenedor para crear una firma de acceso compartido

(Shared Access Signature Key) sobre el contenedor.

3. Crear credenciales en SQL Server 2014 para enlazar tu instancia con Azure.

4. Crear la BBDD.

Asumiendo que tiene una cuenta de Windows Azure Storage empezaremos por el punto 2);

para ello aunque hay una API de Windows Azure para automatizarlo como es una accin

poco frecuente, utilizarmos Azure Storage Explorer. Los pasos a realizar son los siguientes:

Gestin

Permisos

sobre

contenedor

Nivel de

acceso a

contenedor

(privado)

Creacin

de policy:

lectura y

escritura
mailto:[email protected]://azurestorageexplorer.codeplex.com/http://blogs.solidq.com/elrincondeldba/Lists/Posts/Attachments/269/1SecurityContenedor_2_15B5B7CB.jpghttp://blogs.solidq.com/elrincondeldba/Lists/Posts/Attachments/269/2ContenedorPrivado_2_15B5B7CB.jpghttp://blogs.solidq.com/elrincondeldba/Lists/Posts/Attachments/269/3SharedAccessPolicy_2_15B5B7CB.jpg

SolidQ Global S.L.



Spain

solidq.com

+34.914.148.950 office

+34.966.007.008 fax

[email protected]

Generacin

de clave de

la policy

Resuelta esta parte, podemos proceder a crear la BBDD desde SQL Server Management Studio:

1) Creacin de la credencial:

CREATE CREDENTIAL [https://.blob.core.windows.net/] WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'sr=c&si=XXXXXXXXXXXXXXXXXXXXXXXXXX'

Notas sobre este comando:

Utilizar https

pialo del ltimo paso de la

creacin del SAS

2) Creacin de la BBDD oportuna:

CREATE DATABASE myDB_Azure ON ( NAME = myDB_Azure_data, FILENAME = 'https://.blob.core.windows.net//myDB_Azure.mdf' , SIZE = 20MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%) LOG ON ( NAME = myDB_Azure_log, FILENAME = N'C: \ DBs\ myDB_log_Azure.ldf' , SIZE = 10MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%) Notas sobre este comando:

En nuestro caso, para la siguiente publicacin crearemos el archivo de registro de

transacciones en local.

Conclusiones Cuanto ms importante te resulte tu aplicacin, ms seguro debes estar de los cambios

seran puntos fuertes y dbiles de la tecnologa. Como elemento crtico de esta nueva

posibilidad es las latencias que puedan suceder en lo que se asemeja a un entorno WAN. Por

ello, debes evaluar qu te aporta y qu comprometes con la tecnologa. En siguientes

publicaciones haremos algunas pruebas para ver en qu escenarios nos puede servir.
mailto:[email protected]://blogs.solidq.com/elrincondeldba/Lists/Posts/Attachments/269/4PermisoPolicy_2_15B5B7CB.jpg

SolidQ Global S.L.



Spain

solidq.com

+34.914.148.950 office

+34.966.007.008 fax

[email protected]

Buffer Pool Extension

Introduccin En SQL Server 2014 aparece una nueva funcionalidad denominada Buffer Pool Extension que

permite ampliar la capacidad de nuestro buffer pool mediante el uso de discos rpidos.

Habitualmente situaremos esta extensin en discos SSD o en unidades que tengan garantizado

un rendimiento muy elevado comparado con los discos que almacenan los archivos de datos.

Esta caracterstica extiende el buffer pool para almacenar nicamente pginas limpias,

permitindonos mantener un Working Set mayor entre la memoria y el SSD. De esta forma se

reducir las operaciones aleatorias que se realicen sobre los archivos de datos, mejorando el

rendimiento.

En el siguiente grfico podemos apreciar donde se sita esta cach de nivel 2 (L2) y cmo el

acceso es transparente desde el punto de vista del Storage Engine:

Ms detalles del funcionamiento de este tipo de cachs se puede encontrar en el documento

de Microsoft Research: Turbocharging DBMS Buffer Pool Using SSDs

Detalles de la Funcionalidad En concreto el diseo utilizado en SQL Server 2014 es el denominado Clean-Write (CW) Design

mencionado en el anterior documento, donde las pginas almacenadas en el SSD son siempre

pginas limpias.

Como en muchas caches, el dimensionamiento de stas para que el rendimiento sea ptimo es

importante. En la jerarqua de acceso (dentro de SQL Server, dejando de lado las cachs de los

procesadores) tendramos la L1 (memoria RAM), la L2 (Buffer Pool Extension) y finalmente la

L3 podra ser el disco magntico. El tamao recomendado, aunque debera ser ajustado

mediante pruebas es que la L2 la Buffer Pool Extension sea entre 4 y 8 veces mayor que la

memoria. Por otra parte, se estn reportando resultados pobres en sistemas que disponen de

cantidades de memoria ya bastante significativas (>64GB) por lo que esta funcionalidad

creemos que podra tener un mejor desempeo en entornos virtuales donde la cantidad de

memoria asignable puede ser ms reducida que en un entorno fsico.
mailto:[email protected]://gsl.azurewebsites.net/Portals/0/Users/Projects/SSD/sigmod2010.pdfhttp://blogs.solidq.com/elrincondeldba/Lists/Posts/Attachments/252/image_2_60E9CD57.png

SolidQ Global S.L.



Spain

solidq.com

+34.914.148.950 office

+34.966.007.008 fax

[email protected]

Alternativas de Uso Eficiente de SSD Tambin debemos tener en cuenta que si tenemos disponible almacenamiento rpido tipo SSD

en el servidor tambin podramos sacarle partido para tempdb o para logs de transacciones

de bases de datos OLTP. Quizs no sea posible utilizarlo para datos o logs por ser

almacenamiento local y nuestra instancia estar en failover cluster pero desde SQL Server 2012

si podemos usarlo para tempdb aunque estemos en una instancia en cluster. Tambin

podramos utilizar este almacenamiento SSD para mantener las particiones o tablas ms

utilizadas por nuestra aplicacin. En definitiva, si tenemos almacenamiento SSD el utilizarlo

como Buffer Pool Extension no nos ayudar a aliviar problemas de congestin en tempdb o

latencias excesivas en el log de transacciones, ambos problemas con los que nos encontramos

muy frecuentemente.

El Escenario que Podra ser Ideal Para que esta funcionali

necesitamos que la implementacin Lazy-Cleaning (LC) realizada para el documento de

Microsoft, ya probada sobre SQL Server 2008 R2, pueda ver la luz en algn momento. Con esta

implementacin el SSD se utilizara como una cach write-back de forma que se pudieran

realizar mucho ms rpidamente las operaciones de escritura a la vez que el acceso a las

pginas sucias se podra realizar directamente desde el SSD y no nicamente desde la

memoria. Tambi

reemplazo en la cach dinmica, basada en el uso, la frecuencia del acceso y el tipo de acceso

(priorizando los accesos aleatorios al SSD respecto a los secuenciales). En definitiva estaramos

utilizando el SSD como una cach real para el acceso a disco en todos sus sentidos,

mejorando de forma importante la capacidad de entrada/salida a disco

Anlisis de Ventajas en Escenarios de Pruebas Estndar de la Industria Como muestra de las ventajas que obtendramos os muestro los dos siguientes grficos que

muestran el beneficio de la implementacin LC (en gris) en TPC-C, TPC-E y TPC-H:

SolidQ Global S.L.



Spain

solidq.com

+34.914.148.950 office

+34.966.007.008 fax

[email protected]
mailto:[email protected]://blogs.solidq.com/elrincondeldba/Lists/Posts/Attachments/252/image_6_0ED72010.png

SolidQ Global S.L.



Spain

solidq.com

+34.914.148.950 office

+34.966.007.008 fax

[email protected]

Conclusiones La versin actual de la Buffer Pool Extension puede ser til en escenarios muy especficos

donde la cantidad de memoria disponible (o a la que podemos ampliar la mquina) es

claramente insuficiente para mantener el Working Set necesario para nuestra carga (que

puede llegar a ser de cientos de GB en escenarios DW). En estos casos veremos esperanzas de

vida de pginas muy bajos y una actividad sobre los ficheros de datos elevada. En escenarios

donde la memoria est correctamente dimensionada las mejoras que podemos obtener con

esta caracterstica no seran apreciables por lo que en cierta forma podemos ver esta

(mquinas virtuales con poca memoria, entornos con Working S

SolidQ Global S.L.



Spain

solidq.com

+34.914.148.950 office

+34.966.007.008 fax

[email protected]

Conectividad SQL Server a Cloud (Windows Azure)

Introduccin Un factor importante a tener en cuenta cuando queremos migrar a un entorno Cloud como

Windows Azure nuestros servidores de base de datos es la latencia y throughput de la red.

Algunos escenarios tpicos que podran ser problemticos:

Aplicaciones cliente-servidor de 2 capas donde el cliente es una aplicacin de

escritorio y el servidor es un SQL Server.

Lgica de acceso a datos que utiliza cursores de cliente o implementan

programticamente la lgica de acceso a datos de los cursores.

Aplicaciones que tienen un bajo grado de paralelismo y tienden a realizar muchas

operaciones en serie contra la base de datos.

Informes, volcados, etc. que generen un trasvase de un gran volumen de datos entre

el servidor y el cliente.

Un Ejemplo y Pruebas de Impacto Imaginemos un proceso que implementa una lgica de tipo cursor en la que itera a lo largo de

100 elementos realizando una sencilla consulta por la clave primaria a una tabla de una base

de datos. En estos casos el tamao de cada peticin en base al protocolo TDS ser de

aproximadamente 8 KB. Con un MTU de 1500 bytes este paquete se descompondr en 6

paquetes IP. Si procesamos fila a fila esperando la respuesta de la base de datos entre

iteraciones la duracin del proceso puede pasar desde unos pocos milisegundos en red local

hasta ms de un minuto en un entorno WAN lento:

En los casos en los que se generan consultas de cierto volumen el ancho de banda total

disponible tiene un mayor impacto que la latencia. Por ejemplo si tenemos que devolver 8 MB

de datos y realizamos 10 operaciones, presuponiendo una red de 100 Mbps, el impacto de la

latencia empezar a ser perceptible a partir de unos 30-40ms:
mailto:[email protected]://blogs.solidq.com/elrincondeldba/Lists/Posts/Attachments/254/image_2_5DAED45B.png

SolidQ Global S.L.



Spain

solidq.com

+34.914.148.950 office

+34.966.007.008 fax

[email protected]

Herramientas de Medicin Aunque desde el punto de vista terico es muy importante tener claro el impacto que tendr

el aumento de latencia y/o la limitacin del throughput al final lo principal es realizar pruebas

simulando esta latencia de red. Existen herramientas especializadas en simular enlaces de red

con caractersticas especiales de latencia, de ancho de banda, jitter, prdida de paquetes, etc.

Por mencionar algunas herramientas tenemos SoftPerfect Connection Emulator, Network

Simulator o incluso podemos utilizar la funcionalidad de emulacin de redes de Visual Studio.

Elementos a Medir y Probar Por ejemplo si quisiramos simular un enlace tpico desde nuestro CPD a un servidor en la

nube de Windows Azure deberamos configurar:

Ancho de banda de subida/bajada. En general el ancho de banda disponible con Azure

es de al menos 100 Mbps, por lo que habitualmente deberemos configurar los lmites

de la conexin de nuestro proveedor.

Latencia. La latencia con los CPDs de Azure desde Espaa est alrededor de 60 ms para

las conexiones habituales. Conviene testear este valor desde una mquina de Azure

hasta nuestro CPD y viceversa para ajustarlo ya que en ciertas conexiones la latencia

puede ser de hasta 100 ms.

Errores, jitter, etc. En general las conexiones empresariales son fiables y no tienen

prdidas de paquetes ni un jitter significativo. Si nuestra aplicacin cliente-servidor se

tuviera que conectar mediante ADSL/3G o conexiones similares s podra convenir

parametrizar estos valores para simular mejor la realidad del canal.

Conclusiones Cuando se plantea la migracin de un servidor de base de datos a la nube debemos tener en

cuenta factores ms all que la cantidad de memoria, capacidad de procesamiento y de

espacio en disco. Desgraciadamente el pasar de un entorno LAN a WAN puede tener un

impacto mucho mayor que el nmero de CPUs o la cantidad de RAM de nuestra base de datos

en la nube. Realizar pruebas, bien mediante una prueba de concepto (PoC) desplegando

nuestra base de datos en la nube o mediante el uso de herramientas de simulacin de redes es

fundamental para que nuestra migracin sea exitosa.
mailto:[email protected]://www.softperfect.com/products/connectionemulatorhttp://akmalabs.com/downloads_netsim.phphttp://akmalabs.com/downloads_netsim.phphttp://msdn.microsoft.com/en-us/library/dd505008.aspxhttp://blogs.solidq.com/elrincondeldba/Lists/Posts/Attachments/254/image_4_5DAED45B.png

SolidQ Global S.L.



Spain

solidq.com

+34.914.148.950 office

+34.966.007.008 fax

[email protected]

Durabilidad Diferida de Transacciones

Introduccin En ocasiones tenemos sistemas que soportan una carga de escritura elevada en hardware con

una entrada/salida inadecuada. Esto produce una percepcin de rendimiento pobre cuando

las latencias de escritura en el log de transacciones son elevadas.

La nueva funcionalidad de Durabilidad Diferida de Transacciones en SQL Server 2014 reduce

el impacto de la latencia al mantener en memoria los registros del log de transacciones,

empaquetndolos para escribirlos en disco en batches, de forma que se reduzcan el nmero

de operaciones necesarias. La confirmacin de las transacciones se realiza tan pronto se

escriben los registros en memoria, sin esperar a la confirmacin del disco, por lo que solo

deberamos aplicarlo cuando cierta prdida de datos es aceptable. En cierta forma podemos

verlo como una mejora del buffer para transacciones in-flight donde la confirmacin no se

retrasa al flush de dicho buffer a disco.

Configuracin a Nivel de Base de Datos Afortunadamente la aplicacin de la durabilidad diferida puede configurarse de forma

granular. A nivel de base de datos podemos deshabilitarla (valor por defecto), permitir su uso

o forzar su uso para todas las transacciones:

ALTER DATABASE SET DELAYED_DURABILITY = { DISABLED | ALLOWED | FORCED}

Definicin de las Pruebas Para comprobar que ventaja de cara al rendimiento nos aporta esta funcionalidad vamos a

configurar tres bases de datos distintas cuya nica diferencia ser la ubicacin fsica del fichero

de log:

La ms lenta de ellas almacenar el log en un disco de 5400 rpm, la intermedia en un disco SSD

y la tercera en un disco SSD con cach DRAM. Para la prueba utilizaremos una tabla con 1

milln de registros sobre la que realizaremos UPDATEs aleatorios con varios threads. Para

crear la tabla utilizaremos el siguiente script:

CREATE TABLE test ( id INT IDENTITY ( 1, 1) PRIMARY KEY, contador INT ) GO INSERT INTO test ( contador ) SELECT TOP 1000000 0 FROM sys . objects s1, sys . objects s2, sys . objects s3, sys . objects s4, sys . objects s5
mailto:[email protected]://blogs.solidq.com/elrincondeldba/Lists/Posts/Attachments/263/image_2_4C68C6E2.png

SolidQ Global S.L.



Spain

solidq.com

+34.914.148.950 office

+34.966.007.008 fax

[email protected]

Ejecucin de las Pruebas y Anlisis de Resultados En la ejecucin de este script ya podemos ver diferencias en base a la calidad del tipo de

almacenamiento. En el caso del disco de 5400 rpm la insercin ha necesitado 13 segundos, en

el caso del SSD 8 segundos y en el caso del SSD con cach nicamente 4 segundos.

Para generar los updates de forma aleatorios utilizaremos el siguiente script el cual

encapsularemos en un procedimiento almacenado:

SET NOCOUNT ON; GO DECLARE @i INT = 0 WHILE ( @i < 10000) BEGIN UPDATE test SET contador = contador + 1 WHERE id =CONVERT( INT, RAND()* 1000000) SET @i = @i + 1 END A continuacin lo que haremos es lanzar con 1, 10, 20 y 40 threads simultneos la ejecucin de

este procedimiento para las tres bases de datos. Repetiremos este proceso tres veces y

promediaremos el resultado. Una vez tengamos estos datos, activaremos la durabilidad

diferida y repetiremos el proceso. A continuacin os muestro grficamente los resultados

obtenidos, comenzando con la configuracin de durabilidad completa:

Como podemos ver con durabilidad completa la utilizacin de medios ms rpidos para el log

de transacciones nos aporta un aumento del nmero de updates por segundo bastante

considerable, especialmente en el momento que tenemos concurrencia de ms de un thread.

Vemos tambin como el disco lento con 10 threads ya se satura y no se producen aumentos

significativos aumentando el nmero de threads.
mailto:[email protected]://blogs.solidq.com/elrincondeldba/Lists/Posts/Attachments/263/image_8_7A56199A.png

SolidQ Global S.L.



Spain

solidq.com

+34.914.148.950 office

+34.966.007.008 fax

[email protected]

El siguiente grfico muestra los resultados con durabilidad diferida:

Lo primero que apreciamos es que ya con concurrencias muy bajas, con un 1 solo thread

ejecutando 10.000 UPDATEs en serie, conseguimos valores de ms de 20.000 UPDATEs por

segundo, independientemente del disco utilizado. Vemos tambin que al aumentar a 10

threads concurrentes obtenemos un incremento de los UPDATEs por segundo muy

significativo. Con concurrencias mayores a 10 vemos que en el caso de discos rpidos, la curva

se aplana casi totalmente mientras que en el caso del disco lento sigue creciendo hasta los 40

threads. Al final el throughput mximo que podemos obtener sigue ordenado en el orden

lgico en funcin del tipo de almacenamiento pero las diferencias existentes entre ellos son

mucho menores que con la durabilidad completa.

Observamos tambin que, con diferencia, el escenario ms beneficiado por esta funcionalidad

es el del disco lento, que consigue acercarse mucho al rendimiento de los otros discos:

SolidQ Global S.L.



Spain

solidq.com

+34.914.148.950 office

+34.966.007.008 fax

[email protected]

Como prueba adicional hemos repetido la creacin de la tabla inicial con la insercin de

1.000.000 de registros. Podemos ver como el uso de durabilidad diferida prcticamente

equipara los tiempos a la insercin del escenario ms rpido. En base a ello consideramos que

los escenarios de potencial uso se amplan a aquellos donde las operaciones de modificacin

(insercin, modificacin y borrado) tengan un peso importante.

SolidQ Global S.L.



Spain

solidq.com

+34.914.148.950 office

+34.966.007.008 fax

[email protected]

Conclusiones La conclusin general que extraemos de esta funcionalidad es que la mejora en el rendimiento

nos ha sorprendido muy gratamente. Esperbamos aumentos de rendimiento pero no

esperbamos tanta diferencia en el caso de medios rpidos como el SSD + DRAM.

Las pruebas muestran que el impacto de esta funcionalidad es muy elevado en aquellas

cargas que generan muchas operaciones de entrada/salida en el log. El empaquetado de

estas operaciones y la confirmacin anticipada de la transaccin producen que las escrituras

sean de mayor tamao con lo que el tiempo de respuesta del disco no es tan importante

comparado con el throughput. En casos de operaciones puntuales de un tamao mayor

tambin el almacenamiento previo en RAM aporta ventajas de rendimiento.

En resumen, observamos que en sistemas donde el cuello de botella de la carga sean las

escrituras en el log y sea permisible una pequea cantidad de prdida de datos esta

funcionalidad marcar una gran diferencia de rendimiento a igualdad de hardware.

SolidQ Global S.L.



Spain

solidq.com

+34.914.148.950 office

+34.966.007.008 fax

[email protected]

Eficiencia CPU en In-Memory

Introduccin Ante el prximo lanzamiento de SQL Server 2014 la mayora hemos ledo bastante sobre las

nuevas tablas In-Memory (Heckaton). En base a los resultados quedaba claro que existan

ventajas claras en dos escenarios principalmente:

Escenarios de alta concurrencia con muchas esperas por bloqueos/latches.

Escenarios donde se necesita aumentar el throughput, aunque no se sufriera de

bloqueos/latches.

En la prctica, ambos escenarios suelen darse de forma simultnea. Ante el primer escenario, y

presuponiendo que las consultas e indexacin sean las correctas, las esperas propias de un

modelo pesimista se pueden aliviar utilizando concurrencia optimista (niveles de aislamiento

SNAPSHOT o READ COMMITED SNAPSHOT). Ante el segundo escenario tenemos menos

herramientas en el modelo clsico y las opciones se reducen a asegurarnos que disponemos

del plan de ejecucin ms efectivo para la operacin a realizar y el apoyo de los ndices

apropiados.

Escenario de Mejora Presentado en PASS y Anlisis En la Keynote de PASS de David DeWitt se presentaron unas mediciones muy interesantes orientadas

precisamente a este segundo escenario.

La primera de ellas mide el coste de una operacin bsica (evaluacin de un predicado)

utilizando un plan de ejecucin interpretado (T-SQL clsico) sobre tablas clsicas o tablas en

memoria versus un plan de ejecucin nativo (procedimientos compilados) sobre tablas en

memoria:
mailto:[email protected]://www.sqlpass.org/summit/2013/PASStv.aspx?watch=aW3-0G-SEj0http://blogs.solidq.com/elrincondeldba/Lists/Posts/Attachments/256/image_2_1545EA1B.png

SolidQ Global S.L.



Spain

solidq.com

+34.914.148.950 office

+34.966.007.008 fax

[email protected]

Podemos ver que la reduccin en el nmero de instrucciones a ejecutar por fila llega a

reducirse casi 10 veces. Esto es fundamental cuando nuestro predicado no es un predicado

que podamos optimizar al 100% con un ndice y necesitamos realizar siempre un filtrado de las

N filas obtenidas del ndice para obtener el resultado final.

Imaginemos por ejemplo la siguiente consulta que busca aquellas personas que tengan

USE [ AdventureWorks2012] GO SELECT [FirstName] , [MiddleName] , [LastName] FROM [AdventureWorks2012] . [Person] . [Person] where LastName between 'g' and 'z' and firstname like '%%'

Suponiendo que tenemos un ndice que nos permita buscar por apellido, lo que veremos en el

plan de ejecucin ser algo como esto:

En las propiedades del index seek veremos que tenemos una parte del predicado convertido

en un seek predicate

Si el nmero de filas devuelto por el seek predicate es elevado, el coste de CPU para evaluar

fila a fila si se cumple el filtro %% ser elevado. Es en estos escenarios donde creemos que

debido a la reduccin de CPU obtenida al combinar planes de ejecucin compilados y tablas en

memoria ser muy apreciable de cara al throughput total.

De la misma Keynote vemos que la eficiencia en los lookups de filas aumenta cuanto mayor es

el nmero de filas devueltas por el lookup:
mailto:[email protected]://blogs.solidq.com/elrincondeldba/Lists/Posts/Attachments/256/image_4_1545EA1B.pnghttp://blogs.solidq.com/elrincondeldba/Lists/Posts/Attachments/256/image_6_1545EA1B.png

SolidQ Global S.L.



Spain

solidq.com

+34.914.148.950 office

+34.966.007.008 fax

[email protected]

En el caso de operaciones de escritura (updates) mencionar que si no tenemos colisiones

significativas (fijaros que se trata de random updates) el limitante en el rendimiento est en el

disco, en las escrituras en el log. Debemos tomar estas cifras como vlidas solo para escenarios

donde sea viable perder los datos (estados de sesin?) y no como una mejora general aplicable

en operaciones habituales de UPDATE:

Finalmente me gustara comentar la ltima slide de la keynote:
mailto:[email protected]://blogs.solidq.com/elrincondeldba/Lists/Posts/Attachments/256/image_8_1545EA1B.pnghttp://blogs.solidq.com/elrincondeldba/Lists/Posts/Attachments/256/image_10_1545EA1B.png

SolidQ Global S.L.



Spain

solidq.com

+34.914.148.950 office

+34.966.007.008 fax

[email protected]

La tendencia hardware est clara y es seguir aumentando el nmero de cores disponibles,

memoria ms barata y un rendimiento por core menor respecto al nmero de cores

ofrecidos

quedar rezagado por las soluciones que puedan explotar este nuevo modelo. No debemos

olvidar que venimos de una racha de muchos aos donde los mayores incrementos de

rendimiento se producan precisamente por el aumento de la frecuencia de trabajo y la mayor

cantidad de instrucciones ejecutadas por ciclo (y no por el nmero de procesadores).

Diseo de Arquitecturas basadas en ROI Desde el punto de vista de SQL Server on-premise debemos tener en cuenta que este

aumento en el nmero de cores tambin tiene un componente de coste de licenciamiento

importante. Por tanto lo mejor que podemos hacer es disear nuestra plataforma de forma

que sea capaz de ajustarse a la tipologa de mquina que, sumndole el coste de licencias,

nos d un mejor rendimiento global.

Por poner un ejemplo, en el caso de SQL Server 2012 o superior una mquina con 2

procesadores con 4-6 cores fsicos con Hyperthreading (gama intel E5 v2) con una buena

cantidad de cach L3 y un alta frecuencia de trabajo puede ser mucho ms eficiente desde el

punto de vista de rendimiento/coste comparado con otras alternativas. Obviamente debemos

contar con una solucin optimizada que pueda trabajar con este tipo de mquinas, sin olvidar

que estas configuraciones rinden tanto o ms que mquinas de 4 sockets de hace solo unos

pocos aos.

Si no podemos alcanzar la potencia suficiente con un nico servidor, lo ideal es que nuestra

aplicacin se disee para pueda trabajar contra distintas bases de datos/instancias de SQL

Server (escalabilidad horizontal). Desgraciadamente vemos cada da aplicaciones que sufren

de cuellos de botella en las bases de datos causados por operaciones que seran fcilmente

cacheables en la capa de negocio. Es decir, muchas veces la carga que se lanza contra la base
mailto:[email protected]://blogs.solidq.com/elrincondeldba/Lists/Posts/Attachments/256/image_12_1545EA1B.png

SolidQ Global S.L.



Spain

solidq.com

+34.914.148.950 office

+34.966.007.008 fax

[email protected]

utilizramos cacheo en otros niveles.

Conclusin En resumen, en casos donde la escalabilidad es muy importante es necesario disear

arquitecturas distribuidas, replicadas y/o particionadas en varios niveles. No podemos

confiar que nicamente las mejoras del hardware y el software de base de datos vayan a

solucionar nuestra escalabilidad a futuro. En aquellos casos donde la escalabilidad no es tan

crtica podremos obtener aumentos espectaculares de rendimiento con una buena

optimizacin y con el aprovechamiento de las mejoras de SQL Server 2014.

SolidQ Global S.L.



Spain

solidq.com

+34.914.148.950 office

+34.966.007.008 fax

[email protected]

In-Memory Databases con SQL Server 2014

Introduccin Una de las novedades ms relevantes de SQL Server 2014 es la posibilidad de utilizar

tecnologa de bases de datos en memoria. Esta tecnologa va mucho all del cacheo de datos

tradicional de SQL Server. Esta nueva funcionalidad puede resultar algo compleja de entender

a bajo nivel ya que supone un cambio bastante radical. Sin embargo, el equipo de producto ha

hecho un gran trabajo para que desde el punto de vista funcional sea relativamente sencilla de

utilizar.

El Caso de Uso que Probamos En este post no vamos a entrar en el bajo nivel de la implementacin y nos vamos a centrar en

intentar solucionar un caso muy especfico donde el motor tradicional mostraba sus

limitaciones. El caso que queremos analizar es el comportamiento de inserciones multithread

sobre una tabla con una columna identity que es clave primaria y a su vez ndice cluster.

Vamos a crear primero la tabla de forma tradicional con el identity:

CREATE TABLE TEST ( ID int identity primary key)

Y realizaremos la siguiente insercin de forma concurrente:

INSERT INTO TEST DEFAULT VALUES

Si realizamos 1.000.000 de inserciones de 1 fila con 100 threads concurrentes (10.000

inserciones por thread) el tiempo total de dicho proceso en mi equipo de prueba es de 223

segundos. Esto nos da un tiempo medio por insercin de 0.22 milsimas de segundo lo cual a

priori no es un mal tiempo. Debemos tener en cuenta que el equipo de pruebas cuenta con un

disco SSD lo cual ayuda a mantener la latencia de disco en valores razonables. Si observamos

las esperas veremos que la mayora de esperas son de tipo PAGELATCH_EX lo cual es lo

esperable en este escenario en particular.

A continuacin vamos a crear esta misma tabla utilizando la nueva funcionalidad de tablas in-

memory. El primer paso es aadir un FILEGROUP nuevo a nuestra base de datos para

almacenar este nuevo tipo de tablas:

ALTER DATABASE test ADD FILEGROUP test_memory CONTAINS MEMORY_OPTIMIZED_DATA GO ALTER DATABASE test ADD FILE ( name='test_memory' , filenam e='c: \ test_memory' ) TO FILEGROUP test_memory

est soportado en tablas en memoria. Si intentamos crear una tabla in-memory con identity

obtenemos el siguiente error:

CREATE TABLE dbo. test ( id INT IDENTITY NOT NULL, CONSTRAINT PK PRIMARY KEY HASH ( id ) WITH ( BUCKET_COUNT = 1000000) ) WITH ( MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)

SolidQ Global S.L.



Spain

solidq.com

+34.914.148.950 office

+34.966.007.008 fax

[email protected]

Como workaround, crearemos la tabla sin identity e insertaremos utilizando como tope el

valor mximo existente en la tabla en un procedimiento compilado atmico:

CREATE TABLE dbo. test ( id INT NOT NULL, CONSTRAINT PK PRIMARY KEY HASH ( id ) WITH ( BUCKET_COUNT = 1000000) ) WITH ( MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA) GO CREATE PROCEDURE [dbo] . test_insert WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER AS BEGIN ATOMIC WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'us_english' ) INSERT INTO dbo. test ( id ) SELECT TOP 1 id +1 FROM dbo. test ORDER BY id DESC END

Desgraciadamente nuestra aproximacin no es vlida ya que en cuanto la tabla contiene ms

de 8000 filas obtenemos el siguiente error:

Otra alternativa sera el uso de secuencias, que s estn soportadas, pero no lo estn en

procedimientos almacenados compilados. Por tanto necesitaramos de un procedimiento

CREATE SEQUENCE seq_1 AS INT START WITH 1 INCREMENT BY 1; GO CREATE PROCEDURE [dbo] . test_insert ( @id int ) WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER AS BEGIN ATOMIC WITH ( TRANSACTION ISOLATION LEVEL = SERIALIZABLE, LANGUAGE = 'us_english' ) INSERT INTO dbo. test ( id ) values ( @id) END
mailto:[email protected]://blogs.solidq.com/elrincondeldba/Lists/Posts/Attachments/249/image_2_2EA2C33A.pnghttp://blogs.solidq.com/elrincondeldba/Lists/Posts/Attachments/249/image_4_2EA2C33A.png

SolidQ Global S.L.



Spain

solidq.com

+34.914.148.950 office

+34.966.007.008 fax

[email protected]

GO CREATE PROCEDURE test_insert_wrapper AS BEGIN DECLARE @id INT = NEXT VALUE FOR seq_1 EXEC test_insert @id END

Con esta alternativa el tiempo de ejecucin de un milln de inserciones de 1 fila con 100

threads concurrentes es de 280 segundos. Indicar que en este caso el cuello de botella est en

la CPU que se mantiene al 100% durante todo el proceso (4 cores) y es lo que probablemente

hace que la solucin in-memory no escale mejor. En un sistema con muchos procesadores el

resultado podra ser muy distinto. No olvidemos que esta tecnologa est diseada pensando

en estos escenarios donde tenemos una cantidad de CPU muy elevada pero que no podemos

explotar debido a contencin interna del motor.

Otra alternativa distinta sera no utilizar secuencias y gestionar nosotros mismos nuestra

deberamos implementar una lgica de reintentos para los casos en los que colisionen las

operaciones de update sobre el contador (que con 100 threads concurrentes son bastante

frecuentes). Una de las caractersticas de las tablas en memoria es que utilizan una

aproximacin optimista a la concurrencia. Esta alternativa nos ofrece rendimiento peor en

nuestro caso, de ms de 370 segundos para el milln de inserciones. Este tipo de soluciones

solo seran vlidas en los escenarios donde la concurrencia optimista no genere una colisin en

la mayora de casos:

CREATE TABLE dbo. contador ( id int NOT NULL, contador int not null CONSTRAINT PK_2 PRIMARY KEY HASH ( id ) WITH ( BUCKET_COUNT = 1000000) ) WITH ( MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA) GO INSERT INTO dbo. contador VALUES ( 1, 1) GO CREATE PROCEDURE [dbo] . test_insert2 WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER AS BEGIN ATOMIC WITH ( TRANSACTION ISOLATION LEVEL = SERIALIZABLE, LANGUAGE = 'us_english' ) INSERT INTO dbo. test ( id ) SELECT top 1 contador from dbo. contador UPDATE dbo. contador SET contador =contador +1 WHERE id =1 END GO

SolidQ Global S.L.



Spain

solidq.com

+34.914.148.950 office

+34.966.007.008 fax

[email protected]

CREATE PROCEDURE test_insert_wrapper2 AS BEGIN DECLARE @retry INT = 10 WHILE ( @retry > 0) BEGIN BEGIN TRY EXEC test_insert2 SET @retry = 0 END TRY BEGIN CATCH SET @retry - = 1 IF ( @retry > 0) IF ( @@TRANCOUNT>0) ROLLBACK TRANSACTION ELSE THROW END CATCH END END

Conclusin Aunque la tecnologa in-memory busca solucionar problemas de contencin no siempre va a

ser viable implementarla ni tiene que mejorar necesariamente el rendimiento de nuestra

solucin actual. Ser necesario realizar pruebas especficas, con hardware lo ms similar al de

produccin, para asegurarnos que la solucin con tablas en memoria es claramente ventajosa.

En nuestra experiencia en muchos casos los problemas de rendimiento en las inserciones

vienen de la mano de problemas con la entrada/salida a disco en el servidor. Si sta es

deficiente y tiene latencias excesivas, un throughput deficiente, etc. los tiempos de respuesta

de las operaciones de insercin, actualizacin o borrado sern altos.

SolidQ Global S.L.



Spain

solidq.com

+34.914.148.950 office

+34.966.007.008 fax

[email protected]

IOPS bajo Control con Resource Governor

Introduccin Una de las nuevas funcionalidades que se incluirn en SQL Server 2014 es la posibilidad de

controlar la cantidad de IOPS por volumen que un determinado resource pool puede utilizar.

Las Pruebas Realizadas Antes de nada, prepararemos nuestro entorno de pruebas sobre un SQL Server 2014 CTP2.

Crearemos una tabla e insertaremos unas cuantas filas:

Como vamos a forzar un escenario que genere bastantes IOPS, vamos a aadir un ndice que

luego forzaremos a utilizar en nuestra query :

Nuestra carga ser un simple bucle WHILE que ejecutar una consulta y borrar la cach (para

forzar la IO a disco):

En el plan de ejecucin vemos que se leer el ndice ix_test y se realizarn lookups sobre el

ndice cluster. Este plan es muy ineficiente pero lo que buscamos es precisamente con dicha

ineficiencia generar bastantes IOPS:

Si lanzamos esta carga sin limitar con Resource Governor vemos que obtenemos

aproximadamente unas 1600 IOPS por segundo de media:
mailto:[email protected]://blogs.solidq.com/elrincondeldba/Lists/Posts/Attachments/255/image_2_123B67E1.pnghttp://blogs.solidq.com/elrincondeldba/Lists/Posts/Attachments/255/image_4_123B67E1.pnghttp://blogs.solidq.com/elrincondeldba/Lists/Posts/Attachments/255/image_8_123B67E1.pnghttp://blogs.solidq.com/elrincondeldba/Lists/Posts/Attachments/255/image_6_123B67E1.png

SolidQ Global S.L.



Spain

solidq.com

+34.914.148.950 office

+34.966.007.008 fax

[email protected]

A continuacin vamos a crear una funcin clasificadora que devuelva siempre el mismo

nombre de pool. Esto sera un sinsentido en un entorno de produccin, pero es adecuado para

la prueba:

El siguiente paso es intentar configurar los lmites de IOPS para un Resource Pool.

Desgraciadamente si acudimos al interfaz grfico vemos que no han contemplado dicha opcin

(esperamos que s aparezca en RTM):
mailto:[email protected]://blogs.solidq.com/elrincondeldba/Lists/Posts/Attachments/255/image_10_123B67E1.pnghttp://blogs.solidq.com/elrincondeldba/Lists/Posts/Attachments/255/image_12_123B67E1.png

SolidQ Global S.L.



Spain

solidq.com

+34.914.148.950 office

+34.966.007.008 fax

[email protected]

Deberemos por tanto crear el Resource Pool y el Workload Pool de forma manual.

Especificaremos el mnimo y mximo de IOPS dentro del margen que tenemos de IOPS sin

limitar. Nuestra intencin es que no consumamos ms de 1000 IOPS desde este pool y le

garanticemos un mnimo de 500:
mailto:[email protected]://blogs.solidq.com/elrincondeldba/Lists/Posts/Attachments/255/image_14_7D49E56D.pnghttp://blogs.solidq.com/elrincondeldba/Lists/Posts/Attachments/255/clip_image001_2_7D49E56D.png

SolidQ Global S.L.



Spain

solidq.com

+34.914.148.950 office

+34.966.007.008 fax

[email protected]

Una vez configurado, lanzamos la carga con Resource Governor activado y vamos activndolo y

desactivndolo sucesivamente para ver cmo realiza el control de las IOPS:
mailto:[email protected]://blogs.solidq.com/elrincondeldba/Lists/Posts/Attachments/255/image_16_7D49E56D.pnghttp://blogs.solidq.com/elrincondeldba/Lists/Posts/Attachments/255/image_18_7D49E56D.png

SolidQ Global S.L.



Spain

solidq.com

+34.914.148.950 office

+34.966.007.008 fax

[email protected]

Podemos apreciar que realmente s realiza un control de las IOPS, pero cuando habilitamos

Resource Governor nos est limitando a 320 IOPS aproximadamente pese a que le indicamos

un mximo de 1000 y un mnimo de 500. Si lanzamos varias sesiones concurrentes vemos que

vamos subiendo de IOPS hasta llegar a unas 750, donde ms o menos se estabiliza, quedando

an por debajo del mximo de 1000 establecido.

Conclusin La funcionalidad en CTP2 para limitar las IOPS por volumen de Resource Governor funciona

aunque no al 100% (hecho sin embargo normal en versiones no finales). Parece que quedan

algunos flecos para afinar de forma que nos acerquemos ms a los valores realmente

configurados en el Resource Group. Cuando est disponible la versin final RTM volveremos a

analizar el comportamiento de esta nueva caracterstica de SQL Server 2014 para ver si cumple

lo prometido.
mailto:[email protected]://blogs.solidq.com/elrincondeldba/Lists/Posts/Attachments/255/image_20_7D49E56D.png

SolidQ Global S.L.



Spain

solidq.com

+34.914.148.950 office

+34.966.007.008 fax

[email protected]

Reconstruccin de ndices Particionados en SQL Server 2014

Introduccin Uno de los problemas histricos desde que se incorpor el particionado nativo en SQL Server

2005 ha sido el no disponer de reconstruccin online de particiones individuales. Esto no haca

tener que elegir entre procesos de reorganizacin (que son siempre online) o bien realizar en

ventanas de mantenimiento las reconstrucciones offline sobre las particiones que se

encontraran fragmentadas. Afortunadamente en SQL Server 2014 se permiten las

operaciones de reconstruccin de ndices online particin a particin. Esta ha sido una de las

caractersticas ms demandadas por la comunidad de SQL Server.

Instruccin para Reconstruir ONLINE una Particin Con SQL Server 2014 podemos reconstruir online una de nuestras particiones de la siguiente

forma:

ALTER INDEX MyIndex ON MyTable

REBUILD PARTITION = 3 WITH ( ONLINE = ON) Adems se ha aadido una opcin interesante para las operaciones de SWITCH y de

reconstruccin online llamada WAIT_AT_LOW_PRIORITY:
mailto:[email protected]://blogs.solidq.com/elrincondeldba/Lists/Posts/Attachments/250/image_2_1B39FC7E.png

SolidQ Global S.L.



Spain

solidq.com

+34.914.148.950 office

+34.966.007.008 fax

[email protected]

Nuevas Opciones para Reconstruir ndices La instruccin tiene las siguientes nuevas opciones:

La opcin WAIT_AT_LOW_PRIORITY nos permite que la operacin espere con baja

prioridad un tiempo determinado (MAX_DURATION) a poder adquirir el bloqueo de

esquema (mnimo necesario para poder acometer la operacin online).

La opcin ABORT_AFTER_WAIT nos permitir determinar qu hacer si llegamos al

tiempo mximo definido y no hemos podido adquirir an el bloqueo.

La opcin NONE nos permitir seguir esperando el bloqueo, eso si ya con prioridad

normal. La opcin SELF nos permitir abortar la operacin que estamos intentando

realizar.

La opcin BLOCKERS realizar un KILL de todas las transacciones que nos impiden

continuar.

De cara a futuro sera interesante que esta caracterstica de WAIT_AT_LOW_PRIORITY se

pudiera extender a otras operaciones que tambin suelen sufrir de bloqueos. Me refiero por

ejemplo a operaciones como aadir a una tabla una columna. Este tipo de operaciones en

sistemas con carga continua puede llegar a eternizarse o incluso requerir de intervencin

manual (matar procesos bloqueadores). Con estas opciones y otras (como la conocida WITH

ROLLBACK IMMEDIATE para ALTER DATABASE) lo que se busca es facilitar la labor del

administrador (o de sus scripts de administracin) dando prioridad a ciertas operaciones sobre

otras.

Frenos que Detectamos en Clientes de Cara a Implementar Particionado Con esta nueva opcin en SQL Server 2014 mejoramos el mantenimiento del particionado en

lo que a fragmentacin respecta. En base a nuestras experiencias en clientes, existen otros

frenos importantes a la implantacin de particionado. Uno de ellos es la no gestin de

estadsticas a nivel de particin y, aunque es posible que se est trabajando en ello, no vendra

mal algo de apoyo en Connect:

https://connect.microsoft.com/SQLServer/feedback/details/468517/update-statistics-at-the-

partition-level

Otro freno a la implantacin de particionado es que el particionado no se auto-mantiene

automticamente. Es decir, habitualmente se comienza definiendo para cada tabla/ndice a

particionar la funcin de particionado y el esquema de particionado. Estos elementos, aunque

se suelen compartir entre varios objetos particionados, requieren que de forma manual se les

den mantenimiento. Esto ocurre tanto si se trata de un escenario de ventana deslizante como

si se trata de un particionado fijo por meses/trimestres/aos, etc. Aunque existen ejemplos de

cmo automatizar este mantenimiento creemos que dicha funcionalidad, para al menos los

casos bsicos, debiera estar incluida en el producto y existieran un conjunto de plantillas

predefinidas para facilitar el particionado. Es decir, por ejemplo una plantilla debera

permitirnos siguiendo un wizard particionar por fecha una tabla a nivel de mes y

mantener una ventana de X meses dentro de la ventana. De forma automtica se generaran

los nuevos filegroups (si as se configurara) y se modificara el esquema de particionado para ir

aadiendo/borrando particiones. En cierta forma sera una especie de plan de mantenimiento

pero orientado a particiones.

Un hecho constatable es que el volumen de datos manejados por las bases de datos sigue

creciendo por lo que esperamos que en sucesivas versiones de SQL Server el particionado siga

mejorando y nos permita aplicarlo en un mayor nmero de escenarios.
mailto:[email protected]://connect.microsoft.com/SQLServer/feedback/details/468517/update-statistics-at-the-partition-levelhttps://connect.microsoft.com/SQLServer/feedback/details/468517/update-statistics-at-the-partition-level

SolidQ Global S.L.



Spain

solidq.com

+34.914.148.950 office

+34.966.007.008 fax

[email protected]

SQL Server In-Memory OLTP Engine

Introduccin - es un nuevo motor relacional optimizado para datos residentes en

memoria y cargas de trabajo tipo OLTP. Tngase presente que este motor surge como la

motivacin en Microsoft, de llegar a aumentar 100 veces el rendimiento de SQL Server en

entornos puramente OLTP.

El motor en memoria incluido en SQL Server 2014, al contrario de lo que ofrecen otros

fabricantes se encuentra completamente integrado en el producto y por tanto no se trata de

un sistema distinto. Se trata de un motor relacional completamente transaccional y

persistente, que se utiliza exactamente igual que el motor on-disk (mediante T-SQL) y que a

simple vista, se encuentra completamente integrado en el producto, posibilitando una

aparente transparencia a la hora de utilizarlo.

Arquitectura a Alto Nivel y Comunicacin entre los Engines Aunque se trata de un motor completa y radicalmente diferente al actual, ambos motores se

comunican perfectamente y se permite por ejemplo realizar consultas que toquen objetos

(tablas por ejemplo) de tipo on-disk e in-memory de forma transparente.

Diagrama de comunicacin entre el motor in- -

y SQL Components (aka on-disk)

La Motivacin Las razones de la aparicin de este nuevo motor es que como parte de un proyecto interno de

conseguir un aumento de rendimiento de 100X en SQL Server se realizaron investigaciones

previas para determinar la mejor forma de hacerlo. Durante esa investigacin, el equipo de

Microsoft Research lleg a la conclusin de que la nica forma de optimizar el rendimiento

actual de SQL Server era por estas 3 vias:

1. Reducir el nmero de ciclos por instruccin

2. Mejorar la escalabilidad (uso de recursos HW mejor aprovechados)
mailto:[email protected]://blogs.solidq.com/ElRinconDelDBA/Lists/Posts/Attachments/259/image_4_40EFED75.png

SolidQ Global S.L.



Spain

solidq.com

+34.914.148.950 office

+34.966.007.008 fax

[email protected]

3. Disminuir el nmero de instrucciones ejecutadas por peticion

De dicho anlisis, extrajeron la conclusin de que con los puntos 1 y 2, en el mejor de los casos

se podran llegar a obtener 3-4x rendimiento OLTP y que el punto 3 podia llegar a conseguir un

de ser complejo hasta cotas insospechadas, dista mucho del ratio de 100X de mejora de

rendimiento buscado.

Los principios de diseo con los que se ha creado el nuevo motor In-memory son los

siguientes:

1. Optimizacin de estructuras en memoria RAM

2. Eliminar latches y bloqueos

3. Compilacin de cdigo nativo maquina

Optimizacin de Estructuras en Memoria RAM Los motores relacionales actuales basan su diseo en asumir que los datos se encuentran en

disco y se crean estructuras en memoria para mapearlos y que su acceso sea mucho ms

ptimo. En SQL Server conocemos a dicha rea como buffer pool, y es donde residen las

pginas con las que bsicamente opera el motor relacional on-

por qu un motor en memoria? Parecera que esto no tiene

sentido hasta que entramos al detalle de realmente el funcionamiento de dicho motor on-disk.

Al asumir estructuras de disco trasladadas a RAM, se incrementa obviamente la complejidad

de los mecanismos de control de los datos. Hay que pensar que cuando se disearon la

mayora de motores relacionales se careca principalmente de memoria RAM y se crearon

estructuras como los buffer pool para optimizar utilizando el poco tamao RAM de que s

disponia. Pero esto ha cambiado y nos encontramos en una poca donde no es difcil comprar

hardware con 1Tb de memoria RAM, donde la mayora de bases de datos OLTP caben de

sobra.

En estos escenarios se pone el foco con el motor in-memory, escenarios donde la RAM ya no

es el problema sino la propia arquitectura on-disk del motor relacional tradicional. No quiero

entrar en detalles internos porque llevara mucho tiempo, pero bsicamente tenemos que

tener en cuenta que tradicionalmente en un entorno con alta concurrencia, el acceso a una

pgina de datos en RAM debe ser protegida por ejemplo de otros hilos mientras es accedida

(concepto de latch). En entornos con alto nmero de CPUs e hilos accediendo

simultneamente a las mismas estructuras, resulta que el mismo proceso de proteccin de

datos se convierte en cuello de botella del sistema (recordemos que se busca un aumento de

rendimiento del 100x)

Teniendo esto presente, el motor in-memory OLTP que aparece en SQL Server 2014 posee

estructuras pensadas para trabajar directamente en RAM, que no tienen equivalencia directa

con nada de lo que se ha visto hasta ahora. Estas estructuras se conocen como in-memory

indexes y tenemos de dos tipos:

HASH

Bw-tree

NOTA: La durabilidad de los datos se mantiene mediante logging y checkpoints a una

estructura parecida al log de transacciones pero altamente optimizado. Esto queda de

momento fuera de este artculo

SolidQ Global S.L.



Spain

solidq.com

+34.914.148.950 office

+34.966.007.008 fax

[email protected]

Eliminar Latches y Bloqueos Dado que hoy por hoy no es dificil tener decenas o cientos de CPU en mquinas de

procesamiento OLTP, el problema que salpica como comentamos es que en escenarios de una

altsima concurrencia, los propios locks a estructuras de datos de memoria compartida son un

cuello de botella importante. Construir un motor OLTP pensando en escalabilidad mxima pasa

-memory

OLTP. En este motor, conceptos como el memory allocator, los ndices hash o Bw-tree o los

transaction map no se rigen por locks y latches.

El motor in-memory OLTP est basado en:

1. Control de concurrencia optimista

2. Multiversion de filas

3. Estructuras latch-free

NOTA: En este motor no existen muchos de los conceptos a los que estamos acostumbrados:

latch, lock, fill-

Compilacin de Cdigo Nativo Mquina Hasta ahora, tal como lo conocemos, un motor OLTP suele basar sus planes de ejecucin en un

intrprete que evala en tiempo de ejecucin el algoritmo seleccionado de acceso a datos.

Este tipo de intrpretes poseen una ventaja muy alta debido a su gran flexibilidad pero tienen

un problema cuando tenemos puesta la vista en conseguir 100X rendimiento: El nmero de

instrucciones a ejecutar para el acceso ms liviano (un simple lookup) tiene ms de 1000

instrucciones, con lo que el nmero de instrucciones a ejecutar es realmente elevado.

Al principio del artculo mencionbamos que una de las formas de optimizar el rendimiento era

precisamente eliminar el 99% de las instrucciones a ejecutar. Precisamente este motor posee

su propio sistema de compilacin nativo de instrucciones, para reducir al mximo el n de ellas

y que el rendimiento sea espectacular. El siguiente diagrama (publicado en el paper de

Microsoft Research) nos muestra cmo ha cambiado el proceso de generacin de cdigo para

SP en in-memory OLTP

SolidQ Global S.L.



Spain

solidq.com

+34.914.148.950 office

+34.966.007.008 fax

[email protected]

Lo que se ilustra a grandes rasgos es que una vez mandado a compilar un SP de tipo in-

memory, este acaba siendo compilado a cdigo C en forma de .dll y cargado al SQLOS. Por

tanto, lo que a priori era una representacin de plan de ejecucin, ahora contiene el propio

algoritmo de acceso a los datos de forma esttica.

Lo siguiente es un ejemplo cualquiera de un SP compilado in-memory:

CREATE PROCEDURE [dbo] . test_insert WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER AS BEGIN ATOMIC WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'us_english' ) INSERT INTO dbo. test ( id ) SELECT TOP 1 id +1 FROM dbo. test ORDER by id DESC END

Componentes Clave In-memory OLTP engine posee (al menos en esta CTP2 de SQL Server 2014) los siguientes

componentes:

Metadata: Sus objetos son compatibles con el motor on-disk e incluyen las mismas
mailto:[email protected]://blogs.solidq.com/ElRinconDelDBA/Lists/Posts/Attachments/259/image_2_40EFED75.png

SolidQ Global S.L.



Spain

solidq.com

+34.914.148.950 office

+34.966.007.008 fax

[email protected]

objetos on-disk en las restricciones que poseen y las clusulas para indicar que deben

ser creadas en el motor in-memory

Query optimization: Si se disea el cdigo para utilizar nicamente objetos in-

memory, el compilador realiza la mxima optimizacin posible y de puede por tanto

obtener el mximo rendimiento de este nuevo motor

Query interop: Dado que nada nos limita a realizar consultas T-SQL que toque objetos

del motor on-disk e in-memory, el motor query interop se encarga de utilizar el mismo

query plan interpretado, pero para acceso a tablas in-memory. De esta forma se

pueden acceder a dichas estructuras aunque no se obtenga todo el potencial del

motor

Transacciones: Se permiten transacciones entre ambos motores

Alta disponibilidad: El motor in-memory OLTP est completamente integrado en

AlwaysOn, por lo que podemos tener una solucin AlwaysOn que haga uso de tablas

en memoria

Almacenamiento y log: Para mantener la persistencia de los datos en memoria, el

motor in-memory OLTP utiliza un sistema de log de transacciones de forma especial.

En lugar de escribirlo todo en el log como en un sistema transaccional normal, se

utiliza un sistema de checkpoints bajo un grupo de ficheros especial (filestream por

ahora) y nicamente se realiza flush al log de transaccciones en el momento de

rendimiento posible.

Comparacin de Rendimiento Las siguientes tablas de rendimiento las he extrado del propio paper de Microsoft Research

n versin RTM, podemos hacernos una idea de lo que

podremos conseguir con este nuevo motor OLTP. Sin duda, estamos de enhorabuena en lo que

a mejoras OLTP se avecina en esta futura release de SQL Server 2014.

SolidQ Global S.L.



Spain

solidq.com

+34.914.148.950 office

+34.966.007.008 fax

[email protected]

Bibliografa y Documentacin de Referencia

Documento de Microsoft Research sobre Hekaton

Keynote del Dr Dewitt en PASS2013
mailto:[email protected]://research.microsoft.com/pubs/193594/Hekaton%20-%20Sigmod2013%20final.pdfhttps://www.youtube.com/watch?v=aW3-0G-SEj0&list=WL6AAA223C63D42F15http://blogs.solidq.com/ElRinconDelDBA/Lists/Posts/Attachments/259/image_6_40EFED75.pnghttp://blogs.solidq.com/ElRinconDelDBA/Lists/Posts/Attachments/259/image_8_40EFED75.png

SolidQ Global S.L.



Spain

solidq.com

+34.914.148.950 office

+34.966.007.008 fax

[email protected]

Tablas Temporales vs Tablas in-memory

Introduccin Con la llegada de SQL Server 2014 y las tablas in-memory cabe preguntarse si podrn dichas

tablas sustituir escenarios tpicos donde se utilizan actualmente tablas temporales. En nuestra

experiencia en consultora vemos habitualmente como las tablas temporales se utilizan como

contenedores de resultados intermedios dentro de procesos ms complejos. En estos casos se

suele o bien crear la tabla temporal de forma explcita con un CREATE TABLE o bien se crea al

vuelo mediante el uso de la estructura SELECT INTO.

Escenario de Prueba Con las nuevas tablas in-memory tenemos la posibilidad de definir tablas cuya persistencia

sea nicamente de esquema. Es decir, la informacin que grabemos en ellas nunca se

materializar en disco, trabajarn nicamente en memoria. Este tipo de tablas considerarlas

como una alternativa ms a las tablas temporales tradicionales y usarlas en escenarios en los

que la no persistencia de los datos en caso de cada del servicio no es un problema.

Para complementar el abanico de posibilidades que tenemos, aadiremos tambin otro

escenario que consiste en almacenar tempdb en memoria voltil. Este sera el caso al utilizar

un disco RAM o ciertos aceleradores basados en memoria RAM/flash conectados al bus PCI-E.

En definitiva lo que buscamos es minimizar el impacto que tendra un sistema lento de

entrada/salida sobre el rendimiento de tempdb. De esta forma podremos evaluar si tempdb,

por diseo, nos limita considerablemente el rendimiento o es el sistema de entrada/salida el

mayor limitante.

Durante las pruebas que hemos realizado hemos tenido en cuenta otros factores como por

ejemplo el que la tabla deba crearse y destruirse cada vez o no o que los datos sean aleatorios

o no. El objetivo es concluir si determinados patrones se ajustan o no a cada uno de los

mtodos que hemos comentado y si existe, en el caso de las tablas in-memory, de un impacto

sustancial por colisiones de hash.

Pruebas y Anlisis Comenzamos analizando el impacto que tiene la creacin y destruccin del objeto temporal.

El siguiente grfico nos muestra como el coste en el caso de la tabla in-memory es muy

superior al coste de crear una tabla temporal tradicional.
mailto:[email protected]://blogs.solidq.com/elrincondeldba/Lists/Posts/Attachments/260/image_2_3B8384DA.png

sql server 2014

Documents

contenido introduccin

escenarios de pruebas

containers de windows

solidq global

pruebas de impacto

equipo de solidq relacional

juan carlos i

albatera alicante spain