Aktuell untersuche ich bei einem Kunden eine Applikation, dessen Performance optimiert werden soll. Bei der Durchsicht des Codes ist mir aufgefallen, dass die Programmierer eine einfache Notation für die Aufrufe von Prozeduren oder für die Generierung von SQL-Abfragen verwendet haben.

EXEC myproc;
GO
SELECT * FROM myTable;
GO

Leider hält sich dieser Programmierstil hartnäckig obwohl er für Microsoft SQL Server eher kontraproduktiv ist. Der folgende Artikel zeigt die Nachteile dieses Stils. Vielleicht nimmt sich ja der eine oder andere Programmierer die Beispiele zu Herzen und ändert seinen Programmierstil.

Ausführungsprozess von Microsoft SQL Server

Wenn eine Abfrage an Microsoft SQL Server gesendet wird, wird zunächst im Standardschema des Benutzers nach einem entsprechenden Objekt gesucht wird. Befindet sich das gewünschte Objekt im Standardschema des Benutzers, so wird auf dieses Objekt verwiesen. Sollte jedoch das angeforderte Objekt nicht im Standardschema liegen, sucht Microsoft SQL Server im [dbo]-Schema der Datenbank nach dem gewünschten Objekt. Sobald Microsoft SQL Server die gewünschten Objekte für die Ausführung der Abfrage gefunden hat, wird ein Ausführungsplan erzeugt. Der Ausführungsplan ist die Basis der Execution Engine!

Testumgebung

Für die nachfolgenden Demos werden zwei Benutzer in der Datenbank erzeugt. Der Benutzer [User1] verwendet als Standardschema [dbo] während [User2] ein eigenes Schema verwendet.

CREATE USER [USER1] WITHOUT LOGIN WITH DEFAULT_SCHEMA = [dbo];
CREATE USER [USER2] WITHOUT LOGIN WITH DEFAULT_SCHEMA = [User2]
GO 
CREATE SCHEMA [User2] AUTHORIZATION [USER2];
GO

Nachdem die Benutzer angelegt wurden, wird eine Testtabelle [dbo].[messages] angelegt, auf die beide Benutzer lesenden Zugriff besitzen.

SELECT TOP (1) *
INTO  dbo.messages
FROM  sys.messages
WHERE language_id = 1033;
GO

GRANT SELECT ON dbo.messages TO USER1;
GRANT SELECT ON dbo.messages TO USER2;
GO

Demonstration

Ausführung ohne vollständig qualifizierte Objektreferenz

Im ersten Beispiel führen beide Datenbankbenutzer ein SELECT aus, ohne dabei auf das Schema zu referenzieren. Allen Beispielen geht voraus, dass der Plancache vorher gelöscht wird!

EXECUTE AS USER = 'USER1';
GO
SELECT USER_NAME() AS [User], * FROM messages;
GO
REVERT;
GO

EXECUTE AS USER = 'USER2'
GO
SELECT USER_NAME() AS [User], * FROM messages;
GO
REVERT;
GO

Jeder Benutzer der Datenbank erhält das gleiche Ergebnis. Scheinbar gibt es also keine Probleme bei der Anwendung der obigen Technik. Dennoch gilt es, zu berücksichtigen, dass Microsoft SQL Server für die Ausführung einer Abfrage einen Ausführungsplan benötigt. Ein Blick in den Plancache nach der Ausführung der Abfrage durch beide Datenbankbenutzer zeigt, welche Probleme sich – im Hintergrund – ergeben!

SELECT cp.plan_handle ,
       cp.usecounts ,
       cp.size_in_bytes ,
       cp.cacheobjtype ,
       st.text
FROM   sys.dm_exec_cached_plans cp
       CROSS APPLY sys.dm_exec_sql_text(plan_handle) st
WHERE  st.dbid = DB_ID()
       AND st.text NOT LIKE '%exec_cached_plans%';

Microsoft SQL Server speichert zwei Ausführungspläne mit jeweils 40 KByte ab. In heutigen Serverlandschaften sicherlich kein größeres Problem; aber neben Speicherbedarf darf man nicht unterschätzen, dass die Kompilierung CPU-Zeit konsumiert.

Ausführung mit vollständig qualifizierter Objektreferenz

Das gleiche Beispiel wird nun mit vollständig qualifizierten Objekten durchgeführt. Hierbei adressieren beide Benutzer das Referenzobjekt mit dem Schemanamen.

EXECUTE AS USER = 'USER1';
GO
SELECT USER_NAME() AS [User], * FROM <strong>dbo</strong>.messages;
GO
REVERT;
GO

EXECUTE AS USER = 'USER2'
GO
SELECT USER_NAME() AS [User], * FROM <strong>dbo</strong>.messages;
GO
REVERT;
GO

Schaut man anschließend in den Plancache, wird man überrascht feststellen, dass nur noch ein Ausführungsplan gespeichert wurde.

Der Plancache

Viele Anwendungsentwickler wissen, dass Microsoft SQL Server vor der Ausführung einer neuen Abfrage einen Ausführungsplan generiert, der dann im Plancache abgespeichert wird. Kleinste Änderungen am SQL-Text führen dazu, dass ein weiterer Plan erstellt und abgespeichert werden muss. Im ersten Beispiel ist der Text exakt identisch für beide Benutzer und dennoch wurden zwei Ausführungspläne gespeichert. Microsoft SQL Server muss noch weitere Informationen zu einem Ausführungsplan speichern, die eine Rolle für die “Eindeutigkeit” des Ausführungsplans spielen.
Neben dem Ausführungstext speichert Microsoft SQL Server weitere Informationen im Plancache, die bestimmen, ob ein Ausführungsplan “eindeutig” identifizierbar ist ist. Diese Informationen befinden sich in den Planeigenschaften, die mit der Systemfunktion [sys].[dm_exec_plan_attributes] abgerufen werden können.  Die nachfolgende Abfrage zeigt die Planeigenschaften für beide weiter oben ausgeführten Abfragen, die ohne voll qualifizierten Objekte aufgerufen wurden.

SELECT pq.*
       ,s.name
FROM   sys.dm_exec_cached_plans cp
       CROSS APPLY sys.dm_exec_plan_attributes(plan_handle) pq
       CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
       INNER JOIN sys.schemas AS s
       ON
       (
           s.schema_id = CAST(pq.value AS INT)
           AND pq.attribute = N'user_id'
       )
WHERE  st.dbid = DB_ID()
       AND st.text LIKE '%messages%'
       AND st.text NOT LIKE '%exec_cached_plans%'
       AND pq.is_cache_key = 1;
GO[code]

Die Abbildung zeigt Unterschiede der beiden Abfragen. Das Attribut [user_id] unterscheidet sich in beiden Abfragen. Der Begriff [user_id] ist hier etwas unglücklich gewählt; handelt es sich doch eigentlich nicht um einer [user_id] sondern um eine [schema_id]. Während der Benutzer [USER2] das Standardschema [User2] verwendet, greift [USER1] auf das Schema [dbo] als Standard zu. Diese Informationen werden im Plancache abgespeichert. Da sie sich für beide Prozesse unterscheiden, muss Microsoft SQL Server zwei Ausführungspläne generieren und speichern.

Zusammenfassung

Dass die Verwendung von qualifizierten Objekten nicht nur freundlicher zu lesen ist sondern auch umständliche Suchen des SQL Servers nach dem geeigneten Objekt vermieden werden, sind nur einige Vorteile. Viel deutlicher wird jedoch der immense Vorteil durch die Wiederverwendung von Abfrageplänen, da sie nicht mehrfach im Plancache hinterlegt werden müssen. Die Abfragen können also optimiert ausgeführt werden und der Speicher von SQL Server dankt es auch noch.

Herzlichen Dank fürs Lesen!