Microsoft SQL Server Performance Guide

Allgemeine Tipps

SQL Server-Dienst-Berechtigungen

hinweis

Zur Konfiguration der Berechtigungen sind Administratorrechte erforderlich.

Um die Leistung und Stabilität des SQL-Servers zu verbessern, können zusätzliche Berechtigungen für das Dienstkonto sinnvoll sein. Dabei geht es in der Regel um den Benutzer, unter dem der SQL Server ausgeführt wird, zum Beispiel „Netzwerkdienst“ oder „Netzwerkservice“. Die folgenden Schritte zeigen, wie Sie diese Einstellungen in der lokalen Sicherheitsrichtlinie anpassen:

1. Öffnen Sie das Programm „Lokale Sicherheitsrichtlinie“.
2. Klicken Sie auf „Lokale Richtlinien“ und öffnen Sie „Zuweisen von Benutzerrechten“.
3. Suchen Sie nach der Richtlinie „Sperren von Seiten im Speicher“ und doppelklicken Sie darauf.
4. Klicken Sie auf „Benutzer oder Gruppe hinzufügen“ und geben Sie den Namen des Benutzers ein, der den SQL-Server ausführt (i.d.R. „Netzwerkdienst“/„Netzwerkservice“).
5. Klicken Sie auf „OK“ und schließen Sie das Fenster.
6. Suchen Sie nach der Richtlinie „Durchführen von Volumewartungsaufgaben“, doppelklicken Sie darauf und wiederholen Sie die Schritte 4 und 5.
7. Starten Sie den Computer neu, damit die Änderungen wirksam werden.

Mit diesen Einstellungen vermeiden Sie unter anderem unnötige Verzögerungen bei Speicherverwaltung und Dateierweiterungen. Das kann die Leistung und Stabilität des SQL Servers deutlich verbessern.

Tabellen-Indizes

Clustered Index

Für eine gute Performance sollte jede Tabelle nach Möglichkeit über einen Clustered Index verfügen, vorzugsweise über einen Primary Clustered Index.

Ein Clustered Index bestimmt die physische Reihenfolge der Datensätze in einer Tabelle. Dadurch können Abfragen beschleunigt und Fragmentierung reduziert werden. Besonders sinnvoll ist ein Primary Clustered Index auf Basis des Primärschlüssels, weil dadurch Suche, Einfügen, Aktualisieren und Löschen effizienter ausgeführt werden können.

Weniger ist mehr

Bei der Datenbankoptimierung ist eine ausgewogene Anzahl von Indizes wichtig. Indizes beschleunigen zwar Abfragen, verlangsamen aber gleichzeitig INSERT, UPDATE und DELETE, weil sie bei jeder Änderung aktualisiert werden müssen. Legen Sie deshalb nur die notwendigen Indizes an und prüfen Sie regelmäßig, ob diese noch sinnvoll sind.

Mit dem Skript Abfrage fehlender Indizes aus der Skript-Sammlung können Sie fehlende Indizes in der Datenbank identifizieren.

Wiederherstellungsmodell

Das Wiederherstellungsmodell der Datenbank sollte in vielen Fällen auf „Vollständig“ eingestellt sein. Dabei wächst das Transaction Log fortlaufend, weshalb regelmäßige Transaction-Log-Backups erforderlich sind, typischerweise alle 15 bis 30 Minuten. Zusätzlich sollte täglich ein Full-Backup erstellt werden.

tipp

Das Wiederherstellungsmodell „Vollständig“ ermöglicht die Wiederherstellung von Daten seit dem letzten Backup sowie zu beliebigen Zeitpunkten, z.B. vor einem Crash mithilfe der Transaction-Logs.

warnung

Wenn Sie kein Transaction-Log benötigen oder auf eine einfache Wiederherstellung angewiesen sind, können Sie alternativ das Wiederherstellungsmodell „Einfach“ verwenden. Dabei besteht jedoch ein erhöhtes Risiko für Datenverlust, weil nur Daten aus dem letzten Backup sicher wiederhergestellt werden können.

Voll qualifizierte Namen in Views und Prozeduren

Ein weiterer Performance-Tipp ist die vollständige Qualifizierung von Datenbankobjekten, indem Sie das Schema, zum Beispiel dbo, vor den Objektnamen setzen. Dadurch reduziert sich der Aufwand für die Namensauflösung. Außerdem vermeiden Sie Verwechslungen, wenn mehrere Objekte mit demselben Namen in unterschiedlichen Schemata existieren.

Skript-Sammlung

Hier sind einige nützliche SQL-Skripte, die Sie für verschiedene Zwecke verwenden können.

warnung

Beachten Sie, dass diese Skripte möglicherweise nicht für den direkten Einsatz in einer Produktionsumgebung geeignet sind und unerwartete Probleme verursachen können. Es wird dringend empfohlen, sie zuerst in einer geschützten Umgebung zu testen, bevor Sie sie auf Ihre Daten anwenden.

Abfrage fehlender Indizes

