November 10, 2015 at 1:05 am
These are the values on a 3rd party production virtual sql server, with 12 CPUs & 32 GB RAM:
object_namecounter_nameinstance_namecntr_value
SQLServer:Buffer ManagerBuffer cache hit ratio27669
SQLServer:Buffer ManagerBuffer cache hit ratio base27787
SQLServer:Buffer ManagerPage life expectancy75705
SQLServer:Plan CacheCache Hit RatioSQL Plans575
SQLServer:Plan CacheCache Hit Ratio BaseSQL Plans743
SQLServer:SQL StatisticsBatch Requests/sec81980543
SQLServer:SQL StatisticsSQL Compilations/sec46401390
SQLServer:SQL StatisticsSQL Re-Compilations/sec9428242
BCHR & PLE seem fine - so no memory pressure, right ?
However, more than 50% of batch requests are compilations.
Optimize for ad-hoc workloads is set to true.
Out of 35000 cached plans, 25000 are single-use :
select count(*) from sys.dm_exec_cached_plans where usecounts = 1
Does this indicate CPU pressure? How can no. of compilations be reduced ?
November 10, 2015 at 9:10 am
Find out what is causing the compile.
Could be an application sending text down the line with the parameter values in the string.
Could be a proc with DSQL that also has not been properly parameterised and is doing exec(@sql) instead of
exec sp_executeSQL @sql,@param,@var1,@var2 etc.
Here is some sql that will give the query string which has the same query plan but differeing query text, where the query has been executed only once.
declare @var varchar(50)
set @var = (select query_hash from (
SELECT top 1 query_hash,COUNT(*) AS sum_count
FROM (SELECT query_hash FROM sys.dm_exec_query_stats s WHERE execution_count = 1) window
GROUP BY query_hash
ORDER BY 2 DESC) window)
SELECT top 10
s.total_elapsed_time/CAST(s.execution_count AS DECIMAL) AS elapsed_time_per_execution,
SUBSTRING(text, statement_start_offset/2+1,
((CASE WHEN statement_end_offset = -1 THEN DATALENGTH(text)
ELSE statement_end_offset
END - statement_start_offset)/2) + 1) AS running_statement,
text AS current_batch,
CAST(p.query_plan AS XML) AS query_plan,
db_name(t.dbid) AS DatabaseName,
s.*
FROM sys.dm_exec_query_stats s
cross apply sys.dm_exec_sql_text(sql_handle) t
outer apply sys.dm_exec_text_query_plan(plan_handle, statement_start_offset, statement_end_offset) p
WHERE query_hash = @var
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply