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
Donnerstag, 20. Oktober 2016
Dienstag, 6. September 2016
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.
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.
Abonnieren
Posts (Atom)