There is a plethora of recommendations to deal with recompilation and keep the performance in optimum conditions, but for now you can take into consideration the following options: Select top 20 SQLText.text, sql_handle, plan_generation_num, execution_count, dbid, objectidĬross apply sys.dm_exec_sql_text(sql_handle) as SQLText Here is an example to check the top 20 most procedures that have been recompiled. Another DMV to use is 'sys.dm_exec_query_stats' whose most important columns to look at are sql_handle, total worker time, plan generation number (the number of times the query has recompiled), and statement Start Offset. If you see that Time Elapsed is very close to the CPU time, you might reach the conclusion that the compilation and recompilation time is attributable to that high CPU use. Talking of DMVs to diagnose recompilation issues, looking into 'sys.dm_exec_query_optimizer_info' is very helpful, in particular, look at the Elapsed Time, which is the time elapsed due to optimizations, and also Final Cost. Once you know where the problem is you can use Engine Tuning Advisor to see whether any indexing changes improve the compile time and the execution time of the query. So, just do it for a very short time as long as you see a high value for the SQL Compilations/sec counter in Performance Monitor. Where EventClass in(37,75,166) - 37 = Sp:Recompile, 75 = CursorRecompile, 166 = SQL:StmtRecompileįurthermore, we can also capture the showplan XML for query compile, but doing that has significant performance overhead because it is captured for each compilation or recompilation. Select spid, StartTime, Textdata, EventSubclass, ObjectID, DatabaseID, SQLHandleįrom fn_trace_gettable( 'C:\RecompilationTrace_01.trc', 1)
0 kommentar(er)
