| Groups | Blog | Home |
sql server new users : How to: find the last time a procedure was compiled and why
Hello,
Does anyone know how to select the last time a stored procedure was compile and which recompile condition caused it? Thanks, E
I am not aware of any way to query that information from the system catalog.
What you can do if you want to start monitoring recompiles is set up a server-side trace and watch the SP:Recompile event. You can periodically insert the trace data into a table, and then you will have your own running record of what happened. The recompile event populates an EventSubStatus column that will tell you why the recompile occurred. For more information see: http://msdn2.microsoft.com/en-us/library/ms187105.aspx -- Adam Machanic SQL Server MVP - http://sqlblog.com Author, "Expert SQL Server 2005 Development" http://www.apress.com/book/bookDisplay.html?bID=10220 [quoted text, click to view] "Edmund" <die_spambot@hatespam.com> wrote in message
news:%23YHLLr9FIHA.5544@TK2MSFTNGP02.phx.gbl... > Hello, > > Does anyone know how to select the last time a stored procedure was > compile and which recompile condition caused it? > > Thanks, > > E >
Thanks Adam, I'll give this a try. By the way, if any does know how to select this information from the system tables, I'm still checking this thread for an answer. [quoted text, click to view] "Adam Machanic" <amachanic@IHATESPAMgmail.com> wrote in message news:04F4785E-C06F-4282-A550-93D5572640F8@microsoft.com... >I am not aware of any way to query that information from the system >catalog. What you can do if you want to start monitoring recompiles is set >up a server-side trace and watch the SP:Recompile event. You can >periodically insert the trace data into a table, and then you will have >your own running record of what happened. The recompile event populates an >EventSubStatus column that will tell you why the recompile occurred. > > For more information see: > http://msdn2.microsoft.com/en-us/library/ms187105.aspx > > > -- > > Adam Machanic > SQL Server MVP - http://sqlblog.com > > Author, "Expert SQL Server 2005 Development" > http://www.apress.com/book/bookDisplay.html?bID=10220 > > > > "Edmund" <die_spambot@hatespam.com> wrote in message > news:%23YHLLr9FIHA.5544@TK2MSFTNGP02.phx.gbl... >> Hello, >> >> Does anyone know how to select the last time a stored procedure was >> compile and which recompile condition caused it? >> >> Thanks, >> >> E >> >
If you are on SQL 2005 have a look at the sys.dm_exec_query_stats DMV. The
Creation_Time should give you what you want. -- Andrew J. Kelly SQL MVP Solid Quality Mentors [quoted text, click to view] "Edmund" <die_spambot@hatespam.com> wrote in message
news:OW10fSXGIHA.4584@TK2MSFTNGP03.phx.gbl... > > Thanks Adam, I'll give this a try. By the way, if any does know how to > select this information from the system tables, I'm still checking this > thread for an answer. > > "Adam Machanic" <amachanic@IHATESPAMgmail.com> wrote in message > news:04F4785E-C06F-4282-A550-93D5572640F8@microsoft.com... >>I am not aware of any way to query that information from the system >>catalog. What you can do if you want to start monitoring recompiles is set >>up a server-side trace and watch the SP:Recompile event. You can >>periodically insert the trace data into a table, and then you will have >>your own running record of what happened. The recompile event populates >>an EventSubStatus column that will tell you why the recompile occurred. >> >> For more information see: >> http://msdn2.microsoft.com/en-us/library/ms187105.aspx >> >> >> -- >> >> Adam Machanic >> SQL Server MVP - http://sqlblog.com >> >> Author, "Expert SQL Server 2005 Development" >> http://www.apress.com/book/bookDisplay.html?bID=10220 >> >> >> >> "Edmund" <die_spambot@hatespam.com> wrote in message >> news:%23YHLLr9FIHA.5544@TK2MSFTNGP02.phx.gbl... >>> Hello, >>> >>> Does anyone know how to select the last time a stored procedure was >>> compile and which recompile condition caused it? >>> >>> Thanks, >>> >>> E >>> >> > >
Sorry but I just realized you also wanted the reason for a recompile as
well. That can only be found via a trace as Adam suggested AFAIK. Since the plan is removed and replaced with a new one each time it recompiles the previous data is lost. -- Andrew J. Kelly SQL MVP Solid Quality Mentors [quoted text, click to view] "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
news:%23$A3sLbGIHA.1208@TK2MSFTNGP03.phx.gbl... > If you are on SQL 2005 have a look at the sys.dm_exec_query_stats DMV. > The Creation_Time should give you what you want. > > -- > Andrew J. Kelly SQL MVP > Solid Quality Mentors > > > "Edmund" <die_spambot@hatespam.com> wrote in message > news:OW10fSXGIHA.4584@TK2MSFTNGP03.phx.gbl... >> >> Thanks Adam, I'll give this a try. By the way, if any does know how to >> select this information from the system tables, I'm still checking this >> thread for an answer. >> >> "Adam Machanic" <amachanic@IHATESPAMgmail.com> wrote in message >> news:04F4785E-C06F-4282-A550-93D5572640F8@microsoft.com... >>>I am not aware of any way to query that information from the system >>>catalog. What you can do if you want to start monitoring recompiles is >>>set up a server-side trace and watch the SP:Recompile event. You can >>>periodically insert the trace data into a table, and then you will have >>>your own running record of what happened. The recompile event populates >>>an EventSubStatus column that will tell you why the recompile occurred. >>> >>> For more information see: >>> http://msdn2.microsoft.com/en-us/library/ms187105.aspx >>> >>> >>> -- >>> >>> Adam Machanic >>> SQL Server MVP - http://sqlblog.com >>> >>> Author, "Expert SQL Server 2005 Development" >>> http://www.apress.com/book/bookDisplay.html?bID=10220 >>> >>> >>> >>> "Edmund" <die_spambot@hatespam.com> wrote in message >>> news:%23YHLLr9FIHA.5544@TK2MSFTNGP02.phx.gbl... >>>> Hello, >>>> >>>> Does anyone know how to select the last time a stored procedure was >>>> compile and which recompile condition caused it? >>>> >>>> Thanks, >>>> >>>> E >>>> >>> >> >> >
Hi Andrew,
What is the the sys.dm_exec_query_stats DMV? I've looked in the systems stored procedures in the database but do not see one by that name. E [quoted text, click to view] "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message news:%23$A3sLbGIHA.1208@TK2MSFTNGP03.phx.gbl... > If you are on SQL 2005 have a look at the sys.dm_exec_query_stats DMV. > The Creation_Time should give you what you want. > > -- > Andrew J. Kelly SQL MVP > Solid Quality Mentors > > > "Edmund" <die_spambot@hatespam.com> wrote in message > news:OW10fSXGIHA.4584@TK2MSFTNGP03.phx.gbl... >> >> Thanks Adam, I'll give this a try. By the way, if any does know how to >> select this information from the system tables, I'm still checking this >> thread for an answer. >> >> "Adam Machanic" <amachanic@IHATESPAMgmail.com> wrote in message >> news:04F4785E-C06F-4282-A550-93D5572640F8@microsoft.com... >>>I am not aware of any way to query that information from the system >>>catalog. What you can do if you want to start monitoring recompiles is >>>set up a server-side trace and watch the SP:Recompile event. You can >>>periodically insert the trace data into a table, and then you will have >>>your own running record of what happened. The recompile event populates >>>an EventSubStatus column that will tell you why the recompile occurred. >>> >>> For more information see: >>> http://msdn2.microsoft.com/en-us/library/ms187105.aspx >>> >>> >>> -- >>> >>> Adam Machanic >>> SQL Server MVP - http://sqlblog.com >>> >>> Author, "Expert SQL Server 2005 Development" >>> http://www.apress.com/book/bookDisplay.html?bID=10220 >>> >>> >>> >>> "Edmund" <die_spambot@hatespam.com> wrote in message >>> news:%23YHLLr9FIHA.5544@TK2MSFTNGP02.phx.gbl... >>>> Hello, >>>> >>>> Does anyone know how to select the last time a stored procedure was >>>> compile and which recompile condition caused it? >>>> >>>> Thanks, >>>> >>>> E >>>> >>> >> >> >
It is not a stored procedure it is a view. More specifically a Dynamic
Management View or DMV. You can find details in BooksOnLine. -- Andrew J. Kelly SQL MVP Solid Quality Mentors [quoted text, click to view] "Edmund" <die_spambot@hatespam.com> wrote in message
news:OEUS7E%23GIHA.280@TK2MSFTNGP03.phx.gbl... > Hi Andrew, > > What is the the sys.dm_exec_query_stats DMV? I've looked in the systems > stored procedures in the database but do not see one by that name. > > E > > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message > news:%23$A3sLbGIHA.1208@TK2MSFTNGP03.phx.gbl... >> If you are on SQL 2005 have a look at the sys.dm_exec_query_stats DMV. >> The Creation_Time should give you what you want. >> >> -- >> Andrew J. Kelly SQL MVP >> Solid Quality Mentors >> >> >> "Edmund" <die_spambot@hatespam.com> wrote in message >> news:OW10fSXGIHA.4584@TK2MSFTNGP03.phx.gbl... >>> >>> Thanks Adam, I'll give this a try. By the way, if any does know how to >>> select this information from the system tables, I'm still checking this >>> thread for an answer. >>> >>> "Adam Machanic" <amachanic@IHATESPAMgmail.com> wrote in message >>> news:04F4785E-C06F-4282-A550-93D5572640F8@microsoft.com... >>>>I am not aware of any way to query that information from the system >>>>catalog. What you can do if you want to start monitoring recompiles is >>>>set up a server-side trace and watch the SP:Recompile event. You can >>>>periodically insert the trace data into a table, and then you will have >>>>your own running record of what happened. The recompile event populates >>>>an EventSubStatus column that will tell you why the recompile occurred. >>>> >>>> For more information see: >>>> http://msdn2.microsoft.com/en-us/library/ms187105.aspx >>>> >>>> >>>> -- >>>> >>>> Adam Machanic >>>> SQL Server MVP - http://sqlblog.com >>>> >>>> Author, "Expert SQL Server 2005 Development" >>>> http://www.apress.com/book/bookDisplay.html?bID=10220 >>>> >>>> >>>> >>>> "Edmund" <die_spambot@hatespam.com> wrote in message >>>> news:%23YHLLr9FIHA.5544@TK2MSFTNGP02.phx.gbl... >>>>> Hello, >>>>> >>>>> Does anyone know how to select the last time a stored procedure was >>>>> compile and which recompile condition caused it? >>>>> >>>>> Thanks, >>>>> >>>>> E >>>>> >>>> >>> >>> >> > >
Hi Andrew,
I've tried the following but with the database name in place of {db} and so on. It does not get me anything. Does that mean there is no execution plan in the database for it. I've run the stored proceedure so there should be something there. SELECT * FROM sys.dm_exec_query_stats WHERE sql_handle = OBJECT_ID('{db}.{scheme}.{name}') [quoted text, click to view] "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message news:elAsFR%23GIHA.5544@TK2MSFTNGP02.phx.gbl... > It is not a stored procedure it is a view. More specifically a Dynamic > Management View or DMV. You can find details in BooksOnLine. > > -- > Andrew J. Kelly SQL MVP > Solid Quality Mentors > > > "Edmund" <die_spambot@hatespam.com> wrote in message > news:OEUS7E%23GIHA.280@TK2MSFTNGP03.phx.gbl... >> Hi Andrew, >> >> What is the the sys.dm_exec_query_stats DMV? I've looked in the systems >> stored procedures in the database but do not see one by that name. >> >> E >> >> "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message >> news:%23$A3sLbGIHA.1208@TK2MSFTNGP03.phx.gbl... >>> If you are on SQL 2005 have a look at the sys.dm_exec_query_stats DMV. >>> The Creation_Time should give you what you want. >>> >>> -- >>> Andrew J. Kelly SQL MVP >>> Solid Quality Mentors >>> >>> >>> "Edmund" <die_spambot@hatespam.com> wrote in message >>> news:OW10fSXGIHA.4584@TK2MSFTNGP03.phx.gbl... >>>> >>>> Thanks Adam, I'll give this a try. By the way, if any does know how >>>> to select this information from the system tables, I'm still checking >>>> this thread for an answer. >>>> >>>> "Adam Machanic" <amachanic@IHATESPAMgmail.com> wrote in message >>>> news:04F4785E-C06F-4282-A550-93D5572640F8@microsoft.com... >>>>>I am not aware of any way to query that information from the system >>>>>catalog. What you can do if you want to start monitoring recompiles is >>>>>set up a server-side trace and watch the SP:Recompile event. You can >>>>>periodically insert the trace data into a table, and then you will have >>>>>your own running record of what happened. The recompile event >>>>>populates an EventSubStatus column that will tell you why the recompile >>>>>occurred. >>>>> >>>>> For more information see: >>>>> http://msdn2.microsoft.com/en-us/library/ms187105.aspx >>>>> >>>>> >>>>> -- >>>>> >>>>> Adam Machanic >>>>> SQL Server MVP - http://sqlblog.com >>>>> >>>>> Author, "Expert SQL Server 2005 Development" >>>>> http://www.apress.com/book/bookDisplay.html?bID=10220 >>>>> >>>>> >>>>> >>>>> "Edmund" <die_spambot@hatespam.com> wrote in message >>>>> news:%23YHLLr9FIHA.5544@TK2MSFTNGP02.phx.gbl... >>>>>> Hello, >>>>>> >>>>>> Does anyone know how to select the last time a stored procedure was >>>>>> compile and which recompile condition caused it? >>>>>> >>>>>> Thanks, >>>>>> >>>>>> E >>>>>> >>>>> >>>> >>>> >>> >> >> >
It doesn't work that way, you need to cross apply the sql text like this:
SELECT t.[text] AS [Adhoc Batch or Object Call], SUBSTRING(t.[text], (qs.[statement_start_offset]/2) + 1, ((CASE qs.[statement_end_offset] WHEN -1 THEN DATALENGTH(t.[text]) ELSE qs.[statement_end_offset] END - qs.[statement_start_offset])/2) + 1) AS [Executed Statement] , qs.* FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t ORDER BY [Adhoc Batch or Object Call] Then just look for or filter on the proc name -- Andrew J. Kelly SQL MVP Solid Quality Mentors [quoted text, click to view] "Edmund" <die_spambot@hatespam.com> wrote in message
news:eqI4GK$GIHA.1316@TK2MSFTNGP02.phx.gbl... > Hi Andrew, > > I've tried the following but with the database name in place of {db} and > so on. It does not get me anything. Does that mean there is no execution > plan in the database for it. I've run the stored proceedure so there > should be something there. > SELECT * > > FROM sys.dm_exec_query_stats > > WHERE sql_handle = OBJECT_ID('{db}.{scheme}.{name}') > > > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message > news:elAsFR%23GIHA.5544@TK2MSFTNGP02.phx.gbl... >> It is not a stored procedure it is a view. More specifically a Dynamic >> Management View or DMV. You can find details in BooksOnLine. >> >> -- >> Andrew J. Kelly SQL MVP >> Solid Quality Mentors >> >> >> "Edmund" <die_spambot@hatespam.com> wrote in message >> news:OEUS7E%23GIHA.280@TK2MSFTNGP03.phx.gbl... >>> Hi Andrew, >>> >>> What is the the sys.dm_exec_query_stats DMV? I've looked in the systems >>> stored procedures in the database but do not see one by that name. >>> >>> E >>> >>> "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message >>> news:%23$A3sLbGIHA.1208@TK2MSFTNGP03.phx.gbl... >>>> If you are on SQL 2005 have a look at the sys.dm_exec_query_stats DMV. >>>> The Creation_Time should give you what you want. >>>> >>>> -- >>>> Andrew J. Kelly SQL MVP >>>> Solid Quality Mentors >>>> >>>> >>>> "Edmund" <die_spambot@hatespam.com> wrote in message >>>> news:OW10fSXGIHA.4584@TK2MSFTNGP03.phx.gbl... >>>>> >>>>> Thanks Adam, I'll give this a try. By the way, if any does know how >>>>> to select this information from the system tables, I'm still checking >>>>> this thread for an answer. >>>>> >>>>> "Adam Machanic" <amachanic@IHATESPAMgmail.com> wrote in message >>>>> news:04F4785E-C06F-4282-A550-93D5572640F8@microsoft.com... >>>>>>I am not aware of any way to query that information from the system >>>>>>catalog. What you can do if you want to start monitoring recompiles is >>>>>>set up a server-side trace and watch the SP:Recompile event. You can >>>>>>periodically insert the trace data into a table, and then you will >>>>>>have your own running record of what happened. The recompile event >>>>>>populates an EventSubStatus column that will tell you why the >>>>>>recompile occurred. >>>>>> >>>>>> For more information see: >>>>>> http://msdn2.microsoft.com/en-us/library/ms187105.aspx >>>>>> >>>>>> >>>>>> -- >>>>>> >>>>>> Adam Machanic >>>>>> SQL Server MVP - http://sqlblog.com >>>>>> >>>>>> Author, "Expert SQL Server 2005 Development" >>>>>> http://www.apress.com/book/bookDisplay.html?bID=10220 >>>>>> >>>>>> >>>>>> >>>>>> "Edmund" <die_spambot@hatespam.com> wrote in message >>>>>> news:%23YHLLr9FIHA.5544@TK2MSFTNGP02.phx.gbl... >>>>>>> Hello, >>>>>>> >>>>>>> Does anyone know how to select the last time a stored procedure was >>>>>>> compile and which recompile condition caused it? >>>>>>> >>>>>>> Thanks, >>>>>>> >>>>>>> E >>>>>>> >>>>>> >>>>> >>>>> >>>> >>> >>> >> > >
Don't see what you're looking for? Try a search.
|
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |