0

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Brian Duncan
  • 480
  • 3
  • 12
  • 1
    We'd probably need more information. What type of function is it (scalar, table-valued, or inline)? What are the structures of the tables it operates on? What indexes are available, and which do you expect it to use? What exactly did it do when it started behaving badly? – Chris Shain Jan 29 '11 at 00:31

1 Answers1

5

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);
Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • I agree it sounds like parameter sniffing. Out of date statistics don't seem to fit the facts however as if the statistics were bad then recompiling the plan via `Alter Function` wouldn't fix the issue. – Martin Smith Jan 29 '11 at 00:53
  • If the stats are skewed (say after a big data load), an incorrect plan might be choosen. But it is more likely due to parameter sniffing – Mitch Wheat Jan 29 '11 at 00:55