August 22, 2014 at 8:24 am
Hi,
We have SQL 2008 R2 SP2 CU10 on a ERP system. Recently, users will complain that it is very slow, screens are frozen, going from one screen to another will take minutes. When I looked in the database, I don't see any "stuck" query. Things were running below or in seconds. However, as soon as I run DBCC FREEPROCACHE, users confirmed that the system is fast again.
Cache Hit Ratio is about 100%, CPU was a little high but nothing was really slowing down on the server itself. Queries do seem running faster (below seconds) after DBCC FREEPROCACHE. How can queries running in seconds instead of sub-seconds freeze users' screen? That is puzzling.
Where would you start to investigate? Could I have your thought and idea? Thank you.
August 22, 2014 at 8:52 am
It sounds like bad parameter sniffing. What's likely happening is that a query is getting an execution plan that doesn't work well for most data sets, that's causing issues, you nuke the entire procedure cache, a new plan gets created.
What you need to do is identify the query that's performing badly. It's not that it'll be stopped, just that it'll run longer than normal. To do that, you need to capture query metrics to understand what "normal" looks like on your system. Then, you can be more targeted about how you address the issue.
"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
August 29, 2014 at 11:05 pm
Thank you Grant.
I have been investigating, same issue is still occurring off and on, DBCC Freeproccache always resolved it.
I did some profiler tracing, wrote down a list of tables that I can find while the slowness happened. I agree with you, it does appear to be parameter sniffing, however when I did a sp_recompile to every table on the list the last time, it didn't help. Slowness continued. I then ran DBCC Freeproccache, it resolved the slowness again, instantly.
From the profiler trace, I noticed there were a lot of Pagelatch_sh wait during the slowness. My research shows that it has to do with database files, table partitioning, and index selection, and etc. That doesn't explain why DBCC Freeproccache resolves the slowness. Do I have a memory issue? Why was the instant relief when DBCC Freeproccache was issued? Shouldn't a forced recompile only applies to the next execution, and has no effect on the queries that is already running?
August 31, 2014 at 1:13 pm
The page latches could be from slow disk, or memory issues, or stuff like that, sure. But, it could also be from scans where seeks would work better because bad parameter sniffing is leading to poor plan choice which leads to serious performance bottlenecks. Since DBCC FREEPROCCACHE is fixing the problem, it's very unlikely that it's a disk issue, at all, except, again, where a query plan can hammer the disk inappropriately. If you have tons and tons of unused plans, it's possible that freeing the cache could help with memory, but since the plan cache is limited, it's not likely that freeing it would have an instantaneous affect. Nope. I'm still on bad parameter sniffing. You need to identify the queries that are running long, get their execution plans when they're running long and when they're running fast. Compare them. The ones that have egregious differences are pretty likely to be the ones that you need to address.
"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
November 5, 2014 at 1:21 am
Thanks Grant, you are right. It does appear to be parameter after all. We noticed a FETCH API query that doesn't show long running time, but since it loops multiple times, accumulated total shows on user end. We are looking to add query hint, plan guide. In the mean time, turned on flag 4136 to put out the fire at the moment. Thanks for all your help.
November 6, 2014 at 6:11 am
Glad to hear it was narrowed down. It sure didn't sound like a physical problem. Good luck on the fix.
"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
November 7, 2014 at 6:50 am
TrailRunner (8/29/2014)
From the profiler trace, I noticed there were a lot of Pagelatch_sh wait during the slowness. My research shows that it has to do with database files, table partitioning, and index selection, and etc.
These types of latches are in memory latches. Disk based latches are PageIOLatch requests
TrailRunner (8/29/2014)
Shouldn't a forced recompile only applies to the next execution, and has no effect on the queries that is already running?
Applies to new executions, this is all assuming you're forcing a recompile on the query with the rogue plan.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply