SQL Server Performance Forum – Threads Archive
Recomplies
Have i missed something or will this give me a top 10 list over recompliles. select top 10[name],
count(objectid) as recomplies
from sys.dm_exec_query_stats as qs
Cross apply sys.dm_exec_sql_text(sql_handle) sql_text
join sys.all_objects as al
on objectid=object_id
where plan_generation_num > 10
group by [name]
order by recomplies desc
Yes, andhttp://sqlserver-qa.net/blogs/perft…op-stored-procedures-that-are-recompiled.aspx fyi. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
I want to have relation between the total cpu time and the time cpu spends on recompiles. Just to be able how impact recompiles have on the server time as total. Is this the query for this or have I missed something. select
(([value]*10001000) /
(select sum(total_worker_time) from sys.dm_exec_query_stats)) * 100 AS ‘% Recompile’
from
sys.dm_exec_query_optimizer_info
where
counter=’elapsed time’
I would like to know what value you are getting with thsi query, any matches to the above query. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
select
(([value]*1000000) /
(select sum(total_worker_time) from sys.dm_exec_query_stats)) * 100 AS ‘% Recompile’
from sys.dm_exec_query_optimizer_info
where counter=’elapsed time’ % Recompile
———————-
1.10665954914644E-05 (1 row(s) affected) Comparing cpu-time from start of the sql with time the server spends on optimazing plans. The start snapshots this to get feeling about what direction the server is heading on recompiles.
I sum up the "Total amount of CPU time, in microseconds, that was consumed by executions of this plan since it was compiled." put that to realtion to "elapsed time, which is the time elapsed due to optimizations. Since the elapsed time during optimization is generally close to the CPU time that is used for the optimization (since the optimization process is very CPU bound), you can get a good measure of the extent to which the compile time is contributing to the high CPU use" ….
Sounds good to me also you could compare by collecting the PERFMON counters –>SQL Statistics/SQL Compilations/sec counter would be a good counter to identify if SQL Server is compiling a number of execution plans; a high value for this counter would be indicative of high CPU utilization. The SQL Server –> SQL Statistics/SQL Re-Compilations/sec counter will indicate the number of times that the server had to recompile an execution plan. If the values are higher than 10 percent, then the many recompilations could be the possible cause of the high CPU utilization. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
]]>