MS SQL Server

MS SQL Server
Sperren, Transaktionen, Statistiken
• sp_lock
• sys.cm_tran_locks
• sys.dm_tran_active_transactions
• Identifiziere Waits und Bottlenecks
• sys.dm_os_wait_stats
• DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR) - Zähler
zurücksetzen
• Statistiken für I/O für Dateien und Logdateien
• sys.dm_io_virtual_file_stats(database_id, file_id) –
für database_id und file_id, NULL = alle
Query Governor and DBCC LOG
• SET QUERY_GOVERNOR_COST_LIMIT
• Anfrageoptimierer schätzt die Laufzeit der Anfrage (in Sekunden); Wenn die
Laufzeit größer ist als dieses Limit, dann wird die Anfrage nicht ausgeführt
• Der Wert 0 erlaubt alle Anfragen ausgeführt zu werden
• DBCC LOG (<databasename>,<output id>)
• Gibt aus Informationen über die Transaktionen Logs
• Output id: 0-4 gibt den Level von Details an
Isolationsstufen
• READ UNCOMMITTED: keine Sperre beim Lesen
• READ COMMITED: behaltet die Sperre nur für die Ausführung dieser
Anweisung (dafault)
• erlaubt keine Dirty-Reads mehr
• REPEATABLE READ: sperrt die Daten bei Lesevorgängen und behaltet diese
bis am Ende der Transaktion
• erlaubt keine unrepeatable Reads mehr
• SERIALIZABLE: behaltet die während der ganzen Transaktion
• keine Anomalien mehr
• SNAPSHOT: man arbeitet auf Daten Snapshots
Isolationsstufen
• Je geringer die Isolation der Transaktionen, desto besser ist die
Parallelität
→ aber dann können Inkonsistenzen in den Daten vorkommen
• SQL Syntax:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED | READ
UNCOMMITTED | ...
Multiversioning
• In einem Multiversion DBMS erstellt jede Schreiboperation auf das
Objekt X eine neu Kopie/Version von X
• Für jede Leseoperation, muss das DBMS eine Version des Objektes X
auswählen und diese lesen
• Da die Schreiboperationen keine Werte überschreiben und die
Leseoperationen jede Version lesen können, ist das DBMS mehr
flexible bei dem Bestimmen der Reihenfolge der Lese- und
Schreiboperationen
Zeilenversionsverwaltung
(Row-Level Versioning)
• Wurde in SQL Serevr 2005 eingeführt
• Nützlich wenn committed Daten gebraucht werden, aber nicht
unbedingt die letzte Version
• Read Committed Snapshot Isolation & Full Snapshot Isolation –
Leseoperationen blockieren keine Transaktionen, diese lessen die
letzte committed Version
• Alle ältere Versionen werden in der Datenbank tempdb gespeichert
• Man kann einen Snapshot der Datenbank von den älteren Versionen
rekonstruierten
Read Committed Snapshot Isolation
ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON
• Alle Operationen sehen Tupeln die committed sind wenn die SQL
Anweisung anfängt ⇒
• Snapshot von Daten bei Anweisung Level
• Wenn die Datenbank für READ_COMMITTED_SNAPSHOT aktiviert ist,
verwenden alle Abfragen, die unter der Isolationsstufe READ COMMITTED
ausgeführt werden, Zeilenversionsverwaltung
Full Snapshot Isolation
ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON
• Alle Operationen sehen committed Tupeln wenn die Ausführung der
Transaktion anfängt ⇒
• Snapshot von Daten bei Transaktions Level
• Eine Transaktion, die unter Snapshot Isolation Level
(Momentaufnahmeisolation) ausgeführt wird, kann auf Tabellen in der
Datenbank zugreifen, die für die Snapshotfunktion aktiviert wurden
Zeilenversionsverwaltung
• Einen Logeintrag enthält den TSN (transaction sequence number)
• Alle Versionen werden in einer verketteten Liste gespeichert:
Zeilenversionsverwaltung
• Vorteile:
• Die Nebenläufigkeit wird erhöht
• Die Effizienz der Triggern und Indexerstellung wird verbessert
• Nachteile:
• Zusätzliche Verwaltung Anforderungen um die Verwendung von tempdb zu
überwachen
• Langsamere Update-Operationen
• Die Leseoperationen können langsamer sein da diese einen Wert in der
verketteten Liste suchen müssen
• Löst den Konflikt zwischen Lese- und Schreioperationen, aber gleichzeitige
Schreiboperationen werden trotzdem nicht erlaubt
PIVOT / UNPIVOT
• Verwenden Sie die relationalen Operatoren PIVOT und UNPIVOT, um
einen Tabellenwertausdruck in eine andere Tabelle zu ändern
• Mit dem PIVOT-Operator wird ein Tabellenwertausdruck umgesetzt,
indem die eindeutigen Werte einer Spalte des Ausdrucks in mehrere
Spalten der Ausgabe versetzt werden und dabei gegebenenfalls
Aggregationen für verbliebene Spaltenwerte erfolgen, die in der
endgültigen Ausgabe erwünscht sind
• Der UNPIVOT-Operator führt den umgekehrten Vorgang aus, d. h. er
setzt Spalten eines Tabellenwertausdrucks in Spaltenwerte zurück
PIVOT
SELECT <non-pivoted column>,
[first pivoted column] AS <column name>,
[second pivoted column] AS <column name>,
...
[last pivoted column] AS <column name>
FROM
(<SELECT query that produces the data>) AS <source query>
PIVOT
(
<aggregation function>(<column being aggregated>)
FOR
[<column that contains values that become column headers>]
IN ( [first pivoted column], [second pivoted column], ... [last pivoted
column])
) AS <alias for the pivot table>
<optional ORDER BY clause>;
PIVOT - Beispiel
USE AdventureWorks2008R2;
GO
SELECT DaystoManufacture, AVG(StandardCost) AS
AverageCost
FROM Production.Product
GROUP BY DaysToManufacture
DaystoManufacture
AverageCost
0
5.0885
1
223.88
2
359.1082
4
949.4105
PIVOT – Beispiel
• Dasselbe Ergebnis pivotiert angezeigt:
SELECT ‘AverageCost’ AS Cost_Sorted_By_Production_Days,
[0],[1],[2],[3],[4]
FROM
(SELECT DaystoManufacture, StandardCost
FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0],[1],[2],[3],[4])
) AS PivotTable;
PIVOT – Beispiel
Cost_Sorted_By_Production_Days
0
1
2
3
4
AverageCost
5.0885
223.38
359.1082
NULL
949.4105
• Mehrere Beispiele: https://technet.microsoft.com/de-de/library/ms177410(v=sql.105).aspx
Tools zum Analysieren der
Anfrageeffizienz/Query Performance
• Graphical Execution Plan
• STATISTICS IO: scan count, logical reads, physical reads, read ahead
reads
• STATISTICS TIME: duration and net CPU time
• SHOWPLAN_TEXT: estimated plan
• SHOWPLAN_ALL: detailed estimated plan
• STATISTICS PROFILE: detailed actual plan
• SET STATISTICS XML: detailed actual performance finfo in XML format
• SET SHOWPLAN_XML: detailed estimated performance info in XML format
(2005 only)
Graphical execution plan
Graphical execution plan
SELECT c.CustomerID, SUM(LineTotal)
FROM Sales.SalesOrderDetailod
JOIN Sales.SalesOrderHeaderoh ON
od.SalesOrderID=oh.SalesOrderID
JOIN Sales.Customerc ON
oh.CustomerID=c.CustomerID
GROUP BY c.CustomerID
Graphical execution plan
Graphical execution plan
Graphical execution plan
SELECT oh.CustomerID, SUM(LineTotal)
FROM Sales.SalesOrderDetailod
JOIN Sales.SalesOrderHeaderoh ON
od.SalesOrderID=oh.SalesOrderID
GROUP BY oh.CustomerID
Graphical execution plan
Graphical execution plan
CREATE INDEX IDX_OrderDetail_OrderID_TotalLine
ON Sales.SalesOrderDetail(SalesOrderID)
INCLUDE (LineTotal)
STATISTICS IO und STATISTICS TIME
STATISTICS IO und STATISTICS TIME
• DBCC DROPCLEANBUFFERS –clears SQL Server data
• DBCC FREEPROCCACHE –clears procedure cache
• CPU time –amount of CPU resources used to execute the query
• Elapsed time –how long the query took to execute
• Physical reads --number of pages read from disk
• Read-ahead reads -number of pages placed in cache for the query
• Scan count --the number of times that tables have been accessed
• Logical reads -number of pages read from the data cache
STATISTICS IO und STATISTICS TIME
STATISTICS IO und STATISTICS TIME
USE [AdventureWorks] GO
CREATE NONCLUSTERED INDEX [IDX_firstname] ON
[Person].[Contact]
(
[FirstName] ASC
)
GO
STATISTICS IO und STATISTICS TIME
Gespeicherte Prozeduren - Optimierungstipps
• SET NOCOUNT ON
• die Anzahl der betroffenen Zeilen wird nicht als Teil des Resultsets
zurückgegeben
• Reduces Network Traffic
• Benutze Schema Name mit Objekt Name:
• Es hilft den Ausführungsplan schneller zu finden
SELECT * FROM dbo.MyTable
EXEC dbo.StoredProcedure
Gespeicherte Prozeduren - Optimierungstipps
• Benutze den sp_ Prefix nicht
• MS SQL Server sucht erstmal in dem master Datenbank und erst dann in der
aktuellen Datenbank
• Benutze UNION um „OR“ Operationen zu implementieren
• Vermeide Joins zwischen Spalten von unterschiedlichen Typen
• Index wird nicht benutzt für eine konvertierte Spalte
Gespeicherte Prozeduren - Optimierungstipps
• sp_executesql vs exec
• Der Ausführungsplan für eine dynamische Abfrage kann nur dann wieder
benutzt werden wenn die Anfragen konsekutiv sind und genau gleich
• wenn dynamisches SQL regelmäßig verwendet wird, ist sp_executesql die
bessere Wahl, da der Abfrageplan/ Ausführungsplan wiederverwendet werden
kann
exec(‘Select * from Categories where ID = 1’)
exec(‘Select * from Categories where ID = 2’)
EXECUTE sp_executesql N‘Select * from Categories
where ID = @ID’, N‘@ID int’,@ID=1;
Gespeicherte Prozeduren - Optimierungstipps
• Verwende nicht COUNT() in einer Abfrage um die Existenz zu
überprüfen
• Verwende IF EXISTS (SELECT 1 FROM ...) anstatt
• Verringert die Bearbeitungszeit und die Netzwerkübertragung
Gespeicherte Prozeduren - Optimierungstipps
• OPTIMIZE FOR / RECOMPILE query hints
ALTER PROCEDURE test (@pidint)
AS
SELECT * FROM Sales.SalesOrderDetail
WHERE ProductID= @pid
OPTION (OPTIMIZE FOR (@pid= 870))
ALTER PROCEDURE test (@pidint)
AS
SELECT * FROM Sales.SalesOrderDetail
WHERE ProductID= @pid
OPTION (RECOMPILE)
Gespeicherte Prozeduren - Optimierungstipps
• Das erste Mal, das eine Prozedur ausgeführt wird, wird ein
Ausführungsplan berechnet und in einem Cache gespeichert
• Der Ausführungsplan wird dann wieder benutzt
• Vielleicht ist dieser Ausführungsplan nicht effizient für andere
Parameterwerte → RECOMPILE
• sys.dm_exec_query_stats – enthält Statistiken über die
Effizienz der cached Ausführungspläne
Gespeicherte Prozeduren - Optimierungstipps
• OPTIMIZE FOR UKNOWN
• Der Ausführungsplan wird allgemein optimisiert und nicht für die ersten
Parameterwerte
ALTER PROCEDURE test (@pidint)
AS
SELECT * FROM Sales.SalesOrderDetail
WHERE ProductID= @pid
OPTION (OPTIMIZE FOR UNKNOWN)
Gespeicherte Prozeduren - Optimierungstipps
• UNION Tipps:
• MERGE UNION vs
• HASH UNION vs
• CONCAT UNION
SELECT...
UNION
SELECT ...
OPTION( MERGE UNION )
Gespeicherte Prozeduren - Optimierungstipps
• Join Tipps:
• LOOP JOIN vs
• MERGE JOIN vs
• HASH JOIN
SELECT * FROM Sales.CustomerAS c
INNER JOIN Sales.vStoreWithAddressesAS sa
ON c.CustomerID= sa.BusinessEntityID
WHERE TerritoryID= 5
OPTION (MERGE JOIN);
GO
Gespeicherte Prozeduren - Optimierungstipps
• Join Tipps:
• FAST n – DBMs versucht die ersten n Tupeln so schnell wie möglich
zurückzugeben
SELECT * FROM Sales.CustomerAS c
INNER JOIN Sales.vStoreWithAddressesAS sa
ON c.CustomerID= sa.BusinessEntityID
WHERE TerritoryID= 5
OPTION (FAST 10);
GO
Gespeicherte Prozeduren - Optimierungstipps
• Join Tipps:
• FORCE ORDER – zwinge den Optimierer genau die angegebene Reihenfolge
der Joins zu behalten
SELECT * FROM Table1
INNER JOIN Table2 ON Table1.a = Table2.b
INNER JOIN Table3 ON Table2.c = Table3.d
INNER JOIN Table4 ON Table3.e = Table4.f
OPTION (FORCE ORDER);
Gespeicherte Prozeduren - Optimierungstipps
• Mehr über Hints für die Berechnung des Ausführungsplans:
• https://www.simple-talk.com/sql/performance/controlling-execution-planswith-hints/
Temporäre Tabellen
• Nützlich wenn:
• Man einen Zwischenergebnis hat, den man mehrmals braucht
• Man einen temporären Speicherplatz für Daten braucht, z.B. bis eine
Prozedur zu Ende läuft
• Man mit große Datenmengen arbeitet, wo die Effizienz wichtig ist
• Aber, ansonsten ineffizient!
SQL Server Options