Compilation and Recompilation Information from Performance Counters

First create the following function  which will capture information from the current instance of SQL Server

create function dbo.lib_fn_sqlobject ()

returns varchar(80)
as
begin

 declare @sqlobject varchar(80)
 select @sqlobject = object_name
 from sys.dm_os_performance_counters
 where object_name like '%sql statistics%'
 and counter_name = 'sql compilations/sec'
 set @sqlobject = substring(@sqlobject,1,charindex(':',@sqlobject))
 return @sqlobject
end

/*

Check Number of Compilations and Recompilations / Sec
If % of Compliations and Recompilations compared
to batch requests are high ( > 50 %) , you need to check
queries are properly parameterized.

*/

declare @sqlobject varchar(80)
exec @sqlobject = dbo.lib_fn_sqlobject
select b.counter_name,a.cntr_value,b.cntr_value,b.cntr_value *100.00/a.cntr_value as percentage
from sys.dm_os_performance_counters a,
sys.dm_os_performance_counters b
where a.object_name = @sqlobject+'SQL Statistics'
and a.counter_name in ('Batch Requests/sec')
and b.object_name = @sqlobject + 'SQL Statistics'
and b.counter_name in ('SQL Compilations/sec','SQL Re-Compilations/sec')
----------------------------------------------------------------------

/*

Check Number Safe Auto-Params and Failed Auto-Params /sec
If Failed Auto-Params /sec Percentage returns a high value ,
queries are creating new plans . Try to use sp_executesql and
check the query parameters for high faile auto parameterization.
This will occur mostly for adhoc queries.

You can test whether setting parameterization to 'FORCED' at database level

will improve the performance. Do a test on development server before promoting to production server.
*/

declare @sqlobject varchar(80)
exec @sqlobject = dbo.lib_fn_sqlobject
select b.counter_name,b.cntr_value *100.00/a.cntr_value as percentage
from sys.dm_os_performance_counters a,
sys.dm_os_performance_counters b
where a.object_name = @sqlobject+'SQL Statistics'
and a.counter_name in ('Auto-Param Attempts/sec')
and b.object_name = @sqlobject+'SQL Statistics'
and b.counter_name in ('Failed Auto-Params/sec','Safe Auto-Params/sec')
----------------------------------------------------------------------
/*
Check Plan Cache Hit Ratio for each cache Object type
If  % is less than 80 % investigate further
*/



declare @sqlobject varchar(80)

exec @sqlobject = dbo.lib_fn_sqlobject

select b.instance_name,a.cntr_value base ,b.cntr_value counter,
b.cntr_value *100.00/a.cntr_value cache_hit_ratio
from sys.dm_os_performance_counters a,
sys.dm_os_performance_counters b
where a.object_name = @sqlobject+'Plan Cache'
and a.cntr_type = 1073939712
and b.object_name = @sqlobject+'Plan Cache'
and b.cntr_type = 537003264
and a.instance_name = b.instance_name
and a.cntr_value > 0
----------------------------------------------------------------------