August 6, 2010 at 6:24 am
We've got a procedure in our system that i cannot get an estimated execution plan (actual plan is fine) for. If i drop and run the procedure back in i can get an estimated plan but within about 10seconds if i try it again i can't get it. The error i'm getting is as if i had a:
SELECT Columns INTO #tempTable FROM TableName
in the procedure. Please note i do not have this code in my proc, i use a create table and insert into!
The error is:
Msg 208, Level 16, State 0, Procedure spProcName, Line 307
Invalid object name '#tempTable'.
Now, this procedure does have this temporary table within it but it is definately created and inserted into in the correct manner in order to get and estimated plan - as proved by the fact i can get one when i first run the procedure into the system.
If i run:
SELECT TOP 10
[cp].[refcounts]
, [cp].[usecounts]
, [cp].[objtype]
, [st].[dbid]
, [st].[objectid]
, [st].[text]
, [qp].[query_plan]
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text ( cp.plan_handle ) st
CROSS APPLY sys.dm_exec_query_plan ( cp.plan_handle ) qp
WHERE [st].[text] LIKE '%spProcName%';
on the live system i have 2 entries, one with an XML query_plan output, the other without but both with the same Text. On our Test system its very similar apart from neither have an XML query_plan output.
Has anyone encountered this before?
System setup:
Live - SQL 2005 Enterprise (9.0.4226)
Test - SQL 2005 Standard (9.0.4230)
thanks
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
August 6, 2010 at 6:43 am
The first thing I notice is that your SELECT statement and your error message don't use the same temp table name. Is this how it is in your code? Or was it just a scrub accident when pasting into SCC?
Secondly, if everything is going into a temp table, my assumption is that the Estimated Execution Plan can't find itself later because the engine doesn't know what your temp table will look like. Hence, no plan.
Try this experiment. Use a CREATE dbo.#TempTable statement and do a regular INSERT instead of a SELECT...INTO. Then run your Estimated plan a few times (at the same intervals as before) and see if that changes the behavior.
August 6, 2010 at 7:28 am
You can't get estimated plans on queries with temp tables because the algebrizer fails on the initial run. The recompile that occurs when temp tables are present is when the temp table is acknowledged by the algebrizer & the actual plan is generated.
"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
August 6, 2010 at 7:56 am
@Brandie:
Temp tablename is a typo from me, i changed the tablename when copying the code and error.
As stated in my original post, i do create the temporary table in the correct manner as in i do a create table then an insert into.
@Grant:
Yes you can get a plan as i also stated in my original post that i can get one if i drop and create the procedure again but after about 10 seconds, if i try and get the plan again it fails.
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
August 6, 2010 at 8:12 am
Swirl80 (8/6/2010)
Yes you can get a plan as i also stated in my original post that i can get one if i drop and create the procedure again but after about 10 seconds, if i try and get the plan again it fails.
Estimated plans will fail when the proc creates a temp table within it, unless the temp table has been created prior to the request for the estimated plan. Because an estimated plan doesn't run any code, it won't run the CREATE TABLE statements and any reference to that table will then fail with a 208 error. Doesn't matter whether it's a CREATE TABLE or a SELECT INTO. Actual plan will work, because the code is run.
Are you just creating the proc, or running it as well? If there's a temp table with matching name, the estimated plan may work, because, while the create table doesn't run, there's an existing matching temp table name.
Re the two plans in cache, same set options? What's the text? The full CREATE PROCEDURE? Schemas the same? Databases the same?
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
August 6, 2010 at 9:30 am
@Gila:
If you create this:
CREATE PROCEDURE [dbo].[spProcTest]
AS
BEGIN
CREATE TABLE #qwerty
(
id INT
)
SELECT * FROM #qwerty
END
and get and estimated plan on it then it works. I can 100% guarantee that there is not a temporary table in any part of our system called #qwerty.
As for the Text on the 2 entries, they're the same in the fact that they're showing the full procedure text, ie the header/Create Proc/Contents etc etc. Both exactly the same, word for word.
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
August 6, 2010 at 11:20 am
Swirl80 (8/6/2010)
@Gila:If you create this:
CREATE PROCEDURE [dbo].[spProcTest]
AS
BEGIN
CREATE TABLE #qwerty
(
id INT
)
SELECT * FROM #qwerty
END
and get and estimated plan on it then it works. I can 100% guarantee that there is not a temporary table in any part of our system called #qwerty.
As for the Text on the 2 entries, they're the same in the fact that they're showing the full procedure text, ie the header/Create Proc/Contents etc etc. Both exactly the same, word for word.
Time to rewrite my book. You're right. I'm wrong. I just tested 2000, 2005 & 2008 R2. It works as you described in '05 & 08. A little research shows the change occurred on SP2 of 2005. I missed that one. Sorry.
You're right. This is odd behavior.
"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
August 6, 2010 at 11:50 am
This may sound odd, because I'm throwing things at the wall, but what if it works the first time is because when it creates the proc initially, SQL Server is "temporarily" creating the temp tables as it compiles the proc.
Yeah, I'm know I'm not making sense. If it's going to do it once, it should do it twice, but what if the second re-run in the same session is going off some cached information that doesn't include all the info from when the first Create statement was run?
August 6, 2010 at 12:26 pm
I notice they're not at the same SP... since this functionality was introduced with an SP on 2005, maybe you're on the wrong one?
"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
August 6, 2010 at 12:47 pm
@Grant:
Not sure what you're refering to? Whats not on the same SP? We're on Service Pack 3 Cumulative Update 4 on live and Service Pack 3 Cumulative Update 5 on test and get the same issue on both...
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
August 6, 2010 at 12:53 pm
Swirl80 (8/6/2010)
@Grant:Not sure what you're refering to? Whats not on the same SP? We're on Service Pack 3 Cumulative Update 4 on live and Service Pack 3 Cumulative Update 5 on test and get the same issue on both...
Sorry, said SP, meant Version. Whatever... Just guessing at this point.
The thing is, it sounds like behavior I would expect from 2000 or 2005 pre-sp2. But it's only coming on intermittently... Not sure. You should probably try opening a case with Connect.
"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
August 9, 2010 at 7:51 am
Thanks for that, i'll do some more digging before splashing out on Microsoft support which i've found in the past to be of very little use 😉
Bound to be something very simple but i just can't spot it yet
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
August 10, 2010 at 9:04 am
Bit of an update for those interested:
Found that the reason for the duplicate entries is down to SET options being different in the procedures which call this procedure. There are 5 procs which can call this and there are 3 variations of the set options on the "parent" procedure when they were ran onto the system - funnily enough only 2 procedures cached though.......
From looking into the Binary values of the set options i could see that it was the ANSI_NULLS and QUOTED_IDENTIFIER SET options that differed. I plan to amend the 3 procedures that differ from the rest and run them in and see how the plan cache is affected.
Still need to find out why i'm not getting an XML plan from one of these cached plans as well as why it sometimes doesn't give an estimated plan due to the temp table issue.
Its a start tho.....:-)
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply