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, müssen Sie einige Einstellungen in der lokalen Sicherheitsrichtlinie des Computers vornehmen, auf dem der SQL-Server ausgeführt wird. Diese Einstellungen ermöglichen es dem Benutzer, der den SQL-Server ausführt (i.d.R. „Netzwerkdienst“/„Netzwerkservice“), bestimmte Rechte zu erhalten, die normalerweise nur Administratoren haben. Die folgenden Schritte zeigen, wie Sie diese Einstellungen ändern können:

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 können Sie verhindern, dass der SQL-Server Speicherplatz freigeben muss, wenn er mehr Speicher benötigt, und dass er lange warten muss, wenn er Dateien erstellt oder erweitert. Dies kann die Leistung und Stabilität des SQL-Servers erheblich verbessern.

Tabellen-Indizes

Clustered Index

Für optimale Performance wird empfohlen, dass jede Tabelle über einen Clustered Index verfügt, vorzugsweise einen Primary Clustered Index.

Ein Clustered Index ist eine spezielle Art von Index, der die Reihenfolge der Datensätze in einer Tabelle bestimmt. Er legt fest, wie die Daten physisch auf der Festplatte gespeichert werden. Ein Clustered Index ist wichtig für die Leistung einer Datenbank, da er die Abfragegeschwindigkeit erhöht und die Fragmentierung reduziert. Jede Tabelle sollte einen Clustered Index haben, vorzugsweise einen Primary Clustered Index. Ein Primary Clustered Index ist ein Clustered Index, der auf dem Primärschlüssel der Tabelle basiert. Der Primärschlüssel ist eine Spalte oder eine Kombination von Spalten, die jeden Datensatz in der Tabelle eindeutig identifizieren. Ein Primary Clustered Index garantiert, dass die Daten in der Tabelle nach dem Primärschlüssel sortiert sind, was die Suche, das Einfügen, das Aktualisieren und das Löschen von Daten erleichtert.

Weniger ist mehr

Bei der Datenbankoptimierung ist es ratsam, eine ausgewogene Anzahl von Indizes zu verwenden. Indizes beschleunigen zwar Abfragen, verlangsamen aber auch Datenänderungen. Jeder Index muss bei jedem INSERT, UPDATE oder DELETE aktualisiert werden, was zusätzliche Zeit und Ressourcen in Anspruch nimmt. Es ist daher empfehlenswert nur die notwendigen Indizes anzulegen und regelmäßig zu überprüfen, ob sie noch effizient sind.

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

Wiederherstellungsmodell

Das standardmäßige Wiederherstellungsmodell für die Datenbank sollte auf „Vollständig“ eingestellt sein, was bedeutet, dass das Transaction-Log stetig wächst. Es wird dringend empfohlen, regelmäßig Transaction-Log-Backups durchzuführen, in der Regel alle 15 bis 30 Minuten, um das kontinuierliche Wachstum des Transaction-Logs zu verhindern und täglich ein Full-Backup zu erstellen.

tipp

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

warnung

Wenn Sie jedoch das Transaction-Log nicht benötigen oder aus bestimmten Gründen auf eine einfache Wiederherstellung angewiesen sind, können Sie alternativ das Wiederherstellungsmodell auf „Einfach“ umstellen. Bitte beachten Sie, dass dies mit dem Risiko des Datenverlusts verbunden ist, da nur Daten, die im letzten Backup enthalten sind, sicher wiederhergestellt werden können.

Voll qualifizierten Namen in Views & Prozeduren

Ein weiterer Performance-Tipp für SQL Server ist, die Datenbankobjekte vollständig zu qualifizieren, indem man den Besitzer (z.B. dbo) vor den Tabellennamen setzt. Dies vermeidet, dass der SQL Server für jedes Objekt eine Namensauflösung durchführen muss, was die Ausführungszeit verlängern kann. Außerdem kann es zu unerwarteten Ergebnissen führen, wenn mehrere Objekte mit dem gleichen Namen in verschiedenen Schemata existieren. Durch die Verwendung von voll qualifizierten Namen wird sichergestellt, dass immer das richtige Objekt adressiert wird.

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 die Indexe zu optimieren, sollte ein Wartungs-Task (index defrag.sql) durchgeführt werden, der das Skript (Variable >>@ViewOnly<< auf 0 setzen zum Starten) verwendet:

-- 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
 proposed actions:
SET @ViewOnly=1
-- Entscheiden Sie, wie viel Fragmentierung maximal erlaub 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