Eladio Rincón y SQL Server

Estado actual (18 de abril 2012) de los Appliances de Data Warehouse en Plataforma Microsoft

Introducción

Hace algo más de un mes que salió SQL Server 2012 con funcionalidades muy interesantes para el mundo de los data warehouses. La funcionalidad a la que más provecho se le puede sacar de SQL Server 2012 es la compresión – almacenamiento – de tipo columnar;  si desea ver un video sobre dicha característica, Enrique Puig lo detalló en las 12 horas de SQL Server 2012 (en castellano). También, si desea más profundidad sobre el tema, podría asistir a la siguiente sesión del SolidQ Summit 2012: Índices Columnares en SQL Server 2012;

Sin embargo, los fabricantes todavía no tienen actualizados sus appliances a SQL Server 2012 por lo que dicha funcionalidad no se puede utilizar. DELL parece que está preparando una novedad importante en su generación 12 de PowerEdge, donde parece que además de utilizar Índices columnares, utilizarán almacenamiento tipo Flash via PCI – por fín. Por parte de HP, la generación de servidores G6 es la que actualmente se comercializa con los los appliances, pero están trabajando muy duro para actualizar todos sus appliances a G7, que también promete. Ayer jueves 17, en un evento de HP-España comentaron que se está preparando un G7 con almacenamiento Flash (no especificaron si discos SSD o tarjetas PCI) para el Business Data Warehouse appliance con SQL Server 2012.

Todos estos movimientos no son gratuitos; Oracle lleva ya un tiempo con Exadata utilizando flash PCI en sus servidores Sun, y a diferencia de los appliances de SQL Server – parece – que tiene mecanismos nativos de Oracle para decidir qué información cachear en la PCI y cual no. Desde luego es una ventaja competitiva importante, porque puede aplicar inteligencia al uso de PCI. El contrapunto es el coste de la solución que resulta poco competitiva desde el punto de vista de precios.

La diferencia entre Exadata y lo que parece va a liberar HP con almacenamiento Flash es que HP ofrecería todo el storage en almacenamiento Flash, mientras que Exadata sólo ofrece un porcentaje de todo el almacenamiento; desde el pto. de vista de arquitectura, en Exadata la memoria Flash actúa como caché-intermedia entre los discos y el SGBD, que resulta diferente a la propuesta de HP-G7-Flash donde todo el data estará almacenado en Flash.

¿Cuales son los appliances que existen ahora mismo en el mercado?

Vivimos unos días en los que la explosión de los appliance está viviendo su momentum, y si a nosotros – orientados a tecnologías específicas – nos cuesta seguir la pista, prefiero no imaginar el lío que deben tener en sus cabezas los CIO o CTO que deben tener una perspectiva muy a alto nivel.

Para explicar los appliances existentes, me centraré en HP porque es el fabricante con los el que estoy más familiarizados; creo que HP es quién más está apostando por los appliances de SQL Server, aunque ahora está “empujando” DELL también en la misma línea.

HP tiene 3 líneas diferenciadas de appliances para gestión de datos y 1 arquitectura de referencia que en realidad no es appliance:

Puede continuar con el resto de la publicación aquí:

http://blogs.solidq.com/ElRinconDelDBA/Post.aspx?ID=221&title=Estado+actual+(18+de+abril+2012)+de+los+Appliances+de+Data+Warehouse+en+Plataforma+Microsoft

Conclusión

Cada appliance está construido para escenarios concretos:

• HP Business Decision Appliance: Datawarehouse chiquititos con SQL Server y SharePoint en el mismo servidor.
• HP Business Datawarehouse Appliance: Data Marts Departamentales.
• Arquitectura de Referencia (RA) Fast Track Data Warehouse: Realmente no es un appliance.
• HP Enterprise Datawarehouse Appliance: para sistemas realmente grandes (hasta 500TB).

Actualmente todos los Appliances están diseñados y optimizados para SQL Server 2008R2; los fabricantes se están esforzando en actualizarlos a 2012 en los próximos meses.

Gratis parte del servicio SolidQ Health Check: agregación de trazas de SQL Server Profiler

En el equipo relacional de SolidQ España hemos desarrollado un sistema de agregación de consultas propio que queremos demostrar que es superior al de la competencia, sea gratuita o de pago. Para demostrarlo, hemos puesto a disposición de todo el mundo, el sistema de agregación de consultas vía web, para que subas tus trazas de SQL Server Profiler y veas por ti mismo el nivel de detalle que podrás obtener en análisis de patrones de consultas gracias a SolidQ. ¿Para qué sirve el servicio de agregación de consultas? Este servicio cubre uno de los escenarios más importantes en la optimización de un sistema SQL Server: La detección de patrones T-SQL ineficientes. Este servicio se encarga de analizar trazas de SQL Profiler en busca de aquellos patrones de consulta que están penalizando el servidor. Cuando hablamos de patrones de consulta, no hablamos de una única consulta puntual, sino de ver cuantas veces una misma consulta (con diferentes parámetros, se entiende) ha sido lanzada y cual es el coste total del mismo para el servidor. Pero nosotros vamos mas allá y analizamos patrones con ejecuciones sp_executesql, sp_execute,…e incluso cursores. Para información mas detallada, acceda al siguiente link. Si te interesa y quuieres utilizarlo, accede aquí: http://www.solidq.com/ib-es/servicios/sqlserver-relacional/Pages/Como-Utilizar-Gratis-la-Agregacion-de-Trazas-de-SolidQ-Health-Check-para-SQL-Server-en-tres-pasos.aspx Sigue la cuenta de twitter @solidqes, donde se publicarán regularmente cupones gratuitos.

Sesiones de SQL Server enviadas a CodeMotion.es

Si te interesa SQL Server y vas a asistir al evento CodeMotionES (http://codemotion.es/info), ya está abierto el periodo de evaluación de sesiones.

He enviado las siguientes propuestas de sesiones, que a mi entender, deberían ser imprescindibles para cualquier desarrollador de aplicaciones.

Introducción a SQL Server para desarrolladores

Existe la regla no escrita de que por un lado están las aplicaciones y por otro lado el servidor de bases de datos. En esta sesión entenderá conceptos básicos que debe tener en cuenta un desarrollador de bases de datos para programar aplicaciones eficientes contra SQL Server; al finalizar la sesión, podrá explicar con naturalidad conceptos como caché de planes de ejecución, estadísticas, y transacciones, aspectos que son fundamentales para comprender cómo funcionan los SGBD; por cierto, el 80% de los conceptos son muy parecidos en todos los SGBD transaccionales.

Índices y cómo se almacena la información en SQL Server para desarrolladores

Un problema habitual en la eficiencia de las aplicaciones que acceden a bases de datos es la falta de índices; el desarrollador dice que los índices los deben crear los administradores y los administradores dicen que es una tarea del desarrollador de bases de datos; al final: la casa sin barrer; ¿y para qué necesita los índices? Al fin y al cabo, como desarrollador necesita información almacenada, y la estructura física de cómo se almacena debería resultarle irrelevante. Antes de asistir a la sesión haga el siguiente ejercicio: coja las Páginas Blancas, y busque en la ciudad de Torrevieja todos los ciudadanos cuyo primer apellido sea Rincón; sin saberlo, habrá utilizado los índices Población y Apellidos. En definitiva, inconscientemente, habrá utilizado el método más rápido para acceder a la información; SQL Server funciona de forma similar, si le proporciona los accesos adecuados. En esta sesión, además, le explicaremos las reglas más habituales a la hora de indexar su base de datos con las que podrá cubrir la mayoría de las necesidades de sus aplicaciones.

Planes de ejecución en SQL Server para desarrolladores

Un plan de ejecución muestra cómo accede SQL Server a los datos para devolver la información que solicitan las consultas; el plan de ejecución tiene operadores, en el que cada uno de ellos utiliza diferentes tipos de recursos como memoria, objetos intermedios, diccionarios, etc. Analizar un plan de ejecución tiene su miga; sobre todo cuando el plan de ejecución tiene 15 página de largo y el número de flechas con flujos de datos supera las varias decenas. En esta sesión conocerá algunos de los operadores imprescindible para interpretar que está haciendo SQL Server a la hora de procesar las consultas; considere que cuanto antes se procese la consulta en el servidor, antes podrá servir los datos a la aplicación cliente, por lo que estará tratando directamente con aspectos que afecta al rendimiento de su aplicación; verá cómo fluyen las filas entre los distintos componentes, y entenderá que el tamaño y la ubicación de las flechas y operadores sí que importan. Entenderá y podrá explicar la diferencia entre un nested loop y un merge join, y como se parecen a aspectos que ya conoces en el mundo del desarrollo como bucles for, recorridos ordenados de listas, y diccionarios de datos.

¿las ves interesantes y te gustaría verlas? si es así, pre-registrate en el evento (http://codemotion.es/register) y vota por ellas

Cambios en el modelo de licenciamiento de SQL Server 2012

La comunidad anda un poco revuelta – supongo que será la necesidad de tener la información de forma inmediata.

Microsoft ha anunciado dos cambios:

• licenciamiento por cores en lugar de procesadores
• Reducción del número de versiones disponibles; aperece una versión nueva llamada SQL Server 2012 Business Inteligence Edition que tiene todas las funcionalidades de BI (Semantics, DQ, etc.); la versión Standard tendrá funcionalidades BI limitadas. La versión Enterprise será como hasta ahora: contiene todas las funcionalidades empresariales como AlwaysOn, todo BI, column-store, etc.

Además, desaparecen las versiones Workgroup y Datacenter. Respecto a la versión Web Edition, su licenciamiento se realizará a través de hosters.

El documento completo lo tienes aquí.

En la home de SQL Server tienes más información. Verás información como esta donde se detalla que la versión standard sólo podría tener 16 cores. también verás esto, donde se comentan factores de multiplicación en los cores.

Si necesitas datos exactos de los cambios, mira la home de SQL Server, si necesitas más información contacta con tus representantes locales de Microsoft – considera que se acaba de anunciar el modelo de licenciamiento para un producto que comprarás mínimo dentro de un cuatrimestre.

Yo veo bien el cambio, fundamentalmente porque el modelo actual basado en procesadores físicos es de hace más de 10 años, donde no se podía tener en cuenta que la capacidad de procesamiento de los servidores iba a explotar por la via de multi-cores.

Como siempre, ahora Oracle e IBM tirarán “flores” a los cambios, pero saben bien que siguen siendo bastante más caros

Conectar a SQL Server desde Linux

Por fin podrá ser en SQL Server 2012; es probable que la comunidad no le ha dado la importancia que tiene. Además de los cambios relacionados con la vida “útil” de los drivers OLEDB de SQL Server, estrategicamente hablando este es el movimiento de Microsoft más pro-interoperabilidad que he visto nunca en lo que se refiere a SQL Server.

¿Por qué es así? la comunidad siempre se ha visto imposible – desde el pto de vista de negocio, no por imposibilidad técnica – implementar mecanismos de acceso a SQL Server desde plataformas no-Microsoft. Aquí entra en juego la vieja historia de que Microsoft es una empresa que vende productos, donde uno de sus mejores productos (desde el punto de vista de ventas) es el sistema operativo. La gente decía: “Imposible que Microsoft permita conectarse a su servidor de bases de datos por dos razones: 1) no venden licencias de OS, y 2) SQL Server es un producto poco importante dentro de Microsoft.”

Bueno, pues para lo que pensaban así, aquí está el movimiento reciente de Microsoft al respecto anunciado en el último PASS en Seattle:

“In this morning’s keynote address at the Professional Association of SQL Server (PASS) Summit 2011, Quentin Clark (Corporate Vice President at Microsoft) announced that a preview of the Microsoft SQL Server ODBC Driver for Linux will soon be widely available to customers. Yes, you read that right. Microsoft plans to release a driver that provides 1st class access to SQL Server from Linux/Unix operating systems. Recently, Microsoft has been working closely with a handful of partners to get feedback on private releases of the driver. A publicly available preview release of the driver is planned for later this fall.”

TOMA YA!!!!! Me alegra mucho ver que por fin Microsoft está haciendo esfuerzos muy grandes en la linea de interoperabilidad hacia SQL Server

Parece que de momento no tendrá soporte para Database Mirroring y los Always On Availability Groups, pero seguro que tardarán poco en sacarlo; más información del equipo de producto la tienes aquí:

http://blogs.msdn.com/b/brian_swan/archive/2011/10/13/microsoft-announces-sql-server-odbc-driver-for-linux.aspx

Por cierto, en aras de la interoperabilidad, el otro entorno al que siempre se ha dicho que Microsoft estaba muy cerrado era a la intercionexión con Java; aquí tenéis información al respecto: la versión 4.0 del driver para java está aquí: en la versión 4.0 tienes soporte para todos los temas AlwaysOn y Autenticación Kerberos.

Recursos y How-Tos a Troubleshooting en Replicación Transaccional

Siguiendo con los temas de replicación, aquí tienen unos enlaces que espero les sean de utilidad:

• Introducción a Troubleshooting en Replicación Transaccional, por Rubén Garrigós.
• Troubleshooting Transactional Replication Parte 1.
• Troubleshooting Transactional Replication Parte 2.
• Troubleshooting Transactional Replication Parte 3.
• Troubleshooting Transasctional Replication. Un poco viejo (del año 2002), pero las guias son aplicables.

finalmente, en cuanto a literatura las “dos biblias” de replicación:

• Replicación en SQL Server 2005.
• Replicación en SQL Server 2008.

Sort Warning in SQL Server: Complicaciones derivadas del Sort Operator

Introducción

Cada día hay más literatura sobre los problemas derivados del uso del Sort Operator en SQL Server ineficientemente, y a pesar de ello, sigue siendo habitual encontrar el problema en clientes.

En esta publicación, podrá reproducción uno de los problemas derivados del Sort Operator, y tendrá claro cómo diagnosticar el problema asociado.

http://dilbert.com/mashups/comic/130971/

El problema

SQL Server tiene decenas de operadores; uno de ellos es el operador Sort, que ordena un conjunto de filas. La ordenación de las filas puede suceder por varias razones:

• El usuario especificó la claúsula ORDER BY en su consulta
• SQL Server necesita ordenar las filas para usar el conjunto en el siguiente operador (Stream Aggregate, o Merge Join)
• SQL Server necesita ordenar varias filas para insertar en una tabla – ordenado en función de la(s) columna(s) en el índice clustered.
• otros casos adicionales

Para ordenar ese conjunto de filas, SQL Server utiliza memoria para ello, y esa memoria, la reserva antes de comenzar a procesar la consulta. Imaginese que SQL Server estima que en la ordenación estarán involucradas 300 filas; el Engine reservará memoria para ordenar esas filas, y comenzará a procesar la consulta; cuando llegue a la parte de la consulta en la que necesita ordenar las filas, si el número de filas es diferente al número de filas estimado puede resultar problemático:

• Si estimó filas de más de las que están entrando, SQL Server habrá reservado memoria de más par ese operador, lo cual no es negativo para esa consulta, pero es potencialmente peligroso porque se estará usando memoria ineficientemente.
• Si estimó filas de menos de las que están entrando, SQL Server no tendrá espacio suficiente en memoria para ordenar todas las filas, por lo que deberá apoyarse en otra estructura alternativa para hacer la ordenación: esa estructura alternativa es Tempdb.

Puede continuar leyendo el artículo aquí:

http://blogs.solidq.com/ElRinconDelDBA/Post.aspx?ID=188&title=Sort+Warning+in+SQL+Server%3a+Complicaciones+derivadas+del+Sort+Operator

Paso a paso (step by step) Transactional Replication in SQL Server

Tengo un amigo que dice que un sistema comienza a ser serio cuanto tiene algún modelo de Replicación en SQL Server entre medias; no comparto ese comentario al 100% porque la tipología de arquitecturas de sistemas SQL Server es muy variada y no pretendo desmerecer ningún sistema SQL Server sin Replicación de Datos, pero si en un 90% ; con la explosión de datos que agraciadamente estamos sufriendo, comienza a ser habitual tener sistemas mixtos OLTP-BI; muchos de estos sistemas – principalmente por necesidades de rendimiento y disponibilidad – necesitan que estén aislados unos de otros, aunque la necesidad de tener el dato “casi al instante” sigue ahí:

• Por un lado, te encuentras con necesidades de atender peticiones del tipo OLTP que requieren un sistema muy bien normalizado, sobre indexado en ocasiones, y muy muy controlado en cuanto a la complejidad de las consultas donde el índice de peticiones concurrentes es muy alto, y
• por otro lado, te encuentras con un sistema analítico (BI) que requiere muchas veces informacion de-normalizada, información muy poco fragmentada (recordar los post sobre Fras Track Datawarehouse de SQL Server), y que recibirá consultas muy pesadas, pero con consultas poco concurrentes.

Como están intuyendo, son estamos hablando de diseño de datos muchas veces que tiene poco en común, y fundamentalmente, naturaleza de procesamiento completamente diferente: aquí es donde entra en juego la Replicación de datos.

La idea fundamental de Transastional Replication en SQL Server es dado un origen con datos, hacer posible que esos datos estén disponibles en otro destino lo más rápido posible; por lo más rápido posible queremos decir que no será información disponible en modelo two-phase-commit (que sería una locura por la lentitud que implicaría), pero si en cuestión de segundos; una vez los datos en el destino – normalmente un servidor diferente – el servidor de destino puede hacer sus procesamientos de información (ETL) para preparar la información para ser consumida por los sistemas analíticos.

Para seguir una guia paso a paso de Transactional Replication, le recomiendo el siguiente enlace:

http://blogs.solidq.com/ElRinconDelDBA/Post.aspx?ID=187&title=Replicaci%c3%b3n+transaccional+en+SQL+Server%3a+Una+historia+de+publicadores%2c+suscriptores+y+agentes

IN SQL Server – la cláusula: Otra curiosidad quizás no tan conocida

Como vimos hace poco en ElRinconDelDBA, es bueno utilizar la palabra clave IN para hacer pensar a los alumnos sobre el funcionamiento de SQL Server. No vamos a repetir el post anterior, porque obviamente ahí está para que pueda leerlo. Volvamos a la situación de la clase: usted está impartiendo un seminario y se marca como objetivo al inicio del seminario, sembrar las dudas en la audiencia para dos cosas: 1) que dejen de asumir cuestiones hasta antes de la clase irrefutables, y 2) que intenten aproximarse a cómo funciona SQL Server.

Lo que vamos a ver

Como sabe, al analizar el comportamiento de sus consultas, deberá guiarse de componentes comocidos como:

• Microsoft SQL Server Profiler
• Microsoft SQL Server Management Studio para 1) analizar planes de ejecución, 2) analizar estadisticas de Entrada/Salida (SET STATISTICS IO ON)
• Microsoft SQL Server Tuning Advisor

En la publicación anterior, vimos como un mismo plan de ejecución (aparentemente), para consultas diferentes implicaba un coste muy diferente a nivel de Entrada/Salida, pero que “aparentemente” no se veía reflejado en el plan de ejecución y las estimaciones mostradas en SQL Server Management Studio. Ahora, haremos lo contrario, veremos una consulta “tipo” para la que se calculan diferentes planes de ejecución y sin embargo a nivel de Entrada/Salida el coste es prorcional.

Puede continuar leyendo el artículo en la siguiente URL:

http://blogs.solidq.com/ElRinconDelDBA/Post.aspx?ID=185&title=IN+SQL+Server+%e2%80%93+la+cl%c3%a1usula%3a+Otra+curiosidad+quiz%c3%a1s+no+tan+conocida

Conclusión

Como comentamos en la publicación anterior, la figura del Desarrollador de Bases de Datos (Database Developer) es una figura a tratar en Mayúsculas, y como diría Tom Peters, es imprescidible para la EXCELENCIA de tu software. Cualquier amago de evitar dicha figura en su organización tarde o temprano deberá reemplazarla por alguien, o como en muchos casos, será una figura “asumida” por miembros de la organización.

Alguna curiosidad sobre la palabra clave (keyword) IN en SQL Server

Introducción

En los seminarios de SQL Server que impartimos periodicamente, ayudamos a entender a los alumnos cómo SQL Server utiliza los planes de ejecución; una parte fundamental es que el alumno comprenda la representación lógica de la consulta que está pidiendo a SQL Server; una vez entendido el procesamiento lógico de la consulta, mostramos cómo traduce SQL Server esa representación lógica en acceso (físico/logico) para obtener los datos que se necesitan para resolver la consulta. Para romper el hielo con los alumnos, solemos empezar con un ejemplo como el siguiente:

Dada la siguiente tabla llamada predicados:

use Adventureworks 
go
drop table predicados;
create table predicados
(id int identity, d date, s char(10)
constraint predicados_pk primary key (id))

Con más del 100.000 filas:

insert predicados 
output inserted.*
values
  ('20081010', 'aaa'), ('20081011', 'abb')
, ('20081012', 'acc') ,('20081013', 'add')
, ('20081012', 'baa') ,('20081013', 'bdd')
, ('20081012', 'bcc') ,('20081013', 'bdd')
go

insert predicados
output inserted.*
select top 100000 p.d, p.s
from predicados p
cross join master.dbo.spt_values c

Para continuar leyendo el artículo le recomiendo visite la siguiente URL:

http://blogs.solidq.com/ElRinconDelDBA/Post.aspx?ID=184&title=Alguna+curiosidad+sobre+la+palabra+clave+(keyword)+IN+en+SQL+Server

Conclusión

Es fundamental que como desarrollador de bases de datos, conozca el funcionamiento interno de SQL Server para que las consultas que usted codifica sean eficientes en SQL Server. Lamentablemente, la figura de Desarrollador de Bases de Datos (DBD) es una figura en extinción en estos días en los que se tiende a entender la base de datos como un simple repositorio de información. Mientras las empresas no adquieran conciencia de la importancia de la base de datos, los desarrollos serán pseudo-eficientes y además de los problemas habituales que suelen encontrarse en los desarrollos, se encontrarán con problemas de eficiciencia de acceso a los datos.

Generalmente, con aumentar la escalabilidad del servidor suele resultar: incrementar los recursos asignados a la máquina (disco, memoria, CPU), pero hay otros muchos casos en los que las posibilidades de escalabilidad llegaron a sus límites y se necesitan aplicar soluciones alternativas.

Así que desde aquí sirva este artículo como llamada a los decisores en las empresas a que cuiden un poco sus arquitecturas y le den mucha importancia a la base de datos: fíjese que sin base de datos es imposible que funcione su aplicación, así que considerela como un recurso crítico!

Notas sobre sequential reads (lecturas secuenciales), fragmentación y MCR en Fast Track Datawarehouse de SQL Server

En estos casos, una imagen es mejor que 1000 palabras:

• La línea azul representa el tamaño de cada lectura realizada; se mantiene constante sobre 500Kb, lo cual es bueno; en publicaciones anteriores podía ver la diferencia con un sistema fragmentado.
• La línea roja representa el tamaño total de las lectura que se están realizado: entre 1.5GB/sec y 2GB/sec; este valor dependerá del número de bandejas (enclosures) que tenga y del número de discos al que se esté accediendo (cómo de distribuida esté la información). Este valor siempre estará por debajo de la pruebas sintéticas realizadas con SQLIO.

Cuando revise la fragmentación de sus objetos, intente que el sistema tenga un alto índice de páginas en cada fragmento: columna avg_fragment_size_in_pages de la DMV sys.dm_db_index_physical_stats; como ejemplo, le dejo esta consulta que puede servir para sus análisis:

select 
    t.name, si.name, dpages * 8. / (1024*1024) GB 
    , si.rows, v.avg_fragment_size_in_pages 
from sys.dm_db_index_physical_stats (DB_ID(),
null,null,null, 'sampled') v
join sys.sysindexes si
  on v.object_id = si.id
  and v.index_id = si.indid 
join sys.tables t
  on si.id = t.object_id 
order by 3 desc

En la configuración en la que estamos preparando pruebas de laboratorio, obtenemos los siguientes resultados:

Las tablas “importantes” – tablas de hechos – se encuentran con valores por encima de 1000; el valor de referencia suele ser por encima de 400.

Una vez hechas dichas validaciones, es cuando debe proceder a validar los valores de MCR; en dicho caso, debe elegir una consulta “tipo”, FTDW suele utilizar la siguiente como referencia:

SELECT SUM(l_extendedprice*l_discount) AS revenue  
FROM staging.lineitem
WHERE l_discount BETWEEN 0.04 - 0.01 AND 0.04 + 0.01 
AND l_quantity < 25
OPTION (maxdop 12); -- 4, 8, 12, 24

y validar cual es el ratio de MB/sec que devuelve la máquina; para hacer ese cálculo, use la siguiente fórmula (que devolverá el ratio en MB/sec):

select 
-- ( PAGES * 8. / 1024 ) / (DURATION-MS / 1000 ) 
( 27665973 * 8. / 1024 ) / (821892 / 1000 ) 

En nuestro caso, hemos rellenado dos tablas (stagins y “real”) con los mismos datos, y ejecutando el siguiente código:

set statistics io on
set statistics time on
go
dbcc dropcleanbuffers
go

SELECT SUM(l_extendedprice*l_discount) AS revenue  
FROM staging.lineitem
WHERE l_discount BETWEEN 0.04 - 0.01 AND 0.04 + 0.01 
AND l_quantity < 25
OPTION (maxdop 12); -- 4, 8, 12, 24
go

dbcc dropcleanbuffers
go

SELECT SUM(l_extendedprice*l_discount) AS revenue  
FROM dbo.lineitem
WHERE l_discount BETWEEN 0.04 - 0.01 AND 0.04 + 0.01 
AND l_quantity < 25
OPTION (maxdop 12); -- 4, 8, 12, 24
go

-- los siguientes valores se extraen de la pestaña de “mensajes”:

-- CPU Time, y Logical Reads
select 
-- ( PAGES * 8. / 1024 ) / (DURATION-MS / 1000 ) 
( 27665973 * 8. / 1024 ) / (821892 / 1000 ) 


select 
-- ( PAGES * 8. / 1024 ) / (DURATION-MS / 1000 ) 
( 27708513 * 8. / 1024 ) / (758696 / 1000 ) 

y hemos obtenido el siguiente resultado:

En la tabla “real” hemos obtenido en una tabla 263MB/sec y en otra 285 MB/sec con grado de paralelismo a 12 -- la máquina tiene 24 cores.

Esta consulta la debe realizar con diferentes grados de paralelismo para identificar la mejor relación “duración consulta” vs. “paralelización de consulta”. Normalmente los mejores valores de MB/sec se obtienen con MAXDOP = 1, pero considere que no tiene sentido tener una máquina de 24 cores, con el grado de paralelismo a 1

