如何微调SQL Server查询执行计划生成?

Question

我有一个很大的" Deals"表(130万行)需要显示在我的应用程序中的分页网格上，应用程序还包括过滤器来帮助用户搜索这些行，生成的SQL遵循以下结构:

SELECT TOP 10 * 
FROM (
SELECT 
ROW_NUMBER() OVER(ORDER BY [DealID] DESC) AS RowNumber, * 
FROM (
select d.DealID, a.[Description] as Asset, 
from Deals d
inner join Assets a on d.AssetID = a.AssetID
) as Sorted
where Asset like '%*my asset%' 
) as Sorted

我的问题是为这个查询生成执行计划,因为它是DealID下令,SQL Server是选择DealID的聚集索引上执行这个查询并执行一个聚集索引扫描这个表有130万行,但查询也被过滤后的资产只有171行,满足过滤器,这是更快的使用集群指数的资产,然后生成的行,我已经能够通过在查询中添加WITH INDEX(IX_Asset_ID))提示来解决这个问题，但问题是，由于这是一个生成的查询，这将为生成此查询的代码增加很多复杂性。

所以我的问题是，有没有办法让SQL Server检测这种情况没有提示?比如更新统计数据之类的?或者甚至将提示移动到查询的末尾也会有所帮助，因为查询的中间实际上是由客户端编写的报告。

——编辑——

正如在评论中指出的那样，查询有一些问题，但这些问题实际上是由于我试图创建一个最小可重复的问题示例而创建的，因此我省略了查询的分页部分，下面的结构是一个更完整的版本，应该更有意义:

SELECT TOP @pageLength * FROM (
SELECT ROW_NUMBER() OVER(ORDER BY [DealID] DESC) AS RowNumber, * 
FROM (
SELECT d.DealID, a.[Description] AS Asset, FROM Deals d
INNER JOIN Assets a on d.AssetID = a.AssetID
) AS Sorted
WHERE Asset LIKE '%*my asset%'
) AS Paged 
WHERE RowNumber > @startRow
ORDER BY RowNumber
OPTION(RECOMPILE)

Answer 1

最好根据聚集索引键值进行分页，如下所示

SELECT TOP (@pageLength)  
d.DealID, 
a.[Description] AS Asset, 
@startRowNumber + ROW_NUMBER() OVER(ORDER BY [DealID] DESC) AS RowNumber 
FROM Deals d
INNER JOIN Assets a on d.AssetID = a.AssetID
WHERE DealId > @startDealId
and a.[Description] LIKE '%*my asset%'
ORDER BY DealId

这种技术有时被称为"键集分页"，它利用有序索引允许SQL在最后一页之后直接查找下一个聚集索引键。您根据键值而不是生成的行号跟踪行。

Answer 2

最后，我的解决方案是使用DISABLE_OPTIMIZER_ROWGOAL提示。

我认为这里发生的事情是SQL Server对查询只需要10行过于乐观，并且扫描了太多的表，因为它认为查找前10行不会花很长时间，但实际上最好使用可用的索引，添加提示导致它改变计划，查询运行得很快。

SELECT TOP @pageLength * FROM (
SELECT ROW_NUMBER() OVER(ORDER BY [DealID] DESC) AS RowNumber, * 
FROM (
SELECT d.DealID, a.[Description] AS Asset, FROM Deals d
INNER JOIN Assets a on d.AssetID = a.AssetID
) AS Sorted
WHERE Asset LIKE '%*my asset%'
) AS Paged 
WHERE RowNumber > @startRow
ORDER BY RowNumber
OPTION(RECOMPILE, USE HINT ('DISABLE_OPTIMIZER_ROWGOAL'))