SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Excessive SQL Compilations
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

DevB
Starting Member

2 Posts

Posted - 05/03/2010 :  16:55:36  Show Profile  Reply with Quote
Hi,
For last one week (since a new application release), I have been seeing lot of SQL compilations has been happening. We used to have 400-500 SQL compilation per minute and 100 Procedure cache hit ratio. But now the SQL compilation per minute went up to 8000-12000 SQL compilations per minute.

The changes we have made in the application
1. Changed 3 columns in table XYZ from VARCHAR to NVARCHAR
2. Created a new Datasource in the application to support unicode characters and using that data source to access that table XYZ.

We have verified that all the prepared statement using that data course doesn't cause any index scan, because of the data type change.

The new symptoms we see...
1. Excessive SQL compilations.
2. Procedure cache hit ratio is around 94% (went down from 100%)
3. Page Life Expectancy went down too.

4. I have already ran a sql query to see what is there is in the sys.dm_os_memory_cache_counters & sys.dm_Exec_cached_plans.

sys.dm_os_memory_cache_counters

Object Cache in MB Num of entries
SQL Plans 3235 114779
Object Plans 141 385
Bound Trees 31 337
TokenAndPermUserStore 8 17384
Extended Stored Procedures 0 32

sys.dm_Exec_cached_plans

objtype number_of_plans size_in_MBs avg_use_count
UsrTab 1 0 4
Prepared 809 86 56378
View 318 30 2576
Adhoc 114053 2964 1152
Check 18 0 35
Trigger 7 0 109016
Proc 410 144 70752


The questions I have...

1. How do I see which statements causing SQL compilations (I can trace SQL Recompile using Profiler or server side tracing), can I SQL Profiler to
capture? If so what event?

2. I'm assuming that procedure and prepared statements cache plans are flushed out, it compiles a new plan every time a new SQL runs. How do I find out what is causing the plan cache to be flushed? Is my assumtion correct?

3. I already have a schedules SQL Job to clear TokenAndPermUserStore entries using DBCC FREESYSTEMCACHE ('TokenAndPermUserStore'), as we have a lot of ad-hoc SQL queries. But this has been there long time, so I'm sure that this is not causing the sudden SQL compilation spike.
I'm planning to disable this job, to see if this causing this issue.


Thank you for your help.

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 05/03/2010 :  23:05:11  Show Profile  Reply with Quote
The Problem I see over here is you are using DBCC FREESYSTEMCACHE to clear the cache which causes recompilations for execution plans. Remove this job immediately.
Go to Top of Page

DevB
Starting Member

2 Posts

Posted - 05/04/2010 :  00:25:20  Show Profile  Reply with Quote
I'm not clearing the entire plan cache and I'm only clearing part of it, 'TokenAndPermUserStore', do you think that will cause SQL compilations. BTW - we only see compilations not re-compilations.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 05/05/2010 :  19:45:49  Show Profile  Reply with Quote
You donot need to do this. The issue with TokenandPermuserStore was fixed in SP3 or after build 09.00.3179.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000