Compartir a través de

  • Artículo

Usar la sugerencia de consulta USE PLAN en consultas con cursores

Se puede utilizar la sugerencia de consulta USE PLAN con consultas que especifican solicitudes de cursores. En la tabla siguiente se muestran las combinaciones de opciones de desplazamiento de cursor admitidas con USE PLAN para los cursores de servidor API, los cursores de Transact-SQL que utilizan la sintaxis extendida de Transact-SQL y los cursores de Transact-SQL que utilizan la sintaxis ISO.

Opción SCROLL (valor @scrollopt para los cursores de servidor de la API)

USE PLAN compatible con los cursores de servidor de la API

USE PLAN compatible con los cursores de Transact-SQL que utilizan la sintaxis extendida de Transact-SQL

USE PLAN compatible con los cursores de Transact-SQL que utilizan la sintaxis ISO

STATIC

Y

Y

No aplicable

DYNAMIC

N

N

No aplicable

KEYSET

N

N

No aplicable

FORWARD_ONLY

N

N

No aplicable

FAST_FORWARD

Y

Y

No aplicable

FORWARD_ONLY STATIC

No aplicable

Y

No aplicable

INSENSITIVE

No aplicable

No aplicable

Y

Las consultas con cursores tienen dos planes de consulta asociados, en lugar de un solo plan asociado con consultas enviadas sin cursores. Estos planes pueden ser de tipo OPEN, FETCH o REFRESH, según el tipo del cursor.

Uno de los dos planes de un cursor se genera directamente a partir de la consulta de entrada y el otro se genera automáticamente. Estos planes se denominan respectivamente plan de consulta de entrada y plan generado. En la tabla siguiente se muestran los planes que se generan para los cursores FAST_FORWARD y STATIC (INSENSITIVE).

Tipo de cursor

Plan de cursor Open

Plan de cursor Fetch

Plan de cursor Refresh

FAST_FORWARD

No aplicable

Consulta de entrada

Generado

STATIC

Consulta de entrada

Generado

No aplicable

En ocasiones, los planes de una consulta XML para una consulta de cursor aparecen como un solo documento XML que contiene ambos planes. Estos planes se denominan planes de dos partes.

En ocasiones, los planes de un cursor también aparecen como dos planes distintos. Por ejemplo, en una traza del SQL Server Profiler para un plan de consulta de cursor STATIC API o de Transact-SQL, observará que se generan dos eventos Showplan XML For Query Compile distintos. En este caso, sólo el plan de consulta de entrada (OPEN) es significativo para forzar un plan. En una sugerencia USE PLAN se debe utilizar el plan de consulta de entrada. También se crea un plan simple (FETCH) generado, pero no es obligatorios o no se permite para forzar un plan. Reconocerá el plan de consulta de entrada (OPEN) porque es el que recopila en primer lugar el conjunto de filas que coinciden con la consulta de cursor.

Nota importanteImportante

No intente forzar un plan sin cursor para una consulta de cursor o viceversa. Se producirá un error aunque la consulta de cursor y la consulta sin cursor sean la misma.

Los siguientes tipos de salidas de plan de consulta XML que describen planes de cursor se pueden utilizar para forzar un plan con USE PLAN en determinados tipos de cursor:

  • Un plan de dos partes para el cursor

  • Un plan de consulta de entrada de una parte para el cursor

El plan de cursor exigido puede ser un plan obtenido mediante cualquiera de los siguientes mecanismos de obtención de un plan de consulta XML:

  • Eventos de traza del SQL Server Profiler basados en XML. Estos eventos pueden incluir Showplan XML, Showplan XML For Query Compile y Showplan XML Statistics Profile.

  • SET SHOWPLAN_XML ON

  • SET STATISTICS XML ON

  • Funciones y vistas de administración dinámica, como la consulta siguiente:

    SELECT *
FROM sys.dm_exec_query_stats 
CROSS APPLY sys.dm_exec_query_plan(plan_handle)

Observar el uso por parte de las aplicaciones de cursores de servidor de la API

Las aplicaciones DB Library, ODBC, ADO y OLEDB interactúan a menudo con SQL Server mediante cursores de servidor de la API. Puede ver qué llamadas se envían a los procedimientos almacenados de cursores de servidor de la API examinando los eventos RPC:Starting del SQL Server Profiler cuando se ejecuta una aplicación que se crea mediante una de estas interfaces.

Ejemplo: forzar un plan en una consulta con un cursor

Este ejemplo supone que se está utilizando una aplicación que interactúa con la base de datos AdventureWorks2008R2 mediante cursores ODBC, y que se desea forzar el plan para una consulta enviada a SQL Server mediante una rutina de cursor de servidor de la API. Para forzar el plan, recopile un plan para una consulta enviada mediante una rutina de API de cursores y, a continuación, cree una guía de plan para exigir el plan de esa consulta. Vuelva a ejecutar la consulta con la aplicación y examine el plan para comprobar que se ha forzado.

Paso 1: recopilar el plan

Inicie una traza del SQL Server Profiler y seleccione los eventos Showplan XML y RPC:Starting. Ejecute con la aplicación la consulta para la que desea forzar el plan. Haga clic en el evento RPC:Starting generado. Suponga que el evento RPC:Starting tiene los datos de texto siguientes:

DECLARE @p1 int
SET @p1=-1
DECLARE @p2 int
SET @p2=0
DECLARE @p5 int
SET @p5=8
DECLARE @p6 int
SET @p6=8193
DECLARE @p7 int
SET @p7=0
EXEC sp_cursorprepexec @p1 OUTPUT,@p2 OUTPUT,NULL,N'SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d WHERE h.SalesOrderID = d.SalesOrderID AND h.OrderDate BETWEEN ''20030101'' AND ''20040101''',@p5 OUTPUT,@p6 OUTPUT,@p7 OUTPUT
SELECT @p1, @p2, @p5, @p6, @p7

Recopile el plan para la consulta haciendo clic con el botón secundario en el evento de traza Showplan XML que contenga el plan de consulta de entrada para la consulta que aparece como un argumento en la instrucción sp_cursorprepexec anterior y, a continuación, seleccione Extraer datos de evento. Guarde los datos de evento (un plan de presentación XML) en un archivo CursorPlan.SQLPlan en el escritorio. Copie el archivo CursorPlan.SQLPlan en CursorPlan.txt. En SQL Server Management Studio, abra CursorPlan.txt en una ventana de editor. Para ahorrar tiempo más adelante, utilice Buscar y reemplazar para reemplazar las comillas simples (') del plan por cuatro comillas simples (''''). Guarde CursorPlan.txt.

Paso 2: crear la guía de plan para forzar el plan

Cree una guía de plan escribiendo y ejecutando la siguiente instrucción sp_create_plan_guide para exigir el plan. Esta definición de guía de plan incluye el plan XML capturado en el paso anterior en una sugerencia de consulta USE PLAN de la guía de plan.

Cuando escriba esta definición de guía de plan, pegue el contenido de CursorPlan.txt en la posición apropiada en el argumento @hints (justo después de OPTION(USE PLAN N'').

exec sp_create_plan_guide 
@name = N'CursorGuide1',
@stmt = N'SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d WHERE h.SalesOrderID = d.SalesOrderID AND h.OrderDate BETWEEN ''20030101'' AND ''20040101''',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION(USE PLAN N''<ShowPlanXML xmlns=''''https://schemas.microsoft.com/sqlserver/2004/07/showplan'''' Version=''''0.5'''' Build=''''9.00.1116''''><BatchSequence><Batch><Statements><StmtSimple>
   …
</StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>'')'

Paso 3: ejecutar la consulta y comprobar que se ha aplicado la guía de plan a la misma

Vuelva a ejecutar la consulta con la aplicación y recopile su plan de ejecución XML mediante el evento XML Showplan en el SQL Server Profiler.

Haga clic en el evento XML Showplan para el plan. Observará que el plan es el exigido en la guía de plan.

Consultas de cursor con parámetros

Si la consulta de cursor de servidor de la API para la que desea crear una guía de plan tiene parámetros, asegúrese de incluir tanto la cadena de instrucción como la cadena de definición de parámetros del evento RPC:Starting del SQL Server Profiler en la definición de guía de plan. También es necesaria la cadena de definición de parámetros para obtener una coincidencia correcta de guía de plan, al igual que cuando se realizan consultas con parámetros enviadas mediante sp_executesql.