SQL Server - Index manquants - Qu'est-ce qui utiliserait l'index ?

Question

J'utilise SQL Server 2008 et nous utilisons les DMV pour trouver les index manquants. Cependant, avant de créer le nouvel index, j'essaie de savoir quelle procédure ou requête veut cet index. Je veux obtenir le maximum d'informations pour pouvoir prendre des décisions éclairées sur mes index. Parfois, les index demandés par SQL Server n'ont aucun sens pour moi. Quelqu'un sait-il comment je peux savoir ce que veut l'index ?

Answer 1

Vous pourriez essayer quelque chose comme cette requête, qui liste le 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'
                        )

ajoutez simplement un ORDER BY sur quelque chose comme la combinaison des UseCounts et EstimatedCost.

Answer 2

Voici ce qui a finalement fonctionné :

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

Answer 3

Vous pouvez lancer un traceur et vérifier les procédures en cours d'exécution et leur efficacité en termes de recherche et d'utilisation des index.

Plutôt que de faire tous les indices pour tout le monde, il est préférable d'optimiser le plus gros problème - vous en tirerez généralement le plus grand bénéfice.

Dans la trace du profileur, déterminez quelle proc stockée / instruction tsql s'exécute le plus grand nombre de fois et consomme le plus de ressources. Ce sont ceux-là que vous voulez vraiment poursuivre.