Одна из задач, которую разработчикам и DBA приходится решать для улучшения производительности базы данных это удаление лишних индексов, которые не используются при выборках, а только занимают место в базе и замедляют изменение данных. В таких случаях очень полезной может оказаться замечательная статья Грега Робиду "How to get index usage information in SQL Server".
Следующий запрос, взятый из этой статьи позволяет получить статистику всех операций, произведенных над индексом с момента старта SQL сервера:
Однако часто самого факта, что индекс используется может оказаться недостаточно и хотелось бы знать, какие именно запросы этот индекс используют. Когда мы имеем дело с одним запросом, то легко увидеть, какие индексы он использует, проанализировав план его выполнения. Будем использовать тот же подход и в случае множества запросов. Вначале получим статистику о выполненных запросах из кэша планов сервера:
query_hash – хэш SQL-предложения (часть пакета)
query_plan_hash – хэш плана всего запроса
text – текст всего пакета или процедуры
query_plan – план запроса в виде xml (можно посмотреть его в SQL Server Management Studio, если сохранить его этот xml с расширением .sqlplan)
creation_time – когда план был скомпилирован
last_execution_time – когда запрос был выполнен по этому плану в последний раз
execution_count – сколько раз запрос был выполнен по этому плану
Затем из xml-плана каждого запроса с помощью методов типа данных xml получим информацию о том, какие индексы были задействованы и какие операции над ними выполнялись. Это достаточно несложно сделать, т.к xml плана запроса подчиняется схеме:
http://schemas.microsoft.com/sqlserver/2004/07/showplan
Например, для запроса (база данных master):
На моем SQL Server 2008 генерируется такой план:
Для упрощения запросов к такому xml первым делом удалим namespace (в данном случае строку xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan). Это можно сделать например такой функцией:
На входе – xml с namespace-ом и сам namespace. На выходе – xml без namespace-а. Функция не отличается изяществом и просто удаляет первое вхождение строки @namespaceString.
Теперь функция, возвращающая табличное представление информации об индексах:
JOIN с выборкой из представления information_schema.tables нужен, чтобы ограничить возвращаемые индексы только текущей базой данных и только пользовательскими таблицами.
Следующий запрос, взятый из этой статьи позволяет получить статистику всех операций, произведенных над индексом с момента старта SQL сервера:
SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], I.[NAME] AS [INDEX NAME], USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES FROM SYS.DM_DB_INDEX_USAGE_STATS AS S INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID WHERE OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
Однако часто самого факта, что индекс используется может оказаться недостаточно и хотелось бы знать, какие именно запросы этот индекс используют. Когда мы имеем дело с одним запросом, то легко увидеть, какие индексы он использует, проанализировав план его выполнения. Будем использовать тот же подход и в случае множества запросов. Вначале получим статистику о выполненных запросах из кэша планов сервера:
SELECT qs.query_hash, qs.query_plan_hash, st.[text], qp.query_plan, qs.creation_time, qs.last_execution_time, qs.execution_count FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) st CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
Возвращаемые столбцы:
query_hash – хэш SQL-предложения (часть пакета)
query_plan_hash – хэш плана всего запроса
text – текст всего пакета или процедуры
query_plan – план запроса в виде xml (можно посмотреть его в SQL Server Management Studio, если сохранить его этот xml с расширением .sqlplan)
creation_time – когда план был скомпилирован
last_execution_time – когда запрос был выполнен по этому плану в последний раз
execution_count – сколько раз запрос был выполнен по этому плану
Затем из xml-плана каждого запроса с помощью методов типа данных xml получим информацию о том, какие индексы были задействованы и какие операции над ними выполнялись. Это достаточно несложно сделать, т.к xml плана запроса подчиняется схеме:
http://schemas.microsoft.com/sqlserver/2004/07/showplan
Например, для запроса (база данных master):
SELECT TOP 10 * FROM sys.objects ORDER BY [name]
На моем SQL Server 2008 генерируется такой план:
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.50.2550.0"> <BatchSequence> <Batch> <Statements> <StmtSimple StatementText="SELECT TOP 10 * FROM sys.objects ORDER BY [name]" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="0.0240863" StatementEstRows="10" StatementOptmLevel="FULL" QueryHash="0xFBA5FED7D0BBA861" QueryPlanHash="0x2E11BC9EAFF10957" StatementOptmEarlyAbortReason="GoodEnoughPlanFound"> <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" /> <QueryPlan CachedPlanSize="56" CompileTime="7" CompileCPU="7" CompileMemory="864"> <RelOp NodeId="0" PhysicalOp="Top" LogicalOp="Top" EstimateRows="10" EstimateIO="0" EstimateCPU="1e-006" AvgRowSize="134" EstimatedTotalSubtreeCost="0.0240863" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"> <OutputList> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="name" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsid" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pid" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="created" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="modified" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[syssingleobjrefs]" Alias="[r]" Column="indepid" /> <ColumnReference Database="[mssqlsystemresource]" Schema="[sys]" Table="[syspalnames]" Alias="[n]" Column="name" /> <ColumnReference Column="Expr1006" /> <ColumnReference Column="Expr1009" /> <ColumnReference Column="Expr1010" /> </OutputList> <Top RowCount="0" IsPercent="0" WithTies="0"> <TopExpression> <ScalarOperator ScalarString="(10)"> <Const ConstValue="(10)" /> </ScalarOperator> </TopExpression> <RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Left Outer Join" EstimateRows="10" EstimateIO="0" EstimateCPU="0.00039292" AvgRowSize="134" EstimatedTotalSubtreeCost="0.0240853" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"> <OutputList> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="name" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsid" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pid" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="created" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="modified" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[syssingleobjrefs]" Alias="[r]" Column="indepid" /> <ColumnReference Database="[mssqlsystemresource]" Schema="[sys]" Table="[syspalnames]" Alias="[n]" Column="name" /> <ColumnReference Column="Expr1006" /> <ColumnReference Column="Expr1009" /> <ColumnReference Column="Expr1010" /> </OutputList> <NestedLoops Optimized="0"> <OuterReferences> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type" /> </OuterReferences> <RelOp NodeId="2" PhysicalOp="Nested Loops" LogicalOp="Left Outer Join" EstimateRows="10" EstimateIO="0" EstimateCPU="0.00039292" AvgRowSize="72" EstimatedTotalSubtreeCost="0.0191962" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"> <OutputList> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="name" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsid" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pid" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="created" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="modified" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[syssingleobjrefs]" Alias="[r]" Column="indepid" /> <ColumnReference Column="Expr1006" /> <ColumnReference Column="Expr1009" /> <ColumnReference Column="Expr1010" /> </OutputList> <NestedLoops Optimized="0"> <OuterReferences> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" /> </OuterReferences> <RelOp NodeId="3" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="10" EstimateIO="0" EstimateCPU="0.00014852" AvgRowSize="68" EstimatedTotalSubtreeCost="0.0143071" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"> <OutputList> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="name" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsid" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pid" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="created" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="modified" /> <ColumnReference Column="Expr1006" /> <ColumnReference Column="Expr1009" /> <ColumnReference Column="Expr1010" /> </OutputList> <Filter StartupExpression="0"> <RelOp NodeId="4" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="10" EstimateIO="0" EstimateCPU="9.4e-006" AvgRowSize="70" EstimatedTotalSubtreeCost="0.0142913" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"> <OutputList> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="name" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsid" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsclass" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pid" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pclass" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="created" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="modified" /> <ColumnReference Column="Expr1006" /> <ColumnReference Column="Expr1009" /> <ColumnReference Column="Expr1010" /> </OutputList> <ComputeScalar> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1006" /> <ScalarOperator ScalarString="CONVERT(bit,[master].[sys].[sysschobjs].[status] as [o].[status]&(1),0)"> <Convert DataType="bit" Style="0" Implicit="0"> <ScalarOperator> <Arithmetic Operation="BIT_AND"> <ScalarOperator> <Identifier> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="status" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="(1)" /> </ScalarOperator> </Arithmetic> </ScalarOperator> </Convert> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Column="Expr1009" /> <ScalarOperator ScalarString="CONVERT(bit,[master].[sys].[sysschobjs].[status] as [o].[status]&(16),0)"> <Convert DataType="bit" Style="0" Implicit="0"> <ScalarOperator> <Arithmetic Operation="BIT_AND"> <ScalarOperator> <Identifier> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="status" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="(16)" /> </ScalarOperator> </Arithmetic> </ScalarOperator> </Convert> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Column="Expr1010" /> <ScalarOperator ScalarString="CONVERT(bit,[master].[sys].[sysschobjs].[status] as [o].[status]&(64),0)"> <Convert DataType="bit" Style="0" Implicit="0"> <ScalarOperator> <Arithmetic Operation="BIT_AND"> <ScalarOperator> <Identifier> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="status" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="(64)" /> </ScalarOperator> </Arithmetic> </ScalarOperator> </Convert> </ScalarOperator> </DefinedValue> </DefinedValues> <RelOp NodeId="5" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="10" EstimateIO="0" EstimateCPU="0.00039292" AvgRowSize="73" EstimatedTotalSubtreeCost="0.0142903" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"> <OutputList> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="name" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsid" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsclass" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="status" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pid" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pclass" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="created" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="modified" /> </OutputList> <NestedLoops Optimized="0"> <OuterReferences> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" /> </OuterReferences> <RelOp NodeId="6" PhysicalOp="Index Scan" LogicalOp="Index Scan" EstimateRows="10" EstimateIO="0.003125" EstimateCPU="0.0002604" AvgRowSize="45" EstimatedTotalSubtreeCost="0.003293" TableCardinality="94" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"> <OutputList> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="name" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsid" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsclass" /> </OutputList> <IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0"> <DefinedValues> <DefinedValue> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="name" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsid" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsclass" /> </DefinedValue> </DefinedValues> <Object Database="[master]" Schema="[sys]" Table="[sysschobjs]" Index="[nc2]" Alias="[o]" IndexKind="NonClustered" /> </IndexScan> </RelOp> <RelOp NodeId="8" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="34" EstimatedTotalSubtreeCost="0.0109555" TableCardinality="94" Parallel="0" EstimateRebinds="9.89362" EstimateRewinds="0"> <OutputList> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="status" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pid" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pclass" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="created" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="modified" /> </OutputList> <IndexScan Lookup="1" Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0"> <DefinedValues> <DefinedValue> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="status" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pid" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pclass" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="created" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="modified" /> </DefinedValue> </DefinedValues> <Object Database="[master]" Schema="[sys]" Table="[sysschobjs]" Index="[clst]" Alias="[o]" TableReferenceId="-1" IndexKind="Clustered" /> <SeekPredicates> <SeekPredicateNew> <SeekKeys> <Prefix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="[master].[sys].[sysschobjs].[id] as [o].[id]"> <Identifier> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" /> </Identifier> </ScalarOperator> </RangeExpressions> </Prefix> </SeekKeys> </SeekPredicateNew> </SeekPredicates> </IndexScan> </RelOp> </NestedLoops> </RelOp> </ComputeScalar> </RelOp> <Predicate> <ScalarOperator ScalarString="has_access('CO',[master].[sys].[sysschobjs].[id] as [o].[id])=(1) AND [master].[sys].[sysschobjs].[nsclass] as [o].[nsclass]=(0) AND [master].[sys].[sysschobjs].[pclass] as [o].[pclass]=(1)"> <Logical Operation="AND"> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Intrinsic FunctionName="has_access"> <ScalarOperator> <Const ConstValue="'CO'" /> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="" /> </ScalarOperator> <ScalarOperator> <Const ConstValue="" /> </ScalarOperator> </Intrinsic> </ScalarOperator> <ScalarOperator> <Const ConstValue="(1)" /> </ScalarOperator> </Compare> </ScalarOperator> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsclass" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="(0)" /> </ScalarOperator> </Compare> </ScalarOperator> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pclass" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="(1)" /> </ScalarOperator> </Compare> </ScalarOperator> </Logical> </ScalarOperator> </Predicate> </Filter> </RelOp> <RelOp NodeId="30" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00484728" TableCardinality="167" Parallel="0" EstimateRebinds="9.89362" EstimateRewinds="0"> <OutputList> <ColumnReference Database="[master]" Schema="[sys]" Table="[syssingleobjrefs]" Alias="[r]" Column="indepid" /> </OutputList> <IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0"> <DefinedValues> <DefinedValue> <ColumnReference Database="[master]" Schema="[sys]" Table="[syssingleobjrefs]" Alias="[r]" Column="indepid" /> </DefinedValue> </DefinedValues> <Object Database="[master]" Schema="[sys]" Table="[syssingleobjrefs]" Index="[clst]" Alias="[r]" IndexKind="Clustered" /> <SeekPredicates> <SeekPredicateNew> <SeekKeys> <Prefix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[master]" Schema="[sys]" Table="[syssingleobjrefs]" Alias="[r]" Column="depid" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[syssingleobjrefs]" Alias="[r]" Column="class" /> <ColumnReference Database="[master]" Schema="[sys]" Table="[syssingleobjrefs]" Alias="[r]" Column="depsubid" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="[master].[sys].[sysschobjs].[id] as [o].[id]"> <Identifier> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" /> </Identifier> </ScalarOperator> <ScalarOperator ScalarString="(97)"> <Const ConstValue="(97)" /> </ScalarOperator> <ScalarOperator ScalarString="(0)"> <Const ConstValue="(0)" /> </ScalarOperator> </RangeExpressions> </Prefix> </SeekKeys> </SeekPredicateNew> </SeekPredicates> </IndexScan> </RelOp> </NestedLoops> </RelOp> <RelOp NodeId="31" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="71" EstimatedTotalSubtreeCost="0.0048641" TableCardinality="127" Parallel="0" EstimateRebinds="6.70213" EstimateRewinds="3.29787"> <OutputList> <ColumnReference Database="[mssqlsystemresource]" Schema="[sys]" Table="[syspalnames]" Alias="[n]" Column="name" /> </OutputList> <IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0"> <DefinedValues> <DefinedValue> <ColumnReference Database="[mssqlsystemresource]" Schema="[sys]" Table="[syspalnames]" Alias="[n]" Column="name" /> </DefinedValue> </DefinedValues> <Object Database="[mssqlsystemresource]" Schema="[sys]" Table="[syspalnames]" Index="[cl]" Alias="[n]" IndexKind="Clustered" /> <SeekPredicates> <SeekPredicateNew> <SeekKeys> <Prefix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[mssqlsystemresource]" Schema="[sys]" Table="[syspalnames]" Alias="[n]" Column="class" /> <ColumnReference Database="[mssqlsystemresource]" Schema="[sys]" Table="[syspalnames]" Alias="[n]" Column="value" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="'OBTY'"> <Const ConstValue="'OBTY'" /> </ScalarOperator> <ScalarOperator ScalarString="[master].[sys].[sysschobjs].[type] as [o].[type]"> <Identifier> <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type" /> </Identifier> </ScalarOperator> </RangeExpressions> </Prefix> </SeekKeys> </SeekPredicateNew> </SeekPredicates> </IndexScan> </RelOp> </NestedLoops> </RelOp> </Top> </RelOp> </QueryPlan> </StmtSimple> </Statements> </Batch> </BatchSequence> </ShowPlanXML>
Для упрощения запросов к такому xml первым делом удалим namespace (в данном случае строку xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan). Это можно сделать например такой функцией:
USE [master] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RemoveNamespaceString]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[RemoveNamespaceString] GO CREATE FUNCTION [dbo].[RemoveNamespaceString](@xml xml, @namespaceString nvarchar(max)) RETURNS xml AS BEGIN DECLARE @xc nvarchar(max) = CAST(@xml as nvarchar(max)) DECLARE @ci int = CHARINDEX(@namespaceString, @xc) IF @ci = 0 RETURN @xml RETURN CAST(STUFF(@xc, @ci, LEN(@namespaceString), '') as xml) END
На входе – xml с namespace-ом и сам namespace. На выходе – xml без namespace-а. Функция не отличается изяществом и просто удаляет первое вхождение строки @namespaceString.
Теперь функция, возвращающая табличное представление информации об индексах:
USE [master] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[IndexesUsedByPlan]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[IndexesUsedByPlan] GO CREATE FUNCTION [dbo].[IndexesUsedByPlan](@xml xml) RETURNS TABLE AS RETURN ( SELECT * FROM ( SELECT statement_xml = s.query('.'), operation_xml = op.query('.'), object_xml = obj.query('.'), statement_id = s.value('.[1]/@StatementId', 'nvarchar(max)'), statement_text = s.value('.[1]/@StatementText', 'nvarchar(max)'), query_hash = convert(binary(8), s.value('.[1]/@QueryHash', 'varchar(max)'), 1), query_plan_hash = convert(binary(8), s.value('.[1]/@QueryPlanHash', 'nvarchar(max)'), 1), operation = op.value('.[1]/@PhysicalOp', 'nvarchar(max)'), index_database = obj.value('.[1]/@Database', 'nvarchar(max)'), index_schema = obj.value('.[1]/@Schema', 'nvarchar(max)'), index_table = obj.value('.[1]/@Table', 'nvarchar(max)'), [index] = obj.value('.[1]/@Index', 'nvarchar(max)'), index_kind = obj.value('.[1]/@IndexKind', 'nvarchar(max)') FROM @xml.nodes('//StmtSimple') T(s) CROSS APPLY s.nodes('.//RelOp') S(op) CROSS APPLY op.nodes('.//Object') Ro(obj) ) p WHERE operation LIKE '%Index%' and [index] IS NOT NULL )
@xml – xml плана (без namespace-а)
Возвращаемые столбцы:
statement_xml – часть плана, содержащая данные о том SQL-предложении (часть batch-а или процедуры), которое задействовало индекс
operation_xml – часть плана, содержащая данные об операции с индексом (такой, как Index Seek или Index Scan)
object_xml – часть плана, содержащая данные об объекте, (в данном случае - индексе), над которым производилась операция
statement_id – порядковый номер SQL-предложения в batch-е или процедуре
statement_text – текст SQL-предложения
query_hash – хэш SQL-предложения
query_plan_hash – хэш плана запроса
operation – операция, которая производится с индексом (например "Index Scan")
index_database – база данных, в которой находится индекс
index_schema – схема таблицы, которой принадлежит индекс
index_table – имя таблицы, которой принадлежит индекс. Дополнительное
index – имя индекса
index_kind – тип индекса (Clustered/NonClustered)
operation_xml – часть плана, содержащая данные об операции с индексом (такой, как Index Seek или Index Scan)
object_xml – часть плана, содержащая данные об объекте, (в данном случае - индексе), над которым производилась операция
statement_id – порядковый номер SQL-предложения в batch-е или процедуре
statement_text – текст SQL-предложения
query_hash – хэш SQL-предложения
query_plan_hash – хэш плана запроса
operation – операция, которая производится с индексом (например "Index Scan")
index_database – база данных, в которой находится индекс
index_schema – схема таблицы, которой принадлежит индекс
index_table – имя таблицы, которой принадлежит индекс. Дополнительное
index – имя индекса
index_kind – тип индекса (Clustered/NonClustered)
И наконец запрос, выбирающий все нужные данные:
SELECT pind.statement_text, query_text = st.[text], pind.statement_id, qs.statement_start_offset, qs.statement_end_offset, qs.creation_time, qs.last_execution_time, qs.execution_count, pind.index_database, pind.index_schema, pind.index_table, [index], pind.index_kind, pind.query_hash, pind.query_plan_hash, qp.query_plan, pind.statement_xml, pind.operation_xml, pind.object_xml FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) st CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp CROSS APPLY ( SELECT p.* FROM [master].dbo.IndexesUsedByPlan( [master].dbo.RemoveNamespaceString(qp.query_plan, 'xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"')) p JOIN ( SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.tables WHERE table_type='BASE TABLE' ) ut ON p.index_schema COLLATE DATABASE_DEFAULT = '[' + ut.TABLE_SCHEMA + ']' AND p.index_table COLLATE DATABASE_DEFAULT = '[' + ut.TABLE_NAME + ']' WHERE p.index_database = '[' + DB_NAME() + ']' ) pind WHERE qp.query_plan IS NOT NULL AND qs.query_hash = pind.query_hash AND qs.query_plan_hash = pind.query_plan_hash ORDER BY pind.[index_table], pind.[index]
JOIN с выборкой из представления information_schema.tables нужен, чтобы ограничить возвращаемые индексы только текущей базой данных и только пользовательскими таблицами.
