We have a function in our database that searches two large tables to see if a value exists. It is a pretty large query, but it is optimized to use indexes and generally runs pretty fast.
Three times over the past 2 weeks, this function decided to go haywire and run extremely slow, which causes deadlocking and bad performance all around. This happens even at times of less than peak usage.
Rebuilding the function using "Alter Function" in SQL Server seems to take care of the issue. Once we do that, the server usage goes back to normal and everything is OK.
This leads us to think that the functions query plan has rebuilt, and is taking the correct indexes into account, but we have no idea why SQL Server decided to change the query plan to a worse plan all of a sudden.
Does anyone have any ideas what might cause this behavior, or how to test for it, or prevent it? We are running SQL Server 2008 Enterprise.
The behaviour you are describing is often due to an incorrectly cached query plan and/or out of date statistics.
It commonly occurs when you have a large number of parameters in a WHERE clause, especially a long list of those that are of the form:
(@parameter1 is NULL OR TableColumn1 = @parameter1)
Say, the cached query plan expires, and the proc is called with an unrepresentative set of parameters. The plan is then cached for this data profile. BUT, if the proc is more oftenly common with a very different set of parameters, the plan might not be appropriate. This is often known as 'parameter sniffing'.
There are ways to mitigate and eliminate this problem but they may involve trade-offs and depend on your SQL Server version. Look at OPTIMIZE FOR and OPTIMIZE FOR UNKNOWN. IF (and it's a big if) the proc is called infrequently but must run as fast as possible you can mark it as OPTION(RECOMPILE), to force a recompile each time it is called, BUT don't do this for frequently called procs OR without investigation.
[NOTE: be aware of which Service pack and Cumulative Update (CU) your SQL Server 2008 box has, as the recompile and parameter sniffing logic works differently in some versions]
Run this query (from Glenn Berry) to determine the state of statistics:
-- 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);
