Início / dba / Perguntas / 198886
Accepted
Heinzi
Asked: 2018-02-28 02:27:05 +0800 CST

sp_cursoropen escolhe um plano de execução terrível

  • 772

Se eu executar minha consulta (simples) diretamente no SQL Server Management Studio...

SELECT auftrag_prod_soll.ID 
  FROM auftrag_prod_soll 
 WHERE auftrag_prod_soll.auftrag_produktion = 51621 
   AND auftrag_prod_soll.prod_soll_über = 539363
 ORDER BY auftrag_prod_soll.reihenfolge

... está tudo bem e rápido ...

Table 'auftrag_prod_soll'. Scan count 2, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 102 ms.

...porque o SQL Server escolhe um plano de execução sensato com base nos dois critérios de filtragem:

Boa

Por outro lado, se meu aplicativo executa a mesma consulta com um cursor...

declare @p1 int
declare @p3 int
set @p3=4
declare @p4 int
set @p4=1
declare @p5 int
set @p5=-1
exec sp_cursoropen @p1 output,N' SELECT auftrag_prod_soll.ID  FROM auftrag_prod_soll  WHERE auftrag_prod_soll.auftrag_produktion =  51621   AND auftrag_prod_soll.prod_soll_über =  539363 ORDER BY auftrag_prod_soll.reihenfolge',@p3 output,@p4 output,@p5 output

exec sp_cursorfetch @p1,2,0,1

exec sp_cursorclose @p1

... o desempenho é terrível ...

Table 'auftrag_prod_soll'. Scan count 1, logical reads 1118354, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
  CPU time = 1094 ms,  elapsed time = 1231 ms.

...porque o SQL Server escolhe um plano de execução terrível:

mau

Eu sei que posso contornar isso usando uma dica de índice. No entanto, quero entender por que isso acontece.

Eu tentei:

  • DBCC FREEPROCCACHE
  • UPDATE STATISTICS auftrag_prod_soll

mas não fez diferença.

Também observei os histogramas dos dois índices em prod_soll_über e auftrag_produktion: Eles são bem distribuídos, portanto o SQL Server deve ser capaz de deduzir que a consulta retornará no máximo algumas linhas e, assim, a pesquisa de chave e a operação de classificação ser muito mais rápido do que a varredura de índice.

Também tentei criar um índice não clusterizado contendo auftrag_produktion e prod_soll_über, mas isso não alterou o plano de execução do cursor (mas tornou a consulta direta ainda mais rápida).

Aqui está a definição completa da tabela, caso seja relevante:

