March 21, 2016 at 1:23 pm
Hi,
i'm using sql server 2008 R2 sp3 and i have some weird issue with some queries. every few days some of my query become very slow.
if i clean the cache with the dbcc freeproccache command those query start to run fine.
i suspect that this maybe a problem of parameter sniffing. how can i find the cause to that problem in the query plan cache or in any other location?
my biggest problem is that i can't change any thing in the query that comes to the sql server 🙁
THX
March 21, 2016 at 1:36 pm
Slowness is relative to your busyness, it this just you getting more and more impatient? Show us the code and your stats.
March 21, 2016 at 6:38 pm
Mad-Dog (3/21/2016)
Hi,i'm using sql server 2008 R2 sp3 and i have some weird issue with some queries. every few days some of my query become very slow.
if i clean the cache with the dbcc freeproccache command those query start to run fine.
i suspect that this maybe a problem of parameter sniffing. how can i find the cause to that problem in the query plan cache or in any other location?
my biggest problem is that i can't change any thing in the query that comes to the sql server 🙁
THX
From the sound of it when you clean the proc cache it's building a good query plan which stops being a good plan as time goes on. dbcc freeproccache again, rinse, repeat...
Is this a stored procedure or ad-hoc query?
If it's adhoc SQL and it's possible to have the query ran with OPTION (RECOMPILE) then try that.
If it's a stored proc perhaps you can run SP_recompile(your stored proc).
-- Itzik Ben-Gan 2001
March 21, 2016 at 9:41 pm
It sounds that there are to many plans in your plan cache memory.10% of buffer pool memory from 4gb-64gb + 5%>64gb is allocated to plan cache.
Can you post the output of this query ?
select objtype,count(*) 'No of plans',
sum(size_in_bytes/1024)/1024 'Cache size in MB'
from sys.dm_exec_cached_plans
group by objtype
Also I would suggest to run a profiler trace and examine the plan output from the trace to confirm if parameter sniffing is indeed the issue ? You can confirm it by checking the "ParameterCompiledValue" from the Plan XML to that of the actual parameter value passed to the SP in the trace.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
March 22, 2016 at 5:11 am
It's possible you're seeing bad parameter sniffing. First, identify the individual query that is running slow (you have to attack these one at a time, there aren't batch solutions for bad parameter sniffing). Then, capture the execution plan while the query is slow. Compare it to when the query is fast. Are they different? And by different, I mean ANY differences. If so, determine what's causing them to be different. It's almost always the row estimates. Determine why the slow execution plan row estimate is a problem. See what you can do to fix it. Sometimes an index might help. Sometimes it's that your statistics are out of date. Other times, you might need to modify the code. If you can't modify the code, occasionally a fix using plan guides is possible.
----------------------------------------------------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
March 22, 2016 at 5:12 am
Sachin Nandanwar (3/21/2016)
It sounds that there are to many plans in your plan cache memory.10% of buffer pool memory from 4gb-64gb + 5%>64gb is allocated to plan cache.Can you post the output of this query ?
select objtype,count(*) 'No of plans',sum(size_in_bytes/1024)/1024 'Cache size in MB'
from sys.dm_exec_cached_plans
group by objtype
Also I would suggest to run a profiler trace and examine the plan output from the trace to confirm if parameter sniffing is indeed the issue ? You can confirm it by checking the "ParameterCompiledValue" from the Plan XML to that of the actual parameter value passed to the SP in the trace.
Be very cautious here. A trace capturing execution plans is extremely costly on the server. If it's already under stress, this may not help at all.
----------------------------------------------------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
March 22, 2016 at 6:59 am
Grant Fritchey (3/22/2016)
Be very cautious here. A trace capturing execution plans is extremely costly on the server. If it's already under stress, this may not help at all.
Yes completely agree.
Just wondering if a server trace wouldn't impact the performance the way a profiler trace would to capture queries with their plans that have high costs ?
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
March 22, 2016 at 7:14 am
Sachin Nandanwar (3/22/2016)
Grant Fritchey (3/22/2016)
Be very cautious here. A trace capturing execution plans is extremely costly on the server. If it's already under stress, this may not help at all.
Yes completely agree.
Just wondering if a server trace wouldn't impact the performance the way a profiler trace would to capture queries with their plans that have high costs ?
Trace is trace. If you mean the trace versus Profiler GUI, yeah, the trace every time. But, capturing execution plans is expensive. Even if you went to extended events, which, in 2008 just aren't quite easy, so I'm hesitant to recommend them, capturing execution plans is expensive.
----------------------------------------------------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
March 22, 2016 at 7:25 am
Grant Fritchey (3/22/2016)
Trace is trace. If you mean the trace versus Profiler GUI, yeah, the trace every time. But, capturing execution plans is expensive. Even if you went to extended events, which, in 2008 just aren't quite easy, so I'm hesitant to recommend them, capturing execution plans is expensive.
Good to know that.Thanks..
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
March 22, 2016 at 11:39 am
ok I'll try to capture the plans while i have this problem.
i update all the stats every night and rebuild the indexes once a week.
can i query the cache plan to find what is causing this problem?
how i can use plan guide while i can't interact with the query?
March 22, 2016 at 1:06 pm
Mad-Dog (3/22/2016)
ok I'll try to capture the plans while i have this problem.i update all the stats every night and rebuild the indexes once a week.
can i query the cache plan to find what is causing this problem?
how i can use plan guide while i can't interact with the query?
Plan guides are specifically for when you can't interact with the query directly.
You have to identify the query, or queries, that are causing things to run slow. You can query the cache, but it only stores aggregate data. You won't see the specifics that at 3pm it ran for 3 seconds, but at 4pm it ran for 30 seconds.
----------------------------------------------------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 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply