Wierd TSQL Conundrum

RangeR

So, we've been having consistently inconsistent [poxymoron I know] speed issues with one department over the past few months. It was a wierd problem that took a lot of debugging. I won'pt bore everyone with the details but the end result is that we narrowed the cause of the problem down to one Stored Proc.

This Stored Proc isn't too detailed. It does three or four data integrity checks, then two or three inserts/updates if all is ok. When all is good, this SP runs in about 30 ms, even with verbose logging on. After a few days, this drops down to 2 to 3 seconds per execution. This SP is a critical piece of the entire system and could potentially be run 40 to 100 times a minute. 3 seconds per execution cycle is a serious impediment.

The problem isn't one of indexes, or data manipulation. It's the actual Stored Proc.

I don't understand it myself but when the department in question rings down and complains of slowdowns, we finally identified [by pure accident as it makes no sense] that if we open this Stored Proc into SQL Management Studio and just "Execute" the ALTER STORED PROCEDURE complete script without any change, it would speed things up for a few days.

It's gotten to a stage where I've written a tiny C# utility with one button. Once clicked, this button simply connects to SQL Server and executes the exact same ALTER STORED PROCEDURE command script. In essence just re-compiling the SP.

This makes no sense to me. I know it speeds things up by performing this simple process that appears to do nothing more than just recompile the SP but it's probably indicative of some other more serious issue. I've never heard of an SP "going bad", consistently, every few days.

I'd be very interested in anything anyone can throw at this issue.

Procasinator

Look through this stuff for tips:
http://stackoverflow.com/questions/1850172/stored-procedure-performance-randomly-plummets-trivial-alter-fixes-it-why

http://www.sommarskog.se/query-plan-mysteries.html

When you alter the sproc, the plan gets re-compiled with a good plan - for a while.

You should try to identify the queries that are causing these problems using the SQL Profiler.

amen

you most likely have a parameter sniffing issue. Basically when the SP runs with a set of parameters a query plan is created which tells SQL server how to search for the data.

Occasionally when your SP runs you are getting a set parameters that are very dissimilar from those for the cached query plan. This causes SQL Server to perform the search in a potentially inefficient manner leading to the behaviour.

When you recompile the SP the cached query plan is thrown away and a new one created which may be efficient for the data that was causing the issue.

If you take offending SP and parameters and run in SQL Server Enterprise Query window you may find that the SP is very quick and efficient as queries run through the Query window may use different query plan from when the SP is execute from an application.

I know you say your query is efficient but it may be worth looking at io statistics, disk io, the execution plan ( looking for scans, key look ups etc)

Of course you may also be having Disk io issues.

Does this happen around the same time each day ?

Have you looked at the lock types when this occurs ?What type of locks are you seeing ? cx_latchs? Whats the memory, cpu etc usage like when this occurs

These can be a pain to correct

RangeR

amen wrote: »

you most likely have a parameter sniffing issue. Basically when the SP runs with a set of parameters a query plan is created which tells SQL server how to search for the data.

Occasionally when your SP runs you are getting a set parameters that are very dissimilar from those for the cached query plan. This causes SQL Server to perform the search in a potentially inefficient manner leading to the behaviour.

When you recompile the SP the cached query plan is thrown away and a new one created which may be efficient for the data that was causing the issue.

If you take offending SP and parameters and run in SQL Server Enterprise Query window you may find that the SP is very quick and efficient as queries run through the Query window may use different query plan from when the SP is execute from an application.

I know you say your query is efficient but it may be worth looking at io statistics, disk io, the execution plan ( looking for scans, key look ups etc)

Of course you may also be having Disk io issues.

Does this happen around the same time each day ?

Have you looked at the lock types when this occurs ?What type of locks are you seeing ? cx_latchs? Whats the memory, cpu etc usage like when this occurs

These can be a pain to correct

Nice one. What you've said has given me a HUGE amount to look into. We use parameters. Some are optional. The SP has one main function but performs differently depending on the optional parameters that are used.

I think you are spot on in what you are saying. I'll investigate this on Monday. You may have saved me a few weeks of work. Cheers.