server索引 SQL Server不使用存储过程中的索引




sql server索引 (8)

我还没有通过使用存储过程来解决这个问题,但我们已经决定超越SP,只是执行普通的SQL

请参阅下面的扩展表格
编辑2:更新索引(不再使用actieGroep)
NB。 SQL Server 2005企业9.00.4035.00
NB2。 似乎与http://www.sqlservercentral.com/Forums/Topic781451-338-1.aspx相关

我在桌上有两个索引:

  • statistiekId上的聚集PK索引
  • foreignId上的非聚集索引

我有以下一段代码:

DECLARE @fid BIGINT
SET @fid = 873926

SELECT foreignId
FROM STAT_Statistieken
WHERE foreignId = @fid

这只是它应该的方式执行; 它指向正确的索引,它所做的只是扫描索引。

现在我正在创建一个存储过程:

ALTER PROCEDURE MyProcedure (@fid BIGINT)
AS BEGIN
    SELECT foreignId
    FROM STAT_Statistieken
    WHERE foreignId = @fid
END

运行的东西:

EXEC MyProcedure @fid = 873926

现在它正在我的PK索引上运行聚簇索引扫描 ! Wtf正在发生?

所以我改变了SP

SELECT foreignId
FROM STAT_Statistieken
    WITH (INDEX(IX_STAT_Statistieken_2))
WHERE foreignId = @fid

现在它提供了: 由于查询中定义的提示,查询处理器无法生成查询计划。 重新提交查询而不指定任何提示,也不使用SET FORCEPLAN。 而同样的功能正在运行,就像直接执行时一样。

额外的信息:完整的计划,可以重现这种行为(英文名字在评论)

CREATE TABLE [dbo].[STAT_Statistieken](
    [statistiekId] [bigint] IDENTITY(1,1) NOT NULL,
    [foreignId] [bigint] NOT NULL,
    [datum] [datetime] NOT NULL, --date
    [websiteId] [int] NOT NULL,
    [actieId] [int] NOT NULL, --actionId
    [objectSoortId] [int] NOT NULL, --kindOfObjectId
    [aantal] [bigint] NOT NULL, --count
    [secondaryId] [int] NOT NULL DEFAULT ((0)),
    [dagnummer]  AS (datediff(day,CONVERT([datetime],'2009-01-01 00:00:00.000',(121)),[datum])) PERSISTED, --daynumber
    [actieGroep]  AS (substring(CONVERT([varchar](4),[actieId],0),(1),(1))) PERSISTED,
    CONSTRAINT [STAT_Statistieken_PK] PRIMARY KEY CLUSTERED --actionGroup
    (
        [statistiekId] ASC
    )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]

指数

CREATE NONCLUSTERED INDEX [IX_STAT_Statistieken_foreignId_dagnummer_actieId_secondaryId] ON [dbo].[STAT_Statistieken] 
(
    [foreignId] ASC,
    [dagnummer] ASC,
    [actieId] ASC,
    [secondaryId] ASC
)WITH (PAD_INDEX  = ON, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 80, ONLINE = OFF) ON [PRIMARY]

执行

SET NOCOUNT ON;

    DECLARE @maand INT, @jaar INT, @foreignId BIGINT
    SET @maand = 9
    SET @jaar = 2009
    SET @foreignId = 828319


DECLARE @startDate datetime, @endDate datetime
SET @startDate = DATEADD(month, -1, CONVERT(datetime,CAST(@maand AS varchar(3))+'-01-'+CAST(@jaar AS varchar(5))))
SET @endDate = DATEADD(month, 1, CONVERT(datetime,CAST(@maand AS varchar(3))+'-01-'+CAST(@jaar AS varchar(5))))

DECLARE @firstDayDezeMaand datetime
SET @firstDayDezeMaand = CONVERT(datetime, CAST(@jaar AS VARCHAR(4)) + '/' + CAST(@maand AS VARCHAR(2)) + '/1')

DECLARE @daynumberFirst int
set @daynumberFirst = DATEDIFF(day, '2009/01/01', @firstDayDezeMaand)

DECLARE @startDiff int
SET @startDiff = DATEDIFF(day, '2009/01/01', @startDate)

DECLARE @endDiff int
SET @endDiff = DATEDIFF(day, '2009/01/01', @endDate)

