Planes de ejecucion y operadores de costo

PUNTO 2

Planes de Ejecución y Operadores Costosos (Expensive Operators) en SQL Server
Como sabemos, el comportamiento general de SQL Server al ejecutar un procedimiento almacenado o una consulta SQL, implica que en la primera ejecución se genera el Plan de Ejecución de la consulta o procedimiento almacenado a ejecutar, en un proceso denominado Compilación, tras lo cual, dicho Plan deEjecución será cacheado (almacenado en memoria RAM, en una zona de memoria específica para ello, denominada PROC CACHE).  De este modo, la próxima vez que volvamos a ejecutar esa misma consulta SQL o procedimiento almacenado, no será necesario volver a generar el Plan de Ejecución, con vistas a optimizar así la utilización de los recursos de SQL Server. Evidentemente, bajo ciertas circunstancias un Plande Ejecución puede volverse inválido, de tal modo que en una próxima ejecución necesite volver a compilarse (ej: cambios en las tablas o índices utilizados por el Plan de Ejecución, actualizaciones de las estadísticas, etc.).
Antes de seguir, aclarar que en SQL Server:
* Existe una zona de memoria denominada Buffer Pool, la cual almacena en memoria RAM una copia de las páginas de datos de losficheros de nuestras bases de datos, para minimizar el acceso a disco, y así, mejorar el rendimiento. Por darle un punto de vista práctico, en ocasiones para realizar Tunning de consultas, es útil utilizar el comando DBCC DROPCLEANBUFFERS para vaciar el Buffer Pool y probar nuestras consultas en igualdad de condiciones (es decir, evitar que una consulta parezca más rápida porque encuentra todaslas páginas en Caché, en lugar de por ser una consulta realmente más óptima). La recomendación es ejecutar un CHECKPOINT y posteriormente ejecutar el DBCC DROPCLEANBUFFERS.
* Existe otra zona de memoria denominada PROC CACHE, la cual almacena los Planes de Ejecución utilizados por SQL Server, con el objetivo de poder reutilizarlos. Ya hemos tocado ligeramente este tema alguna vez, como porejemplo, para reducir TEMPDB utilizando el comando DBCC FREEPROCCACHE.
* Existe otra zona de memoria denominada Query Memory o Workspace Memory, la cual es utilizada para asignar memoria a las consultas SQL que necesitan ejecutarse, con el objetivo de utilizarla para almacenar datos temporales, típicamente para ordenaciones y uniones de filas. Por defecto, el tamaño total de la Query Memory oWorkspace Memory de una instancia de SQL Server es el 75% de la memoria RAM de dicha instancia (ej: si asignamos 8GB de RAM a una instancia de SQL Server, la Query Memory será de unos 6GB). Por verlo en un ejemplo, si una consulta compleja es ejecutada por SQL Server, almacenará su Plan de Ejecución en la PROC CACHE, las páginas de tablas e índices se almacenarán en el Buffer Pool, y la memorianecesaria para ejecutarse (ej: para realizar ordenaciones) se tomará de la Query Memory o Workspace Memory.
Continuamos.
El Plan de Ejecución de una consulta o procedimiento almacenado, indica a SQL Server cómo debe procesar dicha consulta o procedimiento almacenado, especificando detalles cómo qué índices se deben utilizar, cómo se debe acceder a dichos índices (ej: INDEX SEEK  o INDEX SCAN), utilizaro no paralelismo (múltiples procesadores), cuanta memoria RAM es necesariapara la ejecución, que algoritmos utilizar para mezclar diferentes conjuntos de resultados, etc.
Básicamente, podemos entender un Plan de Ejecución como un conjunto de Operaciones, las cuales son ejecutadas en cierto orden por el motor de SQL Server para obtener el resultado de la consulta o procedimiento almacenado que sedesea ejecutar. Algunas de estas Operaciones se conocen como Operaciones Costosas (Expensive Operators), pues implican un consumo de recursos mucho más elevado que el resto de Operaciones que podemos encontrar en un Plan de Ejecución, especialmente en lo relacionado a necesidades de memoria (en concreto, de Query Memory o Workspace Memory). En particular, estamos hablando de Operadores como:...