May 26, 2011 at 1:44 pm
I think the deferred compile event is now called Recompile DNR as both are event subclass 3. Gail mentions Deferred Compile in her blog here[/url]. Also in my 2008 R2 instance I was able to get a Deferred Compile event subclass result in Profiler. It is also listed in sys.trace_subclass_values.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 27, 2011 at 1:17 am
User Parameterization to Forced
check this query
select top 50
st.text
,qt.query_hash
,qt.query_plan_hash
,qt.plan_generation_num
,execution_count
,total_physical_reads
,total_logical_reads
from sys.dm_exec_query_stats qt
cross apply sys.dm_exec_sql_text(qt.sql_handle) st
where total_physical_reads > 10
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 27, 2011 at 2:48 am
temp db by default has auto create and auto update statistics. When data volume changes in your temp table, SQL is likely to update the statistics after approx 10% data change. Now when the tables are referred in your query, it will see that the statistics has changed for the tables and it will try to recompile your query to get an optimal plan.
Questions:
When did it all started? Any pattern or suddenly it started consuming 100% CPU?
Was there any major change to the application referring to the database?
Was there any change at OS level or any other s/w installed in your database server?
Options:
If any major change is implemented and it's a result of that then can the change be reversed? If not, can the code be tuned? If not, go for a better processing power.
Please collect total number of batches for sec (perfmon) and check if recompilations are more than 10% of the batch. If YES then optimize code. If NOT then go for better processing power.
Before going for additional h/w, I 'wd check for the root cause for the issue as mentioned in Questions:.
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply