Comencé esta serie con el post titulado: Little Bobby Tables, SQL Injection y EXECUTE AS. Luego pasé a discutir algunas de las diferencias con el post titulado: EXEC y sp_executesql – ¿en qué se diferencian?
Hoy, quiero abordar algunos de los comentarios, así como continuar con algunos consejos y trucos usando estos comandos.
En primer lugar – ¿podríamos haber ayudado al rendimiento de la sentencia sp_executesql?
Sí…
Si sabemos que una sentencia devuelve una cantidad variable de datos (en función de los parámetros suministrados) entonces podemos utilizar la característica de SQL Server 2005 WITH RECOMPILE para decirle a SQL Server que la sentencia que se está ejecutando debe tener su propio plan creado y que los planes anteriores (si existen) no deben reutilizar la sentencia. También le dice a SQL Server que este plan en particular es un «plan de uso único» que no debe ser reutilizado por usuarios posteriores. Para ver la combinación de todas estas cosas – usaré algunos de los DMVs que rastrean la caché del plan y la utilización del plan.
DBCC FREEPROCCACHE
GO
SELECT st.text, qs.EXECUTION_COUNT -, qs.*, cp.*
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS cp
WERE st.text like ‘%FROM dbo.member%’
GO
Ahora mismo, esta consulta devuelve 0 filas.
Ejecutaré lo siguiente y luego volveré a comprobar la caché del plan:
DECLARE @ExecStr nvarchar(4000)
SELECT @ExecStr = ‘SELECT * FROM dbo.member WHERE lastname LIKE @lastname’
EXEC sp_executesql @ExecStr, N’@lastname varchar(15)’, ‘Tripp’
GO
Ahora, tenemos una fila para nuestro plan de consulta parametrizado:
text EXECUTION_COUNT(@lastname varchar(15))SELECT * FROM dbo.member WHERE lastname LIKE @lastname 1
Entonces, lo que nos muestra esto… es que hay un plan en la caché para esta sentencia. Y, si nos interesa ver el plan, podemos eliminar el cp.* comentado en la consulta anterior para obtener la columna cp.query_plan. Hacemos clic en un showplan XML y SSMS irá DIRECTAMENTE a una ventana gráfica de plan de consulta (2008 en adelante):
Y, una vez más, vemos el plan óptimo (utilizar el índice y hacer una búsqueda de marcadores) porque esta consulta es muy selectiva (sólo 1 fila).
Volvemos a ejecutar exactamente la misma sentencia – utilizando el valor de Anderson sólo para llegar a la configuración en la que estábamos la semana pasada:
DECLARE @ExecStr nvarchar(4000)
SELECT @ExecStr = ‘SELECT * FROM dbo.member WHERE lastname LIKE @lastname’
EXEC sp_executesql @ExecStr, N’@lastname varchar(15)’, ‘Anderson’
GO
Y, vemos que utiliza EXACTAMENTE el mismo plan (mirando showplan). De hecho, lo podemos ver al comprobar nuestra caché de planes también:
text EXECUTION_COUNT(@lastname varchar(15))SELECT * FROM dbo.member WHERE lastname LIKE @lastname 2
Y, aunque sabemos que este plan (para usar el índice) es bueno para el valor altamente selectivo de ‘Tripp’ no es bueno para el valor de Anderson ya que hay muchas filas que coinciden. Si sospecháramos esto y/o lo supiéramos cuando estamos ejecutando (desde el cliente) entonces podríamos usar la OPCIÓN (RECOMPILAR) para forzar a SQL Server a obtener un nuevo plan:
DECLARE @ExecStr nvarchar(4000)
SELECT @ExecStr = ‘SELECT * FROM dbo.member WHERE lastname LIKE @lastname OPTION (RECOMPILE)’
EXEC sp_executesql @ExecStr, N’@lastname varchar(15)’, ‘Anderson’
go
El plan de consulta como resultado de esta ejecución es:
.png)
Y, este es el plan más óptimo dado este parámetro. Llegados a este punto, las preguntas (IMO) son:
- ¿Podría alguien realmente estimar programáticamente que un parámetro enviado desde el cliente es «atípico» y/o que justifica la recompilación? Y, supongo que puedo responder que sí – para algunos parámetros – como los que tienen un comodín. Pero, si sólo estamos hablando de dos valores diferentes en contra de una sola columna, esto sería adivinar las estadísticas de los datos.
- ¿Debería esta declaración particular siempre se ejecuta para recompilar y nunca guardar un plan?
- ¿Qué hizo SQL Server con el plan en sí?
Voy a ir con la respuesta #3 primero como que uno es fácil de responder. Utilizando la misma sentencia, volveré a consultar la caché del plan:
text EXECUTION_COUNT(@lastname varchar(15))SELECT * FROM dbo.member WHERE lastname LIKE @lastname 2
Aunque es la tercera vez que ejecutamos esta sentencia, esta última ejecución NO se puso en caché. Se utilizó únicamente para la ejecución con OPCIÓN (RECOMPILAR). Y, NO afectará a futuras ejecuciones. Si vuelvo y ejecuto sin la OPCIÓN (RECOMPILAR) entonces obtendré el plan anterior (para usar el índice).
Ahora, las otras dos preguntas – son mucho más interesantes y es donde creo que se deben usar los procedimientos almacenados. Personalmente, creo que los desarrolladores que conocen los datos y conocen la aplicación – serán mucho mejores en la creación del código CORRECTO especialmente cuando entienden todas las opciones disponibles para ellos.
Esta es la forma en que pienso acerca de los procedimientos almacenados y la recompilación:
- Si sé que una declaración particular siempre devuelve el mismo número de filas y utiliza el mismo plan (y, lo sabría por las pruebas), entonces voy a crear el procedimiento almacenado normalmente y dejar que el plan se almacene en caché.
- Si sé que una sentencia concreta varía mucho de una ejecución a otra y el plan óptimo varía (de nuevo, debería saberlo por haber probado múltiples ejecuciones de ejemplo), entonces crearé el procedimiento almacenado normalmente y utilizaré OPTION (RECOMPILE) para asegurarme de que el plan de la sentencia no se almacena en caché ni se guarda con el procedimiento almacenado. En cada ejecución ese procedimiento almacenado obtendrá diferentes parámetros y la sentencia particularmente desagradable obtendrá un nuevo plan en cada ejecución.
Sin embargo, aquí es también donde las cosas se vuelven más desafiantes. A menudo he visto procedimientos almacenados en los que la gente trata de utilizar la lógica condicional para romper los diferentes tipos de parámetros y esto no suele funcionar tan bien como se esperaba (voy a bloguear esto a continuación). Y, esto es siempre donde algunos deciden que quieren construir dinámicamente la declaración que se ejecuta – ahora, tenemos que determinar si debemos o no utilizar sp_executesql o EXEC. Y, realmente hay un par de opciones en este punto. En última instancia, en uno o dos posts más – finalmente te mostraré donde EXEC es un claro ganador sobre sp_executesql porque incluso la OPCIÓN (RECOMPILE) no siempre ayuda a TODOS los tipos de planes (y especialmente uno de los tipos más comunes de planes que veo).