慢查询
SELECT MIN(col) AS Firstdate,MAX(col) AS Lastdate FROM table WHERE status = 'OK' AND fk = 4193
表’表’.扫描计数2,逻辑读取2458969,物理读取0,预读读取0,lob逻辑读取0,lob物理读取0,lob预读读取0.
sql Server执行时间：cpu时间= 1966 ms,已用时间= 1955 ms.
SELECT count(*),MIN(col) AS Firstdate,MAX(col) AS Lastdate FROM table WHERE status = 'OK' AND fk = 4193
表’表’.扫描计数1,逻辑读取5803,lob预读读取0.
sql Server执行时间：cpu时间= 0 ms,已用时间= 9 ms.
题
更新
基于作为评论的问题的一点更新：
执行顺序或重复执行不会改变性能.
没有使用额外的参数,(测试)数据库在执行过程中没有做任何其他的事情.
慢查询
|--Nested Loops(Inner Join)
|--Stream Aggregate(DEFINE:([Expr1003]=MIN([DBTest].[dbo].[table].[startdate])))
| |--Top(TOP EXPRESSION:((1)))
| |--Nested Loops(Inner Join,OUTER REFERENCES:([DBTest].[dbo].[table].[id],[Expr1008]) WITH ORDERED PREFETCH)
| |--Index Scan(OBJECT:([DBTest].[dbo].[table].[startdate]),ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT:([DBTest].[dbo].[table].[PK_table]),SEEK:([DBTest].[dbo].[table].[id]=[DBTest].[dbo].[table].[id]),WHERE:([DBTest].[dbo].[table].[FK]=(5806) AND [DBTest].[dbo].[table].[status]<>'A') LOOKUP ORDERED FORWARD)
|--Stream Aggregate(DEFINE:([Expr1004]=MAX([DBTest].[dbo].[table].[startdate])))
|--Top(TOP EXPRESSION:((1)))
|--Nested Loops(Inner Join,[Expr1009]) WITH ORDERED PREFETCH)
|--Index Scan(OBJECT:([DBTest].[dbo].[table].[startdate]),ORDERED BACKWARD)
|--Clustered Index Seek(OBJECT:([DBTest].[dbo].[table].[PK_table]),WHERE:([DBTest].[dbo].[table].[FK]=(5806) AND [DBTest].[dbo].[table].[status]<>'A') LOOKUP ORDERED FORWARD)
|--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1012],0)))
|--Stream Aggregate(DEFINE:([Expr1012]=Count(*),[Expr1004]=MIN([DBTest].[dbo].[table].[startdate]),[Expr1005]=MAX([DBTest].[dbo].[table].[startdate])))
|--Nested Loops(Inner Join,[Expr1011]) WITH UNORDERED PREFETCH)
|--Index Seek(OBJECT:([DBTest].[dbo].[table].[FK]),SEEK:([DBTest].[dbo].[table].[FK]=(5806)) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([DBTest].[dbo].[table].[PK_table]),WHERE:([DBTest].[dbo].[table].[status]<'A' OR [DBTest].[dbo].[table].[status]>'A') LOOKUP ORDERED FORWARD)
回答
马丁史密斯下面给出的答案似乎解释了这个问题.超简短版本是MS-SQL查询分析器在慢查询中错误地使用查询计划,从而导致完整的表扫描.
添加一个Count(*),使用(FORCESCAN)的查询提示或startdate,FK和status列上的组合索引修复了性能问题.
解决方法
- Independence: Data distributions on different columns are independent unless correlation information is available.
- Uniformity: Within each statistics object histogram step,distinct values are evenly spread and each value has the same frequency.
07000
表中有810,064行.
你有查询
SELECT COUNT(*),MIN(startdate) AS Firstdate,MAX(startdate) AS Lastdate
FROM table
WHERE status <> 'A'
AND fk = 4193
1,893(0.23％)行满足fk = 4193谓词,并且那些行失败状态为<> “A”部分总共1,891匹配,需要聚合.
对于您的快速查询,它使用fk上的索引直接查找fk = 4193的行,然后需要执行1,893 key lookups查找聚簇索引中的每一行以检查状态谓词并检索用于聚合的startdate.
从SELECT列表中删除COUNT(*)时,sql Server不再需要处理每个合格的行.因此,它考虑了另一个选择.
您在startdate上有一个索引,所以它可以从一开始就开始扫描,执行关键查找回到基表,一旦找到第一个匹配的行停止,就像找到MIN(startdate)一样,MAX也可以发现另一个扫描开始索引的另一端并向后工作.
sql Server估计,这些扫描中的每一个都会在匹配谓词之前结束处理590行.提供1,180总查询与1,893,所以选择这个计划.
590的数字只是table_size / estimated_number_of_rows_that_match.即基数估计器假设匹配行将在整个表格中均匀分布.
不幸的是,符合谓词的1,891行不是随机分配的,与startdate相关.事实上,它们都被缩小为单个8,205行分段,指向索引的末尾,意味着进入MIN(startdate)的扫描最终可以执行801,859次查询,然后才能停止.
这可以在下面再现.
CREATE TABLE T ( id int identity(1,1) primary key,startdate datetime,fk int,[status] char(1),Filler char(2000) ) CREATE NONCLUSTERED INDEX ix ON T(startdate) INSERT INTO T SELECT TOP 810064 Getdate() - 1,4192,'B','' FROM sys.all_columns c1,sys.all_columns c2 UPDATE T SET fk = 4193,startdate = GETDATE() WHERE id BETWEEN 801859 and 803748 or id = 810064 UPDATE T SET startdate = GETDATE() + 1 WHERE id > 810064 /*Both queries give the same plan. UPDATE STATISTICS T WITH FULLSCAN makes no difference*/ SELECT MIN(startdate) AS Firstdate,MAX(startdate) AS Lastdate FROM T WHERE status <> 'A' AND fk = 4192 SELECT MIN(startdate) AS Firstdate,MAX(startdate) AS Lastdate FROM T WHERE status <> 'A' AND fk = 4193
您可以考虑使用查询提示强制计划使用fk而不是startdate上的索引,或者在(fk,status)INCLUDE(startdate)上执行计划中突出显示的建议的缺失索引,以避免此问题.
