Blog gratis
Reportar
Editar
¡Crea tu blog!
Compartir
¡Sorpréndeme!
img
img

 

 

SQL Server
Blog. (Apuntes y Recopilaciones) por Norman M. Pardell

img
img

Query Optimizer. Parameter Sniffing. SQL Server. Tema: I

 El Query Optimizer es el responsable de obtener el plan de ejecución, de las consultas realizadas en nuestras bases de datos.

 

 Debido a la complejidad de SQL; todas las opciones existentes en un base de datos, tablas, índices, join… hay muchas formas de ejecutar una consulta. El trabajo del Optimizador es encontrar la mejor forma de resolver las consultas. Depende del Query Optimizer, el que una consulta tarde, por ejemplo, minutos o incluso horas frente a milisegundos en resolver la misma consulta. Del buen trabajo que realice el Optimizador depende en gran medida tener un buen Performance en nuestras consultas y aplicaciones.

 

 Es importante conocer la forma en que trabaja el Optimizador, para poder detectar problemas, en muchas ocasiones el Optimizador nos dirá cuales son los problemas para que nosotros los podamos resolver, y en otras ocasiones nosotros podemos ayudar al Optimizador a mejorar los planes de ejecución.

 

 Parameter Sniffing

 

 En esta sección, vamos a intentar optimizar la ejecución de los procedimientos almacenados mediante la creación de planes de ejecución compilados. Se va a tratar las condiciones que se indican en el Where de las consultas con @variables en lugar de valores fijos. Ya que si no usamos @variables, siempre se creará el plan de ejecución para esos valores fijos.

  

 Por ejemplo, dada la variable: @pid, en la siguiente consulta del procedimiento:

 

 CREATE PROCEDURE test (@pid int)

As

SELECT * FROM Sales..SalesOrderDetail

Where ProductID = @pid

 

 El Optimizador pude usar o partir de estas variables/parámetros (@pid), para intentar hacer el mejor plan de ejecución, para resolver una consulta. El Optimizador se basa en las estadísticas para intentar dar ese mejor plan de ejecución.

 

 Voy a tratar de comentar, con mediante un ejemplo, de una forma sencilla, como realiza este trabajo el Optimizador. Ya que cuando aumenta el numero de variables y/o parámetros, se complica la forma en como trabaja.

 

 Por ejemplo, si ejecutamos:

 

EXEC test @pid = 898

 

 Gracias a las estadísticas, podremos conocer cuales es el Estimated Number or Rowns (Numero de registros estimados). El Optimizador saca este valor de las estadísticas que esta acumulando o ha acumulado…

 

 Para conocer este valor, podemos ejecutar la consulta, (Donde “IX_SalesOrderDetail_ProductID“es el índice creado para la columna ProductID en la tabla SalesOrderDetail):

 

DBCC SHOW_STATISTICS (‘Sales.. SalesOrderDetail’, IX_SalesOrderDetail_ProductID)

 

 Podemos ver el Estimated Number or Rowns, en el histograma que devuelve la consulta, y ver que es “9”:


 Dependiendo del valor Estimated Number or Rowns (EQ_ROWS), para nuestro caso “9”, el Optimizador va ha tomar decisiones. Si el Optimizador obtiene un numero bajo de registros puede optar por usar un índice para realizar el plan de ejecución, mientras que si se obtiene un número elevado el Optimizador puede optar por realizar un full Scan, table Scan, Index Scan… Es importante que estos números estén actualizados (actualización de estadísticas).

 Se aconseja leer: Actualización de estadísticas síncronas o asíncronas mejoran la respuesta del optimizador de consultas. SQL Server 

 

 Hay que nombrar, que el Optimizador rehúsa los planes de ejecución ya creados con anterioridad. ¿Por qué se rehúsan los planes de ejecución? Porque optimizar una consulta consume muchos recursos de procesador y nos puede ser muy caro estar optimizando por cada ejecución. Básicamente lo que SQL Server hace, cuando el Optimizador crea un plan, es llevarlo a memoria, y SQL Server va a tratar de rehusar ese plan el mayor número de veces posibles. En ocasiones no es lo adecuado, ni la mejor opción, el rehusar ese mismo plan de ejecución.

 

 Una de las cosas que podemos hacer, para determinar si el plan de ejecución usado por una consulta es el adecuado, Antes de la ejecución de la consulta ejecutamos:

 

SET STATISTICS IO ON

 

  Y una vez ejecutada la consulta, gracias ha SET STATISTICS IO ON, nos devolverá una pestaña (Messages) donde encontraremos el valor de logical reads -> Paginas leídas. Logical reads, ha de ser inferior al numero de registros devueltos por la consulta (un sitio donde indica el numero de registros devuelto por la consulta, es en la esquina inferior derecha del Management Studio)

 

 Podemos estar sufriendo, que las mismas consultas, según los parámetros de las variables (para nuestro ejemplo @pid), en ocasiones tarden segundos y en otras ocasiones tarden minutos o incluso horas… ya que el plan de ejecución que esta en memoria para esa consulta, no es el adecuado para distintos valores/parámetros de las variables.

 

 Si pensamos que el plan de ejecución, que se esta rehusando (ya creado con anterioridad), no es el adecuado. Podemos borrar los planes de ejecución llevados a memoria, para evitar que los use, luego ejecutaremos nuevamente la consulta para que cree su nuevo plan de ejecución, consultando las estadísticas, con su valor actual.

 

