您描述的行为通常是由于缓存了错误的查询计划和/或过时的统计信息造成的。
当您在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);