En esta entrada estrenábamos una sección dedicada a la resolución de problemas de rendimiento en SQL Server, y os comentaba que tenía pensado dedicar éstas primeras entregas principalmente a la arquitectura del producto; mi idea es dedicar cada entrada a describir algunos de los conceptos determinantes para el rendimiento en la ejecución de nuestras consultas y acompañarlo de ejemplos prácticos.
Pues bien, en esta ocasión me gustaría hablaros de las esperas en SQL Server; un concepto fundamental de cara a la identificación de potenciales problemas de rendimiento. Y digo que me gustaría hablaros de las esperas, porque no voy a poder hacerlo :) Antes de ello, deberemos ver una introducción básica a la ejecución de tareas en SQL Server para posteriormente, en una entrada futura, introduciros a las esperas y a mi metodología de resolución de problemas de rendimiento favorita en SQL Server... (pero que bueno soy creando tensión y expectación, ¿eh? :) )
Al turrón...
Ejecución de Tareas en SQL Server
El objetivo último de SQL Server es procesar tareas, y para ello necesita una serie de recursos (disco, memoria, red...), así como, al menos, un procesador. Hasta ahí nada nuevo. Lo que quizá no se conoce tanto es que SQL Server dispone internamente de unos procesadores lógicos llamados schedulers, que van a ser clave a la hora de entender como se procesan las tareas, como interpretar las esperas y un sin fin de detalles relevantes para el rendimiento de nuestros servidores.
Cuando SQL Server arranca, una de las primeras cosas que hace es consultar al sistema operativo, mediante WMI, cuantos procesadores tiene la máquina, para posteriormente crear tantos schedulers como procesadores se hayan detectado. El objetivo de éstos será el de ejecutar las tareas del servidor (nuestras consultas, procesos internos de SQL Server como el lazy writer, etc.), de un modo coordinado con el UMS/SQLOS del que hablaremos en otra entrada.
Cada uno de esos procesadores lógicos tendrá asignada una lista de tareas, implementadas mediante worker threads, que se ejecutarán a medida que el scheduler vaya quedando libre. Estos worker threads se implementan, por defecto, como hilos del sistema operativo (se puede cambiar este comportamiento para que funcionen como fibras, en caso de tener el servidor funcionando en fiber mode), con su consiguiente consumo de memoria y recursos por cada uno de los hilos. Por esta razón, y para evitar un crecimiento masivo de hilos en el sistema, hay un parámetro de configuración del servidor llamado max_worker_threads, que permite limitar el número máximo de hilos que SQL Server va a pedir al sistema operativo para ejecutar tareas en ellos. Éste será también el número máximo de tareas que va a tener el sistema, distribuido entre todos sus schedulers.
Un par de ejemplos
El escenario más básico sería el que representamos en el diagrama de la derecha. En éste caso, disponemos de una máquina tradicional con una única CPU de un núcleo.
El proveedor WMI devolverá que hay una CPU, y SQL Server creará un único scheduler que gestionará la ejecución de todas las tareas del servidor. Habrá, por tanto, una única lista de tareas, con n worker threads, donde n es el número establecido en la variable de configuración max_worker_threads.
El siguiente escenario (en el diagrama de abajo) representa un entorno con dos CPUs de doble núcleo. Como podemos ver, cada uno de las CPUs se enumera como dos CPUs diferentes por WMI (hay dos núcleos por cada CPU), por lo que tendremos un total de cuatro schedulers. Por otra parte, vemos que habrá cuatro listas de ejecución de tareas, cada una de ellas con un limite máximo de una cuarta parte de las tareas máximas definidas por el max_worker_threads. Esto es así porqué SQL Server trata de equilibrar la carga equitativamente entre todos sus procesadores.
Hay un tercer escenario interesante, pero me lo reservo para un poquito más adelante en este mismo post :) Eso si, no me gustaría acabar esta sección sin comentar un par de detalles:
- Una tarea no puede abandonar un scheduler y moverse a otro a mitad de su ejecución. Esto es, una tarea esta atada a un scheduler desde su creación hasta su finalización.
- Se puede establecer una Mascara de Afinidad para determinar que CPUs vamos a usar en SQL Server, que schedulers vamos a crear, etc. pero esta fuera del ámbito de esta entrada por lo que no lo comentaremos de momento.
Consultando los Schedulers
Bien, ya hemos visto que, en teoría, existe por ahí algo que se llaman schedulers, que son CPUs lógicas que sirven para controlar la ejecución de tareas en SQL Server, y que además tienen forma de cajas de color verde con un suave y artístico degradado. Pero, sin duda alguna, en este instante os estáis preguntando si hay alguna manera de ver éstas estructuras en el servidor. Y también sé que, hasta que no descubráis como, no vas a poder conciliar el sueño. Por eso mismo, y porque no puedo tolerar el sufrimiento humano, pasamos sin más preámbulos a echar un vistazo a los metadatos que SQL Server expone para que veamos la información de los schedulers.
La siguiente query consulta sys.dm_os_schedulers, la DMV principal para obtener información acerca de los mismos:
SELECT
scheduler_id,
cpu_id,
status
FROM sys.dm_os_schedulers
Mi máquina de pruebas tiene una única CPU dual core. En ella, la ejecución de la consulta muestra el siguiente resultado:
¿Y esto? ¿No deberían aparecer solo dos schedulers según lo que hemos visto arriba? Bien, lo cierto es que solo los dos que aparecen marcados en naranja son normales. Los dos que aparecen con estado HIDDEN ONLINE se emplean para procesar las tareas del sistema, mientras que los VISIBLE ONLINE se dedican al proceso de tareas de usuario, y son en las que nos fijaremos normalmente de cara a identificar potenciales problemas de rendimiento.
El último scheduler, categorizado como VISIBLE ONLINE (DAC), esta reservado exclusivamente para la Dedicated Administrator Connection (Conexión Dedicada de Administrador). Simplificando mucho, SQL Server 2005 incorpora, por defecto, un nodo NUMA oculto, compuesto por una pequeña cantidad de memoria y un scheduler, que nos permiten retomar el control del servidor para hacer troubleshooting básico del sistema en caso de que los schedulers 'normales' no estén disponibles o se encuentren monopolizados.
El la práctica, lo habitual es filtrar la búsqueda para que no nos muestre ningún scheduler cuyo ID sea menor a 255, con lo que nos quedamos solo con los schedulers de usuario.
Complicando un poco más el asunto...
En realidad el modelo no es tan sencillo como una lista de tareas por scheduler. En SQL Server la planificación de tareas es bastante mas compleja, con una serie de listas adicionales. Nosotros vamos a ver a continuación un modelo un poco más real, introduciendo la lista de esperas por recursos (resource waits).
Si os fijáis en el diagrama que tenemos a continuación, vemos como el Scheduler contiene una tarea que se haya en estado Running (en ejecución). También vemos una lista de tareas listas para ejecución (Runnable Queue), que contiene las tareas que el Scheduler podría ejecutar en cualquier momento, ya que no dependen de recursos externos (disco, otras transacciones, etc.). Por último, tenemos la lista de esperas por recursos (resource waits list) que contiene todas las tareas que aun no se han completado pero que no pueden pasar a la CPU porque dependen de una espera por un recurso externo, como pueda ser un bloqueo, operaciones de sincronización de paralelismo, una operación de disco o de red, etc.
En la siguiente entrada hablaremos de las esperas, y profundizaremos en este diagrama con ejemplos prácticos. Por ahora me conformo con haber introducido los conceptos que darán pie a hablar de las esperas en SQL Server.
Canzando Mitos: Hyperthreading vs. SQL Server
No se si os acordáis, pero en un post reciente (el término reciente en mi vida 'bloggeril' es, como podréis apreciar, bastante relativo) hice un ejercicio descarado de plagio y posiblemente violé tres o cuatrocientas leyes al adoptar el tema de los 'cazadores de mitos' para buscar un escenario práctico real en el que se aplicara la teoría vista en la entrada. Pues bien, vamos a seguir cazando mitos... en esta ocasión será el siguiente: ¿Realmente tiene sentido deshabilitar Hyperthreading(*) en nuestros servidores SQL Server?
Vamos a ver lo que sucede en una máquina con una CPU con Hyperthreading 
habilitado. Vemos como tenemos una única CPU, con un único núcleo, y a pesar de todo, SQL Server crea dos schedulers. ¿Por que hace esto SQL Server? Pues sencillamente porque, como os comenté, SQL Server le pregunta a Windows, a través de WMI, cuantos procesadores tiene la máquina... y no sé quien tomo la decisión de diseño (o quién pagó a quien, ni cuanto...) pero el caso es que para Windows, una máquina con Hyperthreading habilitado cuenta como si tuviera dos CPUs.
Nosotros, que somos chicos y chicas listos, sabemos que no es cierto; sabemos que el Hyperthreading es, simplificando mucho, una mejora en en las Execution Units(EU) del procesador que permiten que, en ciertas circunstancias, se ejecute trabajo en estas EU que en otros casos estarían ociosas. Pero (y éste es un gran pero...) realmente solo hay una CPU: las diferentes EUs comparten, entre otras cosas, las cachés de procesador L1 y L2. Y hete aquí que tales cachés son importantes en SQL Server, hasta el punto de que si se comparten puede haber serios problemas en dos partes fundamentales del servidor: el mecanismo de agujas del reloj del lazy writer (que sirve para sacar del Buffer Pool las páginas menos utilizadas), y el mecanismo para adquirir spinlocks (bloqueos livianos que se solicitan mediante polling constante). Al compartir las caches, es muy posible que en operaciones paralelizadas las cachés no tengan la información que deben de tener, lo que puede provocar que se saquen del Buffer Pool páginas que no deberían irse fuera (con su consiguiente pérdida de rendimiento), o peor aún, que no se pueda adquirir un spinlock.
Sin entrar en demasiado detalle, quedémonos con la idea de que SQL Server ve dos procesadores, y crea dos schedulers, pero en realidad solo es un 'truco', interesante en otro tipo de aplicaciones pero muy dañino para SQL Server. ¿Conclusión? Al menos en SQL Server 2000 y 2005 os recomiendo encarecidamente deshabilitar Hyperthreading desde la BIOS si vuestros servidores lo soportan. No se aún si SQL Server 2008 implementa algún cambio que afecte a servidores Hyperthreaded, pero en cuanto consiga hacerme con una máquina con un procesador apropiado haré la prueba.
(*): Ahhh... hyperthreading, aquella tecnología que amenaza con volver a nuestros procesadores :_) ¡que malo soy a veces con los chicos de Intel.
Conclusiones
Bueno, nos ha salido otro post un poco 'ladrillo', pero hemos sentado una buena base a la que nos referiremos en posteriores entradas. Lo más importante aquí es comprender lo que son los schedulers, tener una aproximación a las diferentes listas de tareas y ver como podemos estudiar los metadatos para hacernos una idea de si el sistema esta sufriendo de contención de CPU o no.
Evidentemente en este artículo hemos realizado algunas simplificaciones (sobre todo en el ámbito de las diferentes listas de tareas), pero en siguientes entradas entraremos en más detalle, y si no me equivoco empezaremos precisamente con las esperas de las diferentes tareas, los estados que pueden tener y como pasan de uno a otro y, sobre todo, cómo podemos utilizar esa información para comprender por donde se nos escapa el rendimiento.
Espero que os resulte interesante :)
Rock Tip:
Ya os dije en la presentación de esta entrada que vamos a tener que tener un poco de paciencia antes de poder saltar a las esperas de SQL Server, por lo que me ha parecido apropiado preceder el titulo de este post con el temazo 'Patience' de los Guns n' Roses.
Hablando de los Guns N' Roses (que, por cierto, son/fueron uno de mis grupos favoritos), ahí va mi órdago: sí el señor William 'Axl' Rose Jr. y sus secuaces (sean quienes sean) sacan el 'Chinese Democracy' durante éste año del señor de 2008, un servidor se corta el pelo. ¿Mi predicción? El disco será la banda sonora del Duke Nukem Forever xD