How to determine what is compiling in SQL Server -

How to determine what is compiling in SQL Server -

in tracking performance monitors on sql server 2008 std edition installation, i've noticed sql compilations/sec spikes every 5 seconds or 3 around 50.

we have relatively high ratio of compilations batch requests/sec. understand should ideally 1/10 ratio, working @ more 8/10.

the db supports busy website number of applications, it's hard pin downwards causing excess compilation, 5 sec spikes. queries stored procedure calls rather embedded sql, , have substantial (48gb) ram.

is there way see @ given moment in time queries in compilation? if so, work out if problematic.

when i’ve had issues plan caching/excessive query recompilation in past i’ve followed guidance provided in microsoft whitepaper ‘plan caching in sql server 2008’ , suggest reading covers plan caching, query plan reuse, causes of recompilations, identifying recompilations , other related topics.

with said, sql server profiler (should under located under microsoft sql server 2008 -> performance tools if installed part of client tools installation) exposes 3 events straight related query compilation may of help you:

cursor cursorrecompile performance showplan xml query compile stored procedure sp:recompile

you using stored procedures need worry sp:recompile event. event fire time stored procedure, trigger or user defined function has been recompiled. textdata column show text of tsql statement caused statement recompilation , eventsubclass column show code indicates reason recompilation.

eventsubclass codes sp:recompile in sql 2008

1 = schema changed 2 = statistics changed 3 = recompile dnr 4 = set alternative changed 5 = temp table changed 6 = remote rowset changed 7 = browse perms changed 8 = query notification environment changed 9 = mpi view changed 10 = cursor options changed 11 = recompile option

if monitor next 5 events you’ll able see stored procedures , statements beingness invoked on sql server , ones triggering recompilations:

store procedures sp:starting sp:stmtstarting sp:recompile sp:completed performance auto stats

i setup profiler trace capture columns events. setup trace 5 events, run trace 30 60 seconds , pause , should have snapshot of causing recompiles.

if there much noise can start adding column filters trace properties filter in/out events. instance if find of recompiles happening on 1 time database, setup column filter on databaseid or databasename column queries run against database included in trace.

then start looking patterns in queries beingness recompiled , utilize whitepaper microsoft guide why might triggering recompile.

sql-server sql-server-2008