Excessive SQL Compilation

  • 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

    ObjectCache in MBNum of entries

    SQL Plans3235114779

    Object Plans141385

    Bound Trees31337

    TokenAndPermUserStore817384

    Extended Stored Procedures032

    sys.dm_Exec_cached_plans

    objtypenumber_of_planssize_in_MBsavg_use_count

    UsrTab104

    Prepared8098656378

    View318302576

    Adhoc11405329641152

    Check18035

    Trigger70109016

    Proc41014470752

    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.

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

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

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

    Piotr

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

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

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