Dynamische Verwaltungssichten und -funktionen für Indizes - I
SQL Server 2005 teilt die dynamischen Verwaltungssichten und -funktionen nach der Modul-Komponente - zu der sie in Beziehung stehen - in zwölf Kategorien auf.
Das diesem Artikel zugrunde liegende Objekt sys.dm_db_index_operational_stats gehört zur Kategorie Dynamische Verwaltungssichten und -funktionen im Zusammenhang mit dem Index, welche sich in sieben Verwaltungsobjekte -
- sys.dm_db_index_operational_stats,
- sys.dm_db_index_physical_stats,
- sys.dm_db_index_usage_stats,
und mit SQL Server 2005 Service Pack 1 sind hinzu gekommen,
- sys.dm_db_missing_index_columns,
- sys.dm_db_missing_index_details,
- sys.dm_db_missing_index_group_stats,
- sys.dm_db_missing_index_groups
- Gesamtzahl der im Index oder Heap gestarteten Bereichs- und Tabellenscans + Gesamtzahl der Abrufvorgänge einzelner Zeilen aus dem Index oder Heap
- Gesamtzahl der im Index oder Heap gestarteten Bereichs- und Tabellenscans + Gesamtzahl der Abrufvorgänge einzelner Zeilen aus dem Index oder Heap + Gesamtzahl der Einfügevorgänge auf Blattebene
- Gesamtzahl der Einfügevorgänge + Gesamtzahl der Löschvorgänge + Gesamtzahl der Aktualisierungen ,-auf Blattebene
- Gesamtzahl der Einfügevorgänge + Gesamtzahl der Löschvorgänge + Gesamtzahl der Aktualisierungen ,-über der Blattebene
- Blockierung in Prozent = 100 * Gesamthäufigkeit, mit der das Datenbankmodul auf eine Zeilensperre gewartet hat / Gesamtzahl der angeforderten Zeilensperren
- Mittelwert in ms pro Zeilensperre = Gesamtzahl der Millisekunden, die das Datenbankmodul auf eine Zeilensperre gewartet hat / Gesamthäufigkeit, mit der das Datenbankmodul auf eine Zeilensperre gewartet hat
- Gesamtzahl der angeforderten Seitensperren
- Mittelwert in ms pro Seitensperre = Gesamtzahl der Millisekunden, die Datenbankmodul auf eine Seitensperre gewartet hat / Gesamthäufigkeit, mit der Datenbankmodul auf eine Seitensperre gewartet hat
- Mittelwert in ms pro Latchkonflikt = Gesamtzahl der Millisekunden, die das Datenbankmodul aufgrund eines Latchkonflikts gewartet hat / Gesamthäufigkeit, mit der das Datenbankmodul aufgrund eines Latchkonflikts gewartet hat
- Mittelwert in ms pro E/A-Seitenlatch = Gesamtzahl der Millisekunden, die das Datenbankmodul auf einen E/A-Seitenlatch gewartet hat / Gesamthäufigkeit, mit der Datenbankmodul auf einen E/A-Seitenlatch gewartet hat
- aufteilt.
Alle dynamischen Verwaltungsobjekte dieser Art sind serverweit gültig und erfordern die VIEW SERVER STATE-Berechtigung, um darauf Zugriff zu nehmen.
Das dynamische Verwaltungsobjekt sys.dm_db_index_operational_stats gibt aktuelle Statistiken zu Sperren-, Latch- und Zugriffsmethoden sowie E/A-Aktivitäten zu allen Partitionen einer Tabelle oder eines Indexes in der Datenbank zurück - so lange das Metadaten-Cacheobjekt für den Index oder Heap vorhanden ist. Einfacher gesagt, die entsprechenden Statistiken müssen noch im Zwischenspeicher sein, was natürlich auch bedeutet das auf den HEAP oder Index schon mal zugegriffen wurde, da er sich sonst nicht im Metadatencache befindet.
Sicht oder Funktion?
Schauen wir uns nun das Objekt sys.dm_db_index_operational_stats aus der Nähe an - ist es eine Sicht oder eine Funktion?
SELECT
QUOTENAME(OBJECT_SCHEMA_NAME(i.[object_id]))
+ N'.'
+ QUOTENAME(OBJECT_NAME(i.[object_id])) AS 'OBJECT_NAME',
i.type_desc AS 'OBJECT_TYPE_DESCRIPTION'
FROM sys.system_objects i
WHERE (i.name = 'dm_db_index_operational_stats') --** durch Ändern der WHERE-KLAUSEL nach - (i.name LIKE 'dm%') ORDER BY i.type_desc, i.name - können Sie für alle DM's feststellen, ob es eine Sicht(VIEW) oder eine Tabellenwertfunktion(SQL_INLINE_TABLE_VALUED_FUNCTION) ist.
Ergebnis:
OBJECT_NAME OBJECT_TYPE_DESCRIPTION ------------------------------------- -------------------------------- [sys].[dm_db_index_operational_stats] SQL_INLINE_TABLE_VALUED_FUNCTION
Das Ergebnis liefert uns als OBJECT_TYPE_DESCRIPTION den Wert SQL_INLINE_TABLE_VALUED_FUNCTION zurück, also eine Funktion und zwar eine Tabellenwertfunktion.
Nun stellt sich die Frage, welche Input-Parameter braucht die Tabellenwertfunktion sys.dm_db_index_operational_stats. Um dies herauszufinden fragen wir die Objekt-Definition ab:
SELECT OBJECT_DEFINITION(OBJECT_ID('[sys].[dm_db_index_operational_stats]')) AS 'OBJECT_DEFINITION';
Das Ergebnis sieht folgendermaßen aus:
OBJECT_DEFINITION
--------------------------------------------------
create function sys.dm_db_index_operational_stats
(
@DatabaseId SMALLINT = 0,
@TableId INT = 0,
@IndexId INT = -1,
@PartitionNumber INT = 0
)
returns table
as
return select * from OpenRowset
(
VirtualIndexStats,
@DatabaseId,
@TableId,
@IndexId,
@PartitionNumber
)Das Ergebnis liefert uns eine CREATE FUNCTION Anweisung für sys.dm_db_index_operational_stats, mit folgenden Eingabe-Parametern: @DatabaseId, @TableId, @IndexId, @PartitionNumber und Ihren Standardwerten; als Ausgabetabelle erhalten wir die Eingabe-Parameter und einen nicht näher detailierten Wert VirtualIndexStats.
Um doch noch einen näheren Detailierungsgrad des Objekts sys.dm_db_index_operational_stats - hinsichtlich des Wertes VirtualIndexStats - zu erhalten, bemühen wir die gespeicherte Prozedur sp_helptext, welche uns die Definition eines Systemobjekts anzeigt:
USE [master];
GO
EXEC sp_helptext 'master.sys.dm_db_index_operational_stats';
GODas Ergebnis ist gleich dem Ergebnis der Abfrage OBJECT_DEFINITION, welches uns immer noch nicht die gesamte Ausgabetabelle zurückliefert.
Also versuchen wir mit der Prozedur sp_help Informationen zum Datenbankobjekt sys.dm_db_index_operational_stats zu erhalten:
USE [master];
GO
EXEC sp_help 'master.sys.dm_db_index_operational_stats';
GOUnd erhalten folgendes Ergebnis:
Wir erhalten einen Gesamtüberblick über das Datenbankobjekt sys.dm_db_index_operational_stats. Im ersten Teil der Ergebnismenge erhalten wir Informationen zum Typ und Erstellungsdatum. Der zweite - und hinsichtlich VirtualIndexStats interessante Teil - liefert uns nun die Ausgabetabelle der Tabellenwertfunktion sys.dm_db_index_operational_stats, mit den Spaltennamen database_id bis hin zu page_io_latch_wait_in_ms, so wie wir sie aus der Onlinedokumentation her kennen. Im dritten Teil der Ergebnismenge erhalten wir die Input-Parameter und deren Eigenschaften.
Funktions-Diagramm sys.dm_db_index_operational_stats:
Analyse-Methoden
Analyse von Zugriffen
Folgende Abfrage gibt Ihnen Auskunft über Zugriffe beim einfügen, löschen und aktualisieren auf und über der Blattebene, sowie der gestarteten Bereichs- /Tabellenscans und der Abrufvorgänge einzelner Zeilen aus dem Index oder Heap. Es werden der übersichthalber folgende Produkte gebildet:
SELECT DB_NAME(database_id) AS 'DB_NAME',
QUOTENAME(OBJECT_SCHEMA_NAME(ios.[object_id], database_id))
+ N'.'
+ QUOTENAME(OBJECT_NAME(ios.[object_id], database_id)) AS 'OBJECT_NAME',
i.name AS 'INDEX_NAME',
i.index_id AS 'INDEX_ID',
i.fill_factor AS'FILL_FACTOR',
ios.partition_number AS 'PARTITION_NUMBER',
CASE WHEN is_unique = 1 THEN 'UNIQUE ' ELSE '' END + i.type_desc
AS 'INDEX_TYPE',
(ios.range_scan_count + ios.singleton_lookup_count) AS 'READS',
(ios.range_scan_count + ios.singleton_lookup_count + ios.leaf_insert_count)
AS 'INDEX_USAGE',
(ios.leaf_insert_count + ios.leaf_update_count + ios.leaf_delete_count)
AS 'LEAF_WRITES',
(ios.nonleaf_insert_count + ios.nonleaf_update_count + ios.nonleaf_delete_count)
AS 'NONLEAF_WRITES'
FROM sys.dm_db_index_operational_stats (DB_ID(), NULL, NULL, NULL) ios
INNER JOIN sys.indexes i
ON i.[object_id] = ios.[object_id]
AND i.index_id = ios.index_id
WHERE OBJECTPROPERTY (ios.[object_id], 'IsUserTable') = 1
ORDER BY READS DESC, INDEX_USAGE, LEAF_WRITES, NONLEAF_WRITESAnalyse von Zeilensperren und -konflikten
Folgende Abfrage gibt Ihnen Auskunft über die Anzahl von Zeilensperren, die Häufigkeit , die daraus resultierende Blockierung und den Mittelwert die das Datenbankmodul pro Zeilensperre gewartet hat.
SELECT DB_NAME(database_id) AS 'DB_NAME',
QUOTENAME(OBJECT_SCHEMA_NAME(ios.[object_id], database_id))
+ N'.'
+ QUOTENAME(OBJECT_NAME(ios.[object_id], database_id)) AS 'OBJECT_NAME',
i.name AS 'INDEX_NAME',
i.index_id AS 'INDEX_ID',
i.fill_factor AS'FILL_FACTOR',
partition_number AS 'PARTITION_NUMBER',
CASE WHEN is_unique = 1 THEN 'UNIQUE ' ELSE '' END + i.type_desc AS 'INDEX_TYPE',
ios.row_lock_count AS 'ROW_LOCK_COUNT',
ios.row_lock_wait_count AS 'ROW_LOCK_WAIT_COUNT',
CAST (100.0 * ios.row_lock_wait_count / (1 + ios.row_lock_count) AS NUMERIC(15,2)) AS '% BLOCKED',
ios.row_lock_wait_in_ms AS 'ROW_LOCK_WAIT_IN_MS',
CAST (1.0 * ios.row_lock_wait_in_ms / (1 + ios.row_lock_wait_count)AS NUMERIC(15,2))
AS 'AVG_ROW_LOCK_WAIT_IN_MS'
FROM sys.dm_db_index_operational_stats (DB_ID(), NULL, NULL, NULL) ios
INNER JOIN sys.indexes i
ON i.[object_id] = ios.[object_id]
AND i.index_id = ios.index_id
AND row_lock_wait_count > 0
WHERE OBJECTPROPERTY (ios.[object_id], 'IsUserTable') = 1
ORDER BY ROW_LOCK_WAIT_COUNT DESC;Analyse Seitensperren und ,-konflikte
Folgende Abfrage gibt Ihnen Auskunft über die Anzahl angeforderter Seitensperren, die Häufigkeit und den Mittelwert die das Datenbankmodul pro Seitensperre gewartet hat.
SELECT DB_NAME(database_id) AS 'DB_NAME',
QUOTENAME(OBJECT_SCHEMA_NAME(ios.[object_id], database_id))
+ N'.'
+ QUOTENAME(OBJECT_NAME(ios.[object_id], database_id)) AS 'OBJECT_NAME',
i.name AS 'INDEX_NAME',
i.index_id AS 'INDEX_ID',
i.fill_factor AS'FILL_FACTOR',
partition_number AS 'PARTITION_NUMBER',
CASE WHEN is_unique = 1 THEN 'UNIQUE ' ELSE '' END + i.type_desc AS 'INDEX_TYPE',
ios.page_lock_count AS 'PAGE_LOCK_COUNT',
ios.page_lock_wait_count AS 'PAGE_LOCK_WAIT_COUNT',
ios.page_lock_wait_in_ms AS 'PAGE_LOCK_WAIT_COUNT_IN_MS',
CAST (1.0 * ios.page_lock_wait_in_ms / (1 + ios.page_lock_wait_count)AS NUMERIC(15,2))
AS 'AVG_PAGE_LOCK_WAIT_IN_MS'
FROM sys.dm_db_index_operational_stats (DB_ID(), NULL, NULL, NULL) ios
INNER JOIN sys.indexes i
ON i.[object_id] = ios.[object_id]
AND i.index_id = ios.index_id
AND ios.page_lock_wait_count > 0
WHERE OBJECTPROPERTY (ios.[object_id], 'IsUserTable') = 1
ORDER BY PAGE_LOCK_WAIT_COUNT DESC;Analyse Latchkonflikte
Folgende Abfrage gibt Ihnen Auskunft über Häufigkeit von Latchkonflikten, deren Wartezeiten, die Häufigkeit von E/A-Seitenlatches und die Zeit die das Datenbankmodul auf einen E/A-Seitenlatch gewartet hat.
SELECT DB_NAME(database_id) AS 'DB_NAME',
QUOTENAME(OBJECT_SCHEMA_NAME(ios.[object_id], database_id))
+ N'.'
+ QUOTENAME(OBJECT_NAME(ios.[object_id], database_id)) AS 'OBJECT_NAME',
i.name AS 'INDEX_NAME',
i.index_id AS 'INDEX_ID',
i.fill_factor AS'FILL_FACTOR',
partition_number AS 'PARTITION_NUMBER',
CASE WHEN is_unique = 1 THEN 'UNIQUE ' ELSE '' END + i.type_desc AS 'INDEX_TYPE',
ios.page_latch_wait_count AS 'PAGE_LATCH_WAIT_COUNT',
ios.page_latch_wait_in_ms AS 'PAGE_LATCH_WAIT_IN_MS',
CAST (1.0 * ios.page_latch_wait_in_ms /(1 + ios.page_latch_wait_count) AS NUMERIC(15,2))
AS 'AVG_PAGE_LATCH_WAIT_IN_MS',
ios.page_io_latch_wait_count AS 'PAGE_IO_LATCH_WAIT_COUNT',
ios.page_io_latch_wait_in_ms AS 'PAGE_IO_LATCH_WAIT_IN_MS',
CAST (1.0 * ios.page_io_latch_wait_in_ms /(1 + ios.page_io_latch_wait_count) AS NUMERIC(15,2))
AS 'AVG_PAGE_IO_LATCH_WAIT_IN_MS'
FROM sys.dm_db_index_operational_stats (DB_ID(), NULL, NULL, NULL) ios
INNER JOIN sys.indexes i
ON i.[object_id] = ios.[object_id]
AND i.index_id = ios.index_id
AND (ios.page_latch_wait_count > 0 OR ios.page_io_latch_wait_count > 0)
WHERE OBJECTPROPERTY (ios.[object_id], 'IsUserTable') = 1
ORDER BY PAGE_LATCH_WAIT_COUNT, PAGE_IO_LATCH_WAIT_COUNT DESC;Abschließend lässt sich sagen, dass die dynamische Verwaltungsfunktion sys.dm_db_index_operational_stats Ihnen ein Gefühl dafür gibt, wie Indizes verwendet werden und was sie "kosten". Und gibt Ihnen gleichzeitig Aufschluss über SQL Server Sperren oder Hardware E/A Latches, die durch Indizes verursacht werden.
Das komplette Script finden Sie als Anhang zu diesem Artikel.
| Print article | This entry was posted by tosc on 12.02.08 at 18:20:34 . Follow any responses to this post through RSS 2.0. |
-
-
backup «best practices» books bug ctp «cumulative update» demo dmv ebook humor index indexoptimize integrity kbfix maintenance «ms sql server 2008» performance php reviews serverproperty «service pack» «service pack 2» «service packs» sharepoint «sql server» «sql server 2005» «sql server 2008 r2» «sql server 2012» «sql server 2014» «sql server builds» sqlcat sqlpass «sqlpass franken» ssms ssmstoolspack t-sql «technical note» tempdb tools whitepapers
Tag cloud
administration backup «best practices» books bug ctp «cumulative update» demo dmv ebook humor index indexoptimize integrity kbfix links maintenance «ms sql server 2008» pass performance php profiler reviews «ross mistry» serverproperty «service pack» «service pack 2» «service pack 3» «service packs» sharepoint «sql server» «sql server 2005» «sql server 2008 r2» «sql server 2012» «sql server 2014» «sql server builds» sqlcat sqlpass «sqlpass franken» ssms ssmstoolspack «stacia misner» t-sql technet «technical note» tempdb tools «trace flag» version whitepapers