DMV Problem - dm_exec_query_stats

Question

DMV Problem - dm_exec_query_stats

brk5tr

Ten Centuries

Points: 1360
More actions
April 5, 2012 at 7:20 am

#266874

I'm using the dm_exec_query_stats DMV to return data related to an SP. I have found that I have the following:
PLAN_HANDLE
ROW 1: 0x0100400C439B6441AR52B0123A000000300000000000000
ROW 2: 0x0100400C439B6441AR52B0123A000000300000000000000
ROW 3: 0x0100400C439B6441AR52B0123A000000300000000000000
PLAN_GENERATION_NUM
ROW 1: 1
ROW 2: 3
ROW 3: 4
SQL_STRING
ROW 1: SET @VALUE_1 = (SELECT NUMBER1 FROM TBL1)
ROW 2: SET @VALUE_2 = (SELECT NUMBER2 FROM TBL2)
ROW 3: SET @VALUE_3 = (SELECT NUMBER3 FROM TBL3)
My question is:
What does the PLAN_GENERATION_NUMBER mean? Because the PLAN_HANDLE is the same for each statement, so this hasn't changed. What exactly has been recompiled? has a single statement been recompiled?
Thanks.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Lots of things can cause a query to be recompiled even when the query hasn't changed and you can get the same plan. Statistics may have been updated, a schema change may have occurred, etc...

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

brk5tr Ten Centuries Points: 1360 More actions · Answer 2

Thanks for the reply.

Can I just confirm the following please:

1. Single queries within a plan can be recompiled, and the Plan_Handle remains the same.

2. If the amount of data has changed a lot since the last time the plan was used, could this cause individual queries within the plan to be recompiled?

Thanks.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Yes

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

I don't know the answer to #1.

For #2 the answer would be yes, if auto update stats is on or a manual statistics update is run, because updating stats will trigger a recompile. If auto update stats is off then you probably won't get a recompile because it is the change in stats that cause a recompile.

You can see a list of what can cause a recompile here, http://msdn.microsoft.com/en-us/library/ms187105.aspx

Fortunately Gail jumped in with the simple answers and I saw the update email while I was typing this response, but I figured I'd post it anyway.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

brk5tr Ten Centuries Points: 1360 More actions · Answer 5

Is there a way that I can determine if a whole Plan has been re-compiled or just a single statement.

I have a query which breaks the plan up into each statement (using sys.dm_exec_query_stats), and it shows an execution count next to each statement. Is this the execution count since the whole plan was last compiled or just since the statement recompliation (as the Plan_Handle is the same)?

Thanks.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Recompilation in SQL 2008 is at a statement level. Compilation is at a batch level.

http://sqlinthewild.co.za/index.php/2011/08/16/compiles-and-recompiles/

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

brk5tr Ten Centuries Points: 1360 More actions · Answer 7

Ok, thanks for your help with this.

I'll read over the article in the link.

Assuming that my plan_handle is the same, I'm now happy that it has been recompiled not compiled.

Thanks.

brk5tr Ten Centuries Points: 1360 More actions · Answer 8

If a data import has caused a plan to re-compile - would this not improve performance,as the engine will now be aware of the extra data?

I'm seeing a recompile causing poor performance after a data import. I would expect poor performance if a plan based on old statistics; however, if a plan is recompiled will it not use new statistics making it aware of the extra data and generating a better plan?

Thanks.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

ggjjbb1983 (4/5/2012)
If a data import has caused a plan to re-compile - would this not improve performance,as the engine will now be aware of the extra data?
I'm seeing a recompile causing poor performance after a data import. I would expect poor performance if a plan based on old statistics; however, if a plan is recompiled will it not use new statistics making it aware of the extra data and generating a better plan?
Thanks.

May depend on how good the statistics are. If prior to the import the statistics had been created/updated using a full scan and after the import the statistics were updated with sample scan (20%), the difference could easily cause a problem.

I may be wrong, but I thought I had read somewhere that the autoupdate statistics does not do a full scan.

brk5tr Ten Centuries Points: 1360 More actions · Answer 10

Ok thanks,

I have an issue where I have a good performing query. I then run a data import. The next query generates a new plan and performance after this point is poor.

If the plan didn't recompile then I 'd expect poor performance; however, I thought that a plan recompile should improve performance.

Is there any reason why a recompile may cause bad performance?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Lynn answered that.

May depend on how good the statistics are. If prior to the import the statistics had been created/updated using a full scan and after the import the statistics were updated with sample scan (20%), the difference could easily cause a problem.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

The statistics may not be as good as they could be? See my previous post.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

From Books Online:

When the AUTO_UPDATE_STATISTICS database option is set to ON (the default), the query optimizer automatically updates this statistical information periodically as the data in the tables changes. A statistics update is initiated whenever the statistics used in a query execution plan fail a test for current statistics. The sampling is random across data pages, and is taken from the table or the smallest nonclustered index on the columns needed by the statistics. After a data page has been read from disk, all the rows on the data page are used to update the statistical information. Almost always, statistical information is updated when approximately 20 percent of the data rows has changed. However, the query optimizer always makes sure that a minimum number of rows are sampled. Tables that are smaller than 8 megabytes (MB) are always fully scanned to gather statistics.

brk5tr Ten Centuries Points: 1360 More actions · Answer 14

Ok thanks,

is there any reason why a sample scan would be performed instead of a full scan?

Sorry,

Ignore this, I just read your reply.

Thanks.