warnung

Das Skript liefert nur Vorschläge für die Optimierung der Datenbank. Es ist wichtig, die Ergebnisse kritisch zu bewerten und nicht blind zu akzeptieren. Die Relevanz des Indexes und die Angemessenheit der Includes hängen von verschiedenen Faktoren ab, die das Skript nicht berücksichtigen kann.

-- Fehlende Indizes
select 
 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 migs.avg_total_user_cost * migs.avg_user_impact *
 (migs.user_seeks + migs.user_scans) desc

TOP 20 Abfragen nach CPU

-- Top 20 Abfragen nach aufaddierter CPU-Last innerhalb der letzten Stunde
select last_execution_time, total_worker_time as [Total CPU
 Time], execution_count,
total_worker_time/execution_count as [Avg CPU Time],
text, qp.query_plan
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
cross apply sys.dm_exec_query_plan(qs.plan_handle) as qp
where DATEDIFF(hour, last_execution_time, getdate()) < 1 
-- Hier kann der Zeitrahmen in Stunden geändert werden
order by total_worker_time desc

TOP 50 nach I/O

-- Top 50 Abfragen nach I/O
select top 50
(qs.total_logical_reads + qs.total_logical_writes) /
qs.execution_count as average_io, substring
 (qt.text,qs.statement_start_offset/2, (case when
 qs.statement_end_offset = -1 then len(convert(nvarchar(max),
 qt.text)) * 2 else qs.statement_end_offset end -
 qs.statement_start_offset)/2) as query_text, qt.dbid,qt.objectid
 from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text
 (qs.sql_handle) as qt
order by average_io desc

Index-Defragmentierung

Um Indizes zu optimieren, kann ein Wartungs-Task wie index defrag.sql verwendet werden. Setzen Sie dafür die Variable @ViewOnly auf 0, wenn das Skript die Änderungen ausführen soll.

-- Geben Sie Ihren Datenbanknamen an
USE DatabaseName
GO
-- Variablen deklarieren
SET NOCOUNT ON
DECLARE @tablename VARCHAR(128)
DECLARE @execstr VARCHAR(255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag decimal
DECLARE @maxfrag decimal
DECLARE @IdxName varchar(128)
DECLARE @ViewOnly bit
-- Auf 1 setzen, um vorgeschlagene Aktionen anzuzeigen, auf 0 setzen, um sie auszuführen
SET @ViewOnly=1
-- Entscheiden Sie, wie viel Fragmentierung maximal erlaubt sein soll.
SET @maxfrag = 30.0
-- Cursor deklarieren.
DECLARE tables CURSOR FOR
SELECT CAST(TABLE_SCHEMA AS VARCHAR(100))
+'.'+CAST(TABLE_NAME AS VARCHAR(100))
AS Table_Name
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
-- Temporäre Tabelle erstellen.
if exists (select name from tempdb.dbo.sysobjects where name like
 '#fraglist%')
drop table #fraglist
CREATE TABLE #fraglist (
ObjectName CHAR(255),
ObjectId INT,
IndexName CHAR(255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity decimal,
BestCount INT,
ActualCount INT,
LogicalFrag decimal,
ExtentFrag decimal)
-- Cursor öffnen.
OPEN tables
-- Durch alle Tabellen in der Datenbank iterieren.
FETCH NEXT
FROM tables
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
-- Showcontig aller Indizes der Tabelle durchführen.
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tablename
END
-- Cursor schliessen und freigeben.
CLOSE tables
DEALLOCATE tables
-- Cursor für die Liste der zu defragmentierenden Indizes deklarieren.
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag, IndexName
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
-- Cursor öffnen.
OPEN indexes
-- Durch die Indizes iterieren.
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag, @IdxName
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@ViewOnly=1)
BEGIN
PRINT 'Würde ALTER INDEX ' + RTRIM(@IdxName)
 + ' ON ' + RTRIM(@tablename) + ' REORGANIZE WITH
 ( LOB_COMPACTION = ON ) ausfuehren - Aktuelle Fragmentierung ' +
 RTRIM(CONVERT(VARCHAR(15),@frag)) + '%'
END
ELSE
BEGIN
PRINT 'Fuehrt nun ALTER INDEX ' + RTRIM(@IdxName) + ' ON ' +
 RTRIM(@tablename) + ' REORGANIZE WITH ( LOB_COMPACTION = ON ) -
 Aktuelle Fragmentierung ' + RTRIM(CONVERT(VARCHAR(15),@frag)) +
 '%'
SELECT @execstr = 'ALTER INDEX ' + RTRIM(@IdxName) + ' ON ' +
 RTRIM(@tablename) + ' REORGANIZE WITH ( LOB_COMPACTION = ON )'
EXEC (@execstr)
END
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag, @IdxName
END
-- Cursor schliessen und freigeben.
CLOSE indexes
DEALLOCATE indexes
-- Temporäre Tabelle löschen.
DROP TABLE #fraglist
GO