SQL Server函数间歇性性能问题

Question

我们的数据库中有一个函数，该函数搜索两个大表以查看是否存在值。这是一个相当大的查询，但已对其进行了优化以使用索引，并且通常运行速度非常快。

在过去的2周中，此功能有3次决定进入麻烦境地，并且运行极其缓慢，这会导致死锁和性能下降。即使在少于高峰使用时间的情况下，也会发生这种情况。

在SQL Server中使用“更改功能”重建功能似乎可以解决此问题。完成后，服务器使用率将恢复正常，一切正常。

这使我们认为功能查询计划已经重建，并考虑了正确的索引，但是我们不知道为什么SQL Server突然决定将查询计划更改为更差的计划。

有谁知道可能导致这种行为的原因，或者如何测试或预防这种行为？我们正在运行SQL Server 2008 Enterprise。

Answer 1

您描述的行为通常是由于缓存了错误的查询计划和/或过时的统计信息造成的。

当您在WHERE子句中有大量参数时，特别是一长串形式的参数时，通常会发生这种情况：

(@parameter1 is NULL OR TableColumn1 = @parameter1)

假设缓存的查询计划已过期，并且使用一组不具有代表性的参数来调用proc。然后为该数据配置文件缓存该计划。但是，如果proc在一组非常不同的参数中更常见，则该计划可能不合适。这通常被称为“参数嗅探”。

有许多方法可以缓解和消除此问题，但是它们可能涉及折衷，并且取决于您的SQL Server版本。看看OPTIMIZE FOR和OPTIMIZE FOR UNKNOWN。如果不经常调用proc（如果这很重要），但是必须尽可能快地运行，您可以将其标记为OPTION(RECOMPILE)，以在每次调用时强制重新编译，但不要对经常调用的proc进行此操作，否则请不要进行调查。

[注意：请注意您的SQL Server 2008框具有哪个Service
Pack和累积更新（CU）
，因为在某些版本中，重新编译和参数嗅探逻辑的工作方式有所不同]

运行此查询（来自Glenn Berry）以确定统计信息的状态：

-- When were Statistics last updated on all indexes?
SELECT o.name, i.name AS [Index Name],  
      STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date], 
      s.auto_created, s.no_recompute, s.user_created, st.row_count
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON o.[object_id] = i.[object_id]
INNER JOIN sys.stats AS s WITH (NOLOCK)
ON i.[object_id] = s.[object_id] 
AND i.index_id = s.stats_id
INNER JOIN sys.dm_db_partition_stats AS st WITH (NOLOCK)
ON o.[object_id] = st.[object_id]
AND i.[index_id] = st.[index_id]
WHERE o.[type] = 'U'
ORDER BY STATS_DATE(i.[object_id], i.index_id) ASC OPTION (RECOMPILE);