January 24, 2011 at 1:10 pm
thanks..let me try.
January 25, 2011 at 5:29 am
Have you checked if you have some sort a late finishing bakckup or reindex job or anything trashing the disks at that time?
February 1, 2011 at 3:47 pm
GilaMonster (1/21/2011)
sqldba_icon (1/21/2011)
My goal is to findout how many different plans are genrated for each procedure.One and only one. A procedure can only have one plan in cache at a time (baring different SET options)
Gail could you please say why am i seeing two exec plans for same procedure? Are these set options causing an issue.
My above query is giving duplicate records for each plan i am not sure why. I am running the above query in context of MyDBName.
I guess you didn't notice what I said about the DMVs.
sys.dm_exec_query_stats (one row per statement in the batch/procedure)
I see two execution plans for same procedure? Mentioned below are the set options i am using.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
February 1, 2011 at 3:54 pm
Compare both queries... you'll most likely find only 1-2 characters difference in the query and that will explain the 2 plans.
February 1, 2011 at 4:11 pm
Ninja's_RGR'us (2/1/2011)
Compare both queries... you'll most likely find only 1-2 characters difference in the query and that will explain the 2 plans.
Not sure what do you mean. I have attached the data in excel. SQL handle is same, plan handle is different. It is the same proc.Thanks
February 1, 2011 at 4:14 pm
You can also get a double entry when there are two plans because of parallelism. One will parallel split, one won't.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 1, 2011 at 4:27 pm
Craig Farrell (2/1/2011)
You can also get a double entry when there are two plans because of parallelism. One will parallel split, one won't.
I need to check but could there be two exec plans because of using MAXDOP query hint? How would i know which plan is better than the other?
February 1, 2011 at 4:40 pm
sqldba_icon (2/1/2011)
Craig Farrell (2/1/2011)
You can also get a double entry when there are two plans because of parallelism. One will parallel split, one won't.I need to check but could there be two exec plans because of using MAXDOP query hint? How would i know which plan is better than the other?
Hard to tell which plan is better. You need to look at execution time & cpu & i/o to determine which is the better.
"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
February 1, 2011 at 7:52 pm
Any clue why do i have two plans?
February 1, 2011 at 10:51 pm
Check the set options, the language, the user_id (sys.dm_exec_plan_attributes where is_cache_key = 1), are any different.
Don't query sys.dm_exec_query_stats for this. There's one row in there per statement in the batch, if can be hard to see how many plans there are. Stick with exec_cached_plans
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
February 2, 2011 at 4:17 pm
TheSQLGuru (1/24/2011)
I don't think your delay on first execution is due to compilation time. 99% of the time that extra wait occuring on first execution is because the DATA is being fed into RAM from the IO system - which can be VERY VERY SLOW. Second and subsequent executions hit the data straight from RAM and things are much faster. You eventually reach a steady-state balance where most frequently used data sits in RAM thus the "performance gradually improved" statement.
Kevin beat me to this. I have had some limited success with running a procedure 3 times in a row, each time preceded by a FREEPROCCACHE. When I see the procedure run slowly the first time and more quickly in subsequent runs, then I figure that it is the data pages being loaded into memory that is the slow part. The recompile will take somewhat longer than a procedure with its plan in cache, but the main thing I look for is a significant improvement from the first run.
This is a rather quick and dirty method and far from thorough, but it has its uses.
Todd Fifield
Viewing 11 posts - 31 through 40 (of 40 total)
You must be logged in to reply to this topic. Login to reply