Generalmente, la arquitectura de referencia de FTDW (Fast Track 3.0 Reference Guide http://download.microsoft.com/download/B/E/1/BE1AABB3-6ED8-4C3C-AF91-448AB733B1AF/Fast_Track_Configuration_Guide.docx), suele indicar que deben obtenerse valores por encima de 200MB/sec; esos valores variarán mucho en función:

• del tipo de consulta que procese: lo es lo mismo un group by sencillo que un group by que cruza 12 tablas.
• de la capacidad de procesamiento de la máquina: no es lo mismo CPUs a 3GHz, que a 1.6GHz.
• de la fragmentación de los datos: el proceso de carga de datos en el DW es clave en este aspecto.

Además, note que en nuestra consulta hemos calculado el BCR (valor posterior – lo que sería el consumo real de un coche, siguiendo el simil de FTDW), que generalmente no debe ser inferior al 80% del MCR. Con esas tablas no hemos podido calcular el MCR porque la tabla es tan grande que no entra toda en memoria (no es posible cargar de un “tirón” 210GB cuando dispones de 98 GB de RAM). Recuerde que MCR mide capacidad del sistema sin considerar el acceso a disco, por lo que en una primera consulta se cargan los datos en memoria y luego se ejecuta el agregado “patrón”.

Ya para finalizar, la tabla sobre la que se han hecho las consultas tiene 2.664.000.000 de filas, y las 2 consultas de agregado realizadas han tardado 5 minutos; es decir, SQL Server ha leido más de 5.000.000.000 de filas en algo más de 5 minutos. Un buen número verdad?

PD. Si está interesado en el script de base de datos, hemos utilizado el estandard de TPCH (http://www.tpc.org/tpch/default.asp); próximamente publicaremos datos adicionales que les puedan resultar interesantes (sugerencias a erincon@solidq.com).

Tamaño de base de datos: casi 700GB; tenemos para 9TB, pero no hemos podido rellenar más de momento.

Filas por tabla; la importante, tabla de hechos – lineItem 2.600 millones de filas:

Cinco razones por las que debes asistir al SQLU Summit 2011

Como sabéis, en Junio haremos nuestro Summit Anual en Madrid:

• Lista de Sesiones (tres tracks paralelos – Relacional, BI y Sharepoint; Azure estará presente en los tres tracks): http://summit.solidq.com/madrid/2011/Paginas/Sesiones.aspx
• Lista de Ponentes: http://summit.solidq.com/madrid/2011/Paginas/Ponentes.aspx
• Dónde será: http://summit.solidq.com/madrid/2011/Paginas/ComoLlegar.aspx
• Página prinicipal del evento: http://summit.solidq.com/madrid/2011/Paginas/Home.aspx

La gente se pregunta si asistir o no asistir, aquí va “mi lista” de 5 puntos por la debes venir:

1. Más que conceptos teóricos
2. Experiencia Formativa
3. Tendencia y Evolución de los productos
4. Social e interacción con tus peers
5. Relacion con los mejores profesores, educadores, y mentores

El contenido completo lo puedes leer en ElRinconDelDBA - Cinco razones por las que debes asistir al SQLU Summit 2011

Fast Track Datawarehouse SQL Server 3.0 (FTDW 3.0) y la importancia de las lecturas secuenciales sin fragmentación física de archivos

En nuestra empresa ya hemos impartido cerca de de 10-15 veces la formación de Fast Track Datawarehousing para SQL Server 2008 R2. A finales de febrero, se ha revisado la versión 3.0 en el evento annual de TDI en Las Vegas.

Cosas de la vida han hecho que el segundo lugar en el que se impartiera la formación oficial de Fast Track 3.0 fuera en Madrid (el primero fué Seattle), y ahí hemos estado entretenidos esta semana pasada

Si estás interesado en recibir esta formación y tu empresa es partner de Microsoft, te recomiendo contactar con tu representante de Microsoft, para conocer la agenda de las próximas ejecuciones.

Si por el contrario no estas en empresas partners de Microsoft, considera asistir a este evento (SolidQ Summit 2011), donde se impartirá algunas sesiones sobre estos temas. Más información en ibinfo@solidq.com.

Antes de seguir:

• Si no sabes que es Fast Track DW 3.0, vete aquí
• Si quieres conocer la documentación oficial de la versión 3.0 – arquitectura de referencia, aquí
• Si quieres conocer la documentación oficial de la versión 2.0 – arquitectura de referencia, aquí
• Si quieres conocer la propuesta de HP, aquí
• Si quieres conocer la propuesta de IBM, aquí
• Si quieres conocer la propuesta de DELL, aquí

En la formación de esta semana, HP nos ha proporcionado un DL5XXX con 48 cores, 212GB de RAM, y 4 bandejas conectadas a HBAs de doble puerto de 8GBits.

Esta fué la configuración de una de las bandejas:

En la que cada bandeja dispone de 22 discos:

• 5 RAID 10 de 4 discos cada uno, todos ellos con un único volumen de 600GB
  • 4 de ellos dedicados para datos
  • 1 de ellos dedicado para archivo de transacciones
• 2 discos Global Spare

Con todo esto, si ha revisado la documentación de la arquitectura de referencia, uno de las validaciones que se suele hacer, es si el sistema está configurado adecuadamente para soportar el volumen de lecturas secuenciales que necesita un datawarehouse; sistemas como estos tienen principalmente lecturas secuenciales de 512KB, por lo esa es la medida fundamental a realizar. Nota, que está en contraposición con los sistemas OLTP tradicionales que buscan fundamentalmente eficiencia en lecturas aleatorias de tamaño reducido: entre 8-64Kb. En nuestro caso, al tratarse de DW, las pruebas se focalizan en lecturas, lectura, muchas lecturas, y todas secuenciales.

Para ello se utiliza una herramienta como SQLIO; si no la conoces, y necesitas más info, aquí la tienes.

Estaba muy interesado en verificar la eficiencia de los discos mecánicos, al realizar lecturas en distintas zonas del disco, y para ello con la ayuda de SQLIO, he creado archivos en uno de los volumenes en las siguientes áreas para medir su eficiencia:

• 1 archivo de 10GB secuencial sin fragmentación en la zona externa del disco
• 1 archivo de 10GB secuencial sin fragmentación en la zona media del disco
• 1 archivo de 10GB secuencial sin fragmentación en la zona interna del disco
• 1 archivo de 10GB secuencial muy fragmentado físicamente por todo el disco

si te interesa saber cómo lo he hecho, explico un poco cómo ha sido el proceso:

• partiendo de unidad vacía completamente.
• un hilo creando un archivo de 10GB.
• a continuación rellenar hasta llegar a la mitad con archivos de 2.5GB y 200MB alternos. fíjate en la imagen posterior
• cuando llego a la mitad del disco, vuelvo a crear otro archivo de 10GB.
• a continuación rellenar hasta llegar a la mitad con archivos de 2.5GB y 200MB alternos. fíjate en la imagen posterior
• finalmente creo un archivo de 10GB para llenar el disco completamente.
• una vez todo relleno, borro todos los archivos de 200MB para generar especio suficiente
• creo otro archivo de 10GB, que por “narices” tendrá que localizarse en los huecos generados

Una imagen, vale más que mil palabras creo yo:

en la imagen sólo ven en el cuadrado Rojo el primer archivo de 10GB.

fijate los alternos entre archivos de 2.5GB (PERMANxx.dat), y los que se borrarán de 200MB (FILLERxx.dat).

una vez creados los archivos, se renombran adecuadamente los que interesan y se ejecutan las siguientes instrucciones:

sqlio.exe -kR -fsequential -t4 -o40 -b512 -s15 C:\FT\data15\TEST__INICIO.dat >INICIO.TXT
sqlio.exe -kR -fsequential -t4 -o40 -b512 -s15 C:\FT\data15\TEST__MEDIO.dat>MEDIO.TXT
sqlio.exe -kR -fsequential -t4 -o40 -b512 -s15 C:\FT\data15\TEST__FIN.dat>FIN.TXT
sqlio.exe -kR -fsequential -t4 -o40 -b512 -s15 C:\FT\data15\TEST__FRAGMENTADO.dat>FRAGMENTADO.TXT

los argumentos de SQLIO son los siguientes:

• -kR: operaciones de lectura
• -fsequential: operaciones secuenciales
• -t4: numero de threads
• -o40: número de peticiones encoladas en disco (outstanding IOs)
• -b512: operaciones de 512Kb
• -s15: duración de la prueba en segundos

Resultados de la prueba:

En color Rojo: bytes leidos por segundo.

En color Azul: tiempo medio de espera por petición de lectura.

Los porcentajes dependerán de cada tipo de disco y configuración, pero de las pruebas realizadas en nuestro entorno:

• la diferencia entre tener el archivo en la zona externa del disco vs la zona interna es de un 33% en cuanto a la capacidad de transferencia de información; fíjate que desciende de poco más de 300MB/sec a algo más de 200MB/sec
• por diferencia, el peor escenario posible es que el archivo esté fragmentado: frente al mejor caso, aparecen tasas de transferencia de 150MB/sec vs los más de 300MB/sec

Conclusiones:

• Cuanto más en la zona interna esté el archivo, menor será la tasa de transferencia y mayores serán las latencias para realizar la operación
• El peor escenario posible es trabajar con archivos fragmentados (en nuestro caso, el número de fragmentos era de 200 para un archivo de 10GB)
• por último, y no menos importante, estas mediciones impactarán negativamente en el sistema completo; considera que estamos hablando de sistemas cuya tasa de transferencia desde el sistema de E/S fácilmente por encima de los 4GB/sec – el sistema probado lo configuramos para 4.5GB/sec, por lo que podríamos estar en casos en los que la tasa de transferencia se reduzca por debajo de 3GB/sec, lo cual implica que el sistema estaría totalmente des-balanceado, que precisamente es lo que persiguen la arquitectura de referencia para FTDW…

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Ya como pié de post, y fuera del contexto del artículo, este sería el resultado para la siguiente ejecución – dejo al lector averiguar para qué tipo de sistema serían esas operaciones , eso si, nota que el impacto es muchísimo mayor!:

sqlio.exe -kR -frandom -t4 -o40 -b64 -s15 C:\FT\data15\TEST__INICIO.dat >INICIO.TXT
sqlio.exe -kR -frandom -t4 -o40 -b64 -s15 C:\FT\data15\TEST__MEDIO.dat>MEDIO.TXT
sqlio.exe -kR -frandom -t4 -o40 -b64 -s15 C:\FT\data15\TEST__FIN.dat>FIN.TXT
sqlio.exe -kR -frandom -t4 -o40 -b64 -s15 C:\FT\data15\TEST__FRAGMENTADO.dat>FRAGMENTADO.TXT

Conclusion general: si tienes un sistema de bases de datos con archivos muy fragmentados físicamente, tienes un problema serio! el problema es que para deshacer esa fragmentación, en muchos casos (sobre todo el FTDW) tienes que parar y empezar de nuevo, así que ya sabes, si no quieres problemas: hazlo bien desde el principio… si eres recién llegado al sistema y te encuentras el pastel… investiga y lucha por arreglarlo; recuerda a Paulo Coelho: “Cuando quieres algo, todo el universo conspira para que realices tu deseo”

Las lecturas no confirmadas (read uncommitted, nolock, también conocido como dirty reads) te pueden hacer leer muchas filas de más y/o de menos

Introducción

Recientemente estuve en un cliente en Vic impartiendo otra iteración del popular seminario de optimización creado con los compañeros de AulaVulcan, y me llamó especialmente la atención la sorpresa que se llevaron con una demo que tenía empolvada sobre los efectos de lecturas no confirmadas. Ellos eran conscientes de que podías leer o dejar de leer ciertas filas (el ejemplo típico de que la fila está y no está si la transacción está o no está confirmada), pero se quedaron impresionados cuando vieron que podías leer la misma fila dos filas en la misma consulta; intentaré explicarlo en el siguiente ejemplo :)

Qué vamos a demostrar

Cuando se hacen lectura sucias (tanto eligiendo el nivel de aislamiento READ UNCOMMITTED, o utilizando el hint NOLOCK), estás leyendo datos que pueden ser inconsistentes desde el punto de vista transaccional; en este post vamos a reproducir un caso extremo para que te convenzas de los “desbarajustes” que se pueden estar haciendo

Qué son las lecturas sucias

Partiendo de una tabla de pedidos, con las siguientes filas:

y dándose las siguientes operaciones ordenadas en el tiempo:

Puede suceder que el Usuario 2 lea datos que realmente no están confirmados.

Sin embargo, el Usuario 3, mientras la transacción no esté confirmada, no podrá leer el dato, y cuando la transacción esté confirmada, podrá leer el dato real.

Para aliviar la situación de que el Usuario 3 está bloqueado en SQL Server 2005 aparece el concepto de lectura confirmada de instantánea (READ COMMITTED SNAPSHOT) que se menciona al final de este post.

Preparando una demo “impactante”

Dada una tabla con XX filas, vamos a definir dos conexiones haciendo las siguientes operaciones:

• una conexión contará el número de filas de la tabla
• otra conexión actualizará la tabla entera

Para ello tenemos la siguiente estructura de tabla:

--
-- bloqueos (lectura sucia)
--
-- Solid Quality Mentors 2010
-- http://creativecommons.org/licenses/by-sa/3.0/
-- Attribution-NonCommercial-ShareAlike 3.0
--
-- http://blogs.solidq.com/elrinconDelDBA
-- http://siquelnet.com
--
use Adventureworks
go

if exists (select * from sys.tables where name='bloqueos')
    drop table bloqueos;
go

create table bloqueos (
    id  int not null identity,
    relleno char(8000),
    mi_guid uniqueidentifier default(newid())
)
go

create unique clustered index ci_bloqueos_malo 
on bloqueos (mi_guid);
create unique nonclustered index nci_bloqueos_id
on bloqueos (id);

En la que insertaremos 10.000 filas – comprobamos espacio usado y número de página asignadas:

insert bloqueos (relleno)
    select 'a' from master.dbo.fn_nums (10000)
go
 
select si.name, page_count, record_count
from sys.dm_db_index_physical_stats (
    db_id()
    , OBJECT_ID ('bloqueos'), null, null, 'detailed') v
    join sys.indexes si
    on v.index_id = si.index_id 
    and v.object_id = si.object_id 
where index_level = 0
go

sp_spaceused bloqueos

Donde obtenemos el siguiente resultado:

Nota como hemos “conseguido” que el índice clustered tenga sólo una fila por página (fíjate que en la creación de la tabla dijimos que la columna relleno ocupaba 8000 bytes). Además, fíjate que el índice nonclustered es muy “ligero” y apenas ocupa 50 páginas (la clave es integer  -- 4 bytes por referencia).

Seguramente te has dado cuenta que hemos cometido una irresponsabilidad al definir como clustered una columna newid(); lo hemos hecho así para provocar que sentencias TSQL sucesivas que veréis después, fuercen a que la fila tratada sea muy volátil en cuanto a su ubicación lógica en la tabla.

Además, vamos a crear una tabla de auditoria, donde vamos a registrar las operaciones que se van a ir haciendo:

-- auditoria
if exists (select * from sys.tables where name='auditoria')
    drop table auditoria;
go

create table auditoria (
    id  int not null identity primary key,
    quien varchar(100),
    operacion varchar(100),
    cuando datetime default (getdate()),
    filas int
)

Provocando el escenario deseado para la demo

vamos a tener dos usuarios realizando las siguientes operaciones:

• Usuario 1: actualización completa de la tabla renovando el valor de la columna GUID.
  • la función NEWID() genera valores muy “dispersos”, lo que provocará que las filas se muevan con un ratio muy alto de unas página a otras
• Usuario 2: bucle iterativo contando cuantas filas hay en la tabla en modo lectura sucia
  • forzaremos que se use el índice clustered (donde están los datos) para mostrar el efecto deseado

Para ello, necesitas dos conexiones de SSMS; en las demostraciones de bloqueos suele ser útil ver las dos conexiones en vertical (menú Window, New Vertical Tab Group).

En una de las conexiones tendrás el siguiente código (la que va a hace los SELECT):

-- Conexión 1: actualizaciones
--
insert auditoria 
(quien, operacion, filas)
select 'Conexion1', 'INICIO UPDATE', NULL
go

update bloqueos
set mi_guid = newid()
go

insert auditoria 
(quien, operacion, filas)
select 'Conexion1', 'FIN UPDATE', NULL

En la segunda conexión, tendrás el siguiente código (el del UPDATE):

-- Conexión 2: contar filas -- por CI
--
truncate table auditoria
go

while 1 = 1
begin
    insert auditoria 
        (quien, operacion, filas)
    select 
        'Conexion2', 'SELECT (NOLOCK)', count(*) 
    from bloqueos with (nolock, index=ci_bloqueos_malo)
    waitfor delay '00:00:00.5'
end

A continuación pones en marcha la consulta que lee (bucle iterativo), y después ejecutas la primera consulta (la del UPDATE). Cuando finalice la consulta del UPDATE, paras (le das a STOP) a la consulta de la SELECT.

Ahora ejecuta la siguiente consulta, y verás la sorpresa:

select *
from auditoria 

Las líneas de color Rojo, marcan la “zona” de tiempo en la que se está ejecutando el UPDATE.

Fíjate que existen una cuantas filas insertadas, en las que el número de filas contadas ha sido distinto de 10.000 que es el número de filas que tiene la tabla. ¿cual es la causa de esto?

Mientras la consulta SELECT se estaba procesando, SQL Server estaba leyendo filas de más o de menos; ¿cómo ha sucedido esto? mejor un dibujo :)

