Parametrizaciones forzadas
Puede reemplazar el comportamiento predeterminado de parametrización simple de SQL Server si especifica que, con algunas limitaciones, todas las instrucciones SELECT, INSERT, UPDATE y DELETE de una base de datos incluyan parámetros. La parametrización forzada se habilita al establecer la opción PARAMETERIZATION en FORCED en la instrucción ALTER DATABASE. Puede que la parametrización forzada mejore el rendimiento de determinadas bases de datos al reducir la frecuencia de las compilaciones y recompilaciones de consultas. Las bases de datos que pueden beneficiarse de la parametrización forzada suelen ser las que experimentan grandes volúmenes de consultas simultáneas de orígenes como las aplicaciones de punto de venta.
Cuando la opción PARAMETERIZATION se establece en FORCED, cualquier valor literal que aparezca en una instrucción SELECT, INSERT, UPDATE o DELETE, enviada de cualquier forma, se convierte en un parámetro durante la compilación de consultas. Las excepciones son los literales que aparecen en las siguientes construcciones de consulta:
Instrucciones INSERT...EXECUTE.
Instrucciones incluidas en el cuerpo de procedimientos almacenados, desencadenadores o funciones definidas por el usuario. SQL Server ya vuelve a utilizar planes de consulta para estas rutinas.
Instrucciones preparadas que ya incluyen parámetros en la aplicación del cliente.
Instrucciones que contienen llamadas a métodos XQuery, en las que el método aparece en un contexto en el que los argumentos suelen incluir parámetros, como una cláusula WHERE. Si el método aparece en un contexto en el que los argumentos no incluyen parámetros, el resto de la instrucción incluye parámetros.
Instrucciones incluidas en un cursor de Transact-SQL. (Las instrucciones SELECT contenidas en cursores de la API incluyen parámetros).
Construcciones de consulta desaprobadas.
Cualquier instrucción que se ejecute en el contexto de ANSI_PADDING o ANSI_NULLS establecido en OFF.
Instrucciones que contienen más de 2.097 literales aptos para parametrización.
Instrucciones que hacen referencia a variables, como WHERE T.col2 >= @bb.
Instrucciones que contienen la sugerencia de consulta RECOMPILE.
Instrucciones que contienen una cláusula COMPUTE.
Instrucciones que contienen una cláusula WHERE CURRENT OF.
Las siguientes cláusulas de consulta no incluyen parámetros. Tenga en cuenta que, en estos casos, las cláusulas son las únicas que no incluyen parámetros. Otras cláusulas de la misma consulta podrían ser candidatas para la parametrización forzada.
El literal <select_list> de cualquier instrucción SELECT. Incluye las listas SELECT de subconsultas y las listas SELECT incluidas en instrucciones INSERT.
Instrucciones SELECT de subconsulta que aparezcan dentro de una instrucción IF.
Las cláusulas TOP, TABLESAMPLE, HAVING, GROUP BY, ORDER BY, OUTPUT...INTO o FOR XML de una consulta.
Argumentos, directos o como subexpresiones, para OPENROWSET, OPENQUERY, OPENDATASOURCE, OPENXML o cualquier operador FULLTEXT.
Los argumentos pattern y escape_character de una cláusula LIKE.
El argumento style de una cláusula CONVERT.
Constantes de tipo entero incluidas en una cláusula IDENTITY.
Constantes especificadas mediante la sintaxis de extensiones ODBC.
Expresiones que admiten el doblado de constantes y son argumentos de los operadores +, -, *, / y %. Al considerar la posibilidad de que se pueda elegir la parametrización forzada, SQL Server tiene en cuenta que una expresión admite el doblado de constantes cuando se cumple alguna de las siguientes condiciones:
No aparecen columnas, variables ni subconsultas en la expresión.
La expresión contiene una cláusula CASE.
Para obtener más información acerca de las expresiones que admiten el doblado de constantes, vea Solucionar problemas de bajo rendimiento de las consultas: doblado de constantes y evaluación de expresiones durante la estimación de la cardinalidad.
Argumentos para cláusulas de sugerencias de consulta. Se trata del argumento number_of_rows de la sugerencia de consulta FAST, el argumento number_of_processors de la sugerencia de consulta MAXDOP y el argumento number de la sugerencia de consulta MAXRECURSION.
La parametrización se produce a nivel de instrucciones Transact-SQL individuales. En otras palabras, las instrucciones individuales de un lote incluyen parámetros. Tras la compilación, una consulta con parámetros se ejecuta en el contexto del lote en el que se envió originalmente. Si un plan de ejecución de una consulta se almacena en caché, puede determinar si la consulta incluía parámetros haciendo referencia a la columna sql de la vista de administración dinámica sys.syscacheobjects. Si una consulta incluye parámetros, los nombres y tipos de datos de parámetros se anteponen al texto del lote enviado en esta columna, como @1 tinyint. Para obtener más información acerca del almacenamiento de planes de consulta en la caché, vea Almacenar en caché y volver a utilizar un plan de ejecución.
[!NOTA]
Los nombres de parámetros son arbitrarios. Los usuarios o las aplicaciones no deben basarse en un determinado orden de nombres. Además, los siguientes elementos pueden cambiar de una versión a otra de SQL Server y de una actualización de Service Pack a otra: los nombres de parámetros, la selección de literales que incluyen parámetros y el espaciado en el texto con parámetros.
Tipos de datos de parámetros
Cuando SQL Server parametriza literales, los parámetros se convierten a los siguientes tipos de datos:
Los literales de tipo entero cuyo tamaño cabría de otro modo dentro del tipo de datos int se parametrizan a int. Los literales de tipo entero mayor que forman parte de predicados que incluyen algún operador de comparación (como <, <=, =, !=, >, >=, , !<, !>, <>, ALL, ANY, SOME, BETWEEN e IN) se parametrizan a numeric(38,0). Los literales de tipo mayor que no forman parte de predicados que incluyen operadores de comparación se parametrizan a numeric cuya precisión sea lo suficientemente grande como para admitir su tamaño y cuya escala sea igual a 0.
Los literales numéricos de punto fijo que forman parte de predicados que incluyen operadores de comparación se parametrizan a numeric, con una precisión de 38 y una escala lo suficientemente grande como para admitir su tamaño. Los literales numéricos de punto fijo que no forman parte de predicados que incluyen operadores de comparación se parametrizan a numeric, con una precisión y escala lo suficientemente grandes como para admitir su tamaño.
Los literales numéricos de punto flotante se parametrizan a float(53).
Los literales de cadena no Unicode se parametrizan a varchar(8000) si el literal cabe dentro de 8.000 caracteres y a varchar(max) si tiene más de 8.000 caracteres.
Los literales de cadena Unicode se parametrizan a nvarchar(4000) si el literal cabe dentro de 4.000 caracteres Unicode y a nvarchar(max) si el literal tiene más de 4.000 caracteres.
Los literales de tipo binario se parametrizan a varbinary(8000) si el literal cabe dentro de 8.000 bytes. Si tiene más de 8.000 bytes, se convierte a varbinary(max).
Los literales de tipo dinero se parametrizan a money.
Directrices para utilizar la parametrización forzada
Tenga en cuenta lo siguiente al establecer la opción PARAMETERIZATION en FORCED:
La parametrización forzada, en efecto, cambia las constantes literales de una consulta a parámetros al compilar una consulta. Por tanto, puede que el optimizador de consultas elija planes menos adecuados para las consultas. En concreto, es menos probable que el optimizador de consultas haga coincidir la consulta con una vista indizada o un índice de una columna calculada. Puede que también elija planes menos adecuados para consultas formuladas en tablas con particiones y vistas con particiones distribuidas. No se debe utilizar la parametrización forzada en entornos que se basan en su mayor parte en vistas indizadas e índices en columnas calculadas. Por lo general, sólo los administradores de bases de datos con experiencia deben utilizar la opción PARAMETERIZATION FORCED después de determinar que con ello no se afecta negativamente al rendimiento.
Las consultas distribuidas que hacen referencia a más de una base de datos se pueden elegir para la parametrización forzada siempre que la opción PARAMETERIZATION se establezca en FORCED en la base de datos en cuyo contexto se ejecuta la consulta.
Al establecer la opción PARAMETERIZATION en FORCED se vacían todos los planes de consulta de la caché de planes de una base de datos, excepto aquéllos que se estén compilando, recompilando o ejecutando en ese momento. Los planes de consulta que se compilen o ejecuten durante el cambio de opción incluyen parámetros la próxima vez que se ejecute la consulta.
El establecimiento de la opción PARAMETERIZATION es una operación en línea que requiere bloqueos no exclusivos de la base de datos.
La parametrización forzada se deshabilita (se establece en SIMPLE) cuando la compatibilidad de una base de datos de SQL Server se establece en 80, o cuando se adjunta una instancia anterior de una base de datos a una instancia de SQL Server 2005 o posterior.
El valor actual de la opción PARAMETERIZATION se mantiene al volver a adjuntar o restaurar una base de datos.
Puede reemplazar el comportamiento de parametrización forzada si especifica que se trate de realizar la parametrización simple en una sola consulta, y en cualquier otra que sea sintácticamente equivalente pero sólo se diferencie en los valores de parámetro. Por el contrario, puede especificar que se trate de forzar la parametrización sólo en un conjunto de consultas sintácticamente equivalentes, aunque la parametrización forzada esté deshabilitada en la base de datos. Se utilizan guías de planes con este fin. Para obtener más información, vea Especificar el comportamiento de parametrización de consultas por medio de guías de plan.
[!NOTA]
Cuando la opción PARAMETERIZATION está establecida en FORCED, la notificación de mensajes de error podría diferir de la parametrización simple: podrían notificarse varios mensajes de error en casos en los que se notificarían menos mensajes con la parametrización simple, y los números de línea en los que ocurren los errores podrían indicarse incorrectamente.