Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Query text during caching in SQL 2005 Expand / Collapse
Author
Message
Posted Monday, November 15, 2010 6:31 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 1:32 PM
Points: 581, Visits: 733
Great question! Learned something new. Thanks.
Post #1020711
Posted Monday, November 15, 2010 7:10 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, October 13, 2014 2:35 PM
Points: 985, Visits: 1,828
It is correct to say that removing the GO between the EXEC call and the SELECT against the DMV changes the behavior. Clearly there is some caching going on at least for the duration of the batch itself, and removing it from cache when the batch completes.
Post #1020759
Posted Monday, November 15, 2010 7:37 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 11:26 AM
Points: 668, Visits: 485
Learned something new from the discussion. Thanks guys.
Post #1020779
Posted Monday, November 15, 2010 8:05 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:48 AM
Points: 2,600, Visits: 3,947
The distribution of answers is almost even. That shows that this was a real brain tickler.
Post #1020794
Posted Monday, November 15, 2010 9:39 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:21 AM
Points: 1,676, Visits: 1,757
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
Post #1020883
Posted Monday, November 15, 2010 9:53 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:02 AM
Points: 2,560, Visits: 2,419
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
Post #1020896
Posted Monday, November 15, 2010 2:50 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 10:46 AM
Points: 911, Visits: 831
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.
Post #1021105
Posted Monday, November 15, 2010 2:55 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, October 13, 2014 2:35 PM
Points: 985, Visits: 1,828
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?
Post #1021109
Posted Monday, November 15, 2010 3:30 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,160, Visits: 2,191
Thaks for the question.
Post #1021136
Posted Monday, November 15, 2010 3:31 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 4:17 PM
Points: 477, Visits: 1,047
EXEC uspPrintError
GO

I know before this thing but I answered it wrong bcoz

EXEC uspPrintError
GO

what this doing over there.



--SQLFRNDZ
Post #1021137
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse