Fehlende Indexe (Missing Indexes) mit den dazugehörigen Ausführungsplänen

Da ich beruflich öfters mit Auswertungen im Performance-Bereich des SQL Servers zu tun habe, bin ich auch naturgemäß mit den DMV für die fehlenden Indexe ( sys.dm_db_missing_index...) in Berührung gekommen. Was mir immer dabei gefehlt hat, war ein Link zu den dazugehörigen Ausführungsplänen.
Ich empfinde diese Information als wesentlich um die Situation zu verstehen, warum das SQL System genau diesen Vorschlag gemacht hat.
Das folgende Skript habe ich mir geschrieben, um mir diese fehlende Information zukommen zu lassen. Dabei frage ich nur die gespeicherten Ausführungspläne ab und extrahiere die gewünschten Informationen aus den XML-Dateien.

-- Missing Index with Execution Plan
-- Created by Maic Beher
-- Version 1.0
-- Date: 19.10.2016
With xmlnamespaces (DEFAULT  'http://schemas.microsoft.com/sqlserver/2004/07/showplan'), Missing_Index_CTE (Usecounts,Index_Desription,ExecutionPlan)
as
(
select
usecounts,
MissingIndex.query('<MissingIndexes> { for $MissingIndexes in * return $MissingIndexes } </MissingIndexes>'),
MissingIndex.query('//ShowPlanXML')
from sys.dm_exec_cached_plans as cp
cross apply sys.dm_exec_query_plan (cp.plan_handle) as qp
cross apply query_plan.nodes('//MissingIndexes') as t(MissingIndex)
where
query_plan is not  null
and
MissingIndex.exist('//MissingIndex') = 1
)
,
Missing_Index_2_CTE (ExecutionPlan,Index_Desription,Usecounts)
as
(
select
ExecutionPlan,
c.query('.'),
Usecounts
from Missing_Index_CTE
cross apply Index_Desription.nodes ('//MissingIndexes') as t(c)
)
,
Missing_Index_3_CTE (DBName, SchemaName, TableName, UseCounts, Impact, Equality, Inequality, Includes, ExecutionPlan)
as
(
select
c1.value('(./MissingIndex/@Database)[1]', 'sysname')  As [DB Name],
c1.value('(./MissingIndex/@Schema)[1]', 'sysname')  As [Schema Name],
c1.value('(./MissingIndex/@Table)[1]', 'sysname')  As [Table Name],
Usecounts,
c1.value('./@Impact[1]', 'float')  As [Impact],
c1.query('./MissingIndex/ColumnGroup[@Usage="EQUALITY"]') As [EQUALITY],
c1.query('./MissingIndex/ColumnGroup[@Usage="INEQUALITY"]') As [INEQUALITY],
c1.query('./MissingIndex/ColumnGroup[@Usage="INCLUDE"]') As [INCLUDE],
ExecutionPlan
from Missing_Index_2_CTE
cross apply Index_Desription.nodes ('//MissingIndexGroup') as t1(c1)
)
,
Missing_Index_4_CTE (DBName, SchemaName, TableName, UseCounts, Impact, Equality, Inequality, Includes, ExecutionPlan)
as
(
Select
DBName,
SchemaName,
TableName,
UseCounts,
Impact,
Equality.query ('for $Column in /ColumnGroup/Column return concat ($Column/@Name, ",")').value('.', 'nvarchar(max)')  as Equality,
Inequality.query ('for $Column in /ColumnGroup/Column return concat ($Column/@Name, ",")').value('.', 'nvarchar(max)') as Inequality,
Includes.query ('for $Column in /ColumnGroup/Column return concat ($Column/@Name, ",")').value('.', 'nvarchar(max)')  as Includes,
ExecutionPlan
from Missing_Index_3_CTE
)
Select
DBName,
SchemaName,
TableName,
UseCounts,
Impact,
case when len (Equality) = 0
then Equality
else  SUBSTRING (Equality , 1, len (Equality) -1)
end Equality,
case when len (Inequality) = 0
then Inequality
else  SUBSTRING (Inequality , 1, len (Inequality) -1)
end Inequality,
case when len (Includes) = 0
then Includes
else  SUBSTRING (Includes , 1, len (Includes) -1)
end Includes,
ExecutionPlan
from Missing_Index_4_CTE
order by
DBName, SchemaName,TableName, Equality, Inequality, Includes


Viel Spass beim eigenen ausprobieren.

Maic

P.S. : Mir ist durchaus klar, das die letzte CTE nur gemacht wurde, da ich die XQuery nicht überreden konnte, das abschließende Komma innerhalb der XQuery zu unterlassen. Wer da eine bessere Idee hat, darf mir gern seine Lösung für die letzte XQuery mitteilen


   

Hardwarekonfiguration für MS SQL Server Systeme

Im Laufe meiner Tätigkeit als Berater für Datenbanklösungen werde ich regelmässig auch mit Leistungsproblemen der MS SQL Servern konfrontiert. Dabei zeigt sich häufig, das schon bei der Konzeption und Konfiguration der Hardwaresysteme grobe Fehler gemacht wurden, die erst bei Last auf der Umgebung zum Tragen kommen. Mit dieser kleinen Blogreihe möchte ich helfen, schon bei der Konzeption der eingesetzten Hardware die Eigenheiten eines MS SQL Servers zu beachten.

Ich werde in den nächsten Blogs über BIOS, CPU, Speicher und Netzwerk mich äussern.

Hier finden sie nun in Teil 1 Infos über Einstellungen im BIOS (Setup).

Hardware-Konfiguration Teil 1

Energieeinstellung im BIOS-Setup

Nahezu jeder namhafte Serverhersteller gibt für seine jeweiligen Servermodelle entsprechende Einstellungsempfehlungen zum Betrieb von MS SQL Servern für das BIOS aus. Meine Empfehlung ist es, diesen zu folgen, da die vorgeschlagenen Einstellungen mit vielen Tests in den Entwicklungslaboren der Hersteller verifiziert wurden. In der Praxis stelle ich leider fest, dass seltenst diese Empfehlungen bekannt sind.
Wenn solche Empfehlungen nicht vorliegen, kann man generell die BIOS seitigen Power Optionen so einzurichten, dass systemseitige Stromsparmaßnahmen ausgeschaltet sind und der CPU Takt nicht abgesenkt wird. Diese Power Optionen sind auch als sogenannte C-States und P-States benannt.
Grob gesagt behandeln die C-States die Ruhephasen eines Prozessors („Leerlauf“), bei denen die CPU keine Berechnungen durchführt. Dagegen steuern die P-States die Taktung der CPU. Beide States sollten auf der „0“ sein (C0 und P0), da nur hier die Maximalleistung zu erwarten ist. Speziell die unkorrekte Einstellung der P-States führt zu Leistungsreduzierung, wenn I/O Systeme am PCIe Bus sitzen. Dabei sind z.B. Flashspeicher, die als Einsteckkarten im PCIe Bus oder als M2 Bauform eingebunden sind, besonders betroffen.

Eine sehr viel tiefere Beschreibung zu diesen erst auf dem zweiten Blick sehr komplexen Thema findet man unter https://software.intel.com/en-us/articles/power-management-states-p-states-c-states-and-package-c-states#_Toc383778910.

Untersuchung, wie das RDBMS des SQL Servers Seiten für eine neue Tabelle allokiert.

Untersuchung, wie das RDBMS des SQL Servers Seiten für eine neue Tabelle allokiert.

Von einem geschätzten Kollegen wurde an mich die Frage gestellt, warum der SQL Server in einer neuen Datenbank für die erste Tabelle die Seite x (x >70) allokiert und nicht z.B. die Seite 1,2,3 usw.

Das die ersten 8 Seiten durch die speziellen Steuerseiten besetzt sind ( DB-Header, PFS,GAM,SGAM usw.) war auch ihm gekannt, allerdings war hier der exakte Ablauf gefragt.

Die Seitenzuweisung muss im laufenden Betrieb schnell durchzuführen sein. Daher erfolgt die Allokierung von Seiten durch ein Zusammenspiel von PFS, GAM, SGAM und IAM Seiten.

Dazu wird eine Datenbank auf höchst simple Art und Weise erstellt:

create database db_index

 

Zuerst mal ein Blick auf die GAM und SGAM Einträge einer neuen Datenbank direkt nach der Erstellung:

GAM (Global Allocation Map)–Einträge:

Mittels des Befehls DBCC PAGE bekommt man einen Überblick (Der voranstehende DBCC TRACEON (3604) Befehl leitet nur die Ausgabe auf das SQL SERVER Management Studio um):

DBCC TRACEON (3604)

GO

DBCC PAGE (db_index, 1, 2, 3);

Hier wird nun nur der relevante Teil angezeigt:

Dabei bedeutet
‚ALLOCATED‘                 = BIT 0 (Die Seite ist zugewiesen zur Benutzung)
‚NOT ALLOCATED‘        = BIT 1 (Die Seite kann zur Benutzung zugewiesen werden)
 

 

 

SGAM (Shared Global Allocation Map) –Einträge:

DBCC PAGE (db_index, 1, 3, 3);

Hier wird nun nur der relevante Teil angezeigt:

Dabei bedeutet
‚ALLOCATED‘                  = BIT 1 (Gemischter Block mit mindestens 1 freier Page)
‚NOT ALLOCATED‘         = BIT 0 (Vollständig besetzter gemischter Block ode rein dedizierter Block)

 

 

In eine Tabelle zusammengefasst ergibt es folgendes Bild

Seiten	GAM-BIT	SGAM-BIT
1:000 - 1:111	0	0
1:112- 1:127	0	1
1:128 – 1:135	0	0
1:136 – 1:183	0	1

 

Bei Microsoft gibt es hierzu folgende Tabelle:

Aktuelle Blockverwendung	GAM-Biteinstellung	SGAM-Biteinstellung
Frei, wird nicht verwendet	1	0
Einheitlicher Block oder vollständig belegter gemischter Block	0	0
Gemischter Block mit freien Seiten	0	1

Quelle: http://technet.microsoft.com/de-de/library/ms175195(v=sql.105).aspx

Das bedeutet, dass für die erste Tabelle (aus GAM und SGAM Sicht) Seiten ab Seite 1:112 benutzt werden können.

Leider ist das nicht die ganze Wahrheit. Zur Allokierung werden auch noch die IAM Seiten herangezogen.

Schauen wir hierzu erstmal in die PFS:

DBCC PAGE (db_index, 1, 1, 3);
 

Auch hier nur der Ausschnitt aus dem relevanten Teil ab Eintrag für Seite 1:109:

So sehen wir, dass die ersten verfügbaren Blöcke die Seiten 1:112 bis 1:119 und 1:120 bis 1:127 umfasst. Genauer gesagt die Seiten 1:119,1:120, 1:121, 1:126, 1:127.
Hierbei sind die Seiten 1:119, 1:121 und 1:127 als IAM Seiten vormarkiert .

Der Inhalt dieser Seiten mittels
DBCC Page (1,xxx,3)
xxx = Nummer der entsprechenden Seite
zeigt folgendes Ergebnis:

Nun wird die neue Tabelle erstellt und ein Datensatz importiert.

CREATE TABLE [dbo].[tbl_Kunde](

       [KundenNr] [char](400) NULL,
       [Vorname] [char](100) NULL,
       [Nachname] [char](100) NULL,
       [Strasse] [char](100) NULL,
       [PLZ] [char](100) NULL,
       [Ort] [char](100) NULL,
       [Telefon] [char](100) NULL)

 

INSERT INTO tbl_Kunde VALUES

('1001', 'Gabi', 'Schmidt', 'Spielweg 9', '40444', 'Düsseldorf', '0211/968596')

Welche Seiten hat der SQL Server gewählt :

SELECT sys.fn_PhysLocFormatter(%%physloc%%) AS Location, * FROM dbo.tbl_Kunde;

Hier fällt also die Wahl auf die Seite 1:121.

Die IAM Seite wird mittels DBCC IND aufgelistet:

dbcc ind ('db_index','tbl_Kunde',-1)

Also ist die dazugehörige IAM Seite die Seite 1:126.

Frage: Was ist auf den Seiten 1:119 und 1:120 geschehen?

Die Abfrage der Seite 1:119 mittels

DBCC PAGE (db_index, 1, 119, 1);
ergibt:

Diese Seite gehört nun zum Index der Systemtabelle ‚sysschobjs‘ (ObjectID = 34).Jede Zeile dieser Tabelle stellt ein Objekt in der Datenbank dar. Offenbar wurde diese Seite beim Erstellen der Metadaten der neuen Tabelle miterzeugt.

Die Abfrage der Seite 1:120 mittels

DBCC PAGE (db_index, 1, 120, 1);
ergibt:

Diese Seite gehört nun zum Index der Systemtabelle ‚ syscolpars‘ (ObjectID = 41). Diese Systemtabelle enthält eine Zeile für jede Spalte in einer Tabelle. Da auch diese Metadaten erweitert wurden, wurden diese hier abgelegt.

ERGO:
Die erste freie Seite war nun die Seite 1:121! Diese Seite wird nun für die erste Seite der Tabelle verwendet. Die darauffolgende nächste freie Seite 1:126 wird dann für die dazugehörige IAM Seite verwendet.

Ich danke Ihnen für ihr Interesse.