Home > Articoli > Indici : capire quando e come sono veramente utilizzati? Utilizzo dei piani di esecuzione per rispondere a queste domande

Indici : capire quando e come sono veramente utilizzati? Utilizzo dei piani di esecuzione per rispondere a queste domande

Gli indici sono gli oggetti che permettono di fare un salto di qualità da un punto di vista di performance al nostro sistema.

Ma un indice è veramente utilizzato dal nostro sistema?

E, nel caso sia utilizzato come viene utilizzato e qual’è il suo costo in termini di gestione?

Molto spesso gli indici vengono messi ad intuito e, visto, che le performance di una particolare query sono migliorate si riitiene che l’indice è utile e quindi lo lasciamo.

Certe volte, però, il numero di indici inzia ad aumentare a dismisura e il problema della gestione degli indici può rallentare le performance del nostro sistema nel suo complesso anche se, magari, una singola operazione è stata migliorata.

Infatti non è assolutamente detto che un indice venga utilizzato all’interno di una query. L’ottimizzatore di SQL Server fa sempre delle valutazione di costi benefici in termini soprattutto di operazioni di I/O. Ad esempio se l’utilizzo di un indice impone di effettuare degli accessi random al file system (RID LOOKUP) per recuperare le informazioni necessarie alla query ecco che potrebbe considerare molto dispendiosa questa operazione e quindi decidere che è preferibile un accesso sequenziale su indice cluster che contiene o come si dice in termine tecnico "copre" tutte le colonne di una tabella e quindi evita i famigerati accessi random al file system. Qualcuno potrebbe dire che è possibile utilizzare le colonne incluse e ha ragione ma anche qui bisogna stare attenti. Quanto aumenta la dimensione di un indice? Le colonne incluse impongono un aggiornamento dell’indice quando il record è aggiornato oppure sono colonne che non vengono mai modificate in fase di aggiornamento.

Ovviamente anche la scelta degli indici, dell’indice cluster da utilizzare (se riteniamo sia necessario) dovrebbero essere analizzate ben bene ma questo non è lo scopo di queste righe e quindi torniamo a focalizzarci sul nostro tema.

Cercheremo adesso di rispondere a due domande

Prima domanda: Quanto occupa un indice e quanto viene utilizzato/movimentato?

Una prima risposta a questa domanda viene fornita dalla DMV

sys.dm_db_index_usage_stats

e da questa query che utilizza altre DMV per dare informazioni sui "missing index"

SELECT object_name(mid.object_id),
  mig.index_group_handle, mid.index_handle,
  CONVERT (decimal (28,1),
    migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)
  ) AS improvement_measure,
  ‘CREATE INDEX missing_index_’ + CONVERT (varchar, mig.index_group_handle) + ‘_’ + CONVERT (varchar, mid.index_handle)
  + ‘ ON ‘ + mid.statement
  + ‘ (‘ + ISNULL (mid.equality_columns,”)
    + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ‘,’ ELSE ” END
    + ISNULL (mid.inequality_columns, ”)
  + ‘)’
  + ISNULL (‘ INCLUDE (‘ + mid.included_columns + ‘)’, ”) AS create_index_statement,
  migs.*, mid.database_id, mid.[object_id] FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10
ORDER BY object_name(mid.object_id), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

E’ necessario prendere con MOLTA cautela la lista dei missing index e valutare bene quali indici è richiesto di fornire e quale sia il loro impatto su tutto il sistema.

La prima query fornisce un insieme di informazioni un pò ridotte e quindi è preferibile utilizzare lo script riportato di seguito che fornisce molte più informazioni relative anche alle partizioni di una tabelle o di un indice indicando dimensioni e utilizzi suddivisi per ogni singola parzione. Per poter analizzare tutti i database si può utilizzare la stored UTIL_AllObjects che memorizza in una tabella temporane tutti gli oggetti di tutti i database di un server. 

/*
Memorizza all’interno della tabella di cui abbiamo fornito il nome l’elenco degli oggetti di ogni database presente sul nostro server

*/
— =========================================================

CREATE PROCEDURE [dbo].[UTIL_AllObjects]     @Tabella       varchar(200),          — Nome della tabella da utilizzare. In genere temporanea.
  @AncheSistema  bit = ‘false’          — Indica se includere anche i database di sistema oppure no
WITH EXECUTE AS CALLER
AS
BEGIN
    SET NOCOUNT ON

  DECLARE @Id int
  DECLARE @MaxId int
  DECLARE @Sql varchar(MAX)
  DECLARE @DbId   int
  DECLARE @NomeDb varchar(300)

  SET @Sql = N’ALTER TABLE ‘ + @Tabella + ‘
   ADD
     DbId           int,
     DbName         varchar(300),
     ObjectId       int,
     ObjectName     varchar(300),
     ObjectType     char(2),
     ObjectTypeDesc nvarchar(60)
  ‘
  EXEC (@SQL)
  DECLARE @TmpDB TABLE
   (
     Id int identity(1,1),
     IdDb   int,
     NameDb varchar(300)
   )

  if @AncheSistema = ‘true’
    begin
      insert into @TmpDB (IdDb, NameDb)
      select database_id, name from sys.databases where name <> ‘tempdb’ and source_database_id is null
    end
  else
    begin
      insert into @TmpDB (IdDb, NameDb)
      select database_id, name from sys.databases where not name in (‘tempdb’,’master’, ‘model’, ‘msdb’) and source_database_id is null
    end

  select @MaxId = MAX(Id) from @TmpDB

  set @Id = 1
  select @DbId = IdDb, @NomeDb = NameDb from @tmpDB where Id = @Id
  while @Id <= @MaxId
    begin
      set @Sql = N’insert into ‘ + @Tabella + ‘ (DbId, DbName, ObjectId, ObjectName, ObjectType, ObjectTypeDesc)
                  select ‘ + cast(@DbId as varchar(20)) + ‘, ”’ + @NomeDb + ”’, object_id, name, type, type_desc from ‘ + @NomeDb + ‘.sys.objects’
      EXEC (@sql)
      set @Id = @Id + 1
      select @DbId = IdDb, @NomeDb = NameDb from @tmpDB where Id = @Id
    end
END

declare @nometabella varchar(500) — se ” TUTTE
declare @DbIdx       int
declare @IdxOperSta  bit          — TRUE per includere anche le statistiche di utilizzo
declare @IdxSta      bit          — TRUE per includere anche le statistiche

set @nometabella = ‘tabella’
set @DbIdx       = DB_ID()
set @IdxOperSta  = ‘true’
set @IdxSta      = ‘true’

DECLARE @Id int
DECLARE @MaxId int
DECLARE @Tab int
DECLARE @Idx int
DECLARe @Part int
DECLARE @Tipo varchar(50)

DECLARE @Tabelle TABLE
(
  id        int identity(1,1),
  IdTabella int
)

DECLARE @Risultato TABLE
(
  id                                  int identity(1,1),
  Tabella                             varchar(200),
  indice                              varchar(200),
  Partizione                          int,
  Righe                               bigint,
  TipoIndice                          varchar(200),
  DatiMemorizzati                     varchar(200),
  total_pages                         bigint,
  used_pages                          bigint,
  data_pages                          bigint,
  FileGroup                           varchar(200),
  NomeFile                            varchar(200),
  PercorsoFisicoFile                  varchar(500),
  StatoFile                           varchar(200),
  DimensioneFile                      bigint, 
  TipoFileGroup                       varchar(200),
  TipoFile                            varchar(200),
  user_seeks                          bigint,
  user_scans                          bigint,
  user_lookups                        bigint,
  user_updates                        bigint,
  IndexDepth                          int,
  IdTabella                           int,
  IdIndice                            int,
  IdDb                                int
)

DECLARE @RisultatoStat TABLE
(
    database_id                         smallint,
    object_id                           int,
    index_id                            int,
    partition_number                    int,
  leaf_insert_count                      bigint,
  leaf_delete_count                     bigint,   
  leaf_update_count                      bigint,
  leaf_ghost_count                      bigint,   
  nonleaf_insert_count                    bigint,
  nonleaf_delete_count                    bigint,
  nonleaf_update_count                    bigint,
  leaf_allocation_count                    bigint,
  nonleaf_allocation_count                bigint,
  leaf_page_merge_count                    bigint,
  nonleaf_page_merge_count                bigint,
  range_scan_count                        bigint,
  singleton_lookup_count                  bigint,
  forwarded_fetch_count                    bigint,
  lob_fetch_in_pages                      bigint,
  lob_fetch_in_bytes                      bigint,
  lob_orphan_create_count                  bigint,
  lob_orphan_insert_count                  bigint,
  row_overflow_fetch_in_pages              bigint,
  row_overflow_fetch_in_bytes              bigint,
  column_value_push_off_row_count          bigint,
  column_value_pull_in_row_count          bigint,
  row_lock_count                          bigint,
  row_lock_wait_count                      bigint,
  row_lock_wait_in_ms                      bigint,
  page_lock_count                          bigint,
  page_lock_wait_count                    bigint,
  page_lock_wait_in_ms                    bigint,
  index_lock_promotion_attempt_count  bigint,
  index_lock_promotion_count              bigint,
  page_latch_wait_count                    bigint,
  page_latch_wait_in_ms                    bigint,
  page_io_latch_wait_count                bigint,
  page_io_latch_wait_in_ms              bigint
)

insert into @Risultato (Tabella, indice, Partizione, Righe, TipoIndice, DatiMemorizzati, total_pages,
            used_pages, data_pages, FileGroup, NomeFile, PercorsoFisicoFile, StatoFile,
            DimensioneFile, TipoFileGroup, TipoFile, user_seeks, user_scans, user_lookups, user_updates, IndexDepth, IdTabella, IdIndice, IdDb)
select OBJECT_NAME(p.object_id) as Tabella, i.name as indice, p.partition_number as Partizione,p.rows as Righe,
i.type_desc as [Tipo Indice],au.type_desc [Dati Memorizzati],au.total_pages,au.used_pages,au.data_pages,ds.name [FileGroup],
mf.name [Nome File], mf.physical_name [Percorso Fisico File], mf.state_desc [Stato File], mf.size [Dimensione File],
ds.type_desc [Tipo FileGroup], mf.type_desc [Tipo File], US.user_seeks, us.user_scans, us.user_lookups, us.user_updates,
INDEXPROPERTY(i.object_id, i.name, ‘IndexDepth’) as IndexDepth, p.object_id, i.index_id, mf.database_id
from sys.partitions p
LEFT OUTER JOIN sys.indexes as i ON i.object_id = p.object_id AND i.index_id = p.index_id
inner join sys.allocation_units au on au.container_id = p.partition_id
inner join sys.data_spaces ds on ds.data_space_id = au.data_space_id
inner join sys.master_files mf on ds.data_space_id = mf.data_space_id
left outer join sys.dm_db_index_usage_stats US on US.Index_Id = i.index_id AND US.database_id = mf.database_id and us.object_id = i.object_id
where mf.database_id = @DbIdx AND ((@NomeTabella = ”) OR (OBJECT_NAME(p.object_id) = @NomeTabella))
order by i.type, p.partition_number,ds.name,au.type

if @IdxSta = ‘TRUE’
  begin
    insert into @Risultato (Tabella, indice, Partizione, Righe, TipoIndice, DatiMemorizzati, total_pages,
                used_pages, data_pages, FileGroup, NomeFile, PercorsoFisicoFile, StatoFile,
                DimensioneFile, TipoFileGroup, TipoFile, user_seeks, user_scans, user_lookups, user_updates, IndexDepth, IdTabella, IdIndice, IdDb)
    select OBJECT_NAME(STA.object_id) as Tabella, IsNull(i.name, STA.Name) as indice, 0 as Partizione,0 as Righe,
    i.type_desc as [Tipo Indice],au.type_desc [Dati Memorizzati],au.total_pages,au.used_pages,au.data_pages,ds.name [FileGroup],
    mf.name [Nome File], mf.physical_name [Percorso Fisico File], mf.state_desc [Stato File], mf.size [Dimensione File],
    ds.type_desc [Tipo FileGroup], mf.type_desc [Tipo File], US.user_seeks, us.user_scans, us.user_lookups, us.user_updates,
    INDEXPROPERTY(i.object_id, i.name, ‘IndexDepth’) as IndexDepth, STA.object_id, i.index_id, mf.database_id
    from sys.stats STA
    full outer JOIN sys.indexes I  ON STA.object_id = I.object_id AND I.index_id = STA.Stats_id
    left outer join sys.data_spaces ds on ds.data_space_id = I.data_space_id
    left outer join sys.allocation_units au on au.container_id = ds.data_space_id
    left outer join sys.master_files mf on ds.data_space_id = mf.data_space_id
    left outer join sys.dm_db_index_usage_stats US on US.Index_Id = i.index_id AND US.database_id = mf.database_id and us.object_id = i.object_id
    where ((DS.data_Space_ID is NULL) OR (DS.data_Space_ID = 1) OR (DS.data_Space_ID > 65000))
          AND (IsNull(mf.database_id, @DbIdx) = @DbIdx) AND ((@NomeTabella = ”) OR (OBJECT_NAME(STA.object_id) = @NomeTabella))
          AND (not IsNull(i.name, STA.Name) is null)
    order by i.type, ds.name,au.type
  end

if @IdxOperSta = ‘TRUE’
  begin
    insert into @Tabelle (IdTabella)
    select distinct IdTabella from @Risultato

select @MaxId = max(Id) from @Tabelle
set @Id = 1

select @Tab = IdTabella from @Tabelle where Id = @Id
while @Id <= @MaxId
  begin
    insert into @RisultatoStat
    select *
    from sys.dm_db_index_operational_stats(@DbIdx, @Tab, NULL, NULL) OS

    set @Id = @Id + 1
    select @Tab = IdTabella from @Tabelle where Id = @Id
  end
end

select   RI.Tabella,
  RI.indice,
  RI.Partizione,
  RI.Righe,
  RI.TipoIndice,
  RI.DatiMemorizzati,
  RI.FileGroup,
  RI.NomeFile,
  RI.total_pages,
  (RI.total_pages * 8)/ 1024 as [used_MB],
  RI.used_pages,
  RI.data_pages,
  RI.user_seeks,
  RI.user_scans,
  RI.user_lookups,
  RI.user_updates,
  cast (100.0 * row_lock_wait_count / (1 + row_lock_count) as numeric(15,2)) as [block %],
  cast (1.0 * row_lock_wait_in_ms / (1 + row_lock_wait_count) as numeric(15,2)) as [avg row lock waits in ms],
  RI.IndexDepth,
  RI.PercorsoFisicoFile,
  RI.StatoFile,
  RI.DimensioneFile,
  RI.TipoFileGroup,
  RI.TipoFile,
  RIST.leaf_insert_count,
  RIST.leaf_delete_count,
  RIST.leaf_update_count,
  RIST.leaf_ghost_count,
  RIST.nonleaf_insert_count,
  RIST.nonleaf_delete_count,
  RIST.nonleaf_update_count,
  RIST.leaf_allocation_count,
  RIST.nonleaf_allocation_count,
  RIST.leaf_page_merge_count,
  RIST.nonleaf_page_merge_count,
  RIST.range_scan_count,
  RIST.singleton_lookup_count,
  RIST.forwarded_fetch_count,
  RIST.lob_fetch_in_pages,
  RIST.lob_fetch_in_bytes,
  RIST.lob_orphan_create_count,
  RIST.lob_orphan_insert_count,
  RIST.row_overflow_fetch_in_pages,
  RIST.row_overflow_fetch_in_bytes,
  RIST.column_value_push_off_row_count,
  RIST.column_value_pull_in_row_count,
  RIST.row_lock_count,
  RIST.row_lock_wait_count,
  RIST.row_lock_wait_in_ms,
  RIST.page_lock_count,
  RIST.page_lock_wait_count,
  RIST.page_lock_wait_in_ms,
  RIST.index_lock_promotion_attempt_count,
  RIST.index_lock_promotion_count,
  RIST.page_latch_wait_count,
  RIST.page_latch_wait_in_ms,
  RIST.page_io_latch_wait_count,
  RIST.page_io_latch_wait_in_ms
from @Risultato RI
     LEFT JOIN
(SELECT database_id,
       object_id,
       index_id,
       partition_number,
       SUM(leaf_insert_count) leaf_insert_count,
       SUM(leaf_delete_count) leaf_delete_count,
       SUM(leaf_update_count) leaf_update_count,
       SUM(leaf_ghost_count) leaf_ghost_count,
       SUM(nonleaf_insert_count) nonleaf_insert_count,
       SUM(nonleaf_delete_count) nonleaf_delete_count,
       SUM(nonleaf_update_count) nonleaf_update_count,
       SUM(leaf_allocation_count) leaf_allocation_count,
       SUM(nonleaf_allocation_count) nonleaf_allocation_count,
       SUM(leaf_page_merge_count) leaf_page_merge_count,
       SUM(nonleaf_page_merge_count) nonleaf_page_merge_count,
       SUM(range_scan_count) range_scan_count,
       SUM(singleton_lookup_count) singleton_lookup_count,
       SUM(forwarded_fetch_count) forwarded_fetch_count,
       SUM(lob_fetch_in_pages) lob_fetch_in_pages,
       SUM(lob_fetch_in_bytes) lob_fetch_in_bytes,
       SUM(lob_orphan_create_count) lob_orphan_create_count,
       SUM(lob_orphan_insert_count) lob_orphan_insert_count,
       SUM(row_overflow_fetch_in_pages) row_overflow_fetch_in_pages,
       SUM(row_overflow_fetch_in_bytes) row_overflow_fetch_in_bytes,
       SUM(column_value_push_off_row_count) column_value_push_off_row_count,
       SUM(column_value_pull_in_row_count) column_value_pull_in_row_count,
       SUM(row_lock_count) row_lock_count,
       SUM(row_lock_wait_count) row_lock_wait_count,
       SUM(row_lock_wait_in_ms) row_lock_wait_in_ms,
       SUM(page_lock_count) page_lock_count,
       SUM(page_lock_wait_count) page_lock_wait_count,
       SUM(page_lock_wait_in_ms) page_lock_wait_in_ms,
       SUM(index_lock_promotion_attempt_count) index_lock_promotion_attempt_count,
       SUM(index_lock_promotion_count) index_lock_promotion_count,
       SUM(page_latch_wait_count) page_latch_wait_count,
       SUM(page_latch_wait_in_ms) page_latch_wait_in_ms,
       SUM(page_io_latch_wait_count) page_io_latch_wait_count,
       SUM(page_io_latch_wait_in_ms) page_io_latch_wait_in_ms
from @RisultatoStat RIST
group by database_id, object_id, index_id, partition_number) As RIST ON RI.IdTabella = RIST.object_id AND RI.IdIndice  = RIST.index_id AND RI.Partizione = RIST.partition_number
order by RI.Tabella, RI.Indice, RI.TipoIndice, RI.Partizione, RI.DatiMemorizzati

In ogni caso, queste due query forniscono informazioni sull’utilizzo di un indice indicando quante operazioni di aggiornamento ha subito o quante operazioni di seek vi sono state fatte ma non fornisce nessuna informazione su chi ha realmente utilizzato l’indice o, magari, su chi avrebbe potuto utilizzarlo  (nel caso di missing).

L’unica possibilità per avere queste informazioni è il piano di esecuzione generato da SQL Server per ogni stored/query utilizzata all’interno del nostro sistema.

e a questo punto veniamo alla nostra Seconda domanda: Avendo tante stored e tante query come è possibile implementare in maniera automatica o, almeno semi automatica il processo di ricerca all’interno dei piani di esecuzione?.

Il seguente script può darci una mano in questa operazione

  /*
E’ Possibile Effettuare Ricerche su un livello o due livelli
Ricerche su un livello :
    Si ricerca un elemento o un attributo all’interno di un nodo
        Ricerca Utilizzo di un indici : //sqlplan:Object[attribute::Index="[<< NomeIndice>>]"]         Ricerca Piani con Missing Index : MissingIndexes
        Ricerca utilizzo di un operatore fisico : << NomeOperatore>>
        Ricerca utilizzo di un operatore fisico : RelOp[attribute::PhysicalOp="<< NomeOperatoreFisico>>"]         Ricerca utilizzo di un operatore Logico : RelOp[attribute::LogicalOp="<< NomeOperatoreFisico>>"] Ricerche su due livelli :
    Si effettua una ricerca all’interno di un nodo complesso precedentemente selezionato.
        ad esempio nei nodi MissingIndexes si ricerca quelli  che hanno come oggetto buste
          Primo Livello di Ricerca   : MissingIndexes
          Secondo Livello di Ricerca : MissingIndex[attribute::Table="[nometabella]"]

NOTE : E’ necessario utilizzare una espressione letterale XQUERY come valore di ricerca

       Per Elenco degli operatori vedi in fondo al file
*/
— =========================================================

