Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Excessive SQL Compilation


Excessive SQL Compilation

Author
Message
DevB
DevB
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 358
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.



magasvs
magasvs
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1285 Visits: 752
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
DevB
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 358
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
Piotr.Rodak
SSC Eights!
SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)

Group: General Forum Members
Points: 802 Visits: 1761
Thanks for sharing this. The trick with sp:CacheMiss is interesting to know.
Piotr

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search