在Sql Server中,通过执行计划缓存发现的问题SQL实例
注意以下sql都较复杂,在数据库中执行时间可能较长,不要执行过于频繁。
一、 隐式转换sql
版本1
-- 找到含有隐式转换且会导致index seek变为index scan的语句
–find all the plans and query which are being compared with wrong data type in queries and thus casuing the conversion implicit or explicit on column which has index and this in turn is causing the index scan instead of index seek
declare @databasename sysname
set @databasename= quotename(db_name())
;WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select top 100
ColDetails.value('(@Schema)[1]','sysname') as SchemaName
,ColDetails.value('(@Table)[1]','sysname') as TableName
,ColDetails.value('(@Column)[1]','sysname') as ColumnName
,ColDetails.value('(@Alias)[1]','sysname') as TableAliasInQuery
,PhysicalOperator.value('(.//Object/@Index)[1]','sysname') as IndexName
,Cnvrt.value('(@DataType)[1]','sysname') as ConvertedDataType
,ty.name as ColumnDataType
--,Cnvrt.value('(@Implicit)[1]','sysname') as IsImplicit
--,qs.execution_count * ps.total_row_count as MostImportantFactor_total_row_processed
--,ps.total_row_count
--,qs.execution_count * used_page_count as MostImportantFactor_possible_number_of_logical_reads
--,ps.used_page_count
--,PhysicalOperator.value('(.//Predicate/ScalarOperator/@ScalarString)[1]','nvarchar(max)') as PredicateHavingFunction
,cast(substring(cast(st.text as nvarchar(max)),qs.statement_start_offset/2 + 1,(case when qs.statement_end_offset = -1 then datalength(cast(st.text as nvarchar(max))) else qs.statement_end_offset end - qs.statement_start_offset)/2) as nvarchar(max)) as QueryText
--,datalength(st.text) SQLtextLength
,st.text FullSqltext
,qp.query_plan as QueryPlan
,cp.usecounts,cp.refcounts,cp.objtype
,qs.sql_handle,
qs.creation_time,
qs.last_execution_time,
qs.total_worker_time,
qs.total_elapsed_time
/* qs.statement_start_offset,
qs.statement_end_offset,
qs.plan_handle,
qs.creation_time,
qs.last_execution_time,
qs.execution_count,
qs.query_hash,
qs.query_plan_hash,
qs.total_worker_time,
qs.total_elapsed_time,
qs.total_logical_reads,
qs.total_physical_reads,
qs.total_logical_writes,
qs.total_rows,
qs.total_worker_time/ qs.execution_count as worktime_per_exec,
qs.total_elapsed_time / qs.execution_count as elasp_per_exec,
qs.total_logical_reads / qs.execution_count as logiread_per_exec,
qs.total_physical_reads / qs.execution_count as phyread_per_exec,
qs.total_rows / qs.execution_count as rows_per_exec */
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) qp
cross apply query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') as Statements(IndividualStatement)
cross apply IndividualStatement.nodes('.//RelOp[@PhysicalOp="Index Scan" or @PhysicalOp="Clustered Index Scan"]') as RelOp(PhysicalOperator)
cross apply PhysicalOperator.nodes('.//ScalarOperator/Convert') as Conversion(Cnvrt)
cross apply Cnvrt.nodes('./ScalarOperator/Identifier/ColumnReference') as Cols(ColDetails)
inner join sys.tables t on quotename(t.name) = ltrim(rtrim(ColDetails.value('(@Table)[1]','sysname'))) and t.type = 'U'
inner join sys.indexes id on t.object_id = id.object_id
and quotename(id.name) = ltrim(rtrim(PhysicalOperator.value('(.//Object/@Index)[1]','sysname')))
inner join sys.all_columns ac on t.object_id = ac.object_id and ac.name = ltrim(rtrim(ColDetails.value('(@Column)[1]','sysname')))
left join sys.types ty on ac.user_type_id = ty.user_type_id
left join sys.dm_exec_query_stats qs on cp.plan_handle= qs.plan_handle
--left join (select object_id,index_id,sum(used_page_count) as used_page_count, sum(row_count) as total_row_count from sys.dm_db_partition_stats group by object_id,Index_id) as ps on id.object_id = ps.object_id and id.index_id = ps.index_id
outer apply sys.dm_exec_sql_text(cp.plan_handle) st
where
Cnvrt.value('(@Implicit)[1]','sysname')=1
and ColDetails.exist('.[@Database=sql:variable("@databasename")]') =1
and cp.cacheobjtype = 'Compiled Plan'
order by qs.total_worker_time desc
版本2
USE testdb
GO
DECLARE
@db_name SYSNAME
;
SET
@db_name = QUOTENAME(DB_NAME())
;
;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
, planCache
AS(
SELECT
stmt.value('(@StatementText)[1]', 'varchar(max)') AS stmt,
n.t.value('(ScalarOperator/Identifier/ColumnReference/@Database)[1]', 'sysname') AS DatabaseName,
REPLACE(REPLACE(n.t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'sysname'), '[', ''), ']', '') AS SchemaName,
REPLACE(REPLACE(n.t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'sysname'), '[', ''), ']', '') AS ObjectName,
REPLACE(REPLACE(n.t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'sysname'), '[', ''), ']', '') AS ColumnName,
n.t.value('(@DataType)[1]', 'sysname') AS ConvertTo,
n.t.value('(@Length)[1]', 'int') AS ConvertToLength,
PhysicalOperator.value('(.//Predicate/ScalarOperator/@ScalarString)[1]','nvarchar(max)') AS ScalarString,
query_plan,
cp.plan_handle
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt)
CROSS APPLY stmt.nodes('//ScalarOperator/Compare/ScalarOperator/Convert[@Implicit="1"]') AS n(t)
CROSS APPLY batch.stmt.nodes('.//RelOp[@PhysicalOp=''Index Scan'' or @PhysicalOp=''Clustered Index Scan'']') as RelOp(PhysicalOperator)
WHERE n.t.exist('ScalarOperator/Identifier/ColumnReference[@Database=sql:variable("@db_name")][@Schema != ''[sys]'']') = 1
)
SELECT
pc.stmt,
pc.DatabaseName,
pc.SchemaName,
pc.ObjectName,
pc.ColumnName,
ps.UsedPageCount,
ix.name AS IndexName,
CAST(ps.UsedPageCount/ 128. AS decimal(12,2)) AS UsedSizeMB,
ps.TotalRowCount,
qs.execution_count * UsedPageCount AS MostLogicalRead,
cols.DATA_TYPE AS ConvertFrom,
cols.CHARACTER_MAXIMUM_LENGTH AS ConvertFromLength,
pc.ConvertTo,
pc.ConvertToLength,
pc.ScalarString,
pc.query_plan,
qs.creation_time
,qs.last_execution_time
,qs.execution_count
,qs.total_worker_time
,qs.last_worker_time
,qs.min_worker_time
,qs.max_worker_time
,qs.total_physical_reads
,qs.last_physical_reads
,qs.min_physical_reads
,qs.max_physical_reads
,qs.total_logical_writes
,qs.last_logical_writes
,qs.min_logical_writes
,qs.max_logical_writes
,qs.total_logical_reads
,qs.last_logical_reads
,qs.min_logical_reads
,qs.max_logical_reads
,qs.total_clr_time
,qs.last_clr_time
,qs.min_clr_time
,qs.max_clr_time
,qs.total_elapsed_time
,qs.last_elapsed_time
,qs.min_elapsed_time
,qs.max_elapsed_time
,qs.total_rows
,qs.last_rows
,qs.min_rows
,qs.max_rows
FROM planCache AS pc
INNER JOIN INFORMATION_SCHEMA.COLUMNS as cols WITH(NOLOCK)
ON pc.SchemaName = cols.TABLE_SCHEMA
AND pc.ObjectName = cols.TABLE_NAME
AND pc.ColumnName = cols.COLUMN_NAME
INNER JOIN sys.tables as tb WITH(NOLOCK)
ON tb.schema_id = schema_id(pc.SchemaName)
AND tb.name = pc.ObjectName
INNER JOIN sys.indexes as ix WITH(NOLOCK)
ON tb.object_id = ix.object_id
LEFT JOIN (
SELECT
object_id,
index_id,
sum(used_page_count) AS UsedPageCount,
sum(row_count) AS TotalRowCount
FROM sys.dm_db_partition_stats as dps WITH(NOLOCK)
GROUP BY object_id,Index_id
) as ps
ON ix.object_id = ps.object_id and ix.index_id = ps.index_id
left join sys.dm_exec_query_stats qs on pc.plan_handle= qs.plan_handle
--DBCC freeproccache
版本3
-- 按各种消耗排序
DECLARE @dbname SYSNAME
SET @dbname = QUOTENAME(DB_NAME());
WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT top 100 stmt.value('(@StatementText)[1]', 'varchar(max)') AS SQL_Text ,
t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)') AS SchemaName ,
t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)') AS TableName ,
t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)') AS ColumnName ,
ic.DATA_TYPE AS ConvertFrom ,
ic.CHARACTER_MAXIMUM_LENGTH AS ConvertFromLength ,
t.value('(@DataType)[1]', 'varchar(128)') AS ConvertTo ,
t.value('(@Length)[1]', 'int') AS ConvertToLength ,
query_plan,
qs.sql_handle,
qs.statement_start_offset,
qs.statement_end_offset,
qs.plan_handle,
qs.creation_time,
qs.last_execution_time,
qs.execution_count,
qs.query_hash,
qs.query_plan_hash,
qs.total_worker_time,
qs.total_elapsed_time,
qs.total_logical_reads,
qs.total_physical_reads,
qs.total_logical_writes,
qs.total_rows,
qs.total_worker_time/ qs.execution_count as worktime_per_exec,
qs.total_elapsed_time / qs.execution_count as elasp_per_exec,
qs.total_logical_reads / qs.execution_count as logiread_per_exec,
qs.total_physical_reads / qs.execution_count as phyread_per_exec,
qs.total_rows / qs.execution_count as rows_per_exec
--FROM sys.dm_exec_cached_plans AS cp
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple')AS batch ( stmt )
CROSS APPLY stmt.nodes('.//Convert[@Implicit="1"]') AS n ( t )
JOIN INFORMATION_SCHEMA.COLUMNS AS ic ON QUOTENAME(ic.TABLE_SCHEMA) = t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)')
AND QUOTENAME(ic.TABLE_NAME) = t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]','varchar(128)')
AND ic.COLUMN_NAME = t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]','varchar(128)')
WHERE t.exist('ScalarOperator/Identifier/ColumnReference[@Database=sql:variable("@dbname")][@Schema!="[sys]"]') = 1
and ic.DATA_TYPE != t.value('(@DataType)[1]', 'varchar(128)')
order by qs.total_worker_time desc
二、 带有索引列使用函数的sql
-- 找出列上使用函数且会导致index seek变为index scan的语句
-- find all the plans and query which are using the function call on the column which has index and this in turn is causing the index scan instead of index seek
declare @databasename sysname
set @databasename= quotename(db_name())
--find all the plans and query which are using the function call on the column which has index and this in turn is causing the index scan instead of index seek
;WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select top 100
ColDetails.value('(@Schema)[1]','sysname') as SchemaName
,ColDetails.value('(@Table)[1]','sysname') as TableName
,ColDetails.value('(@Column)[1]','sysname') as ColumnName
,ColDetails.value('(@Alias)[1]','sysname') as TableAliasInQuery
,PhysicalOperator.value('(.//Object/@Index)[1]','sysname') as IndexName
,Intrinsic.value('(@FunctionName)[1]','varchar(200)') as FunctionNameUsed
,PhysicalOperator.value('(.//IndexScan/Predicate/ScalarOperator/@ScalarString)[1]','nvarchar(max)') as PredicateHavingFunction
,qs.execution_count * ps.total_row_count as MostImportantFactor_total_row_processed
,ps.total_row_count
,qs.execution_count * used_page_count as MostImportantFactor_possible_number_of_logical_reads
,ps.used_page_count
,cast(substring(cast(st.text as nvarchar(max)),qs.statement_start_offset/2 + 1,(case when qs.statement_end_offset = -1 then datalength(cast(st.text as nvarchar(max))) else qs.statement_end_offset end - qs.statement_start_offset)/2) as nvarchar(max)) as QueryText
,datalength(st.text) SQLtextLength
,st.text FullSqltext
,qp.query_plan as QueryPlan
,cp.usecounts,cp.refcounts,cp.objtype
,qs.sql_handle,
qs.statement_start_offset,
qs.statement_end_offset,
qs.plan_handle,
qs.creation_time,
qs.last_execution_time,
qs.execution_count,
qs.query_hash,
qs.query_plan_hash,
qs.total_worker_time,
qs.total_elapsed_time,
qs.total_logical_reads,
qs.total_physical_reads,
qs.total_logical_writes,
qs.total_rows,
qs.total_worker_time/ qs.execution_count as worktime_per_exec,
qs.total_elapsed_time / qs.execution_count as elasp_per_exec,
qs.total_logical_reads / qs.execution_count as logiread_per_exec,
qs.total_physical_reads / qs.execution_count as phyread_per_exec,
qs.total_rows / qs.execution_count as rows_per_exec
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) qp
cross apply query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') as Statements(IndividualStatement)
cross apply IndividualStatement.nodes('.//RelOp[@PhysicalOp="Index Scan" or @PhysicalOp="Clustered Index Scan"]') as RelOp(PhysicalOperator)
cross apply PhysicalOperator.nodes('.//ScalarOperator/Intrinsic') as Conversion(Intrinsic)
cross apply Intrinsic.nodes('./ScalarOperator/Identifier/ColumnReference') as Cols(ColDetails)
inner join sys.tables t on quotename(t.name) = ltrim(rtrim(ColDetails.value('(@Table)[1]','sysname'))) and t.type = 'U'
inner join sys.indexes id on t.object_id = id.object_id
and quotename(id.name) = ltrim(rtrim(PhysicalOperator.value('(.//Object/@Index)[1]','sysname')))
left join sys.dm_exec_query_stats qs on cp.plan_handle= qs.plan_handle
left join (select object_id,index_id,sum(used_page_count) as used_page_count, sum(row_count) as total_row_count from sys.dm_db_partition_stats group by object_id,Index_id) as ps
on id.object_id = ps.object_id and id.index_id = ps.index_id
outer apply sys.dm_exec_sql_text(cp.plan_handle) st
where
1=1
and ColDetails.exist('.[@Database=sql:variable("@databasename")]') =1
and cp.cacheobjtype = 'Compiled Plan'
order by qs.total_worker_time desc
三、 找到带全表扫描的sql
下面的语句查询执行计划中带有Table Scan的sql,这个语句非常容易改为查带别的扫描方式的sql,例如Cluster index scan,key lookup等。
-- Generate all query SQL text with "table scan" in cached query plan
;WITH XMLNAMESPACES
(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
,N'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS ShowPlan)
,EQS
AS (
SELECT EQS.plan_handle
,SUM(EQS.execution_count) AS ExecutionCount
,SUM(EQS.total_worker_time) AS TotalWorkTime
,SUM(EQS.total_logical_reads) AS TotalLogicalReads
,SUM(EQS.total_logical_writes) AS TotalLogicalWrites
,SUM(EQS.total_elapsed_time) AS TotalElapsedTime
,MAX(EQS.last_execution_time) AS LastExecutionTime
FROM sys.dm_exec_query_stats AS EQS
GROUP BY EQS.plan_handle
), info
AS(
SELECT DISTINCT
EQS.[ExecutionCount]
,EQS.[TotalWorkTime]
,EQS.[TotalLogicalReads]
,EQS.[TotalLogicalWrites]
,EQS.[TotalElapsedTime]
,EQS.[LastExecutionTime]
,ScanObject = StmtSimple.Node.value('(QueryPlan/RelOp//RelOp//TableScan/Object/@Database)[1]','sysname') + '.' +
StmtSimple.Node.value('(QueryPlan/RelOp//RelOp//TableScan/Object/@Schema)[1]','sysname') + '.' +
StmtSimple.Node.value('(QueryPlan/RelOp//RelOp//TableScan/Object/@Table)[1]','sysname')
,Statement = StmtSimple.Node.value('(@StatementText)[1]', 'nvarchar(max)')
,EST.text
,ECP.[objtype] AS [ObjectType]
,ECP.[cacheobjtype] AS [CacheObjectType]
,EQS.plan_handle
FROM sys.dm_exec_cached_plans AS ECP
INNER JOIN EQS
ON ECP.plan_handle = EQS.plan_handle
CROSS APPLY sys.dm_exec_sql_text(ECP.[plan_handle]) AS EST
CROSS APPLY sys.dm_exec_query_plan(ECP.[plan_handle]) AS EQP
CROSS APPLY EQP.query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS StmtSimple(Node)
WHERE StmtSimple.Node.exist('data(QueryPlan/RelOp//RelOp[@PhysicalOp="Table Scan"])') = 1
)
SELECT A.*, EQP.query_plan
FROM info AS A
CROSS APPLY sys.dm_exec_query_plan(A.[plan_handle]) AS EQP
ORDER BY A.TotalElapsedTime DESC, A.ExecutionCount
四、 查询索引缺失sql
版本1
USE master
GO
DECLARE
@EngineEdition INT = CAST(SERVERPROPERTY(N'EngineEdition') AS INT)
;
;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
,planCache
AS(
SELECT
*
FROM sys.dm_exec_query_stats as qs WITH(NOLOCK)
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) as qp
WHERE qp.query_plan.exist('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex')=1
), analyedPlanCache
AS(
SELECT
sql_text = T.C.value('(@StatementText)[1]', 'nvarchar(max)')
,[impact%] = T.C.value('(./QueryPlan/MissingIndexes/MissingIndexGroup/@Impact)[1]', 'float')
,cachedPlanSize = T.C.value('(./QueryPlan/@CachedPlanSize)[1]', 'int')
,compileTime = T.C.value('(./QueryPlan/@CompileTime)[1]', 'int')
,compileCPU = T.C.value('(./QueryPlan/@CompileCPU)[1]', 'int')
,compileMemory = T.C.value('(./QueryPlan/@CompileMemory)[1]', 'int')
,database_name = T.C.value('(./QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Database)[1]','sysname')
,schema_name = T.C.value('(./QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Schema)[1]','sysname')
,object_name = T.C.value('(./QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Table)[1]','sysname')
,equality_columns = (
SELECT
DISTINCT REPLACE(REPLACE(tb.col.value('(@Name)[1]', 'sysname'), N']', N''), N'[', N'') + ','
FROM T.c.nodes('./QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/ColumnGroup') AS T(cg)
CROSS APPLY T.cg.nodes('./Column') AS tb(col)
WHERE T.cg.value('(@Usage)[1]', 'sysname') = 'EQUALITY'
FOR XML PATH('')
)
,inequality_columns = (
SELECT
DISTINCT REPLACE(REPLACE(tb.col.value('(@Name)[1]', 'sysname'), N']', N''), N'[', N'') + ','
FROM T.c.nodes('./QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/ColumnGroup') AS T(cg)
CROSS APPLY T.cg.nodes('./Column') AS tb(col)
WHERE T.cg.value('(@Usage)[1]', 'sysname') = 'INEQUALITY'
FOR XML PATH('')
)
,include_columns = (
SELECT
DISTINCT REPLACE(REPLACE(tb.col.value('(@Name)[1]', 'sysname'), N']', N''), N'[', N'@') + ','
FROM T.c.nodes('./QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/ColumnGroup') AS T(cg)
CROSS APPLY T.cg.nodes('./Column') AS tb(col)
WHERE T.cg.value('(@Usage)[1]', 'sysname') = 'INCLUDE'
FOR XML PATH('')
)
,pc.*
FROM planCache AS pc
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS T(C)
WHERE C.exist('./QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex') = 1
)
SELECT
plan_handle
,query_plan
,query_hash
,query_plan_hash
,sql_text
,[impact%]
,cachedplansize
,compileTime
,compileCPU
,compileMemory
,object = database_name + '.' + schema_name + '.' + object_name
,miss_index_creation =
N'CREATE NONCLUSTERED INDEX ' + QUOTENAME(N'IX_' + REPLACE(LEFT(equality_columns, len(equality_columns) - 1), N',', N'_') + '_'
+ REPLACE(LEFT(inequality_columns, len(inequality_columns) - 1), N',', N'_') + '_'
+ REPLACE(LEFT(include_columns, len(include_columns) - 1), N',', N'_'), '[]')
+ N' ON ' + database_name + '.' + schema_name + '.' + object_name
+ QUOTENAME(
CASE
WHEN equality_columns is not null and inequality_columns is not null
THEN equality_columns + LEFT(inequality_columns, len(inequality_columns) - 1)
WHEN equality_columns is not null and inequality_columns is null
THEN LEFT(equality_columns, len(equality_columns) - 1)
WHEN inequality_columns is not null
THEN LEFT(inequality_columns, len(inequality_columns) - 1)
END
, '()')
+ CASE
WHEN include_columns is not null
THEN ' INCLUDE ' + QUOTENAME(REPLACE(LEFT(include_columns, len(include_columns) - 1), N'@', N''), N'()')
ELSE ''
END
+ N' WITH (FILLFACTOR = 90'
+ CASE @EngineEdition
WHEN 3 THEN N',ONLINE = ON'
ELSE ''
END + ');'
,creation_time
,last_execution_time
,execution_count
,total_worker_time
,last_worker_time
,min_worker_time
,max_worker_time
,total_physical_reads
,last_physical_reads
,min_physical_reads
,max_physical_reads
,total_logical_writes
,last_logical_writes
,min_logical_writes
,max_logical_writes
,total_logical_reads
,last_logical_reads
,min_logical_reads
,max_logical_reads
,total_clr_time
,last_clr_time
,min_clr_time
,max_clr_time
,total_elapsed_time
,last_elapsed_time
,min_elapsed_time
,max_elapsed_time
,total_rows
,last_rows
,min_rows
,max_rows
FROM analyedPlanCache
版本2
USE TestDb
GO
SELECT TOP 100
c.equality_columns
, c.inequality_columns
, c.included_columns
, create_Index = 'CREATE INDEX IX_' + REPLACE(REPLACE(REPLACE(equality_columns, '[', ''), ']',''), ',', '')
+ '_' + REPLACE(REPLACE(REPLACE(inequality_columns, '[', ''), ']',''), ',', '')
+ '_' +REPLACE(REPLACE(REPLACE(included_columns, '[', '@'), ']',''), ', ', '_') + char(10)
+ 'ON ' + SCHEMA_NAME(tb.schema_id) + '.' + object_name(tb.object_id)
+ '('
+ case
when c.equality_columns is not null and c.inequality_columns is not null
then c.equality_columns + ',' + c.inequality_columns
when c.equality_columns is not null and c.inequality_columns is null
then c.equality_columns
when c.inequality_columns is not null
then c.inequality_columns
end
+ ')' + char(10)
+ case
when c.included_columns is not null
then 'INCLUDE (' + c.included_columns + ')'
else ''
end + char(10)
+ N'WITH (FILLFACTOR = 85);'
FROM sys.dm_db_missing_index_group_stats a
INNER JOIN sys.dm_db_missing_index_groups b
ON a.group_handle = b.index_group_handle
INNER JOIN sys.dm_db_missing_index_details c
ON c.index_handle = b.index_handle
INNER JOIN sys.tables as tb
ON c.object_id = tb.object_id
WHERE db_name(database_id) = db_name()
AND equality_columns is not null
AND tb.object_id = object_id('dbo.SalesOrder', 'U')
ORDER BY a.avg_total_user_cost * a.avg_user_impact * (a.user_seeks + a.user_scans) DESC
五、 统计信息缺失sql
Use master
GO
;WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
,PlanCache
AS(
SELECT
EQP.query_plan,
EQS.plan_handle,
EST.text,
EQS.creation_time,
EQS.last_execution_time,
EQS.execution_count,
EQS.total_worker_time,
EQS.last_worker_time,
EQS.min_worker_time,
EQS.max_worker_time,
EQS.total_physical_reads,
EQS.last_physical_reads,
EQS.min_physical_reads,
EQS.max_physical_reads,
EQS.total_logical_writes,
EQS.last_logical_writes,
EQS.min_logical_writes,
EQS.max_logical_writes,
EQS.total_logical_reads,
EQS.last_logical_reads,
EQS.min_logical_reads,
EQS.max_logical_reads,
EQS.total_elapsed_time,
EQS.last_elapsed_time,
EQS.min_elapsed_time,
EQS.max_elapsed_time,
EQS.total_rows,
EQS.last_rows,
EQS.min_rows,
EQS.max_rows
FROM sys.dm_exec_query_stats AS EQS
CROSS APPLY sys.dm_exec_sql_text(EQS.sql_handle) AS EST
CROSS APPLY sys.dm_exec_query_plan(EQS.plan_handle) AS EQP
)
SELECT
batch.stmt.value('(@StatementText)[1]', 'varchar(max)') AS stmt,
cast(T.C.query('local-name(..)') as varchar) AS warning,
total_Worker_time,
Refer_Columns =
T.C.value('(@Database)[1]', 'sysname') + '.' +
T.C.value('(@Schema)[1]', 'sysname') + '.' +
T.C.value('(@Table)[1]', 'sysname') + '.' +
T.C.value('(@Column)[1]', 'sysname'),
QP.*
FROM PlanCache AS QP
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/RelOp/Warnings/ColumnsWithNoStatistics/ColumnReference') T(C)
CROSS APPLY QP.query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt)
六、 查询高编译时间的sql
WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT TOP 10
CompileTime_ms,
CompileCPU_ms,
CompileMemory_KB,
qs.execution_count,
qs.total_elapsed_time/1000 AS duration_ms,
qs.total_worker_time/1000 as cputime_ms,
(qs.total_elapsed_time/qs.execution_count)/1000 AS avg_duration_ms,
(qs.total_worker_time/qs.execution_count)/1000 AS avg_cputime_ms,
qs.max_elapsed_time/1000 AS max_duration_ms,
qs.max_worker_time/1000 AS max_cputime_ms,
SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,
(CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset) / 2 + 1) AS StmtText,
query_hash,
query_plan_hash
FROM
(SELECT
c.value('xs:hexBinary(substring((@QueryHash)[1],3))', 'varbinary(max)') AS QueryHash,
c.value('xs:hexBinary(substring((@QueryPlanHash)[1],3))', 'varbinary(max)') AS QueryPlanHash,
c.value('(QueryPlan/@CompileTime)[1]', 'int') AS CompileTime_ms,
c.value('(QueryPlan/@CompileCPU)[1]', 'int') AS CompileCPU_ms,
c.value('(QueryPlan/@CompileMemory)[1]', 'int') AS CompileMemory_KB,
qp.query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY qp.query_plan.nodes('ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS n(c)) AS tab
JOIN sys.dm_exec_query_stats AS qs
ON tab.QueryHash = qs.query_hash
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY CompileTime_ms DESC
OPTION(RECOMPILE, MAXDOP 1);
七、 查询缓存中存在并行的sql
SELECT TOP 10
p.* ,
q.* ,
qs.* ,
cp.plan_handle
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.Dm_exec_query_plan(cp.plan_handle) p
CROSS APPLY sys.Dm_exec_sql_text(cp.plan_handle) AS q
JOIN sys.dm_exec_query_stats qs ON qs.plan_handle = cp.plan_handle
WHERE cp.cacheobjtype = 'Compiled Plan'
AND p.query_plan.value('declare namespace p="http://schemas.microsoft.com/SQL Server/2004/07/showplan";
max(//p:RelOp/@Parallel)', 'float') > 0
OPTION ( MAXDOP 1 );
八、 Single-used Plans相关sql
Single-used Plans是指那些第一次执行后被缓存起来的执行计划,而后再也没有被重复利用过的执行计划缓存。其中ad hoc query(即席查询)就是典型的single-used plans中的一种。
single-used plans仅会第一次被使用(从名字也可以很好理解到这一点),所以,实际上single-used plans是对SQL Server内存空间和CPU资源的浪费,对数据库性能有一定的损害。
1. 如何发现single-used plans
USE master
GO
SELECT
database_name = QUOTENAME(db.name),
st.text,
cp.objtype,
cp.size_in_bytes,
qp.query_plan,
cp.cacheobjtype
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
INNER JOIN sys.databases AS db WITH(NOLOCK)
ON st.dbid = db.database_id
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
WHERE cp.cacheobjtype = N'Compiled Plan'
AND cp.objtype IN (N'Adhoc', N'Prepared')
AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC
2. 计划缓存中有多少Single-used Plans
当可以有效的发现single-used plans以后,我们可能又会问:到底SQL Server中有多少执行计划缓存是属于single-used plans类型呢?可以从两个维度来分析:
- Single-used plans记录总数
- Single-used plans总的执行计划占用的内存空间大小
可以使用以下的查询语句来回答这个问题。
USE master
GO
SELECT
COUNT(1) AS [total_plans],
CAST(SUM(size_in_bytes)/1024. AS DECIMAL(18,2)) AS [total_size_in_kb],
CAST(SUM((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END))/1024. AS DECIMAL(12,2)) AS [total_size_single_used_in_kb],
SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [total_plans_count_single_used],
AVG(usecounts) AS [avg_used_counts],
CAST(AVG(size_in_bytes)/1024. AS DECIMAL(12, 2)) AS [avg_used_size_kb]
FROM sys.dm_exec_cached_plans WITH(NOLOCK)
就单单一条Single-used Plan来看的话,对数据库系统的性能影响很小,小到可以忽略的地步,所以,数据库执行计划缓存中,存在少量的Single-used Plans是很正常的,可以不用太关注。但是如果Single-used Plans大量存在的话,可能就会对系统带来比较严重的性能影响。
3. 定性性能分析
定性分析大量Single-used Plans对数据性能的影响,主要体现在以下几个方面:
- 总的执行计划缓存利用率不高:存在大量不能被重复利用的执行计划缓存,拉低了执行计划缓存整体利用率
- 浪费执行计划缓存中内存的开销:每条执行计划缓存或多或少会占用一定的执行计划缓存内存空间,大量的Single-used Plans导致了内存空间的浪费
- 导致CPU使用率的上升:每条SQL语句执行计划的编译、最优路径选择和执行计划缓存,都需要消耗系统CPU资源,如果大量存在Single-used Plans,会导致系统CPU使用率的上升。
举一个最为极端的例子,假设所有执行计划都是Single-used Plans的话,那么导致的严重后果是:
- 执行计划缓存利用率就是0%,因为没有任何的执行计划被重用
- 执行计划缓存这种设计就毫无意义,因为缓存起来也没有被重用
- 浪费执行计划缓存的内存开销和CPU开销
4. 定量性能分析
以上是定性分析Single-used Plans对系统性能的影响,那么到底Single-used Plans达到哪个数量级,占比多少的时候,我们需要密切关注呢?虽然微软没有官方的推荐数字,但是个人比较推荐的两个数字是2GB和50%,即:所有的Single-used Plans使用的内存空间超过2GB或者内存空间使用占比超过50%。当然最终也可能取决于SQL Server可以使用的最大内存数量。
USE master
GO
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp
SELECT
objtype AS [cachetype],
COUNT(1) AS [total_plans],
CAST(SUM(size_in_bytes)/1024. AS DECIMAL(18,2)) AS [total_size_in_kb],
CAST(SUM((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END))/1024. AS DECIMAL(12,2)) AS [total_size_single_used_in_kb],
SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [total_plans_count_single_used],
AVG(usecounts) AS [avg_used_counts],
CAST(AVG(size_in_bytes)/1024. AS DECIMAL(12, 2)) AS [avg_used_size_kb]
INTO #temp
FROM sys.dm_exec_cached_plans WITH(NOLOCK)
GROUP BY objtype
SELECT
[cachetype],
[total_plans],
[total_size_in_kb],
[total_plans_count_single_used],
[total_size_single_used_in_kb],
CAST(([total_plans_count_single_used]*1.0 / [total_plans]*1.0) * 100. AS DECIMAL(12, 2)) AS [single_used_plan_count%],
CAST([total_size_single_used_in_kb]/[total_size_in_kb] * 100. AS DECIMAL(12, 2)) AS [single_used_size%],
[avg_used_counts],
[avg_used_size_kb]
FROM #temp
ORDER BY [total_size_single_used_in_kb] DESC
SELECT
SUM([total_plans]) AS total_plan_counts,
SUM([total_size_in_kb]) AS total_plan_size_in_kb,
SUM([total_plans_count_single_used]) AS [total_plans_counts_single_used],
SUM([total_size_single_used_in_kb]) AS [total_plan_size_single_used_in_kb],
CAST(SUM([total_plans_count_single_used]) * 100. / SUM([total_plans]) AS DECIMAL(12, 2)) AS [plan_counts_single_used%],
CAST(SUM([total_size_single_used_in_kb]) * 100. / SUM([total_size_in_kb]) AS DECIMAL(12, 2)) AS [plan_size_single_used%]
FROM #temp
执行结果如下所示:
从这个执行结果来看,single-used plans占用的执行计划记录数为72条,内存空间占用14016 KB;single-used plans执行计划记录数占总的百分比为39.78%,内存空间占用比例为50.59%。
那么如何解决single-used plans问题呢?
5. 清空Single-used Plans
第一种方法是手动清理single-used plans,当然,你也可以很暴力的将所有执行计划缓存清空,从而也就清理了single-used plans,但是这样会误杀很多有用的无辜的执行计划缓存,从而导致这些执行计划重编译,影响性能。我们推荐使用下面的方法,仅清空哪些single-used plans,方法如下:
USE master
GO
DECLARE
@plan_handle varbinary(64)
;
DECLARE
cur_single_used_plan_handle CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
SELECT cp.plan_handle
FROM sys.dm_exec_cached_plans AS cp WITH(NOLOCK)
WHERE usecounts = 1
OPEN cur_single_used_plan_handle
FETCH NEXT FROM cur_single_used_plan_handle INTO @plan_handle
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @plan_handle
DBCC FREEPROCCACHE (@plan_handle);
FETCH NEXT FROM cur_single_used_plan_handle INTO @plan_handle
END
CLOSE cur_single_used_plan_handle
DEALLOCATE cur_single_used_plan_handle
GO
6. optimize for ad hoc workloads配置选项
除了手动清理single-used plans执行计划缓存外,另外一个更强大的功能是修改SQL Server实例级别的配置选项optimize for ad hoc workloads。为了解决single-used plans带来的问题,从SQL Server 2008开始引入了这个选项。打开以后,ad hoc的查询语句在第一次执行时,系统会创建一个“compiled plan stub”,并不会将执行计划缓存起来,只有当第二次该语句再被执行时,执行计划才会被缓存,从而避免了single-used plans带来的问题。配置这个选项的方法很简单,参见以下语句:
EXEC sys.sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sys.sp_configure 'optimize for ad hoc workloads', 1
RECONFIGURE
GO
参考
https://yq.aliyun.com/articles/180914?spm=a2c4e.11155435.0.0.26865adcjVcuh5
https://yq.aliyun.com/articles/106489?spm=a2c4e.11155435.0.0.26865adcjVcuh5
https://yq.aliyun.com/articles/93785?spm=a2c4e.11155435.0.0.26865adcjVcuh5
https://yq.aliyun.com/articles/72265?spm=a2c4e.11153940.0.0.6b863e27HTvUtV
https://yq.aliyun.com/articles/225035?spm=a2c4e.11155435.0.0.1ab45adcZS12AB
https://yq.aliyun.com/articles/115975?spm=a2c4e.11155435.0.0.26865adcjVcuh5