• En color Rojo, se representa en Index Scan, que lee todas las página para contar cuantas fijas hay
• En color Azul, se representa la actualización de una fija, que la mueve de una página a otra
• Cuando se estaba leyendo a la altura del asterisco (*), se movió la fija a la ubicación donde finaliza la flecha azul
• lo cual implica que cuando el Index Scan pasó por donde se movió la fila ya leída, lo leyera otra vez

Como ejercicio, te propongo que simules el proceso reemplazando el código de Conexión 2 por el siguiente:

-- Conexión 3: contar filas por NCI
--
while 1 = 1
begin
    insert auditoria 
        (quien, operacion, filas)
    select 
        'Conexion3', 'SELECT (NOLOCK) -- NCI', count(*) 
    from bloqueos with (nolock)
    waitfor delay '00:00:00.5'
end

y verás como siempre se leerán el mismo número de filas; ¿por qué razón? porque el UPDATE de la columna mi_guid, no proboca cambio de ubicación de las filas en el índice nonclustered (columna id)

Conclusión

• Este efecto “no deseado”, no sucederá si se usas un índice que no sufre movimientos de páginas (como el NCI de Conexión 3).
• Tampoco sucederá si no utilizas el hint NOLOCK y trabajas en modo de aislamiento LECTURA CONFIRMADA.
• SI sucederá si tienes configurada la base de datos en modo READ_COMMITTED_SNAPSHOT y realizas lecturas SUCIAS (NOLOCK).
• Tampoco sucederá si tienes configurada la base de datos en modo READ_COMMITTED_SNAPSHOT y trabajas en modo de aislamiento LECTURA CONFIRMADA.

Cuestiones sobre tablas variable

Ayer entre las 12PM y 2PM tuvimos via LiveMeeting en complemento formativo del seminario “Optimización Avanzada de Consultas SQL para Desarrolladores y DBA” que ya hemos hecho 3 veces con AulaVulcan.

<cuña_publicitaria> Aprovecho para comentar que la semana del 29 de noviembre haremos una iteración más vía LiveMeeting en formato de 3 horas diarias de lunes a viernes; más información en ibinfo@solidq.com </cuña_publicitaria>.