DECLARE @Sql varchar(MAX)
DECLARE @Ricerca varchar(500)               — Ricerca su un livello
DECLARE @RicercaL1 varchar(500)             — Ricerca su due livello Primo Livello
DECLARE @RicercaL2 varchar(500)             — Ricerca su due livello Secondo Livello
DECLARE @QueryDaEseguire int                — 0 esegue Primo e Secondo Livello; 1 solo Primo Livello 2 solo Secondo Livello

SET @QueryDaEseguire = 1
DECLARE @Idx varchar(200)

set @Idx = ‘buste-1’
SET @Ricerca = ‘Object[attribute::Index="[‘ + @Idx + ‘]"]’
SET @Ricerca = ‘MissingIndexes’
select @ricerca
 
SET @RicercaL1 = ‘MissingIndexes’
SET @RicercaL2 = ‘MissingIndex[attribute::Table="[buste]"]’

— =========================================================
begin try
  DROP TABLE #OggettiDatabase
end try
begin catch
end catch
CREATE TABLE #OggettiDatabase (Id int identity(1,1))

begin try
  DROP TABLE #Piani
end try
begin catch
end catch
CREATE TABLE #Piani
(
  Id int identity(1,1),
  Tipo varchar(2),
  Tipologia varchar(3),
  SQL_handle varbinary(64),
  plan_handle varbinary(64)
)

exec [Repository].[dbo].[UTIL_AllObjects] ‘#OggettiDatabase’;

–SELECT * FROM #OggettiDatabase

— Prima Query su Cached Plan
— ==========================