CREATE TABLE [auftrag_prod_soll](
    [auftrag_produktion] [int] NULL,
    [losgrößenunabh] [smallint] NOT NULL,
    [stückliste_vorh] [smallint] NOT NULL,
    [erledigt] [smallint] NOT NULL,
    [ext_wert_ueberst] [smallint] NOT NULL,
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [prod_soll_über] [int] NULL,
    [artikel] [int] NULL,
    [gesamtmenge_soll] [float] NULL,
    [produktionstext] [nvarchar](max) NULL,
    [reihenfolge] [int] NULL,
    [reihenfolge_druck] [int] NULL,
    [infkst_unter] [int] NULL,
    [ebene] [smallint] NULL,
    [bezeichnung] [varchar](50) NULL,
    [extern_text] [nvarchar](max) NULL,
    [intern_preis] [float] NULL,
    [intern_wert] [float] NULL,
    [extern_preis] [float] NULL,
    [extern_wert] [float] NULL,
    [extern_proz] [float] NULL,
    [dummyfeld] [varchar](50) NULL,
    [mengeneinheit] [varchar](50) NULL,
    [artikel_art] [smallint] NULL,
    [s_insert] [float] NULL,
    [s_update] [float] NULL,
    [s_user] [varchar](255) NULL,
    [preiseinheit] [float] NULL,
    [memo] [nvarchar](max) NULL,
    [lager_nummer] [int] NULL,
    [zweitmenge] [float] NULL,
    [zweit_einheit] [float] NULL,
    [zweit_mengeneinh] [varchar](50) NULL,
    [kst_preis1] [float] NULL,
    [kst_preis2] [float] NULL,
    [kst_preis3] [float] NULL,
    [kst_preis4] [float] NULL,
    [p_position] [int] NULL,
    [zeilen_status] [int] NULL,
    [fs_adresse_lief] [uniqueidentifier] NULL,
    [t_artikel_stückliste] [int] NULL,
    [div_text1] [varchar](255) NULL,
    [div_text2] [varchar](255) NULL,
    [menge_urspr] [float] NULL,
    [fs_artikel_index] [uniqueidentifier] NULL,
    [s_guid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [gemein_kosten] [float] NULL,
    [fs_leistung] [uniqueidentifier] NULL,
    [sonderlogik_ok_rech] [smallint] NOT NULL,
    [sonderlogik_ok_manuell] [int] NULL,
    [menge_inkl_frei] [float] NULL,
    [art_einheit] [int] NULL,
    [drittmenge] [float] NULL,
 CONSTRAINT [PK__auftrag_prod_sol__50E5F592] PRIMARY KEY CLUSTERED ([ID] ASC)
) 

CREATE NONCLUSTERED INDEX [artikel] ON [auftrag_prod_soll] ([artikel] ASC)
CREATE NONCLUSTERED INDEX [auftrag_produktion] ON [auftrag_prod_soll] ([auftrag_produktion] ASC)
CREATE NONCLUSTERED INDEX [dummyfeld] ON [auftrag_prod_soll] ([dummyfeld] ASC)
CREATE NONCLUSTERED INDEX [fs_adresse_lief] ON [auftrag_prod_soll] ([fs_adresse_lief] ASC)
CREATE NONCLUSTERED INDEX [fs_artikel_index] ON [auftrag_prod_soll] ([fs_artikel_index] ASC)
CREATE NONCLUSTERED INDEX [fs_leistung] ON [auftrag_prod_soll] ([fs_leistung] ASC)
CREATE NONCLUSTERED INDEX [lager_nummer] ON [auftrag_prod_soll] ([lager_nummer] ASC)
CREATE NONCLUSTERED INDEX [prod_soll_über] ON [auftrag_prod_soll] ([prod_soll_über] ASC)
CREATE NONCLUSTERED INDEX [reihenfolge] ON [auftrag_prod_soll] ([reihenfolge] ASC)
CREATE UNIQUE NONCLUSTERED INDEX [s_guid] ON [auftrag_prod_soll] ([s_guid] ASC)
CREATE NONCLUSTERED INDEX [s_insert] ON [auftrag_prod_soll] ([s_insert] ASC)
CREATE NONCLUSTERED INDEX [u_test] ON [auftrag_prod_soll] ([auftrag_produktion] ASC,
    [prod_soll_über] ASC)
CREATE NONCLUSTERED INDEX [zeilen_status] ON [auftrag_prod_soll] ([zeilen_status] ASC)
ALTER TABLE [auftrag_prod_soll] ADD  DEFAULT ((0)) FOR [losgrößenunabh]
ALTER TABLE [auftrag_prod_soll] ADD  DEFAULT ((0)) FOR [stückliste_vorh]
ALTER TABLE [auftrag_prod_soll] ADD  DEFAULT ((0)) FOR [erledigt]
ALTER TABLE [auftrag_prod_soll] ADD  DEFAULT ((0)) FOR [ext_wert_ueberst]
ALTER TABLE [auftrag_prod_soll] ADD  CONSTRAINT [DF__auftrag_p__s_gui__28A2FA0E]  DEFAULT (newid()) FOR [s_guid]
ALTER TABLE [auftrag_prod_soll] ADD  DEFAULT ((0)) FOR [sonderlogik_ok_rech]

Como posso ajudar o SQL Server a encontrar o bom plano de consulta, mesmo que os cursores sejam usados?

Eu "consertei" temporariamente esse problema desabilitando o índice "reihenfolge", mas ainda quero entender por que isso acontece, para evitar tais problemas no futuro.

Os valores de @p3, @p4, e @p5permanecem em seus valores iniciais (4, 1, -1) após a chamada para sp_cursoropen, mas assim que eu "resolvo" o problema removendo o índice reihenfolge, eles mudam para (1, 1, 0) .

sql-server performance

2 respostas

  1. Best Answer

    Como posso ajudar o SQL Server a encontrar o bom plano de consulta, mesmo que os cursores sejam usados?

    Literalmente: use um guia de plano ou dicas. Mas seria muito melhor fornecer ao SQL Server um índice ideal, seja um cursor usado ou não:

    CREATE INDEX [IX dbo.auftrag_prod_soll auftrag_produktion prod_soll_über reihenfolge] 
ON dbo.auftrag_prod_soll (auftrag_produktion, prod_soll_über, reihenfolge);

    Isso é melhor do que o plano de interseção de índice mais classificação e muito melhor do que o plano de varredura em ordem e pesquisa. Esse índice permite uma busca de igualdade em auftrag_produktione prod_soll_über, ao mesmo tempo em que garante que as linhas correspondentes possam voltar em reihenfolgeordem:

    Plano ideal

    Cursores

    Os parâmetros fornecidos para sp_cursoropendeterminar o tipo de cursor solicitado e, opcionalmente, quais opções são aceitáveis. O servidor pode alterar essas opções (sendo, portanto, parâmetros de saída) se o tipo e as opções solicitados não forem válidos ou disponíveis (por vários motivos possíveis).

    O código fornecido solicita um cursor somente de encaminhamento e somente leitura, que o servidor entrega como um cursor do tipo dinâmico. Consulte Noções básicas sobre cursores de servidor Fast_Forward do SQL Server para obter detalhes sobre a escolha entre planos de estilo estáticos e dinâmicos.

    Quando você "conserta" o problema, um cursor de conjunto de chaves é entregue, porque um plano dinâmico não é mais possível (um plano de cursor dinâmico não pode classificar).

    Você precisa especificar as opções de cursor necessárias para o aplicativo (por exemplo, para simultaneidade), bem como qualquer tipo que seja melhor para o desempenho, considerando o uso pretendido. Se você pretende buscar todas as linhas ou se o plano para buscar uma linha rapidamente não for ideal, talvez seja necessário especificar um tipo diferente, por exemplo, estático com @P3 = 8. Adicione 0x80000 (estático aceitável) se quiser ter certeza de receber um cursor estático.

    Com base na imagem do plano de execução, parece que o SQL Server escolhe um plano dinâmico subestimando o número de linhas que precisarão ser passadas para o Key Lookup antes que um predicado lá (suponho) corresponda à primeira linha:

    Digitalizar mais pesquisa

    Observe o grande número de linhas sendo lidas na varredura. O melhor que um plano dinâmico pode fazer é verificar o reihenfolgeíndice em ordem. Embora o SQL Server saiba sobre a distribuição de valores das estatísticas, ele não sabe onde estão esses valores em uma determinada ordem de verificação. Portanto, ele estima os custos envolvidos no plano dinâmico e custa mais barato do que um plano com um operador de classificação de bloqueio.

    • 7

  2. Eu quero entender porque isso acontece.

    Parece-me que a razão pela qual isso acontece é a diferença entre uma consulta com valores literais e uma consulta usando parâmetros. Embora você tenha dito que os índices "são bem distribuídos", ainda pode haver alguns valores de borda que não são e o otimizador não está disposto a dar esse salto de fé sem valores reais.

    Você já tentou o cursor com valores literais para ver como ele se comporta? Você já tentou usar parâmetros no Management Studio para ver como ele se comporta lá?

    • 0

relate perguntas