El objetivo del complemento era resolver dudas que los alumnos tuvieron durante el seminario impartido una semana antes; en esas ocasiones, suelo tener preparados ejemplos para profundizar en conceptos de bloqueos (si, esos tan olvidados) para un poco ir calentando el ambiente. Afortunadamente, esta vez, uno de los alumnos tenía una duda concreta en su sistema, y prácticamente monopolizó la sesión con sus problemas :) No hay ningún problema con este tema, porque al ver los problemas que tenía fácilmente convertí su problema en una cuestión general para que todos los asistentes pudieran sacar partido de “su problema”, y es lo que voy a reproducir a continuación.

En mi humilde opinión, este es un tema recurrente, que es raro que no suceda en algo más del 40% de los clientes a los que atendemos, y aunque como lector quizás consideres trivial el problema, estoy convencido que muchos de los seguidores, se sentirán “cautivados” e interesados con el problema.

Por anticipado os digo, que no voy a resolver el problema: lo tendrás que resolver tu, y dentro de 20-30 días, haga otra publicación con la solución y razonamientos al problema. En realidad, me gustaría publicar la solución y justificaciones que vosotros me aportéis :)

Problema: el cliente tiene un proceso que rellena una matriz de disponibilidades, que en entorno de desarrollo funciona bien, cuando sólo hay un usuario, pero una vez puesta en producción, como hay muchos usuarios que usan dicha consulta, el tiempo de respuesta es demasiado lento, y como podéis imaginar, resulta molesto para los usuarios.

Qué necesitas para reproducirlo: necesitas SQL Server 2008-2008R2-DenaliCTP1, y necesitas la base de datos Adventureworks.

Para simplificar el problema, estas son las consultas tipo a optimizar; la lógica del proceso es la siguiente:

• un SP recibe por argumento una lista de Identificadores
• esa lista de identificadores se mete en una tabla variable
• a continuación se hacen JOINs entre la tabla variable y tabla del proceso

Además, como podéis imaginar, el número de filas que llegan en cada llamada resulta variable, por lo que el ejemplo está creado para escenarios en los que se solicita el 1% de las filas de la tabla del proceso, el 10% y el 20%.

El script a optimizar es el siguiente:

use Adventureworks 
go

-- 01% sales.SalesOrderHeader
declare @ids table (id int primary key (id))
insert @ids select top (
    select cast(count(*) * 0.01 as int) c from sales.SalesOrderHeader
) SalesOrderID
from Sales.SalesOrderHeader 

select *
from Sales.SalesOrderHeader h
inner join @ids i
on h.SalesOrderID = i.id
go

-- 10% sales.SalesOrderHeader
declare @ids table (id int primary key (id))
insert @ids select top (
    select cast(count(*) * 0.10 as int) c from sales.SalesOrderHeader
) SalesOrderID
from Sales.SalesOrderHeader 

select *
from Sales.SalesOrderHeader h
inner join @ids i
on h.SalesOrderID = i.id
go


-- 20% sales.SalesOrderHeader
declare @ids table (id int primary key (id))
insert @ids select top (
    select cast(count(*) * 0.20 as int) c from sales.SalesOrderHeader
) SalesOrderID
from Sales.SalesOrderHeader 

select *
from Sales.SalesOrderHeader h
inner join @ids i
on h.SalesOrderID = i.id

En las pruebas de laboratorio que he hecho, he obtenido los siguientes resultados:

Donde:

• en color rojo, los resultados de la consulta anterior
• en color verde, los resultados haciendo cambios

Me gustaría que:

• me mostrarais como llegar a los números de las columnas verdes más o menos: los números varían de un entorno a otro
• me enviarais mail indicándome el problema que veis en esas consultas, y elaboréis la respuesta: imagínate que eres un consultor y tienes que justificar tu razonamiento a un cliente: se agradecen justificaciones en la línea de plan de ejecución, IOs, funcionamiento del motor, etc. etc.
• mi dirección de correo: ELADIO @ SOLIDQ . COM (quitar espacios en blanco)
• por favor, pon como título del mail el siguiente texto: “BLOG: Cuestiones sobre tablas variable”

Te espero!!! :)

Wait stats information to Synchronous Bucketizer Target in Denali CTP1 with Extended Events

Following the article yesterday published in our http://www.solidq.com/sqj (Know Where Your Query Spends Its Time), here is the code to send the wait stats information to a synchronous bucketizer; the obvious pros are that the structure is in memory, and you don’t need to stop the session to process the data; the cons are that you cannot reset the data read (unless you re-create the session); the other big con is that you cannot sum values in the columns (in the case of the wait_stats time is important).

Here is the code:

Create the session:

--
-- Extended Events (query level wait stats)
--
-- Eladio Rincon
-- Solid Quality Mentors 2010
-- http://creativecommons.org/licenses/by-sa/3.0/
-- Attribution-NonCommercial-ShareAlike 3.0
--
-- http://blogs.solidq.com/elrinconDelDBA
-- http://siquelnet.com
--


USE AdventureWorks
GO

SELECT DB_ID('adventureworks') dbid, @@spid spid
GO

CREATE EVENT SESSION xe_waits_bucket
ON SERVER
ADD EVENT
    sqlos.wait_info (
        WHERE  (sqlserver.database_id = 7 AND sqlserver.session_id = 59 AND opcode = 1)
    )
ADD TARGET package0.synchronous_bucketizer ( 
SET filtering_event_name='sqlos.wait_info'
, source_type=0
, source='wait_type'
)
WITH 
(
    MAX_MEMORY = 4096KB, 
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, 
    MAX_DISPATCH_LATENCY = 1 SECONDS, 
    MEMORY_PARTITION_MODE = NONE, 
    TRACK_CAUSALITY = ON, 
    STARTUP_STATE = OFF
)

Then start the session:

ALTER EVENT SESSION xe_waits_bucket
ON SERVER
STATE = START

Run a sample query:

SELECT * from HumanResources.vEmployee

Now; if you want to know where is the information, run the following query:

SELECT CAST(xest.target_data AS XML) xml_data
FROM sys.dm_xe_session_targets xest
JOIN sys.dm_xe_sessions xes ON xes.address = xest.event_session_address
JOIN sys.server_event_sessions ses ON xes.name = ses.name
WHERE xest.target_name = 'synchronous_bucketizer' AND xes.name = 'xe_waits_bucket'

Bear in mind the xes.name column that is the name of the session.

if you see the resultant XML, you’ll see something like this:

<BucketizerTarget truncated="0" buckets="256">
  <Slot count="183" trunc="0">
    <value>121</value>
  </Slot>
  <Slot count="75" trunc="0">
    <value>66</value>
  </Slot>
  <Slot count="11" trunc="0">
    <value>99</value>
  </Slot>
</BucketizerTarget>

Here is the nice part:

• value is an internal code for the wait type
• count is the number of occurrences

So, the thing is find out how to translate the value in the XML to the wait_type description.

you’ll get that information from the sys.dm_xe_map_values, DMV.

finally, the query will be the following:

-- wait stats
SELECT 
    v.count 
    , k.map_value wait_type
from (
SELECT
     t.x.value('(value)[1]', 'int') as k 
     , t.x.value('(@count)[1]', 'int') AS [count]
from ( 
SELECT CAST(xest.target_data AS XML) xml_data
FROM sys.dm_xe_session_targets xest
JOIN sys.dm_xe_sessions xes ON xes.address = xest.event_session_address
JOIN sys.server_event_sessions ses ON xes.name = ses.name
WHERE xest.target_name = 'synchronous_bucketizer' AND xes.name = 'xe_waits_bucket'
) v
CROSS APPLY xml_data.nodes ('//BucketizerTarget/Slot') as T (x)
) v
left join sys.dm_xe_map_values k
on  v.k = k.map_key
where k.name = 'wait_types'

In my case, the results will look like this:

count       wait_type
----------- -------------------------------
215         SOS_SCHEDULER_YIELD
75          PAGEIOLATCH_SH
11          NETWORK_IO

Final Notes:

• The JOIN doesn’t resolve correctly in SQL 2008 because aparently the keys doesn’t match with the dm_xe_map_values (I couldn’t find the relation).
• This works on Denali CTP1; I’m not sure it it will work on next CTPs. UPDATE: Jonathan Kehayias (SQL Server MVP) pointed this PM that this issue is fixed in SQL Server 2008 SP2 (280004); however, still doesn’t work on SQL Server 2008 R2.
• it would be nice if in the future you could select aggegate operations in other columns (such sums, avgs, maxs, etc)

Si no necesitas el índice, ¿por qué no lo borras? (III)

Continuemos la saga; en el post anterior vimos que inserciones en conjuntos no provoca fragmentación en las tablas / índices;

Qué vamos a probar: ahora toca probar que sucede con inserciones en pequeños grupos; en este post vamos a hacer el los grupos sean de filas de 1 en 1, pero considera que es extensible para procesos iterativos que insertan filas poco a poco.

Para el ejemplo utilizaremos el siguiente script de setup:

--
-- fragmentación durante carga de tablas en pequeños grupos
--
-- Solid Quality Mentors 2010
-- http://creativecommons.org/licenses/by-sa/3.0/
-- Attribution-NonCommercial-ShareAlike 3.0
--
-- http://blogs.solidq.com/elrinconDelDBA
-- http://siquelnet.com
--

use Adventureworks 
go
set nocount on
go
if exists (select * from sys.tables where name = 'bloqueos_base')
    drop table bloqueos_base
go
create table bloqueos_base
(id int identity, d date, r int, s char(100)
constraint bloqueos_base_pk primary key (id));
go

if exists (select * from sys.tables where name = 'bloqueos')
    drop table bloqueos
go
create table bloqueos
(id int identity, d date, r int, s char(100)
constraint bloqueos_pk primary key (id));
go
create nonclustered index nci_bloqueos_r
on bloqueos (r) include (s);
create nonclustered index nci_bloqueos_d
on bloqueos (d);
go
insert bloqueos_base values
  ('20080101',1, 'aaa'), ('20081011', 2,'abb')
, ('20081012',1, 'acc') ,('20081013', 2,'add')
, ('20081014',3, 'baa') ,('20081015', 4,'bdd')
, ('20081017',3, 'bcc') ,('20081016', 4,'bdd')
, ('20081017',5, 'bcc') ,('20081016', 5,'bdd')

Si recuerdas los posts anteriores, en este la pequeña diferencia es que vamos a utilizar una tabla que llamamos "bloqueos_base” que será la tabla desde la que se inserta la información en la tabla destino.

El proceso de inserción “poco-a-poco” iterativo será el siguiente:

declare @i int=1
while @i<=100000
begin
    insert bloqueos
    select top 1 p.d, @i % 5, p.s
    from bloqueos_base p 
    order by NEWID()
    set @i+=1
end

Es decir, se leerá de la tabla base una fila, que se insertará en la tabla destino – llamada bloqueos.

fíjate que se utiliza la variable @i para establecer la columna r, que podría considerarse como columna “variable” (en este caso va a generar valores entre 1 y 5, que serán consecutivos, es decir, 1, 2, 3, 4, 5, y así repetir la serie hasta llegar a la fila 100000.

También fíjate que la tabla tiene pre-creados dos índices: uno por la columna r (que podría considerarse un código de región en tablas de ventas) y otro por la columna d que podría ser la fecha del pedido.

Tras esto, analizamos cómo de fragmentados están los índices de la tabla con la siguiente consulta:

-- cómo está de fragmentado -- todo
select 
    si.name,
    index_level, 
    avg_fragmentation_in_percent, 
    avg_page_space_used_in_percent,
    fragment_count, 
    avg_fragment_size_in_pages, 
    page_count, 
    record_count
FROM sys.dm_db_index_physical_stats (
    db_id ('Adventureworks'), 
    object_id('dbo.bloqueos'), 
    NULL, NULL, 'detailed') v
JOIN sys.tables so
on so.object_id = v.object_id 
JOIN sys.indexes si
ON v.index_id = si.index_id 
and so.object_id = si.object_id 
where index_level =0

Y como os anticipo que el proceso de inserción generará fragmentación, lo siguiente que haríamos sería reconstruir los índices de la tabla bloqueos (todos):

alter index all
on bloqueos rebuild
with (fillfactor = 100)

Luego, una vez reconstruido los índices, se volvería a analizar la fragmentación con la DMV anterior.

El resultado obtenido es el siguiente:

El primer resultado es la DMV tras realizar las 100K inserciones, el segundo resultado es tras defragmentar los índices; ¿qué resultados tenemos? – fíjate en las cajitas rojas que he añadido para que veáis donde están los mayores problemas:

• El proceso no ha afectado negativamente al % de relleno de las páginas de ninguno de los índices: valores cercanos al 99%.
• El proceso ha provocado mucha fragmentación de extensión en todos los índices: en el índice clustered nos quedamos con 203 fragmentos, mientras que en los índices non-clustered 1594 y 341; fíjate el mal dato que tenemos en los índices non-clustered, que el número de páginas medio por fragmento es 1!
• El proceso ha provocado mucha fragmentación interna (páginas no-consecutivas) en los índices non-clustered.

Una vez reconstruidos los índices, fíjate que se ha eliminado la fragmentación interna de los índices non-clustered y el número de fragmentos se ha reducido mucho.

La alternativa obvia es: borrar los índices antes del proceso, o deshabilitarlos porque una vez deshabilitado el índice, las operaciones de inserción, no tocarán los objetos deshabilitados; para volver a poder utilizar los índices, tendrás que reconstruir el índice deshabilitado); el código para deshabilitar los índices sería el siguiente:

alter index nci_bloqueos_r
on bloqueos disable;
alter index nci_bloqueos_d
on bloqueos disable;

Para probarlo, inserta el código anterior justo antes del proceso de inserciones iterativas.

Vuelve a ejecutar el proceso, y comprueba los resultados; en mi máquina he obtenido los siguientes resultados:

La duración ha sido un poco menor (no muy significativa); pero fíjate que en este caso, el proceso no ha sido nada negativo para la fragmentación de extensión; como curiosidad, nótese que la reconstrucción del índice ha generado más fragmentación de extensión de la que tenía (como dijimos en el post anterior, seguramente afecte el número de filas – en nuestro caso son poca, apenas 100K).

Conclusión: hemos visto, como un proceso “iterativo” de inserción de filas si genera fragmentación en algunos de los índices que forman parte de nuestro diseño; como conclusión general, te recomendaría que al margen de lo que veas en publicaciones como esta, trates de probar tu escenario, e intenta entender cómo se comporta SQL Server :) prueba, prueba y prueba, no te canses de probar.

En siguientes posts, veremos cómo afecta negativamente la fragmentación a tus índices: no lo vamos a ver desde el pto de vista de ahorro/coste de espacio porque de eso ya hay muchas referencias por ahí, sino desde el punto de vista de ejecución de consultas

Si no necesitas el índice, ¿por qué no lo borras? (II)

Como continuación al post anterior, vamos a ver qué fragmentación se provoca durante las cargas masivas. En esta ocasión, no vamos a mirar información de monitor de rendimiento, nos vamos a centrar en la información que nos proporcionan las DMVs de indexación (en este caso sys.dm_db_index_physical_stats).

Qué vamos a probar: ¿provoca fragmentación dejar índices creados durante cargas masivas? SI / NO / DEPENDE; veámoslo…

Primero definición de esquema de objetos:

--
-- fragmentación durante carga de tablas
--
-- Solid Quality Mentors 2010
-- http://creativecommons.org/licenses/by-sa/3.0/
-- Attribution-NonCommercial-ShareAlike 3.0
--
-- http://blogs.solidq.com/elrinconDelDBA
-- http://siquelnet.com
--

use Adventureworks 
go
if exists (select * from sys.tables where name = 'bloqueos')
    drop table bloqueos
go
create table bloqueos
(id int identity, d date, s char(100)
constraint bloqueos_pk primary key (id));
go
create nonclustered index nci_bloqueos_d
on bloqueos (d);
create nonclustered index nci_bloqueos_a
on bloqueos (s);
go
insert bloqueos values
  ('20080101', 'aaa'), ('20081011', 'abb')
, ('20081012', 'acc') ,('20081013', 'add')
, ('20081014', 'baa') ,('20081015', 'bdd')
, ('20081017', 'bcc') ,('20081016', 'bdd')
go

Ahora insertar unas cuantas filas:

insert bloqueos
select top 100000 p.d, p.s
from bloqueos p 
cross join bloqueos n
cross join master.dbo.spt_values c
go

Tras la inserción, comprobamos cómo está de fragmentada la información en la tabla con la siguiente query:

-- cómo está de fragmentado -- todo
select 
    si.name,
    index_level, 
    avg_fragmentation_in_percent, 
    avg_page_space_used_in_percent,
    fragment_count, 
    avg_fragment_size_in_pages, 
    page_count, 
    record_count
FROM sys.dm_db_index_physical_stats (
    db_id ('Adventureworks'), 
    object_id('dbo.bloqueos'), 
    NULL, NULL, 'detailed') v
JOIN sys.tables so
on so.object_id = v.object_id 
JOIN sys.indexes si
ON v.index_id = si.index_id 
and so.object_id = si.object_id 
where index_level =0

Tenemos como resultado lo siguiente:

¿Qué vemos?

• avg_fragmentation_in_percent: porcentage de páginas no “consecutivas”; muy bajo en general; el peor dato: 5% para el índice de fecha. Cuanto menor sea este dato, mejor,
• avg_page_space_used_in_percent: cómo de llenas están las páginas: muy alto, cercano al 100%; cuanto mayor sea el dato, menos espacio “libre” hay en las páginas.
• fragment_count: número de grupos de extensiones que están “seguidas”; es decir, extensiones consecutivas-contiguas, etc. cuanto menos fragmentos mejor. 74 fragmentos hay que ponerlos en contexto del número de páginas, en este caso 74 / 1614 = 0,04584 que indicaría el número de saltos de extensión por página; este es un buen dato;
• avg_fragment_size_in_pages: media de número de páginas seguidas en los fragmentos; básicamente, cuantas páginas contiene cada fragmento; un número alto indica que los fragmentos tienen muchas páginas, y por lo tanto páginas muy seguidas unas tras otras. cuanto mayor el dato, mejor.
• page_count: número de páginas
• record_count: número de filas

Para ver, cómo de mejor podría ser el dato, podemos reconstruir todos los índices del objeto “bloqueos” con la siguiente instrucción:

alter index all
on bloqueos rebuild
with (fillfactor = 100)

