Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Excessive SQL Compilation Expand / Collapse
Author
Message
Posted Monday, May 3, 2010 2:44 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 12:02 AM
Points: 62, Visits: 335
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.



Post #914951
Posted Tuesday, May 18, 2010 11:29 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, December 5, 2014 8:24 AM
Points: 1,264, Visits: 735
You can use Profiler events "SP:Recompile Event Class" under "Stored Procedures Event Category" and "SQL:StmtRecompile Event Class" under "TSQL Event Category".

I would recommended to disable job for the time of troubleshooting (see the description of the DBCC FREESYSTEMCACHE):
"Executing DBCC FREESYSTEMCACHE clears the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. For each cleared cachstore in the plan cache, the SQL Server error log will contain the following informational message: "SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations." This message is logged every five minutes as long as the cache is flushed within that time interval."

Post #923774
Posted Tuesday, May 18, 2010 2:55 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 12:02 AM
Points: 62, Visits: 335
DBCC FREESYSTEMCACHE ('TokenAndPermUserStore') will not clear all the procedure cache, it will only clear token entries for the adhoc queries.

Tracing sp:recompile or sqlstatementrecompile event will not help, as we are seeing only new compiles not recompiles. We have used sp:cachemiss to idenfity the new compiles and we have resolved the issue.

The issue was caused by the JBOSS connection string, where it has an option to validate the SQL connection using check-valid-connection-sql, there we have used a SQL statement 'SELECT 1', which was not geting cached, becuase its compiling cost is zero and every time SELECT 1 is being executed, it gets complied. SELECT 1 is being called every time JBOSS sends a SQL statement to the SQL Server.

We have replaced check-valid-connection-sql with background-validation option, where it doesn't validate the connection for every SQL statement.



Post #923928
Posted Tuesday, May 18, 2010 6:02 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 10, 2012 9:49 AM
Points: 800, Visits: 1,759
Thanks for sharing this. The trick with sp:CacheMiss is interesting to know.
Piotr


...and your only reply is slàinte mhath
Post #923994
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse