October 2, 2015 at 7:06 am
ChrisM@Work (10/2/2015)
Jason A. Long (10/2/2015)
ChrisM@Work (10/2/2015)
Jason A. Long (10/1/2015)
...Also note that you will need to look at the Actual execution plan to see the plan change. The Estimated plan will continue to show the index scan.
I can't make sense of this at all Jason, can you elaborate?
Actually, Grant does a better job of explaining than I do... The Myth About Estimated Execution Plans
Basically, forcing a recompile can cause the Estimated and Actual plans to differ. So, in this case it would be necessary to look at the actual plan, rather than the estimated plan, to know what's being executed.
This isn't anything to do with recompilation. The whole point of recompilation is to compile a new plan in case the existing cached plan is unsuitable - in circumstances where the likelihood is high enough to make recompiles cost effective.
Execution plans are cached and reused because the process of generating them is CPU-expensive. If you look at a cached plan, it's the "estimated plan". Run the batch associated with it to collect the actual plan and the runtime information is incorporated into it.
The operators won't change though - the actual plan is the estimated plan, with some runtime information added.
Wouldn't it be useful if the relative costs were recalculated from the runtime information each time the plan is used so you have estimated and actual costs side by side, like this: 92% (1.2%)
But what he's saying is, if you set up the query such that gathering the estimated plan comes with one set of statistics creating the plan and then the recompiled plan within the actual execution of the query comes with a second set of statistics creating a second plan, capturing just the estimated plan before executing vs. capturing the "actual" plan or retrieving the plan from cache, they'll look different. I agree. It's not a question of estimated vs. actual, but before and after a recompile with different statistics.
"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
October 2, 2015 at 7:08 am
Grant Fritchey (10/2/2015)
Jason A. Long (10/2/2015)
ChrisM@Work (10/2/2015)
Jason A. Long (10/1/2015)
...Also note that you will need to look at the Actual execution plan to see the plan change. The Estimated plan will continue to show the index scan.
I can't make sense of this at all Jason, can you elaborate?
Actually, Grant does a better job of explaining than I do... The Myth About Estimated Execution Plans
Basically, forcing a recompile can cause the Estimated and Actual plans to differ. So, in this case it would be necessary to look at the actual plan, rather than the estimated plan, to know what's being executed.
Nuts. I was looking forward to a different point of view.
But...never mind 😉 Screenwipe's here somewhere.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 2, 2015 at 7:17 am
Grant Fritchey (10/2/2015)
ChrisM@Work (10/2/2015)
Jason A. Long (10/2/2015)
ChrisM@Work (10/2/2015)
Jason A. Long (10/1/2015)
...Also note that you will need to look at the Actual execution plan to see the plan change. The Estimated plan will continue to show the index scan.
I can't make sense of this at all Jason, can you elaborate?
Actually, Grant does a better job of explaining than I do... The Myth About Estimated Execution Plans
Basically, forcing a recompile can cause the Estimated and Actual plans to differ. So, in this case it would be necessary to look at the actual plan, rather than the estimated plan, to know what's being executed.
This isn't anything to do with recompilation. The whole point of recompilation is to compile a new plan in case the existing cached plan is unsuitable - in circumstances where the likelihood is high enough to make recompiles cost effective.
Execution plans are cached and reused because the process of generating them is CPU-expensive. If you look at a cached plan, it's the "estimated plan". Run the batch associated with it to collect the actual plan and the runtime information is incorporated into it.
The operators won't change though - the actual plan is the estimated plan, with some runtime information added.
Wouldn't it be useful if the relative costs were recalculated from the runtime information each time the plan is used so you have estimated and actual costs side by side, like this: 92% (1.2%)
But what he's saying is, if you set up the query such that gathering the estimated plan comes with one set of statistics creating the plan and then the recompiled plan within the actual execution of the query comes with a second set of statistics creating a second plan, capturing just the estimated plan before executing vs. capturing the "actual" plan or retrieving the plan from cache, they'll look different. I agree. It's not a question of estimated vs. actual, but before and after a recompile with different statistics.
It's a question of two different estimated plans.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 2, 2015 at 7:31 am
ChrisM@Work (10/2/2015)
Grant Fritchey (10/2/2015)
ChrisM@Work (10/2/2015)
Jason A. Long (10/2/2015)
ChrisM@Work (10/2/2015)
Jason A. Long (10/1/2015)
...Also note that you will need to look at the Actual execution plan to see the plan change. The Estimated plan will continue to show the index scan.
I can't make sense of this at all Jason, can you elaborate?
Actually, Grant does a better job of explaining than I do... The Myth About Estimated Execution Plans
Basically, forcing a recompile can cause the Estimated and Actual plans to differ. So, in this case it would be necessary to look at the actual plan, rather than the estimated plan, to know what's being executed.
This isn't anything to do with recompilation. The whole point of recompilation is to compile a new plan in case the existing cached plan is unsuitable - in circumstances where the likelihood is high enough to make recompiles cost effective.
Execution plans are cached and reused because the process of generating them is CPU-expensive. If you look at a cached plan, it's the "estimated plan". Run the batch associated with it to collect the actual plan and the runtime information is incorporated into it.
The operators won't change though - the actual plan is the estimated plan, with some runtime information added.
Wouldn't it be useful if the relative costs were recalculated from the runtime information each time the plan is used so you have estimated and actual costs side by side, like this: 92% (1.2%)
But what he's saying is, if you set up the query such that gathering the estimated plan comes with one set of statistics creating the plan and then the recompiled plan within the actual execution of the query comes with a second set of statistics creating a second plan, capturing just the estimated plan before executing vs. capturing the "actual" plan or retrieving the plan from cache, they'll look different. I agree. It's not a question of estimated vs. actual, but before and after a recompile with different statistics.
It's a question of two different estimated plans.
Exactly. Although I hate the term estimated plan any more. There's a plan, and there's a plan with runtime statistics. Now, how do we get Microsoft to change their terminology?
"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
October 2, 2015 at 7:36 am
Grant Fritchey (10/2/2015)
ChrisM@Work (10/2/2015)
Grant Fritchey (10/2/2015)
ChrisM@Work (10/2/2015)
Jason A. Long (10/2/2015)
ChrisM@Work (10/2/2015)
Jason A. Long (10/1/2015)
...Also note that you will need to look at the Actual execution plan to see the plan change. The Estimated plan will continue to show the index scan.
I can't make sense of this at all Jason, can you elaborate?
Actually, Grant does a better job of explaining than I do... The Myth About Estimated Execution Plans
Basically, forcing a recompile can cause the Estimated and Actual plans to differ. So, in this case it would be necessary to look at the actual plan, rather than the estimated plan, to know what's being executed.
This isn't anything to do with recompilation. The whole point of recompilation is to compile a new plan in case the existing cached plan is unsuitable - in circumstances where the likelihood is high enough to make recompiles cost effective.
Execution plans are cached and reused because the process of generating them is CPU-expensive. If you look at a cached plan, it's the "estimated plan". Run the batch associated with it to collect the actual plan and the runtime information is incorporated into it.
The operators won't change though - the actual plan is the estimated plan, with some runtime information added.
Wouldn't it be useful if the relative costs were recalculated from the runtime information each time the plan is used so you have estimated and actual costs side by side, like this: 92% (1.2%)
But what he's saying is, if you set up the query such that gathering the estimated plan comes with one set of statistics creating the plan and then the recompiled plan within the actual execution of the query comes with a second set of statistics creating a second plan, capturing just the estimated plan before executing vs. capturing the "actual" plan or retrieving the plan from cache, they'll look different. I agree. It's not a question of estimated vs. actual, but before and after a recompile with different statistics.
It's a question of two different estimated plans.
Exactly. Although I hate the term estimated plan any more. There's a plan, and there's a plan with runtime statistics. Now, how do we get Microsoft to change their terminology?
That's unlikely, but encouraging the community to change their terminology shouldn't take much more than a decent book on the subject. Oh, and while we're at it, recalculation of the relative costs using runtime data would be nice too. Am I alone in thinking this would be useful? Where's me corner...
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 2, 2015 at 8:01 am
Got lost here.. What button/menu item should I use to see the actual plan ? ( not the estimated )
October 2, 2015 at 8:03 am
mw112009 (10/2/2015)
Got lost here.. What button/menu item should I use to see the actual plan ? ( not the estimated )
Six objects to the right of "Debug" on the menu bar. Hover your mouse over it and see the tooltip text.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 2, 2015 at 8:05 am
mw112009 (10/2/2015)
Got lost here.. What button/menu item should I use to see the actual plan ? ( not the estimated )
Just remember that the Actual Plan requires that your run the query.
"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
October 2, 2015 at 8:22 am
Can you help me understand this ...
I have the following index defined on hinstp table covering 2 columns ( ADMDT, FORMN )
PK_HINSTPclustered, unique, primary key located on PRIMARYADMDT, FORMN
However when I run the actual query plan it says it does a INDEX SCAN and the cost is 21% Why is this ? See attached 2 files and you will see what I am talking ?
It should have done a INDEX SEEK. Why ? because the 2 columns in the joins are covered by the PRIMARY KEY
October 2, 2015 at 8:24 am
mw112009 (10/2/2015)
Can you help me understand this ...I have the following index defined on hinstp table covering 2 columns ( ADMDT, FORMN )
PK_HINSTPclustered, unique, primary key located on PRIMARYADMDT, FORMN
However when I run the actual query plan it says it does a INDEX SCAN and the cost is 21% Why is this ? See attached 2 files and you will see what I am talking ?
It should have done a INDEX SEEK. Why ? because the 2 columns in the joins are covered by the PRIMARY KEY
Can you attach the plan files rather than pictures of them? Right-click on a plan and save as a .sqlplan file. The files contain a wealth of information which you cannot see in a picture.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 2, 2015 at 8:44 am
Tried to upload file several times and it fails ( Upload about 65% and after that the web page in the small window breaks )
October 2, 2015 at 8:51 am
mw112009 (10/2/2015)
Can you help me understand this ...I have the following index defined on hinstp table covering 2 columns ( ADMDT, FORMN )
PK_HINSTPclustered, unique, primary key located on PRIMARYADMDT, FORMN
However when I run the actual query plan it says it does a INDEX SCAN and the cost is 21% Why is this ? See attached 2 files and you will see what I am talking ?
It should have done a INDEX SEEK. Why ? because the 2 columns in the joins are covered by the PRIMARY KEY
As previously mentioned, please post the entire actual query plan (not estimated).
Some things that could cause this:
Implicit conversion between data types of the columns in the join.
Environmental SET options set different from the query than from when the table was created.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 2, 2015 at 8:53 am
mw112009 (10/2/2015)
Tried to upload file several times and it fails ( Upload about 65% and after that the web page in the small window breaks )
Can you put it somewhere (dropbox, Google Drive, One Drive, etc) and post a link to it?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 2, 2015 at 8:58 am
See whether this works...
https://drive.google.com/file/d/0B2u1Uo4GKWj9V0pQMGZaMlN3Unc/view?usp=sharing
paste link on browser as URL, your browser might think it is an audio file.. never mind.. I was able to download it
October 2, 2015 at 9:08 am
mw112009 (10/2/2015)
Can you help me understand this ...I have the following index defined on hinstp table covering 2 columns ( ADMDT, FORMN )
PK_HINSTPclustered, unique, primary key located on PRIMARYADMDT, FORMN
However when I run the actual query plan it says it does a INDEX SCAN and the cost is 21% Why is this ? See attached 2 files and you will see what I am talking ?
It should have done a INDEX SEEK. Why ? because the 2 columns in the joins are covered by the PRIMARY KEY
The other columns required by the query are not in the index. It's not a "covering" index.
Edit: oops, it's the clustered index. And it's a clustered index scan because most of the rows from the table are retrieved.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 16 through 30 (of 48 total)
You must be logged in to reply to this topic. Login to reply