Teniendo como resultado lo siguiente:

Fíjate que el resultado es un poco confuso, seguramente asociado con el número de páginas que tiene la tabla (pocas – apenas 1500 con un total de 100K filas):

• para los índices de las columnas d y a se ha mejorado en todo (tanto fragmentación lógica como de extensión).
• para el índice clustered, ha mejorado la fragmentación lógica, ha empeorado el relleno de las páginas, y ha empeorado el número de fragmentos.

para finalizar, si ves el plan de ejecución de la sentencia de inserción, verás lo siguiente:

insert bloqueos
select top 100000 p.d, p.s
from bloqueos p 
cross join bloqueos n
cross join master.dbo.spt_values c

que como vimos en el post anterior, nos indicaba que antes de hacer la inserción sobre los índices ordenaba el conjunto de datos, para precisamente evitar la fragmentar la información :)

Conclusión: si tienes varios índices, antes de insertar las filas y rellenar las estructuras paralelas (índices), el motor de SQL Server se encarga de pre-ordenar las filas para poder insertarlas de forma eficiente: es decir, con la mínima fragmentación posible.

En el siguiente post veremos qué efecto tiene insertar múltiples batches.

Ha nacido Solid Quality Journal: Revista digital focalizada en SQL Server, BI, y herramientas de desarrollo

podéis visitar las siguientes URLs: descárgate gratuitamente el contenido.

http://www.solidq.com/sqj/Pages/Home.aspx

http://www.solidq.com/sqj/Pages/The-SolidQ-Journal,-July-2010,-only-TOC,-Editorial,-News.aspx

Para empezar los siguientes artículos:

• Entrevista con Mark Souza.
• The Blueprint for Proper SSAS Dimensions
• Why use Data Mining
• Requirements are Evils: A Rant in 5 acts
• N-Tier: no separation Anxiety Here
• Should SQL Server automatically Index Foreign Keys?
• Hammer, Nails, and PowerShell
• What 3 Events Brought me here

espero que os guste :)

Curiosidades de indices filtrados (Filtered Indexes)

Recientemente tuvimos que dar solución a un escenario “no tan peculiar” de uso de índices filtrados.

Introducción a índices filtrados y sus casos de uso (http://technet.microsoft.com/en-us/library/cc280372.aspx)

Si no tienes claro el concepto, por favor lee el artículo de Technet antes de seguir adelante.

El escenario a resolver era el siguiente:

• Dada una aplicación que por cada visita web, añade una fila a una tabla de auditoría.
• Dicha tabla tiene una columna fecha que indica cuando sucedió la visita. Descartamos información adicional como usuario, url de procedencia, navegador, etc. para simplificar el escenario.
• Cada noche se borraba y creaba un índice filtrado para acotar el conjunto de filas a los últimos 7 días de visitas: el cliente tiene un proceso más o menos importante que requiere manejar la información de los últimos 7 días para alimentar ciertos procesos analíticos.
• En principio el índice parecía bien diseñado, pero las DMVs de actividad de índices decían que no se usaba y las cargas de los procesos analíticos tenían más o menos la misma velocidad que antes de la creación del índice filtrado.

En este post vamos a ver qué sucedió y cómo llegamos a ver qué sucedía.

En primer lugar, creamos un conjunto de datos para la simulación; para ello puedes utilizar el siguiente código:

--
-- filtered indexes
--
-- Solid Quality Mentors 2010
-- http://creativecommons.org/licenses/by-sa/3.0/
-- Attribution-NonCommercial-ShareAlike 3.0
--
-- http://blogs.solidq.com/elrinconDelDBA
-- http://siquelnet.com

use Adventureworks
go

create table top_
(id int identity, d date, s char(10)
constraint top__pk primary key (id))
go

insert top_ values
  ('20081010', 'aaa'), ('20081011', 'abb')
, ('20081012', 'acc') ,('20081013', 'add')
, ('20081016', 'baa') ,('20081013', 'bdd')
, ('20081017', 'bcc') ,('20081013', 'bdd')
go

insert top_
select top 1000 p.d, p.s
from top_ p
cross join master.dbo.spt_values c
go

insert top_ values (null, null)
go

-- mas filas
insert top_
select top 100000 p.d, p.s
from top_ p
cross join master.dbo.spt_values c
go
-- mas filas
insert top_
select top 100000 p.d, p.s
from top_ p
cross join master.dbo.spt_values c
go
-- mas filas
insert top_
select top 100000 p.d, p.s
from top_ p
cross join master.dbo.spt_values c

asdf

Comprobamos cual es la distribución de la columna d (almacena fechas):

select d, count(*) c
from top_
group by d
order by c desc

Quedando de la siguiente forma:

Para nuestro ejemplo, asumamos que buscamos filas >= que el 16 de octubre de 2008.

Primero habilitamos STATISTICS IO

set statistics io on

y comprobamos cuantas filas tenemos posteriores al día 16 comentado anteriormente:

select COUNT(*) c
from top_
where d >= '20081016'

validamos el plan de ejecución y STATISTICS IO y vemos lo siguiente:

Obviamente la tabla sólo tiene índice clustered por la PK por lo que SQL Server tendrá que recorrerse toda la tabla para validar qué filas cumplen el predicado.

Si creamos un índice nonclustered por la columna fecha, tendríamos los siguientes resultados:

create nonclustered index nci_top_d
on top_ (d);

y volviendo a ejecutar la consulta tendríamos:

Donde hemos pasado de las más de 1400 lecturas de páginas iniciales a apenas 28.

hasta aquí todo en orden, veamos ahora qué sucede creando un índice filtrado por la fecha del filtro (16 de octubre de 2008).

La sintaxis para crear el índice es la siguiente:

create nonclustered index nci_top_d_filter
on top_ (d)
where d >= '20081016';

y volviendo a ejecutar la misma consulta, vemos que SQL Server no utiliza el índice recientemente creado :(

Si intentamos forzar el uso del índice tendremos el siguiente resultado:

select COUNT(*)
from top_ with (index=nci_top_d_filter)
where d >= '20081016'

la consulta finalizará correctamente y en el plan de ejecución podrás comprobar que el índice filtrado se ha usado; sin embargo, si cambias la base de datos a parametrización FORZADA, SQL Server te mostrará el siguiente error:

Msg 8622, Level 16, State 1, Line 1
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

Es como si SQL Server determinara que el índice no puede usarse para resolver la consulta; desde el pto. de vista de lógica relacional, el índice filtrado si podría cubrir la consulta; tras buscar en varios sitios, he llegado a leer argumentos que indican que debe forzarse a generar un plan de ejecución nuevo para cada ejecución, pero eso funcionará unas veces si, y otras no :) me explico: en modo parametrización FORZADA, SQL Server parametriza casi todas las consultas (mira en BOL los casos que no), y el plan de ejecución que deje en caché deberá ser estable para siguientes usos que se haga del el; si dejara en caché un plan de ejecución que use el índice filtrado:

• Unas veces funcionará (cuando el argumento se cumpla para el rango del índice filtrado); por ejemplo: filtras para día posterior al día 16: el índice filtrado si cumple el predicado.
• y para otras veces no: filtras por fecha anterior al día 16.

Antes de seguir adelante, borra el índice por la columna fecha sin filtro:

drop index nci_top_d on top_

Configura la base de datos en parametrización SIMPLE, y ejecuta la consulta siguiente:

select COUNT(*)
from top_ 
where d >= '20081016'

Comprueba cómo se usa el índice filtrado, ahora ejecuta la siguiente consulta y comprueba que ha decidido hacer un clustered index scan porque el índice filtrado no es suficiente:

select COUNT(*)
from top_
where d >= '20011016'

Es decir, SQL Server dinámicamente valida que los parámetros son suficientes para resolver con el índice filtrado o no.

Sin embargo, si cambias la BD a parametrización FORZADA y ejecutas la consulta siguiente:

select COUNT(*)
from top_
where d >= '20011016'

notarás cómo a pesar de haber un índice que satisface el predicado, directamente lo descarta; sucede exactamente lo mismo que hemos comentado anteriormente: como el plan de ejecución no es “estable” SQL Server directamente desecha el índice filtrado.

¿Qué puedes hacer?

select COUNT(*)
from top_ 
where d >= '20081016'
option (recompile)

Efectivamente, forzar a que SQL Server cree un plan de ejecuión ad-hoc para la consulta que estás ejecutando; de esta forma, SQL Server si usará el índice filtrado cuando los argumentos sean adecuados para el índice.

Conclusiones:

• Base de datos configurada en Parametrización Forzada
  • No se puede usar un hint para usar índice filtrado: plan inestable
  • SQL Server no usa el índice filtrado en consultas normales porque parametriza casi todo y tendría que reutilizar planes potencialmente inestables
  • SQL Server considera usar el índice filtrado si especificas OPTION RECOMPILE
  • SQL Server considera usar el índice filtrado si utilizas código dinámico sp_executesql…
• Base de datos configurada en Parametrización Simple
• SQL Server considera usar usar índice filtrado si los argumentos son adecuados para el índice filtrado: es decir, dinámicamente se adapta a la cobertura del índice filtrado.
• sigue probándolo porque en escenarios en los que el índice filtrado es un subconjunto del índice completo, suele resultar difícil que SQL Server utilice el índice filtrado (a menos que lo fuerces)… quizás ahondo en el suguiente post en esto :)

¿qué te parece?