Excessive SQL Compilation

  • DevB

    Old Hand

    Points: 314


    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.


    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


    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.

  • magasvs


    Points: 7659

    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."

  • DevB

    Old Hand

    Points: 314

    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.

  • Piotr.Rodak

    SSCrazy Eights

    Points: 9296

    Thanks for sharing this. The trick with sp:CacheMiss is interesting to know.


    ...and your only reply is slàinte mhath

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply