当在where子句中有一个特定的值使用的索引列Azure的SQL查询缓慢 [英] Azure sql query is slow when an indexed column used in where clause has a particular value

问题描述

实例：SQL Azure的实例S2
我们有〜170万记录的候选表。考生表索引的非主键帐户ID。帐户ID：646有80K行和帐户ID：10有365K行

Instance: Azure SQL S2 instance We have a candidates table which has ~1.7Million records. candidates table is indexed on non-primary key AccountID. AccountID:646 has 80K rows and AccountID:10 has 365K rows.

当我们从火表中选择顶（10）其中non_pk_indexed_col = INT

When we fire select top(10) from table where non_pk_indexed_col=int

Select top(10) from candidates where accountid=10

查询与完成的 00:00:00 拍摄时间

Select top(10) from candidates where accountid=646

，查询与完成的 01:45:00 拍摄时间

为什么同一个查询需要这么长的时间，如果值是不同的。

Why would the same query take such a long time when the value is different

推荐答案

最有可能你正在为这两个参数非常不同的查询计划。诸如一个性能比其他显著更好。检查方法之一是让你的实际执行计划，检查出来。

Most likely you are getting very different query plans for these two parameters. As such one performs significantly better than the other. One way to check would be to get your actually execution plan and check it out.

您可以做到这一点通过推动包括在SSMS（约七到执行按钮的左边）实际执行计划按钮的 https://msdn.microsoft.com/en-us/library/ms189562.aspx

You can do that by pushing the Include Actual Execution Plan button in SSMS (about seven to the left of the Execute button) https://msdn.microsoft.com/en-us/library/ms189562.aspx

正如尼尔说，虽然，你将传统与修复更新统计较差的执行计划。你会希望与全扫描虽然更新，以获得最好的结果，你可以通过运行该查询：

As said by Neil though, you will traditionally fix a poor execution plan with updating stats. You will want to update with fullscan though to get the best possible results, you can do that by running this query:

DECLARE @sql nvarchar(MAX);
SELECT @sql = (SELECT 'UPDATE STATISTICS [' + DB_NAME() + '].[' + rtrim(sc.name) + '].[' + rtrim(so.name) + '] WITH FULLSCAN, ALL; '
from sys.sysobjects so
join sys.schemas sc
on so.uid = sc.schema_id
where so.xtype = 'U' 
               FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)');
PRINT @sql
EXEC (@sql)

希望帮助了！

这篇关于当在where子句中有一个特定的值使用的索引列Azure的SQL查询缓慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！