December 7, 2011 at 11:57 am
I have a stored procedure that I'm trying to speed up. I've pulled the code from the stored procedure so I can run it as a standard query. I've written 2 alternate versions and have 2 underlying tables to which each can write, one with a clustered index and one without. I want to make sure they aren't all using the same execution plan, so I'm using the OPTION(RECOMPILE) hint at the end of each query.
I started using OPTION(RECOMPILE) because when I use "Include Actual Execution Plan", they all seem to use the same execution plan. They're very similar queries. The first is designed to avoid a subsequent UPDATE query and the second includes that and avoids the use of views.
Am I interpreting this correctly in thinking that OPTION(RECOMPILE) will create a new execution plan for each of these queries no matter what? I started using it because the original query is partly generated by SQL Server and has a Top (100) PERCENT in the SELECT. I got rid of this in both of my alternate queries, but all six execution plans were the same, including a TOP Cost: 0% in the execution plan. Now that I'm doing the OPTION(RECOMPILE) I thought that would go away in the queries without TOP(100) PERCENT, but it is still there, in fact the execution plans still all look the same. The ones with the UPDATE have a second execution plan, but other than that, they're all the same.
Am I doing anything wrong here?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
December 7, 2011 at 1:43 pm
It almost sounds like there might a view involved. You sure they're all tables? If you eliminated the TOP, but you're still getting it in the plans, even with RECOMPILE... something is up.
"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
December 7, 2011 at 1:55 pm
Grant Fritchey (12/7/2011)
It almost sounds like there might a view involved. You sure they're all tables? If you eliminated the TOP, but you're still getting it in the plans, even with RECOMPILE... something is up.
Yeah, the third and sixth of the six queries I've explicitly removed all views from the query. I just double checked. I'll triple check tonight when I run the tests again.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
December 8, 2011 at 8:40 am
Have you cleared the cache to make sure it isn't still trying to pull info from the cached copy of the proc?
December 8, 2011 at 8:47 am
Brandie Tarvin (12/8/2011)
Have you cleared the cache to make sure it isn't still trying to pull info from the cached copy of the proc?
It is a production server, best I can do is OPTION(RECOMPILE) (I think). Clearing the cache would force new execution plans for everything and I don't want to do that.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
December 8, 2011 at 8:57 am
Can you post the plans?
RECOMPILE means that the plans will never be cached and hence will have to be recreated on each execution. Grant has a blog post on that.
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
December 8, 2011 at 9:11 am
GilaMonster (12/8/2011)
Can you post the plans?RECOMPILE means that the plans will never be cached and hence will have to be recreated on each execution. Grant has a blog post on that.
Posting the plans would mean taking a screenshot and posting the image, right?
I was using RECOMPILE for testing and only for testing. I'd take that out when I turned it into a stored procedure. I'd noticed the TOP in the plan even when there were no views or TOP in the query, so I was trying to force a new plan to make sure it wasn't using a plan created for a different version of the query.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
December 8, 2011 at 9:34 am
Stefan Krzywicki (12/8/2011)
Brandie Tarvin (12/8/2011)
Have you cleared the cache to make sure it isn't still trying to pull info from the cached copy of the proc?It is a production server, best I can do is OPTION(RECOMPILE) (I think). Clearing the cache would force new execution plans for everything and I don't want to do that.
We're talking 2008, right? You can DBCCFREEPROCACHE (plan_handle). That'll get that one plan out.
"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
December 8, 2011 at 9:35 am
Stefan Krzywicki (12/8/2011)
GilaMonster (12/8/2011)
Can you post the plans?RECOMPILE means that the plans will never be cached and hence will have to be recreated on each execution. Grant has a blog post on that.
Posting the plans would mean taking a screenshot and posting the image, right?
No. Pictures of the plan are near-useless. Save the plan and post that file.
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
December 8, 2011 at 9:36 am
Stefan Krzywicki (12/8/2011)
GilaMonster (12/8/2011)
Can you post the plans?RECOMPILE means that the plans will never be cached and hence will have to be recreated on each execution. Grant has a blog post on that.
Posting the plans would mean taking a screenshot and posting the image, right?
I was using RECOMPILE for testing and only for testing. I'd take that out when I turned it into a stored procedure. I'd noticed the TOP in the plan even when there were no views or TOP in the query, so I was trying to force a new plan to make sure it wasn't using a plan created for a different version of the query.
You can do a save as .sqlplan file. That's what's needed.
"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
December 8, 2011 at 9:44 am
Grant Fritchey (12/8/2011)
Stefan Krzywicki (12/8/2011)
Brandie Tarvin (12/8/2011)
Have you cleared the cache to make sure it isn't still trying to pull info from the cached copy of the proc?It is a production server, best I can do is OPTION(RECOMPILE) (I think). Clearing the cache would force new execution plans for everything and I don't want to do that.
We're talking 2008, right? You can DBCCFREEPROCACHE (plan_handle). That'll get that one plan out.
2008 R2
How do I get the plan_handle?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
December 8, 2011 at 9:44 am
GilaMonster (12/8/2011)
Stefan Krzywicki (12/8/2011)
GilaMonster (12/8/2011)
Can you post the plans?RECOMPILE means that the plans will never be cached and hence will have to be recreated on each execution. Grant has a blog post on that.
Posting the plans would mean taking a screenshot and posting the image, right?
No. Pictures of the plan are near-useless. Save the plan and post that file.
OK, got it. Wasn't sure that'd be useful.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
December 8, 2011 at 9:54 am
Here are the three plans.
Initial Query is the initial query. It uses views, has an update to change text after the insert and has a TOP (100) PERCENT in the SELECT
No Update is the first revision. It still uses views, but changes the text with a CASE statement instead of a subsequent UPDATE statement and there's no TOP in the SELECT
No Views is the second revision. It is the same as No Update, but has subqueries replacing the views.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
December 8, 2011 at 12:00 pm
Stefan Krzywicki (12/8/2011)
Grant Fritchey (12/8/2011)
Stefan Krzywicki (12/8/2011)
Brandie Tarvin (12/8/2011)
Have you cleared the cache to make sure it isn't still trying to pull info from the cached copy of the proc?It is a production server, best I can do is OPTION(RECOMPILE) (I think). Clearing the cache would force new execution plans for everything and I don't want to do that.
We're talking 2008, right? You can DBCCFREEPROCACHE (plan_handle). That'll get that one plan out.
2008 R2
How do I get the plan_handle?
Lots of joy to be had from DMOs:
SELECT der.plan_handle
FROM sys.dm_exec_requests AS der
CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) AS dest
WHERE dest.text LIKE '%some recognizable string from my query%'
--and yeah, if you can make that text search an = or LIKE 'xx%'
--you're better off.
Get Tim Ford's & Louis Davidson's book for a great overview.
"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
December 8, 2011 at 12:09 pm
Grant Fritchey (12/8/2011)
Stefan Krzywicki (12/8/2011)
Grant Fritchey (12/8/2011)
Stefan Krzywicki (12/8/2011)
Brandie Tarvin (12/8/2011)
Have you cleared the cache to make sure it isn't still trying to pull info from the cached copy of the proc?It is a production server, best I can do is OPTION(RECOMPILE) (I think). Clearing the cache would force new execution plans for everything and I don't want to do that.
We're talking 2008, right? You can DBCCFREEPROCACHE (plan_handle). That'll get that one plan out.
2008 R2
How do I get the plan_handle?
Lots of joy to be had from DMOs:
SELECT der.plan_handleFROM sys.dm_exec_requests AS der
CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) AS dest
WHERE dest.text LIKE '%some recognizable string from my query%'
--and yeah, if you can make that text search an = or LIKE 'xx%'
--you're better off.
Get Tim Ford's & Louis Davidson's book for a great overview.
Thanks
I have a book on DMVs on my nightstand that I was just about to start reading, it is the one by Ian Stirk. I'd seen him give a presentation on DMVs a few years ago and he's the one that introduced me to DMVs, so I got his book when it came out.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply