UGISS User Group Italiano SQL Server
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à
