Como detectar cuando las consultas no parametrizadas dañan el rendimiento de SQL Server y que hacer

Que no parametrizar las consultas es fuente de problemas es algo que cada vez más desarrolladores conocen. Se ha hecho mucha labor didáctica sobre este tema en los últimos tiempos. Pero nunca esta de más recordar estos problemas. A raíz de un caso que hemos tenido recientemente en el Debuging & Optimization Team de Plain Concepts, me he decidido a hablar de este tema.

Si no usamos consultas parametrizadas los problemas principalmente son dos:

Uno de seguridad, puesto que nuestras consultas serán susceptibles de sufrir ataques por inyección de SQL. Este no es el que me ocupa hoy. Aunque es un tema que ya traté con anterioridad.

Otro de rendimiento, puesto que nuestras consultas no serán capaces de reutilizar el plan de ejecución de consultas similares. Este es el problema que hoy me ocupa. Si no se reutilizan los planes de ejecución al coste de ejecutar una consulta habrá que sumar siempre el coste de buscar el plan de ejecución adecuado.

Si bien es cierto que gracias a dios cada vez es menos frecuente, no es menos cierto que millones de aplicaciones, muchas de ellas desarrolladas antes de que tomásemos consciencia del problema, sufren problemas relacionados con las consultas no parametrizadas.

¿Qué diferencia una consulta parametrizada de una no parametrizada?

Cuando usamos consultas no parametrizadas, típicamente, usamos la concatenación de cadenas para variar la consulta que enviamos a SQL Server desde nuestro programa. Podéis ver esta técnica en la siguiente porción de pseudo código. En este caso SQL Server no puede saber que realmente estamos ejecutando la misma consulta.

int ContactID = 100;

 

string sql =

    “SELECT FirstName, EmailAddress FROM Person.Contact” +

    “WHERE ContactID = “ + ContactID.ToString();

 

ExecuteSql(sql);

Sin embargo cuando usamos consultas parametrizadas, en lugar de concatenar el valor de variables como cadenas, escribimos nuestras sentencias utilizando parámetros y luego ejecutamos la consultas asignando previamente valores a los parámetros. SQL Server siempre verá la misma consulta, podrá detectar la situación y reutilizar el plan de ejecución. El código en este caso sería algo similar a:

int ContactID = 100;

 

string sql =

    “SELECT FirstName, EmailAddress FROM Person.Contact “ +

    “WHERE ContactID = @ContactID”;

 

using (SqlConnection cn = new SqlConnection(connectionString))

using (SqlCommand cmd = new SqlCommand(sql, cn))

{

    cn.Open();

    SqlParameter param = new SqlParameter(“@ContactID”, ContactID);

    cmd.Parameters.Add(param);

    cmd.ExecuteNonQuery();

}

SQL Server asocia los planes de ejecución con la cadena SQL que debe asociar en cada caso, si para consultas similares, que solo se diferencian en parámetros, utilizamos sentencias diferentes fruto de la concatenación de cadenas, SQL Server no será capaz de saber que puede reutilizar el plan de ejecución.

¿Cómo detectar que nuestro SQL Server está sufriendo por culpa de las consultas no parametrizadas?

Tenemos varias técnicas y hay varias pistas que nos pueden llevar a concluir que tenemos este problema en nuestro servidor y que el rendimiento del mismo se está viendo penalizado.

 

 

Podemos usar los contadores de rendimiento de SQL Server:

Contadores de Rendimiento - Queries no parametrizadas

Fijaros en la situación que podemos ver en la pantalla anterior. Mirando el contador SQLServer:SQL Statitics:SQLCompilations/sec vemos que coincide con el número de SQLServer:SQL Statitics:Batch Requests/sec, la conclusión es clara: todas las select que estamos lanzando están provocando la generación de un nuevo plan de ejecución, fuente clara de perdida de rendimiento. Una relación cercana a uno entre estos dos contadores es, casi siempre, síntoma inequívoco de que nuestras consultas no están parametrizadas. Este diagnóstico se ve apoyado por el contador SQLServer:SQL Statitics:Auto-Param Attemps/sec.

 

Si miramos además los contadores relativos a la cache de planes vemos que tenemos un Cache Hit Ratio muy bajo, de más o menos el 50% para los planes de ejecución (SQL Plans) y también podemos observar que casi toda la cache está ocupada por SQL Plans.

También podemos observar el contenido de la cache con la consulta que podéis ver en la siguiente captura:

SysCacheObjects - Consultas no parametrizadas

Se puede observar que todos los planes corresponden a la misma consulta y que esta sería fácilmente parametrizable.

Otro mecanismo para identificar esta situación es ver como se reparte la cache, por ejemplo con la siguiente consulta:

Ocupación de la cache de SQL Server 
Podemos ver en los resultados de la ejecución que muestro en la imagen que tenemos la friolera de 0,92 Mb en 38008 planes de ejecución. Sin duda otro mal síntoma que apunta a una pésima reutilización de planes de ejecución.

También podemos ver esta misma información de forma gráfica utilizando los informes de SQL Server:

Informe de memoria - consultas no parametrizadas

Si la cache de SQL Server se llena de planes de ejecución, un problema adicional que penaliza el rendimiento, es que no tendremos capacidad para cachear otro tipo de objetos como por ejemplo páginas de datos.

Ahora que ya sabemos como detectar la situación…

¿Qué podemos hacer?

Desde SQL Server 2005 tenemos una utilísima característica que puede ayudarnos a mejorar el rendimiento de nuestro servidor. Se trata la parametrización forzada. Cuando la opción Parametrización de se establece en Forzada, cualquier valor literal que aparezca en una instrucción SELECT, INSERT, UPDATE o DELETE, enviada de a nuestro servidor, se convierte en un parámetro durante la compilación de consultas. Esta opción se establece a nivel de base de datos, bien desde la interfaz de usuario:

Establecer parametrización forzada

O con la sentencia:

ALTER DATABASE [NombreDeBaseDeDatos] SET PARAMETERIZATION FORCED

Por defecto la paremetrización forzada no está activada, el motivo es que lógicamente tiene un coste, y que solo supone un beneficio en los casos en los que las aplicaciones están lanzando consultas no parametrizadas a la base de datos.

Corolario

  • Las consultas no parametrizadas son malas para la seguridad y el rendimiento de nuestro SQL Server. Siempre debemos usar consultas parametrizadas, no valen disculpas.
  • SQL Server asocia los planes de ejecución con la cadena SQL que debe asociar en cada caso, si para consultas similares, que solo se diferencian en parámetros, utilizamos sentencias diferentes fruto de la concatenación de cadenas, SQL Server no será capaz de saber que puede reutilizar el plan de ejecución.
  • Contamos con numerosas posibilidades para comprobar si las consultas no parametrizadas están afectando al rendimiento de nuestro servidor.
  • A veces hacer que aplicaciones ya existentes pasen a usar consultas parametrizadas supone cambiar miles de líneas de código.
  • Podemos evitar el impacto sobre el rendimiento de las consultas no parametrizadas estableciendo la parametrización forzada de nuestra base de datos.
  • La parametrización forzada no arreglara los problemas de seguridad.

¡Espero que os sea de ayuda!