Para borrar los planes de ejecución llevados a memoria ejecutaremos:

 

DBCC FREEPROCCACHE

 

 Lo anterior es una práctica que no debe hacerse en servidores de producción, si no en entornos de desarrollo cuando estamos intentando optimizar una consulta.

 

 Para adaptar las consultas, a unos correctos planes de ejecución, pues podemos tener tiempos muy diferentes de ejecución, ya que los parámetros de las variables cambian…, hay diferentes técnicas. Voy a comentar algunas de ellas:

 

 Una de las opciones es usar OPTIMIZE FOR, disponible en versiones de SQL Server 2005 en adelante.

 

 Si hemos detectado que las mayorías de nuestras consultas se benefician de la ejecución de un plan de ejecución, podemos decir al Optimizador de Consultas, que use ese plan de ejecución (por ejemplo para el valor “898”), podremos decir que la primera vez que ejecute este procedimiento lo haga para el valor 898 sea cual sea su valor. Ya que el objetivo es rehusar planes de ejecución, elevamos a memoria el plan de ejecución que deseamos, (ante posibles reinicios de instancias o cualquier acción que elimine los planes de ejecución elevados a memoria), con la primera ejecución:

 

ALTER PROCEDURE test (@pid int)

As

SELECT * FROM Sales..SalesOrderDetail

Where ProductID = @pid

OPTION (OPTIMIZE FOR (@pid = 898))

 

 

 Si por el contrario, para la consulta, no tenemos un valor que predomine, como el caso anterior, sino que tenemos un numero X de valores (para nuestro ejemplo @pid) que se ejecutan con las misma frecuencia. No podemos escoger un plan…  por lo que podremos decantarnos por: OPTION (RECOMPILE), opción que fuerza a que se recompile y se cree el plan para cada ejecución. No nos beneficiaremos de rehusar los planes ya creados en memoria, y esto conlleva algo de penalización en los tiempos de ejecución, a tener en cuenta cuando una consulta se ejecuta cientos de veces…. El procedimiento quedaría de la forma:

 

ALTER PROCEDURE test (@pid int)

As

SELECT * FROM Sales..SalesOrderDetail

Where ProductID = @pid

OPTION (RECOMPILE)

 

 Tendremos que hacer un balance de cual es la mejor opción para nuestra aplicación.

 

 Otra de las opciones, mencionar que no es muy recomendada, a no ser que estemos seguro que nos beneficia, es evitar que, al crear el plan de ejecución, el Optimizador use el histograma de las estadísticas, entonces usara valores generales de las estadísticas, estos valores se llaman Densidad. Cada ejecución no importa el valor que tome las variables (para nuestro ejemplo @pid), por que siempre creará el mismo plan de ejecución para todos valores en la misma consulta. De esta forma ahorraremos tiempo de ejecución. Esto se puede hacer por medio de variables locales o usando OPTIMIZE FOR UNKNOWN

 

Variables locales:

 

ALTER PROCEDURE test (@pid int)

As

Declare @p int = @pid

SELECT * FROM Sales..SalesOrderDetail

Where ProductID = @p

 

OPTIMIZE FOR UNKNOWN (solo disponible en versiones SQL 2008 y superiores):

 

ALTER PROCEDURE test (@pid int)

As

SELECT * FROM Sales..SalesOrderDetail

Where ProductID = @pid

OPTION (OPTIMIZE FOR UNKNOWN)

 

 

 Os dejo el enlace, para aquellos a quienes les interese es tema, donde se habla de: 

Query Optimizer. Cardinality Estimation Error. SQL Server. Tema: II

 

Fuentes:

Microsoft, MSDN, Benjamin Nevares

Apunte y recopilación por Norman M. Pardell

Puedes consultarme, si deseas cualquier aclaración, pregunta o sugerencia en: Contacto, contestaré tan pronto como me sea posible.

publicado por normanmpardell a las 20:14 · Sin comentarios  ·  Recomendar
Comentarios (0) ·  Enviar comentario
Esta entrada no admite comentarios.
img
.Sobre mí
FOTO

Norman M. Pardell

MCITP: Database Administrator & Database Developer, SQL Server 2008. MCC Award Certificate. Consultor Senior de bases de datos en Capgemini España, S.L. Asesoramiento en implementación, desarrollo y gestión de bases de datos en grandes compañías. Actualmente, asignado a proyecto en compañía líder en el sector energético global. Más de 10 años trabajando con SQL Server (y otros gestores de BBDD)

» Ver perfil

img
.Secciones
» Inicio
img
.Enlaces
» Microsoft MSDN Foros
» Windows Server 2012
img
.Más leídos
» Asignar la cantidad correcta de Memoria para SQL Server
» Base de Datos Sospechosa (Suspect)
» Como modificar la Intercalación (Collation) en SQL Server
» Detectar bloqueos. SQL Server V.2005 y superiores
» Funciones SQL Server. Funciones escalares y funciones con valores de tabla.
» Integridad y corrupción en las bases de datos: DBCC CHECKDB
» Log de transacciones ( .ldf ). SQL Server.
» Migrando SQL Server 2005, 2008, 2008 R2 a SQL Server 2012
» Tomo I. Memoria RAM. Optimización de sistemas de 32 y 64 bits. SQL Server 2008.
» Transacciones activas. SQL server 2008
img
.Nube de tags [?]
                                                           
img img
FULLServices Network | Crear blog | Privacidad