![]() ![]() Ups, no compiled plan for any stored procedure, just for the latest Ad-hoc query executed (inside the red rectangle). ![]() So far so good… Now I’ll use the sys.dm_exec_cached_plans DMV that “ Returns a row for each query plan that is cached by SQL Server for faster query execution” to look for the SP plan_handle: Ok, this it it! Let’s free the proc cache again and re-execute the SP: DBCC FREEPROCCACHE Now let me alter the SP by adding the RECOMPILE option: ALTER PROCEDURE uspRecompiled WHERE OBJECT_NAME(qt.objectid, qt.dbid) = 'uspRecompiled'Īs we can see in the picture above, there is an entrance corresponding to the execution I just did. ![]() OBJECT_NAME(qt.objectid, qt.dbid) AS ObjectName,įROM sys.dm_exec_query_stats qs WITH (NOLOCK)ĬROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qtĬROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qpĬROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) tqp Using the DMOs, I’ll look for the exec info regarding this SP: SELECT DB_NAME(qt.dbid) AS DataBaseName, Now we’re ready to go! Let’s exectute the SP: EXEC uspRecompiled freeing the proc cache: DBCC FREEPROCCACHE In order to facilitate results reading I’ll start by doing something that shouldn’t be done in a production environment, i.e. In this post I’ll demonstrate that we CAN’T capture exec info for Stored Procedures using DMOs when they’re created with the RECOMPILE option but using Query Store we CAN!įirst I’ll create the stored procedure without using the RECOMPILE option: use LETsLOOKatQUERY_STORE In today’s post I’ll answer this question comparing DMOs and Query Store □ If you create a Stored Procedure with the RECOMPILE option, will you have access to its runtime statistics and execution plan? You can find Part VII here where I showed that using Query Store it’s possible to get the execution plans even for encrypted stored procedures. This is part VIII of several posts about a new feature in SQL Server 2016 called: Query Store! ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |