SQL Server Performance

Excessive SQL Compliation

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by devadossb@hotmail.com, May 3, 2010.

  1. 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_plans size_in_MBs avg_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.
  2. satya Moderator

    {Please do not duplicate the posts with same subject, see your other post.

Share This Page