Bei der täglichen Arbeit mit Microsoft SQL Server in mittelständischen und großen Unternehmen kommt es immer wieder mal vor, dass Programmcodes in die Testsysteme und Produktionssysteme implementiert werden mussten. Beim durchgeführten Code Review stößt man immer wieder auf die Verwendung einer einfache Notation für die Aufrufe von Prozeduren oder SQL-Abfragen. Insbesondere seit der Trennung von Schemata und Benutzern ist diese “Unart” nicht nur schwieriger zu bearbeiten (aus welchem Schema wird das Objekt aufgerufen?) sondern kann auch gravierende Auswirkungen auf den Plan Cache von Microsoft SQL Server haben.

Warum vollständig qualifizierte Objektverweise?

Aus Sicht des „Suchalgorithmus“ von Microsoft SQL Server nach ausführbaren Objekten ist eine vollständig qualifizierte Notation auf ein Objekt sinnvoll, da Microsoft SQL Server dadurch unmittelbar auf das richtige Schema verwiesen wird, in dem sich das referenzierte Objekt befindet. Hintergrund dafür ist, dass bei „unqualifizierten“ Objekten zunächst im Standardschema des Benutzers nach einem entsprechenden Objekt gesucht wird. Befindet sich ein Objekt im Schema [dbo], muss SQL Server zunächst im Standardschema des Benutzers suchen und – wenn das Objekt dort nicht gefunden wird – im [dbo]-Schema nach dem Objekt suchen. Diese Suchen nach referenzierten Objekten sind immer mit Zeiteinbußen verbunden.
Neben dem oben beschriebenen Effekt kommt aber noch ein anderer – nicht zu unterschätzender Effekt zum Tragen; die Speicherung und Wiederverwendung von Ausführungsplänen (Prozedurcache) kann bei vielen Benutzern mit eigenen Schemata über Gebühr beansprucht werden. Die Aufgabe des Prozedurcaches von Microsoft SQL Server ist die Speicherung von Abfrageplänen für eine weitere Verwendung, sofern die gleiche Abfrage erneut aufgerufen wird. Findet sich bereits ein Ausführungsplan im Cache, muss Microsoft SQL Server keinen neuen Plan erstellen sondern kann die Abfrage unmittelbar ausführen. Weitere Informationen zum Prozedurcache finden sich z. B. hier:
http://blogs.msdn.com/b/sqlserverfaq/archive/2011/12/12/procedure-cache.aspx
Das nachfolgende Beispiel zeigt den Zusammenhang zwischen der Benutzung von „voll qualifizierten“ Objekten und der Speicherung von Abfrageplänen.

Testumgebung

Zunächst wird eine Testdatenbank alle benötigten Objekte für die Demonstration (Tabellen / Schema / User) angelegt.

-- Erstellen einer Testdatenbank
CREATE DATABASE [demo_db];
GO

-- Anlegen von 3 Benutzern in der Datenbank demo_db!
USE demo_db;
GO

CREATE USER demo_1 WITHOUT LOGIN WITH DEFAULT_SCHEMA = dbo;
CREATE USER demo_2 WITHOUT LOGIN WITH DEFAULT_SCHEMA = dbo;
CREATE USER demo_3 WITHOUT LOGIN WITH DEFAULT_SCHEMA = demo_3;
GO

-- Anlegen eines expliziten Schemas für den Benutzer demo_3!
CREATE SCHEMA [demo_3] AUTHORIZATION demo_3;
GO

-- Anlegen einer Tabelle für den gemeinsamen Zugriff!
CREATE TABLE dbo.foo
(
    id         int          NOT NULL    IDENTITY (1, 1),
    FirstName  nvarchar(20) NOT NULL,
    LastName   nvarchar(20) NOT NULL,

    CONSTRAINT pk_foo PRIMARY KEY CLUSTERED (Id)
)
GO

-- Berechtigungen für ALLE Benutzer einrichten
GRANT SELECT ON dbo.foo TO public;
GO

-- Eintragen von Testdaten
INSERT INTO dbo.foo (FirstName, LastName)
VALUES
('Uwe','Ricken'),
('Max','Muster'),
('Michael','Schumacher'),
('Kimi','Räikkönen');

Abfragen ohne qualifizierte Objektnamen

Sind alle Vorbereitungen abgeschlossen, kann der Prozedurcache für die Datenbank „geleert werden (BITTE NICHT IN PRODUKTIONSSYSTEMEN ANWENDEN!)

DECLARE @db_id int=db_id();
DBCC FLUSHPROCINDB(@db_id);

Um Informationen zum Plancache / Prozedurcache abzufragen, werden seit SQL Server 2005 „Dynamic Management Objects (dmo)“ verwendet. Um zu überprüfen, welche Informationen der aktuellen Datenbank sich derzeit im Plancache befinden, wird die nachfolgende Abfrage verwendet.

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

Bei der Ausführung von „Ad hoc“-Abfragen (z. B. konkatenierte Abfragestrings aus einer .NET-Anwendung) wird der Text von SQL Server analysiert und mit den Einträgen im Plancache verglichen. Wird der Abfragetext nicht gefunden, muss ein neuer Abfrageplan erstellt werden, der dann im Plancache abgelegt wird. Selbst „kleinste“ Abweichungen im Text werden als “„neu”“ interpretiert!

-- Abfrage 1
SELECT * FROM foo WHERE id = 3;
GO

-- Abfrage 2 (unterschiedlich)
SELECT * FROM foo
WHERE id = 3;
GO

-- Abfrage 3 (Leerzeichen)
SELECT  * FROM foo WHERE id = 3;
GO

Alle drei Abfragen sind – scheinbar – identisch. Dennoch muss Microsoft SQL Server für jede Abfrage eine Speicherungen im Plancache vornehmen, da sich Abfrage 1 von Abfrage 2  z. B. durch den Zeilenumbruch unterscheidet während Abfrage 3 Leerzeichen zwischen dem „*“ besitzt. Selbst Kommentare erzwingen einen neuen Abfrageplan, wenn die Kommentare Bestandteile des auszuführenden Textes sind! Die Ausführung der obigen drei Abfragen wird wie folgt im Plancache gespeichert:

Ein ähnliches Verhalten kann bei der Ausführung von identischen Abfragen unter verschiedenen Benutzerkontexten beobachtet werden.

EXECUTE AS User = 'demo_1'
SELECT * FROM foo WHERE id = 3;
REVERT
GO

EXECUTE AS User = 'demo_2'
SELECT * FROM foo WHERE id = 3;
REVERT
GO

EXECUTE AS User = 'demo_3'
SELECT * FROM foo WHERE id = 3;
REVERT
GO

Der Code wechselt vor jeder Abfrage den Kontext des Benutzers und führt anschließen die Abfrage aus. Interessant ist dabei, dass alle drei Benutzer exakt das gleiche SQL‑Statement ausführen. Ein Blick in den Plancache zeigt ein “seltsames” Verhalten.

Ein Blick auf das Ergebnis überrascht, da eine IDENTISCHE Abfrage mehrmals im Plancache steht. Während die Benutzer „demo_1“ und „demo_2“ als Standardschema [dbo] verwenden, benutzt Benutzer „demo_3 sein eigenes Schema als Standard. Um mehr Informationen über die Attribute zum Plancache zu erhalten, verwendet man die Systemview sys.dm_exec_plan_attributes.

Links werden die Attribute des ersten Plans aufgezeigt; auf der rechten Seite finden sich die Informationen zum zweiten Plan. Alle Attribute (bis auf [user_id]) sind identisch. Der Eintrag [user_id] ist im Zusammenhang eher unglücklich gewählt worden. Der Eintrag repräsentiert NICHT wie bei MSDN angegeben wird, die principal_id eines Datenbankbenutzers sondern die [schema_id] aus sys.schemas. Bemerkenswert bei diesem Ergebnis ist, dass – sofern man nicht voll qualifizierte Objektnamen verwendet – immer das Standardschema des Benutzers Bestandteil des Plans ist. Für den Benutzer „demo_3“ wurde als Standardschema nicht [dbo] angegeben.

Abfragen mit qualifizierten Objektnamen

Nachdem die Ergebnisse des ersten Tests bekannt sind, wird der Prozedurcache für die Datenbank wieder geleert und die Abfrage, die von allen Benutzern ausgeführt werden soll, geringfügig geändert; es wird nicht nur der Name der Relation angegeben sondern durch die Angabe des Schemas wird das Objekt „qualifiziert“.

—Löschen des Prozedurcaches
DECLARE@db_id int=db_id();
DBCC FLUSHPROCINDB(@db_id);

EXECUTE AS User = 'demo_1';
SELECT * FROM dbo.foo WHERE id = 3 ;
REVERT;
GO

EXECUTE AS User = 'demo_2';
SELECT * FROM dbo.foo WHERE id = 3 ;
REVERT;
GO

EXECUTE AS User = 'demo_3';
SELECT * FROM dbo.foo WHERE id = 3;
REVERT;
GO

Die Analyse des Plancaches zeigt, dass für alle drei Abfragen der gleiche Abfrageplan verwendet worden ist.

Grund für dieses Verhalten ist, wie schon im vorherigen Beispiel gezeigt, dass alle Planattribute identisch sind. Durch die explizite Angabe des Schemas, in dem sich das Objekt befindet, kann der Abfrageplan für alle drei Benutzer verwendet werden!

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. Besonders hervorzuheben bleibt jedoch der immense Vorteil bei die Wiederverwendung von Abfrageplänen, da sie nicht mehrfach im Plancache hinterlegt werden müssen. Die Abfragen können optimiert ausgeführt werden und der Speicher von SQL Server dankt es auch noch.

Verweise

DBCC DROPCLEANBUFFERS	http://msdn.microsoft.com/de-de/library/ms187762.aspx
DBCC FREEPROCCACHE	http://msdn.microsoft.com/de-de/library/ms174283.aspx
DBCC FREESESSIONCACHE	http://msdn.microsoft.com/de-de/library/ms187781.aspx
DBCC FREESYSTEMCACHE	http://msdn.microsoft.com/de-de/library/ms178529.aspx

Herzlichen Dank fürs Lesen!