if @QueryDaEseguire = 0 or @QueryDaEseguire = 1
  begin
    SET @Sql = N’with XMLNAMESPACES (”http://schemas.microsoft.com/sqlserver/2004/07/showplan” as sqlplan)
                 insert into #Piani (Tipo, Tipologia, plan_handle)
                 SELECT DISTINCT ”C”, ”1”, QS.plan_handle
                 FROM sys.dm_exec_cached_plans QS
                 CROSS APPLY sys.dm_exec_query_plan(QS.plan_handle) QP
                 CROSS APPLY QP.query_plan.nodes(
                 ”//sqlplan:’ + @Ricerca + ”’
                                        ) Attributes (Attribute);’
    EXEC (@Sql)

    — Seconda Query su Query Stats
    — =============================

    SET @Sql = N’with XMLNAMESPACES (”http://schemas.microsoft.com/sqlserver/2004/07/showplan” as sqlplan)
                 insert into #Piani (Tipo, Tipologia, SQL_Handle, plan_handle)
                 SELECT DISTINCT ”S”, ”1”, QS.sql_handle, QS.plan_handle
                 FROM sys.dm_exec_query_stats QS
                 CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) QP
                 CROSS APPLY QP.query_plan.nodes(
                 ”//sqlplan:’ + @Ricerca + ”’
                                        ) Attributes (Attribute);’

    EXEC (@Sql)
end

— Query Nidificata per trovare all’interno di un nodo XML un’altro valore (Un livello)
if @QueryDaEseguire = 0 or @QueryDaEseguire = 2
  begin
    SET @Sql = N’with XMLNAMESPACES (”http://schemas.microsoft.com/sqlserver/2004/07/showplan” as sqlplan)
                 insert into #Piani (Tipo, Tipologia, plan_handle)
                 SELECT DISTINCT ”C”, ”2”, QS.plan_handle
                 FROM sys.dm_exec_cached_plans QS
                 CROSS APPLY sys.dm_exec_query_plan(QS.plan_handle) QP
                 CROSS APPLY QP.query_plan.nodes(
                 ”//sqlplan:’ + @RicercaL1 + ”’
                                        ) Attributes (Attribute)
                 CROSS APPLY Attributes.Attribute.nodes(
                     ”//sqlplan:’ + @RicercaL2 + ”’
                                                ) Attributes2 (Attribute);’
    EXEC (@Sql)

    — Seconda Query su Query Stats
    — =============================

    SET @Sql = N’with XMLNAMESPACES (”http://schemas.microsoft.com/sqlserver/2004/07/showplan” as sqlplan)
                 insert into #Piani (Tipo, Tipologia, SQL_Handle, plan_handle)
                 SELECT DISTINCT ”S”, ”2”, QS.sql_handle, QS.plan_handle
                 FROM sys.dm_exec_query_stats QS
                 CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) QP
                 CROSS APPLY QP.query_plan.nodes(
                 ”//sqlplan:’ + @RicercaL1 + ”’
                                        ) Attributes (Attribute)
                 CROSS APPLY Attributes.Attribute.nodes(
                     ”//sqlplan:’ + @RicercaL2 + ”’
                                                ) Attributes2 (Attribute);’
    EXEC (@Sql)
end

select OD.DBName, OD.ObjectName, QS.RefCounts, QS.UseCounts, QS.Size_In_Bytes, QS.Cacheobjtype, QS.objtype, QS.plan_handle
       , QP.* , PIA.Tipologia
from #Piani PIA
     LEFT OUTER JOIN sys.dm_exec_cached_plans QS ON PIA.plan_handle = QS.plan_handle
     CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) QP
     LEFT OUTER JOIN #OggettiDatabase OD ON OD.DbId = QP.dbId AND OD.ObjectID = QP.Objectid
WHERE PIA.Tipo = ‘C’
ORDER BY 1,2

SELECT OD.DBName, OD.ObjectName,XX.*,
           QP.dbid,QP.objectid,QP.number, QP.encrypted, QP.query_plan
—           ,OD2.DBName, OD2.ObjectName
—           , QP2.dbid,QP2.objectid,QP2.number, QP2.encrypted, QP2.query_plan
FROM
(select    SUM(QS.plan_generation_num) plan_generation_num, QS.sql_handle, QS.plan_handle,
           SUM(QS.execution_count) execution_count, PIA.Tipologia
—           ,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
from #Piani PIA
     LEFT OUTER JOIN sys.dm_exec_query_stats QS ON QS.sql_handle = PIA.sql_handle AND QS.plan_handle = PIA.plan_handle
WHERE PIA.Tipo = ‘S’
GROUP BY QS.sql_handle, QS.plan_handle, PIA.Tipologia
—           ,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, PIA.Tipologia
) AS XX
  CROSS APPLY sys.dm_exec_query_plan(XX.plan_handle) QP
  LEFT OUTER JOIN #OggettiDatabase OD ON OD.DbId = QP.dbId AND OD.ObjectID = QP.Objectid
  OUTER APPLY sys.dm_exec_sql_text(XX.sql_handle) QP2
  LEFT OUTER JOIN #OggettiDatabase OD2 ON OD2.DbId = QP2.dbId AND OD2.ObjectID = QP2.Objectid
ORDER BY 1,2

— ==================================================
/*
================
Elenco Operatori
================

Assert
Bitmap
Collapse
ComputeScalar
Concat
ConstantScan
CreateIndex
DeletedScan
Extension
Filter
Generic
Hash
IndexScan
InsertedScan
LogRowScan
Merge
MergeInterval
NestedLoops
OnlineIndex
Parallelism
ParameterTableScan
PrintDataflow
RemoteFetch
RemoteModify
RemoteQuery
RemoteRange
RemoteScan
RowCountSpool
ScalarInsert
Segment
Sequence
SequenceProject
SimpleUpdate
Sort
Split
Spool
StreamAggregate
Switch
TableScan
TableValuedFunction
Top
TopSort
Update

=======================
Elenco Operatori Logici
=======================

Aggregate
Assert
Async Concat
Bitmap Create
Clustered Index Scan
Clustered Index Seek
Clustered Update
Collapse
Compute Scalar
Concatenation
Constant Scan
Cross Join
Delete
Deleted Scan
Distinct Sort
Distinct
Distribute Streams
Eager Spool
Filter
Flow Distinct
Full Outer Join
Gather Streams
Generic
Index Scan
Index Seek
Inner Join
Insert
Inserted Scan
Lazy Spool
Left Anti Semi Join
Left Outer Join
Left Semi Join
Log Row Scan
Merge Interval
Parameter Table Scan
Partial Aggregate
Print
Remote Delete
Remote Insert
Remote Query
Remote Scan
Remote Update
Repartition Streams
RID Lookup
Right Anti Semi Join
Right Outer Join
Right Semi Join
Segment
Sequence
Sort
Split
Switch
Table-valued function
Table Scan
Top
TopN Sort
UDX
Union
Update

=======================
Elenco Operatori Fisici
=======================

Assert
Bitmap
Clustered Index Delete
Clustered Index Insert
Clustered Index Scan
Clustered Index Seek
Clustered Index Update
Clustered Update
Collapse
Compute Scalar
Concatenation
Constant Scan
Deleted Scan
Filter
Generic
Hash Match
Index Delete
Index Insert
Index Scan
Index Seek
Index Spool
Index Update
Inserted Scan
Log Row Scan
Merge Interval
Merge Join
Nested Loops
Online Index Insert
Parallelism
Parameter Table Scan
Print
Remote Delete
Remote Insert
Remote Query
Remote Scan
Remote Update
RID Lookup
Row Count Spool
Segment
Sequence
Sequence Project
Sort
Split
Stream Aggregate
Switch
Table-valued function
Table Delete
Table Insert
Table Scan
Table Spool
Table Update
Top
UDX

*/

Questo script effettua una analisi di tutti i piani memorizzati nella cache e forniti dalla DMV sys.dm_exec_query_stats andando a verificare quali sono quelli che contengono una certa informazione. Ad esempio è possibile analizzare quelli che contengono missingindexes e quindi vedere come utilizzerebbero certi indici oppure quali fanno una table scan ecc.ecc.

Attenzione: Le ricerche in XML sono case sensitive e quindi è necessario scrivere i nomi di operatori, indici, tabelle ecc. esattamente come memorizzati all’interno del sistema.

E’ possibile fare anche ricerche su due livelli ad esempi missingindex e su quale tabella.

Ovviamente non risolve tutti i problemi ma diciamo che può essere un buon strumento di aiuto.

Da considerare che un piano di esecuzione può essere eliminato dalla cache e quindi è possibile, in ogni caso,  creare un agent che periodicamente salva il risultato della tabella DMV sys.dm_exec_query_stats in una tabella fisica e andare successivamente ad effettuare le nostre analisi sul contenuto di questa tabella. 

Per ultimo una query che fornisce informazioni su indici creati e mai utilizzati dal nostro sistema. 

 

select db_name(idu.database_id) as [database], object_name(idu.object_id) as tabella , ID.name, ID.type_desc, IDU.*
from Sys.dm_db_index_usage_stats IDU
inner join sys.indexes ID ON ID.object_id = IDU.object_id and ID.index_id = IDU.index_id AND ID.Type <> 0
inner join sys.objects OBJ ON OBJ.object_id = idu.object_id AND OBJ.type <> ‘S’
where user_seeks = 0 and user_scans = 0 and user_lookups = 0
and id.name <> ‘myprimary’ and id.is_primary_key = 0
order by 1,2,3

Anche il risultato di questa query è da analizzare attentamente e non bisogna eliminare tutti gli indici non utilizzati in maniera acritica. Infatti, è possibile che, ad esempio, un indice per vincolare un certo campo ad essere univoco non sia mai utilizzato ma, ovviamente, l’indice non può essere eliminato pena la perdita del vincolo di univocità
 

Chi è Davide Mauri

Microsoft Data Platform MVP dal 2007, Davide Mauri si occupa di Data Architecture e Big Data nel mondo dell'IoT. Attualmente ricopre il ruolo di "Director Software Development & Cloud Infrastructure" in Sensoria, societa specializzata nella creazione di Wearables e sensori per l'IoT applicati a tessuti ed oggetti sportivi.

Leggi Anche

Unit testing: Il framework tSQLt e l’esecuzione di un test!

Nell’articolo precedente, il primo di questa serie, abbiamo descritto che cosa √® lo Unit Test …