主页 / dba / 问题 / 198886
Accepted
Heinzi
Asked: 2018-02-28 02:27:05 +0800 CST

sp_cursoropen 选择了糟糕的执行计划

  • 772

如果我直接在 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

...一切都很好，很快...

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.

...因为 SQL Server 根据两个过滤条件选择一个合理的执行计划：

好的

另一方面，如果我的应用程序使用游标执行相同的查询...

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

……演技太差了……

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.

...因为 SQL Server 选择了一个糟糕的执行计划：

坏的

我知道我可以通过使用索引提示来解决这个问题。但是，我想了解为什么会这样。

我努力了：

  • DBCC FREEPROCCACHE
  • UPDATE STATISTICS auftrag_prod_soll

但这并没有什么不同。

我还查看了 prod_soll_über 和 auftrag_produktion 上两个索引的直方图：它们分布良好，因此 SQL Server 应该能够推断出查询最多会返回几行，因此，键查找和排序操作将比索引扫描快得多。

我还尝试创建一个包含 auftrag_produktion 和 prod_soll_über 的非聚集索引，但它并没有改变游标的执行计划（尽管它确实使直接查询更快）。

这是完整的表定义，以防相关：

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]

即使使用游标，我如何帮助 SQL Server 找到好的查询计划？

我通过禁用“reihenfolge”索引暂时“修复”了这个问题，但我仍然想了解为什么会发生这种情况，以便将来避免此类问题。

@p3在调用 之后，@p4、 和的值@p5保持在它们的初始值 (4, 1, -1) sp_cursoropen，但是只要我通过删除 reihenfolge 索引“解决”问题，它们就会切换到 (1, 1, 0) .

sql-server performance

2 个回答

  1. Best Answer

    即使使用游标，我如何帮助 SQL Server 找到好的查询计划？

    字面意思：使用计划指南或提示。但最好为 SQL Server 提供最佳索引，无论是否使用游标：

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

    这比索引交集加排序计划好，也比按顺序扫描和查找计划好得多。该索引允许对auftrag_produktion和进行相等查找prod_soll_über，同时确保匹配的行可以按reihenfolge顺序返回：

    最优方案

    光标

    提供的参数用于sp_cursoropen确定请求的游标类型，以及可选的可接受的选项。如果请求的类型和选项无效或不可用（出于一系列可能的原因），服务器可能会更改这些选项（因此成为输出参数）。

    提供的代码请求一个只进、只读游标，服务器将其作为动态类型游标提供。有关在静态和动态样式计划之间进行选择的详细信息，请参阅了解 SQL Server Fast_Forward 服务器游标。

    当您“修复”问题时，将提供一个键集游标，因为动态计划不再可能（动态游标计划无法排序）。

    您需要指定应用程序所需的游标选项（例如，用于并发）以及恰好最适合性能的任何类型，给定预期用途。如果您打算获取所有行，或者快速获取一行的计划实际上不是最佳的，您可能需要指定不同的类型，例如 @P3 = 8 的静态。如果您想确定，请添加 0x80000（静态可接受）交付静态游标。

    根据执行计划图像，SQL Server 似乎选择了一个动态计划，低估了在谓词（我假设）匹配第一行之前需要传递给键查找的行数：

    扫描加查找

    请注意从扫描中读取的大量行。动态计划所能做的最好的事情就是reihenfolge按顺序扫描索引。尽管 SQL Server 从统计信息中知道值的分布，但它不知道这些值在特定扫描顺序中的位置。所以它猜测动态计划中涉及的成本，并且恰好比使用阻塞排序运算符的计划成本更低。

    • 7

  2. 我想了解为什么会这样。

    在我看来，发生这种情况的原因是具有文字值的查询与使用参数的查询之间的区别。尽管您说索引“分布良好”，但仍然可能有一些边缘值不是，并且优化器不愿意在没有实际值的情况下实现这种信念的飞跃。

    您是否尝试过使用带有文字值的游标来查看它的行为方式？您是否尝试过在 Management Studio 中使用参数来查看它在那里的行为？

    • 0

相关问题