Query text during caching in SQL 2005

Question

Query text during caching in SQL 2005

Viewing 11 posts - 16 through 25 (of 25 total)

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

Carlo Romagnano SSC-Insane Points: 22665 More actions · Answer 1

Oleg Netchaev (11/15/2010)
Carlo Romagnano (11/14/2010)
In sql2005, I get an error:
"plan_handle" is not a recognized table hints option.
Carlo,
Please check compat level of your SQL Server 2005 AdventureWorks database. If the level is 80 then you should get "plan_handle" is not a recognized table hints option error, but if it is 90 as it should be then the script should run just fine.
Oleg

I tried at midnight, maybe, my notebook or I was out of order. 😀

Thank you

mojo-168709 SSCommitted Points: 1825 More actions · Answer 2

Using the code supplied, I got this result:

-- uspPrintError prints error information about the error that caused -- execution to jump to the CATCH block of a TRY...CATCH construct. -- Should be executed from within the scope of a CATCH block otherwise -- it will return without printing any error information. CREATE PROCEDURE [dbo].[uspPrintError] AS BEGIN SET NOCOUNT ON; -- Print error information. PRINT 'Error ' + CONVERT(varchar(50), ERROR_NUMBER()) + ', Severity ' + CONVERT(varchar(5), ERROR_SEVERITY()) + ', State ' + CONVERT(varchar(5), ERROR_STATE()) + ', Procedure ' + ISNULL(ERROR_PROCEDURE(), '-') + ', Line ' + CONVERT(varchar(5), ERROR_LINE()); PRINT ERROR_MESSAGE(); END;

SELECT st.text QueryText FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st WHERE text LIKE N'%uspPrintError%';

The only way to get the "correct" answer the author intended is to remove the "GO statement between EXEC uspPrintError and the SELECT stmt.

I'm using SQL 2005, compat. level 90.

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 3

mojo-168709 (11/15/2010)
Using the code supplied, I got this result:
-- uspPrintError prints error information about the error that caused -- execution to jump to the CATCH block of a TRY...CATCH construct. -- Should be executed from within the scope of a CATCH block otherwise -- it will return without printing any error information. CREATE PROCEDURE [dbo].[uspPrintError] AS BEGIN SET NOCOUNT ON; -- Print error information. PRINT 'Error ' + CONVERT(varchar(50), ERROR_NUMBER()) + ', Severity ' + CONVERT(varchar(5), ERROR_SEVERITY()) + ', State ' + CONVERT(varchar(5), ERROR_STATE()) + ', Procedure ' + ISNULL(ERROR_PROCEDURE(), '-') + ', Line ' + CONVERT(varchar(5), ERROR_LINE()); PRINT ERROR_MESSAGE(); END;
SELECT st.text QueryText FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st WHERE text LIKE N'%uspPrintError%';
The only way to get the "correct" answer the author intended is to remove the "GO statement between EXEC uspPrintError and the SELECT stmt.
I'm using SQL 2005, compat. level 90.

I am confused -- the code you posted IS the expected result? CREATE instead of EXEC?

UMG Developer SSChampion Points: 13482 More actions · Answer 4

UMG Developer

SSChampion

Points: 13482

November 15, 2010 at 3:30 pm

#1251036

Thaks for the question.

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 5

EXEC uspPrintError

GO

I know before this thing but I answered it wrong bcoz

EXEC uspPrintError

GO

what this doing over there.

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 6

Oleg Netchaev (11/15/2010)
Carlo Romagnano (11/14/2010)
In sql2005, I get an error:
"plan_handle" is not a recognized table hints option.
Carlo,
Please check compat level of your SQL Server 2005 AdventureWorks database. If the level is 80 then you should get "plan_handle" is not a recognized table hints option error, but if it is 90 as it should be then the script should run just fine.
Oleg

Thanks for resolving the issue, due to project work not able to answer.

Thanks

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 7

Hugo Kornelis (11/14/2010)
Good question. I answered wrong; I thought all statements were cached.
Hardik, do you have any references where I can read more about this?

Thanks Hugo. Sorry I don't have material for the same. I learned this when I face similar type of issue (I asked Pinal who is SQL Server MVP). So, thought to share to all 🙂

Thanks

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 8

Carlo Romagnano (11/15/2010)

Execute the batch without the 'GO'

EXEC uspLogError

SELECT st.text QueryText,objtype

FROM sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

WHERE text LIKE N'%uspLogError%'

Result:

CREATE PROCEDURE [dbo].[uspLogError] ....,'Proc'

EXEC uspLogError SELECT st.text QueryText FROM sys.dm_exec_cached_plans ....,'Adhoc'

The body of the stored procedure is cached in the objtype = 'Proc' (see also other info about the proc). the second record is the plan stored with objtype = 'Adhoc'

See also: http://msdn.microsoft.com/en-us/library/ms187404.aspx%5B/quote%5D

Execute the batch with GO - is the real part. thanks for validating & provide to link to all.

Thanks

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 9

RichardDouglas (11/15/2010)
Good question, thanks for taking the time to help educate the SQL community.
I look at these DMV's a lot so thankfully answered this one correctly.

Good to know.

Thanks

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

Thanks for the question.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

U J SSCrazy Points: 2423 More actions · Answer 11

U J

SSCrazy

Points: 2423

May 24, 2011 at 8:29 am

#1329103

Good Question 🙂