SELECT @foreignId AS foreignId,
    SUM(CASE WHEN dagnummer >= @daynumberFirst THEN (CASE WHEN actieId BETWEEN 100 AND 199 THEN aantal ELSE 0 END) ELSE 0 END) as aantalGevonden, 
    SUM(CASE WHEN dagnummer >= @daynumberFirst THEN (CASE WHEN actieId BETWEEN 200 AND 299 THEN aantal ELSE 0 END) ELSE 0 END) as aantalBekeken, 
    SUM(CASE WHEN dagnummer >= @daynumberFirst THEN (CASE WHEN actieId BETWEEN 300 AND 399 THEN aantal ELSE 0 END) ELSE 0 END) as aantalContact,
    SUM(CASE WHEN dagnummer < @daynumberFirst THEN (CASE WHEN actieId BETWEEN 100 AND 199 THEN aantal ELSE 0 END) ELSE 0 END) as aantalGevondenVorige, 
    SUM(CASE WHEN dagnummer < @daynumberFirst THEN (CASE WHEN actieId BETWEEN 200 AND 299 THEN aantal ELSE 0 END) ELSE 0 END) as aantalBekekenVorige, 
    SUM(CASE WHEN dagnummer < @daynumberFirst THEN (CASE WHEN actieId BETWEEN 300 AND 399 THEN aantal ELSE 0 END) ELSE 0 END) as aantalContactVorige
FROM STAT_Statistieken
WHERE
    dagnummer >= @startDiff
    AND dagnummer < @endDiff
    AND foreignId = @foreignId 
OPTION(OPTIMIZE FOR (@foreignId = 837334, @startDiff = 200, @endDiff = 300))

DBCC统计信息

Name                                                          | Updated               | Rows      | Rows smpl | Steps | Density | Avg. key | String index
IX_STAT_Statistieken_foreignId_dagnummer_actieId_secondaryId    Oct  6 2009  3:46PM 1245058    1245058    92    0,2492834    28    NO

All Density  | Avg. Length | Columns
3,227035E-06    8    foreignId
2,905271E-06    12    foreignId, dagnummer
2,623274E-06    16    foreignId, dagnummer, actieId
2,623205E-06    20    foreignId, dagnummer, actieId, secondaryId
8,031755E-07    28    foreignId, dagnummer, actieId, secondaryId, statistiekId

RANGE HI | RANGE_ROWS | EQ_ROWS | DISTINCT_RANGE_ROWS | AVG_RANGE ROWS
-1         0            2         0                     1
1356       3563         38        1297                  2,747109
8455       14300        29        6761                  2,115072

索引的使用如执行计划中所示。 当我用这个参数来包装这个过程时:

@foreignId bigint,
@maand int, --month
@jaar int --year

并使用_SP_TEMP @foreignId = 873924, @maand = 9, @jaar = 2009

它进行聚簇索引扫描!


首先,我应该说你创建的索引不是最优的,因为它们只能用于过滤foreignId

SQL Server不能执行SKIP SCAN并且您的索引中有一个secondaryId ,它没有被范围条件过滤。

因此,您对foreignId, actieGroep, dagNummer条件不会产生有限的范围,也不是完全可靠的。 它只能在foreignIDforeignID ,而不能在整个过滤器上过滤。

现在,到你目前的指数。

我刚刚创建了你的表格,并用这个脚本填充了随机数据:

DROP TABLE STAT_Statistieken

CREATE TABLE [dbo].[STAT_Statistieken](
    [statistiekId] [bigint] IDENTITY(1,1) NOT NULL,
    [foreignId] [bigint] NOT NULL,
    [datum] [datetime] NOT NULL, --date
    [websiteId] [int] NOT NULL,
    [actieId] [int] NOT NULL, --actionId
    [objectSoortId] [int] NOT NULL, --kindOfObjectId
    [aantal] [bigint] NOT NULL, --count
    [secondaryId] [int] NOT NULL DEFAULT ((0)),
    [dagnummer]  AS (datediff(day,CONVERT([datetime],'2009-01-01 00:00:00.000',(121)),[datum])) PERSISTED, --daynumber
    [actieGroep]  AS (substring(CONVERT([varchar](4),[actieId],0),(1),(1))) PERSISTED,
    CONSTRAINT [STAT_Statistieken_PK] PRIMARY KEY CLUSTERED --actionGroup
    (
        [statistiekId] ASC
    )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_STAT_Statistieken_1] ON [dbo].[STAT_Statistieken] 
(
    [foreignId] DESC,
    [secondaryId] ASC,
    [actieGroep] ASC,
    [dagnummer] DESC, 
    [aantal] ASC --count
)WITH (PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF,  ONLINE = OFF) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_STAT_Statistieken_2] ON [dbo].[STAT_Statistieken] 
(
    [foreignId] DESC,
    [secondaryId] ASC,
    [actieId] ASC,
    [dagnummer] DESC,
    [aantal] ASC -- count
)WITH (PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

;WITH    nums AS
        (
        SELECT  1 AS num
        UNION ALL
        SELECT  num + 1
        FROM    nums
        )
INSERT
INTO    STAT_Statistieken (
        [foreignId], [datum], [websiteId], [actieId],
        [objectSoortId], [aantal])
SELECT  TOP 100000
        500, GETDATE(), num, num, num, num % 5
FROM    nums
UNION ALL
SELECT  TOP 100000
        num % 1000, GETDATE(), num, num, num, num % 5
FROM    nums
OPTION (MAXRECURSION 0)

UPDATE STATISTICS STAT_Statistieken

,它使用INDEX SEEK无论如何,这很可能意味着问题与您的数据分布。

我建议你创建一个额外的索引与secondaryID删除,如下所示:

CREATE NONCLUSTERED INDEX [IX_STAT_Statistieken_3] ON [dbo].[STAT_Statistieken] 
(
    [foreignId] DESC,
    [actieGroep] ASC,
    [dagnummer] DESC, 
    [aantal] ASC --count
)

如果您仍想使用您当前的索引,请运行以下命令:

DBCC SHOW_STATISTICS ('STAT_Statistieken', 'IX_STAT_Statistieken_1')
DBCC SHOW_STATISTICS ('STAT_Statistieken', 'IX_STAT_Statistieken_2')

每个命令将输出三个结果集。

你可以请每个命令发布结果集12 ,结果集3结果集3三个行的值是RANGE_HI ,正好在上面,等于873926


[编辑]

下面的PERSISTED-not-being-used-issue问题仅在我系统上使用actieGroep / actieId(SQL 2008)时才会出现。 但是也有可能在你的SQL 2005系统上使用dagnummer / datum列也会出现同样的问题。 如果确实发生了这种情况,它会解释你所看到的行为,因为需要聚集索引扫描来过滤数据的值。 要诊断这是否确实发生,只需将基准列作为INCLUDE-d列添加到索引,如下所示:

CREATE NONCLUSTERED INDEX [IX_STAT_Statistieken_1] ON [dbo].[STAT_Statistieken]  
(  
    [foreignId] DESC,  
    [dagnummer] DESC,  
    [actieId] ASC,   
    [aantal] ASC    
) INCLUDE (datum)  ON [PRIMARY]

如果这个索引版本的问题消失了,那么你知道dagnummer是个问题 - 你甚至可以从索引中删除dagnummer,因为SQL没有使用它。

此外,修改索引添加actieId是一个好主意,因为它避免了下面提到的问题。 但在这个过程中,您还需要将索引留在索引中,以便您的索引将成为此查询的覆盖索引 。 否则,SQL将不得不读取您的聚集索引以获取该列的值。 这会降低查询速度,因为查找聚集索引的速度很慢。

[结束编辑]

这里有一些想法可以帮助你解决这个问题,最可能/最简单的事情是:

  • 当我尝试重新使用模式和查询(使用假生成的数据)时,我发现您的PERSISTED计算列actieGroep在运行时被重新共享,而不是正在使用的持久值。 这看起来像SQL Server优化器中的一个错误。 由于底层列值actieGroep不存在于您的覆盖索引IX_STAT_Statistieken_1索引(只有计算列在那里),所以如果SQL Server决定需要获取该额外列,SQL可能会认为聚集索引比使用非索引然后查找集群索引中每个匹配行的actieId。 这是因为聚集索引查找相对于顺序I / O来说是非常昂贵的,所以任何需要查找多于百分之几的行的计划对于扫描而言可能更便宜。 无论如何,如果这确实是您遇到的问题,那么将actieGroep添加为IX_STAT_Statistieken_1索引的INCLUDE-d列应该可以解决此问题。 喜欢这个:

    CREATE NONCLUSTERED INDEX [IX_STAT_Statistieken_1] ON [dbo].[STAT_Statistieken]
    (
    [foreignId] DESC,
    [secondaryId] ASC,
    [actieGroep] ASC,
    [dagnummer] DESC,
    [aantal] ASC
    ) INCLUDE (actieId) ON [PRIMARY]

  • 计算列actieGroep的数据类型是一个字符串,但是您将其与WHERE子句和CASE语句中的整数(例如IN(1,2,3))进行比较。 如果SQL决定转换列而不是常量,那么会影响查询性能,并且可能会使计算列扩展问题(如上所述)更有可能。 我强烈建议改变你的计算列定义为一个整数类型,例如

    CASE WHEN actieId BETWEEN 0 AND 9 THEN actieId
    WHEN actieId BETWEEN 10 AND 99 THEN actieId/10
    WHEN actieId BETWEEN 100 AND 999 THEN actieId/100
    WHEN actieId BETWEEN 1000 AND 9999 THEN actieId/1000
    WHEN actieId BETWEEN 10000 AND 99999 THEN actieId/10000
    WHEN actieId BETWEEN 100000 AND 999999 THEN actieId/100000
    WHEN actieId BETWEEN 1000000 AND 9999999 THEN actieId/1000000
    ELSE actieId/10000000 END

  • 你正在做一个只有一个可能值的列。 因此,GROUP BY是不必要的。 希望优化器足够聪明,知道这一点,但是你永远无法确定。

  • 尝试使用OPTIMIZE FOR提示,而不是直接强制索引,这可能会解决您提示的错误

  • Craig Freedman的帖子http://blogs.msdn.com/craigfr/archive/2009/04/28/implied-predicates-and-query-hints.aspx描述了你得到的提示相关错误信息的常见原因当使用RECOMPILE时。 您可能需要查看该文章,并确保您正在运行SQL Server的最新更新。

  • 我相信你已经做到了这一点,但是你可能想通过做我们正在做的事情来构建一个“干净的房间”版本的数据:创建一个新的数据库,在你的问题中使用DDL创建表,然后用数据填充表格。 如果你得到的结果是不同的,请在你的真实表和索引的模式下查看closley,看看它们是不同的。

如果这些都不起作用,评论和我可以建议一些更疯狂的想法。 :-)

此外,请添加SQL Server的确切版本和更新级别到您的问题!


它可能是参数嗅探,所以尝试这样的事情:

ALTER PROCEDURE MyProcedure (@fid BIGINT)
AS BEGIN
    DECLARE @fid_sniff BIGINT
    SET @fid_sniff[email protected]fid
    SELECT foreignId
    FROM STAT_Statistieken
    WHERE foreignId = @fid_sniff
END

阅读更多的参数嗅探: http ://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html


select AU.*
FROM SYS.Allocation_units AS AU
INNER JOIN SYS.Partitions AS P
ON AU.Container_id = P.Partition_id
WHERE Object_ID = object_id('STAT_Statistieken')

试试这个,并检查非集群索引是否有比集群索引更多的页面(这将意味着更容易阅读集群索引)


尝试像这样创建索引:

CREATE NONCLUSTERED INDEX [IX_STAT_Statistieken_2] ON [dbo].[STAT_Statistieken] 
(
    [foreignId] DESC,
    [secondaryId] ASC,
    [actieId] ASC,
    [dagnummer] DESC,
    [aantal] ASC -- count
)
INCLUDE (actieGroep);       
WITH (PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

然后重新创建你的程序


我曾经见过类似的行为,它实际上会采取索引提示,并做了更糟的事情(未经过筛选的书签查找索引扫描)。

其中一个应该有所帮助:

1)向SQL Server 2005启动参数追加; -T4102; -T4118(可能适用于SQL 2008)。 注意:这将带回SQL 2005中对IN和NOT IN查询的SQL 2000错误处理。

2)UPDATE STATISTICS [dbo]。[STAT_Statistieken] WITH FULLSCAN

3)OPTION(MAXDOP 1) - 有时并行会导致生成非常愚蠢的查询

4)确保索引在线。

另请注意,如果在存储过程中创建的表上创建索引,则在编译存储过程查询时该索引不存在,因此不会使用该索引。 由于你的表是在dbo全局创建的,我认为这不是这种情况。

编辑:有时我希望有一个真正的计划,你可以直接键入计划,任何可能的计划将被执行:类似数据库类似汇编语言。


当查询中存在冲突的查询提示时,可能会生成您收到的错误消息。

你可以运行存储过程之外的查询,包括提示吗?

另一种思路是,您是否使用不同的参数值来测试/运行存储过程? 用于创建原始执行计划的参数值可能不适合所有活动。 您可能希望考虑重新编译存储过程,以查看是否在具有不同参数的不同运行之间生成不同的执行计划。

如果您希望确保为存储过程的每次执行计算新的查询计划,则可以使用WITH RECOMPILE子句。 这应该是例外,而不是规范。 验证你的程序的行为,并通过测试生成计划。


表中的foreignId是什么数据类型? 如果是int,那么你可能会得到一个隐式的转换,从而阻止索引查找。 如果表中的数据类型是int,那么重新定义参数为int,并且您应该为此查询获得索引查找(而不是索引扫描)。





indexing