October 21, 2015 at 6:57 pm
Hi
My Application stored procedure is not running well. so we cleared cache.
Now the application is working fine.
What made the application running fine.
We are passing parameters to stored procedure to get the required data.
Is the parameter sniffing causing this issue?
How to identify any clues?
Is there any way that i can compare execution plans before the cache cleanup and execution plan now.
or any other way to resolve these kind of issues.
we have trace at the point of issue.
Is it useful in anyway to identify the issue.
October 22, 2015 at 1:57 am
Brent Ozar has a good script sp_blitzcache[/url], it scrapes the plan cache for whats in cache, you can get it to log results to a table to compare before and after.
I would first start by identifying what procs are actually causing you an issue as it might not be all of them and you only need to clean up certain plans.
Once you know what procs are causing you an issue, post the T-SQL and actual plans along with the server config, min max memory settings etc and people here can look at the problem to see if clearing the whole proc cache is needed or not. Generally its not.
October 22, 2015 at 4:29 am
m.rajesh.uk (10/21/2015)
HiMy Application stored procedure is not running well. so we cleared cache.
Why? You don't know what the problem is, so instead of troubleshooting, looking at wait statistics and sys.dm_exec_requests to see what is blocked you just FREEPROCCACHE? Not a good approach. You don't know that this will solve anything. Better to gather information from sys.dm_os_wait_statistics and the dmv I mentioned above to understand what's happening on the system. If there is no blocking, then queries are running slow. You need to identify which queries. Use sys.dm_exec_query_stats to see which are your slowest running queries. Of course, after you nuke the cache using FREEPROCCACHE, that information is all gone.
Now the application is working fine.
What made the application running fine.
Impossible to say for certain, but at a guess, one or more of your queries was experiencing bad parameter sniffing. By removing everything from cache you forced a recompile on every single query in the system (and seriously spiked your CPU) and one or more the new plans was better than the old plan.
We are passing parameters to stored procedure to get the required data.
Is the parameter sniffing causing this issue?
Possibly, but we can't know because you didn't gather any data. For example, when things are running well, get the execution plan for the query that you think is causing issues (or better still, gather performance metrics on your queries so you know how each query behaves. Use the DMV mentioned above or extended events to capture this information). Then, when the query is suddenly behaving poorly, capture the execution plan again. Compare the two. Are they different? What are the compile time values? How are those values represented within your statistics? You should be able to tell if you're dealing with bad parameter sniffing.
How to identify any clues?
As I said.
Is there any way that i can compare execution plans before the cache cleanup and execution plan now.
Yes, query the plan from cache using sys.dm_exec_query_plan. You can then capture this for good performance and bad.
or any other way to resolve these kind of issues.
we have trace at the point of issue.
Is it useful in anyway to identify the issue.
Trace just shows behavior of the queries, reads/writes/duration. It won't tell you what's wrong, just that something is. You need to go to the execution plan and your statistics to understand how different parameters are being resolved by the optimizer.
The solutions for bad parameter sniffing boil down to if you need a specific plan or a generic plan. You can get a specific plan by using the query hint OPTIMIZE FOR <some value> or using RECOMPILE (but understand that induces CPU cost and locking). You can get a generic plan by using the query hint OPTIMIZE FOR UKNOWN. If you really must remove a query from cache, instead of hitting all the cache and forcing recompile on all queries, use DBCC FREEPROCCACHE(<plan_handle>). You can get the plan handle for a specific query from the DMVs listed above and then use that to remove the single plan from cache. Don't destroy your cache in attempts to blindly fix one query.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply