April 2, 2012 at 12:31 am
Hi,
I've sql 2K5 Sp4 on server w2K8 R2 Sp1 12GB mem 4 CPU and i'm experience some weird issue on some queries that work good for few weeks and suddenly start to work very slow (normal run is 1.5 sec,when slow is 10 sec) until i run DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE on the server and then the queries start to run smooth again.
why is that and how i can fix it?
April 2, 2012 at 5:07 am
It sounds like you might be dealing with a case of bad parameter sniffing. Instead of freeing the entire proc cache and the buffer cache (which requires reloading everything), try the following:
1. Capture the execution plan out of the plan cache for the query that's causing your problems. Save it as a .SQLPLAN file
2. Drop that one query from the cache using the plan handle (which you can get from the same query that used in step 1). That way you're not forcing the server to reload and recompile every single query in the system.
3. Compare the two plans. If they're different, take a look at the compile time parameter values. Then use those values to look at your data. You may be looking at skewed data or out of date statistics.
4. Check your statistics update maintenance plan to be sure it's running, or running frequently enough. Maybe introduce a FULL SCAN for the stats on the query giving your problems.
I did a full write-up on bad parameter sniffing in the book SQL Server MVP Deep Dives 2. That will give you a lot more information than what I just listed here.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 2, 2012 at 5:54 am
Grant Fritchey (4/2/2012)
It sounds like you might be dealing with a case of bad parameter sniffing. Instead of freeing the entire proc cache and the buffer cache (which requires reloading everything), try the following:1. Capture the execution plan out of the plan cache for the query that's causing your problems. Save it as a .SQLPLAN file
2. Drop that one query from the cache using the plan handle (which you can get from the same query that used in step 1). That way you're not forcing the server to reload and recompile every single query in the system.
3. Compare the two plans. If they're different, take a look at the compile time parameter values. Then use those values to look at your data. You may be looking at skewed data or out of date statistics.
4. Check your statistics update maintenance plan to be sure it's running, or running frequently enough. Maybe introduce a FULL SCAN for the stats on the query giving your problems.
I did a full write-up on bad parameter sniffing in the book SQL Server MVP Deep Dives 2. That will give you a lot more information than what I just listed here.
i didn't do all the steps that you wrote but i'm updating the statics daily an night with FULL SCAN on all tables on all databases.
so if it's not statics problem what else it can be?
April 2, 2012 at 6:22 am
Mad-Dog (4/2/2012)
Grant Fritchey (4/2/2012)
It sounds like you might be dealing with a case of bad parameter sniffing. Instead of freeing the entire proc cache and the buffer cache (which requires reloading everything), try the following:1. Capture the execution plan out of the plan cache for the query that's causing your problems. Save it as a .SQLPLAN file
2. Drop that one query from the cache using the plan handle (which you can get from the same query that used in step 1). That way you're not forcing the server to reload and recompile every single query in the system.
3. Compare the two plans. If they're different, take a look at the compile time parameter values. Then use those values to look at your data. You may be looking at skewed data or out of date statistics.
4. Check your statistics update maintenance plan to be sure it's running, or running frequently enough. Maybe introduce a FULL SCAN for the stats on the query giving your problems.
I did a full write-up on bad parameter sniffing in the book SQL Server MVP Deep Dives 2. That will give you a lot more information than what I just listed here.
i didn't do all the steps that you wrote but i'm updating the statics daily an night with FULL SCAN on all tables on all databases.
so if it's not statics problem what else it can be?
Data skew. meaning, the majority of your data has, let's say, 3 unique values on average, but for one set of data it has 30,000. So most of the time, you get a query plan for 3 values that includes a seek with a loop join, and that works fine. But, sometimes, when that other value is passed in, you get a plan with a scan and a merge join. It works great for 30,000 values, but stinks for 3 (or, reverse that whole explanation, same issue).
But this is all speculation since I can't see what you can. Without an execution plan when the query is bad and a plan from when the query is good to compare, it's hard to know if this is the correct answer, but it sounds like that kind of an issue.
If you do identify the issue as bad parameter sniffing, you need to decide how to fix it. There are about six different methods with varying degrees of applicability and you'll have to decide which one works in your situation.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 4, 2012 at 10:56 am
ok I'll do the execution plan history and when the issue will occur i'll update.
can i do anything on the server side to fix this issue because i can't change anything in the application it's very closed and hard
application that all queries run with the "sp_executesql" statement and all is fixed(i can't insert HINTS,etc...).
THX
April 4, 2012 at 11:15 am
Make sure your statistics are up to date is about your only option in SQL SErver 2005.
In 2008 or better you can start looking at filtered indexes, plan guides, other stuff like that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 5, 2012 at 6:04 am
Any advice on what to do if it was data skew (as you mentioned earlier)?
April 5, 2012 at 6:17 am
If you have really severe data skew, you need to evaluate it. Is the situation such that there are a few bad possibilities? If so, you can look to use the OPTIMIZE FOR query hint (I think that one is available in 2005) and set it to a preferred value. If the situation such that there are lots of bad possibilities? Then you're probably better off using a WITH RECOMPILE on the query (not the whole procedure) in order to get an optimized execution plan each time it runs. But, be sure of how long recompile takes because that could become a performance bottleneck too.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply