Я использую SQL Server 2008, и мы используем DMV для поиска отсутствующих индексов. Однако, прежде чем создать новый индекс, я пытаюсь выяснить, какой процедуре/запросу нужен этот индекс. Я хочу получить как можно больше информации, чтобы принять взвешенное решение по своим индексам. Иногда индексы, которые хочет SQL Server, не имеют для меня смысла. Кто-нибудь знает, как я могу понять, чего он хочет?
SQL Server — Отсутствующие индексы — Что будет использовать индекс?
Ответы (3)
вы можете попробовать что-то вроде этого запроса, в котором перечислены QueryText:
;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
, CachedPlans AS
(SELECT
RelOp.op.value(N'../../@NodeId', N'int') AS ParentOperationID
,RelOp.op.value(N'@NodeId', N'int') AS OperationID
,RelOp.op.value(N'@PhysicalOp', N'varchar(50)') AS PhysicalOperator
,RelOp.op.value(N'@LogicalOp', N'varchar(50)') AS LogicalOperator
,RelOp.op.value(N'@EstimatedTotalSubtreeCost ', N'float') AS EstimatedCost
,RelOp.op.value(N'@EstimateIO', N'float') AS EstimatedIO
,RelOp.op.value(N'@EstimateCPU', N'float') AS EstimatedCPU
,RelOp.op.value(N'@EstimateRows', N'float') AS EstimatedRows
,cp.plan_handle AS PlanHandle
,qp.query_plan AS QueryPlan
,st.TEXT AS QueryText
,cp.cacheobjtype AS CacheObjectType
,cp.objtype AS ObjectType
,cp.usecounts AS UseCounts
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
CROSS APPLY qp.query_plan.nodes(N'//RelOp') RelOp (op)
)
SELECT
PlanHandle
,ParentOperationID
,OperationID
,PhysicalOperator
,LogicalOperator
,UseCounts
,CacheObjectType
,ObjectType
,EstimatedCost
,EstimatedIO
,EstimatedCPU
,EstimatedRows
,QueryText
FROM CachedPlans
WHERE CacheObjectType = N'Compiled Plan'
AND PhysicalOperator IN ('nothing will ever match this one!'
--,'Assert'
--,'Bitmap'
--,'Clustered Index Delete'
--,'Clustered Index Insert'
,'Clustered Index Scan'
--,'Clustered Index Seek'
--,'Clustered Index Update'
--,'Compute Scalar'
--,'Concatenation'
--,'Constant Scan'
,'Deleted Scan'
--,'Filter'
--,'Hash Match'
,'Index Scan'
--,'Index Seek'
--,'Index Spool'
,'Inserted Scan'
--,'Merge Join'
--,'Nested Loops'
--,'Parallelism'
,'Parameter Table Scan'
--,'RID Lookup'
--,'Segment'
--,'Sequence Project'
--,'Sort'
--,'Stream Aggregate'
--,'Table Delete'
--,'Table Insert'
,'Table Scan'
--,'Table Spool'
--,'Table Update'
--,'Table-valued function'
--,'Top'
)
просто добавьте ORDER BY к чему-то вроде комбинации UseCounts и EstimatedCost.
person
KM.
schedule
04.06.2010
очень интересная отправная точка. Я посмотрю, что я могу сделать с этим, и дам вам знать. Спасибо.
- person BankZ; 04.06.2010
Вот что наконец сработало:
with xmlnamespaces(default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') , CachedPlans as (
select
query_plan,
n.value('../../../@StatementText' ,'varchar(1000)') as [Statement],
n.value('../../../@StatementSubTreeCost' ,'varchar(1000)') as [Cost],
n.value('../../../@StatementEstRows' ,'varchar(1000)') as [Rows],
n.value('@Impact' ,'float') as Impact,
n.value('MissingIndex[1]/@Database' ,'varchar(128)') as [Database],
n.value('MissingIndex[1]/@Table' ,'varchar(128)') as [TableName],
(
select dbo.concat(c.value('@Name' ,'varchar(128)'))
from n.nodes('MissingIndex/ColumnGroup[@Usage="EQUALITY"][1]') as t(cg)
cross apply cg.nodes('Column') as r(c)
) as equality_columns,
(
select dbo.concat(c.value('@Name' ,'varchar(128)'))
from n.nodes('MissingIndex/ColumnGroup[@Usage="INEQUALITY"][1]') as t(cg)
cross apply cg.nodes('Column') as r(c)
) as inequality_columns,
(
select dbo.concat(c.value('@Name' ,'varchar(128)'))
from n.nodes('MissingIndex/ColumnGroup[@Usage="INCLUDE"][1]') as t(cg)
cross apply cg.nodes('Column') as r(c)
) as include_columns
from (
select query_plan
from sys.dm_exec_cached_plans p
outer apply sys.dm_exec_query_plan(p.plan_handle) tp
) as tab(query_plan)
cross apply query_plan.nodes('//MissingIndexGroup') as q(n)
)
select *
from CachedPlans
person
BankZ
schedule
05.06.2010
Можете ли вы уточнить, что такое dbo.concat?
- person Chris J; 07.04.2011
Очистите это -- заменил dbo.concat конструкцией:
SELECT STUFF(([...] FOR XML PATH('')),1, 2, '')
, заменив [...] на запрос, столбец которого я хочу объединить; например, SELECT STUFF((select c.value('@Name' ,'varchar(128)') from n.nodes('MissingIndex/ColumnGroup[@Usage="EQUALITY"][1]') as t(cg) cross apply cg.nodes('Column') as r(c) FOR XML PATH('')),1, 2, '')
- person Chris J; 07.04.2011
Вы можете запустить трассировку профилировщика и проверить запущенные процедуры и их эффективность с точки зрения поиска/использования индекса.
Вместо того, чтобы просто делать все индексы для всех, лучше оптимизировать самую большую проблему — обычно вы получаете от этого наибольшую выгоду.
В трассировке профилировщика выясните, какой сохраненный оператор proc/tsql выполняется наибольшее количество раз и потребляет больше всего ресурсов. Это те, за которыми вы действительно хотите пойти.
person
Raj More
schedule
04.06.2010
Это не совсем то, о чем я спрашиваю. DMV SQL (sys.dm_db_missing_index_details) дает список отсутствующих индексов. Я хочу знать, что будет использовать индекс, если я его создам.
- person BankZ; 04.06.2010
@BankZ Причина, по которой я говорю вам пойти по маршруту Profiler, заключается в том, что dm_db_missing_index_details сообщает вам, какой индекс отсутствует, но не о том, какую прибыль вы получаете от него. IMO было бы лучше оптимизировать запросы, которые на самом деле вызывают самые большие проблемы.
- person Raj More; 04.06.2010
Я понимаю это, и мы используем не только DMV. Мы отслеживаем и смотрим планы выполнения. Тем не менее, вы не можете отслеживать 24/7, и в DMV есть много информации (и они дают вам некоторые оценки стоимости отсутствующего индекса и некоторых преимуществ). Однако, как говорится, это все еще не отвечает на мой вопрос :)
- person BankZ; 04.06.2010