January 20, 2011 at 9:25 am
I would like to know from other DBA's on how do they manage execution plans for procedures.
i) i have a procedure when it is executed for the first time takes about 25 secs and 2 secs from there onwards. Now from the application side every morning there is slowness and then performance gradually improved. Do i need to worry if query is taking more than 25 secs to exec first time?
ii) is there a way to findout on how long will the execution plan stay in cache?
iii) can i force to stay execution plan for longer time in cache ? any pros /cons on this?
Thanks
January 20, 2011 at 9:35 am
Exec cache would not be my first check here.
Assuming some sort of 9 to 5 env... everybody starts working at the same time so the first few minutes are usually much harder on the server than "normal" use.
Something else that could cause this is if you have a big job that runs at night like reindexing or loading report tables. That might cause the normal day to day data to be paged out. And then in the morning it has to be read from disk instead of ram.
That fact could be greatly compounded by the fact that all your users hit the server at the same time.
Another thing I've seen is users running all their reports at the same time. Even 1 user could slow everybody else down... same effect with smaller reports and 10 users at the same time.
January 20, 2011 at 9:38 am
BTW afaik there's no way to force to plan to stay there (maybe I'm wrong).
The server manage its ram as it sees fit and there's no much you can do about it.
The only think I know you can pin in memory is a table. And I never had to do that as there's always a bottleneck you can resolve first.
January 20, 2011 at 9:43 am
We have isloated reports from our transaction environment. My guess is there is a procedure which is executed like 100 times day and is creating a execution plan everytime. How do i find if the procedure's exec plan is still in cache or not?
January 20, 2011 at 9:55 am
I don't have a ready made answer for you.
I would start by familiarizing myself with sys.dm_exec_cached_plans.
Hopefully someone else has a more usable answer to give you.
January 20, 2011 at 9:56 am
sqldba_icon (1/20/2011)
My guess is there is a procedure which is executed like 100 times day and is creating a execution plan everytime.
Shouldn't be. Unless there's something forcing recompiles, something run that often should stay in cache.
http://sqlinthewild.co.za/index.php/2010/07/27/hit-and-miss/
http://sqlinthewild.co.za/index.php/2010/08/31/come-and-gone/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 20, 2011 at 10:41 am
We had a similar situation. A procedure (with a 76 table join) that was called hundreds of times in an hour. It hit so many tables and the data was so volatile that we got a recompile about every 20 minutes or so. It usually took 3 minutes to recompile, causing processes waiting on it to block. It wouldn't surprise me if you're hitting something similar. How volatile is the data in your system, meaning, how much does it change.
One thing we did, as a stop-gap, not a solution, was to use the KEEP FIXED PLAN option. This will prevent most, but not all, recompiles. But, it creates other problems. In the example above, the query wasn't called much overnight, so it would age out of cache. In the mornings, once or twice a week, when it was compiled again, we ran into bad parameter sniffing and then the plan locked into cache was bad, causing bad performance for hours until we forced a recompile.
The soluion was to rearchitect the system. I'd recommend the same.
"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
January 20, 2011 at 10:46 am
thanks
January 20, 2011 at 10:46 am
Grant Fritchey (1/20/2011)
We had a similar situation. A procedure (with a 76 table join) that was called hundreds of times in an hour. It hit so many tables and the data was so volatile that we got a recompile about every 20 minutes or so. It usually took 3 minutes to recompile, causing processes waiting on it to block. It wouldn't surprise me if you're hitting something similar. How volatile is the data in your system, meaning, how much does it change.One thing we did, as a stop-gap, not a solution, was to use the KEEP FIXED PLAN option. This will prevent most, but not all, recompiles. But, it creates other problems. In the example above, the query wasn't called much overnight, so it would age out of cache. In the mornings, once or twice a week, when it was compiled again, we ran into bad parameter sniffing and then the plan locked into cache was bad, causing bad performance for hours until we forced a recompile.
The soluion was to rearchitect the system. I'd recommend the same.
Holly crap. Was that a NULL - less design????
January 20, 2011 at 11:19 am
No, it was a stupid design.
"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
January 20, 2011 at 11:26 am
Grant Fritchey (1/20/2011)
No, it was a stupid design.
Potato, Potâto :hehe:.
January 20, 2011 at 3:47 pm
ok, i have this query to find some data related to cache. My goal is to find out what procedures are in cache for a specific database and i would like to know how much space it is using for each execution
select o.name,c.cacheobjtype,c.refcounts,c.sqlbytes ,c.usecounts,(c.sqlbytes/c.usecounts)/1024 as SpaceKB
from syscacheobjects c
join MyDBName.sys.objects o
on c.objid=o.object_id
where objtype='Proc'
and(cacheobjtype='Compiled Plan' or cacheobjtype='Executable Plan')
and c.dbid !=32767
and c.dbid=6
order by SpaceKB desc
Please let me know if someone has better script to analyze. I want to findout how the procs are being handled in cache for each db.
January 21, 2011 at 3:44 am
Don't use syscacheobjects. It's deprecated, included only for backward compatibility and it's very limited.
The DMVs you'll need for this are
sys.dm_exec_cached_plans (one row per plan)
sys.dm_exec_query_stats (one row per statement in the batch/procedure)
sys.dm_exec_sql_text
maybe sys.dm_exec_query_plan.
What do you mean by space per execution?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 21, 2011 at 8:52 am
GilaMonster (1/21/2011)
Don't use syscacheobjects. It's deprecated, included only for backward compatibility and it's very limited.The DMVs you'll need for this are
sys.dm_exec_cached_plans (one row per plan)
sys.dm_exec_query_stats (one row per statement in the batch/procedure)
sys.dm_exec_sql_text
maybe sys.dm_exec_query_plan.
What do you mean by space per execution?
Thanks Gail. I was trying to get an avg space(SpaceMB) a procedure is using inside procedurecache for one execution.
January 21, 2011 at 9:11 am
I still don't understand why you're interested in that. An execution plan takes up the same amount of space whether it's used once or 10000 times.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 40 total)
You must be logged in to reply to this topic. Login to reply