Usando a dica de consulta USE PLAN em consultas com cursores
Você pode usar a dica de consulta USE PLAN com consultas que especificam solicitações de cursor. A tabela seguinte mostra as combinações de opções de rolagem de cursor que dão suporte com USE PLAN para cursores de servidor API, cursores Transact-SQL que usam Transact-SQL Sintaxe estendida, e cursores Transact-SQL que usam Sintaxe ISO.
Opção de rolagem (@ valor scrollopt para cursores de servidor API) |
USE PLAN dá suporte a cursores de servidor API |
USE PLAN dá suporte para cursores Transact-SQL que usam Sintaxe estendida do Transact-SQL |
USE PLAN dá suporte para cursores Transact-SQL que usam Sintaxe ISO |
|---|---|---|---|
STATIC |
Y |
Y |
Não aplicável |
DYNAMIC |
N |
N |
Não aplicável |
KEYSET |
N |
N |
Não aplicável |
FORWARD_ONLY |
N |
N |
Não aplicável |
FAST_FORWARD |
Y |
Y |
Não aplicável |
FORWARD_ONLY STATIC |
Não aplicável |
Y |
Não aplicável |
INSENSITIVE |
Não aplicável |
Não aplicável |
Y |
Consultas com cursores têm dois planos de consulta associados a eles, em vez do único plano associado a consultas submetidas sem cursores. Estes planos podem ser do tipo OPEN, FETCH ou REFRESH, dependendo do tipo do cursor.
Um dos dois planos para um cursor é gerado diretamente da consulta de entrada e o outro é gerado automaticamente. Estes planos são chamados de plano de consulta de entrada e plano gerado, respectivamente. A tabela a seguir mostra os planos que são gerados para FAST_FORWARD e cursores STATIC (INSENSITIVE).
Tipo de cursor |
Abrir plano de cursor |
Buscar plano de cursor |
Atualizar plano de cursor |
|---|---|---|---|
FAST_FORWARD |
Não aplicável |
Consulta de entrada |
Gerado |
STATIC |
Consulta de entrada |
Gerado |
Não aplicável |
Os planos de consulta XML para uma consulta de cursor às vezes aparecem como um único documento XML que contém ambos os planos. Estes planos são chamados planos de duas partes.
Os planos para um cursor às vezes também aparecem como dois planos separados. Por exemplo, em um rastreamento SQL Server Profiler para um STATIC API ou Transact-SQL plano de consulta de cursor, você pode consultar que dois eventos diferentes do Plano de execução XML para Query Compile são gerados. Nesse caso, somente o plano de consulta (OPEN) de entrada é significante para a ação de imposição de plano. Você deve usar o plano de consulta de entrada em uma dica USE PLAN. Um plano gerado (FETCH) simples também é criado, mas não é exigido ou permitido, para ação de imposição de plano. Você pode reconhecer o plano de consulta (OPEN) de entrada porque é o plano que primeiro reúne o conjunto de linhas que correspondem à consulta de cursor.
.gif "Observação importante") Importante |
|---|
Não tente impor um plano não cursor para uma consulta de cursor ou vice-versa. A ação de imposição de plano pode falhar se você o fizer, até mesmo se a consulta de cursor e a consulta de não cursor for a mesma. |
Os tipos a seguir do plano de consulta XML de saídas que descrevem planos de cursor podem ser usados para impor um plano com USE PLAN para tipos específicos de cursor:
Um plano de duas partes para o cursor
Um plano de consulta de entrada de uma parte para o cursor
O plano de cursor que você força pode ser um plano obtido por qualquer um dos mecanismos seguintes para obter um plano de consulta XML:
Eventos de rastreamento SQL Server Profiler com base em XML. Esses eventos podem incluir, Plano de execução XML, Plano de execução XML para Query Compile, e Plano de execução XML Statistics Profile.
SET SHOWPLAN_XML ON
SET STATISTICS XML ON
Funções e exibições de gerenciamento dinâmico, como a consulta seguinte:
SELECT * FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_query_plan(plan_handle)
Observando o uso do cursor de Servidor API por aplicativos
Biblioteca de DB, ODBC, ADO e aplicativos OLEDB freqüentemente interagem com SQL Server usando cursores de servidor API. Você pode ver as chamadas que são submetidas aos procedimentos armazenados de cursor de servidor API examinando os eventos SQL Server Profiler RPC:Starting quando um aplicativo que é construído usando uma dessas interfaces for executado.
Exemplo: Forçando um Plano em uma consulta com um cursor
Este exemplo assume que você está usando um aplicativo que interage com o AdventureWorks banco de dados usando cursores ODBC, e você quer impor o plano para uma consulta submetida a SQL Server usando uma rotina de cursor de servidor API. Para impor o plano, colete um plano para uma consulta submetida por uma rotina de cursor API e então crie uma guia de plano para impor o plano para aquela consulta. Faça com que o aplicativo execute novamente a consulta e examine o plano para verificar se ele foi forçado.
Etapa 1: Colete o plano
Inicie um rastreamento SQL Server Profiler e selecione o Plano de Execução XML e eventos RPC:Starting. Faça que o aplicativo execute a consulta para a qual você deseja impor o plano. Clique no evento RPC:Starting que é gerado. Suponha que o evento RPC:Starting tem os seguintes dados de texto:
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
Colete o plano para a consulta clicando com o botão direito no evento de rastreamento do Plano de Execução XML que contém o plano de consulta de entrada para a consulta que aparece como um argumento à instrução sp_cursorprepexec acima e selecionando Extrair Dados de Eventos. Salvar os dados de evento (um plano de execução XML) a um arquivo CursorPlan.SQLPlan na área de trabalho. Copiar o arquivo CursorPlan.SQLPlan a CursorPlan.txt. Em SQL Server Management Studio, abra CursorPlan.txt em uma janela do editor. Para ganhar tempo depois, use Localizar e Substituir para substituir cada aspas simples (') no plano com quatro aspas simples (''''). Salve CursorPlan.txt.
Etapa 2: Criar a guia de plano para impor o plano
Crie uma guia de plano escrevendo e executando a instrução sp_create_plan_guide seguinte para impor o plano. Esta definição de guia de plano inclui o plano XML capturado na etapa anterior em uma dica de consulta USE PLAN na guia do plano.
Quando você estiver gravando esta definição de guia de plano, cole os conteúdos de CursorPlan.txt na posição apropriada no argumento @hints (logo após 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>'')'
Etapa 3: Executar a consulta e verificar se a guia de plano é aplicada a ela.
Faça com que o aplicativo execute novamente a consulta e reúna seu plano de execução XML usando o evento Plano de Execução XML em SQL Server Profiler.
Clique no evento Plano de Execução XML para o plano. Você deve consultar se o plano é aquele forçado na guia de plano.
Consultas de cursor com parâmetros
Se a consulta de cursor de servidor API para a que você deseja criar uma guia de plano tiver parâmetros, tenha certeza que você inclui a cadeia de caracteres de instrução e a cadeia de caracteres de definição de parâmetro que você consulta no evento SQL Server Profiler RPC:Starting na definição de guia de plano. A cadeia de caracteres de definição de parâmetro também é exigida para obter uma correspondência de guia de plano com êxito, semelhante às consultas com parâmetros submetidas usando sp_executesql.
Consulte também