Hinweis

Der nachfolgende Artikel setzt ein grundlegendes Verständnis für die Speicherung von Abfrageplänen in SQL Server voraus. Dieser Artikel beleuchtet lediglich die Auswirkungen von Abfragen, die nicht den „full qualified“  Regeln entsprechen.

Einleitung

Bei meiner Arbeit mit SQL Server in mittelständischen und großen Unternehmen kam es immer wieder vor, dass Programmcodes von uns in die Testsysteme und Produktionssysteme implementiert werden mussten. Dabei war ein zentraler Aufgabenbereich die Überprüfung der Codes auf Fehler / Sicherheitsproblem.

Mir ist sehr häufig aufgefallen, dass die Programmierer (ca. 70%) eine einfache Notation für die Aufrufe von Prozeduren oder für die Generierung von SQL-Abfragen verwendet haben.

Beispiel:

EXEC usp_MeineProzedur;
SELECT * FROM MeineTabelle;

In meinen Fachvorträgen und Schulungen habe ich immer aus Sicht des „Suchalgorithmus“ von SQL Server nach ausführbaren Objekten darauf verwiesen, dass eine vollständig qualifizierte Notation ein Objekt einfacher qualifizierbar macht und somit das Objekt schneller gefunden und ausgeführt werden kann. Hintergrund dafür ist, dass bei „unqualifizierten“ Objekten zunächst im Standardschema des Benutzers nach einem entsprechenden Objekt gesucht wird.

Befindet sich also 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. Diese Suchen kosten natürlich Zeit; das war für mich „der“ Aspekt für die Verwendung von „voll qualifizierten“ Objekten.

Neben dem oben beschriebenen Effekt kommt aber noch ein anderer – wesentlich wichtigerer – Effekt zum Tragen; die Speicherung und Wiederverwendung von Abfrageplänen / Prozedurcache. Hauptaufgabe des Prozedurcaches von SQL Server ist die Speicherung von Abfrageplänen für die weitere Verwendung. Dadurch wird die Zeit für die Kompilierung signifikant reduziert und die Performance deutlich verbessert. 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 soll den Zusammenhang zwischen der Benutzung von „voll qualifizierten“ Objekten und der Speicherung von Abfrageplänen verdeutlichen.

Testdatenbank und ihre Objekte

Zunächst legen wir eine Testdatenbank an und erstellen darin alle benötigten Objekte für die Demonstration (Tabellen / Schema / User)

IF EXISTS(SELECT * FROM sys.databases WHERE name = 'UR_TEST')
    DROP DATABASE [UR_TEST]
    GO

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

Für die Demonstration werden nun drei User in der Datenbank angelegt, die später eine identische Abfrage ausführen sollen. Besondere Beachtung verdient im nachfolgenden Script der User „demo_3“, dessen Standardschema von dem der beiden vorher angelegten Benutzer abweicht!

USE UR_Test
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
CREATE SCHEMA [demo_3] AUTHORIZATION demo_3;
GO

Nachdem die User der Datenbank angelegt wurden, wird nun noch eine Tabelle erstellt und mit Daten gefüllt, die später von den drei zuvor angelegten Benutzern abgefragt werden soll.

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 TOpublic;
GO

INSERT INTO dbo.foo
VALUES
('Uwe','Ricken'),
('Max','Muster'),
('Michael','Schumacher'),
('Kimi','Räikkönen')

Ausführung von Abfragen ohne qualifizierte Objektnamen

Nachdem alle Vorbereitungen getroffen wurden, wird der Prozedurcache für die Datenbank „auf 0“ zurückgesetzt (BITTE NICHT IN PRODUKTIONSSYSTEMEN ANWENDEN!)

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

Weiterführende Informationen zum Leeren von SQL Server Plancaches finden sich hier:

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

Um Informationen zum Plancache / Prozedurcache abzufragen, werden seit SQL Server 2005 „Dynamic Management Views“ und „Functions“ verwendet. Um zu überprüfen, was sich derzeit im Plancache für die aktuelle Datenbank befindet, 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 cp CROSSAPPLYsys.dm_exec_sql_text(plan_handle) st
WHERE  st.dbid=DB_ID()ANDst.text NOT LIKE '%exec_cached_plans%'

Die WHERE-Klausel schränkt das Ergebnis auf die aktuelle Datenbank sowie alle Abfragen – außer die ausgeführte – ein. Es sollten sich Informationen für die aktuelle Datenbank abrufen lassen, da der Plancache ja zuvor geleert wurde.

Bevor es nun an den Test geht, muss noch etwas zur Speicherung von Abfrageplänen im Plancache gesagt werden, was sehr wichtig für das Verständnis des Ergebnisses ist. Bei der Ausführung von „Ad hoc“-Abfragen (z. B. selbst 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. Hierbei ist es besonders wichtig, zu wissen, dass auch „kleinste“ Abweichungen im Text als „neu“ interpretiert werden. Das nachfolgende Beispiel soll das verdeutlichen:

a)  SELECT * FROM foo WHERE id = 3
b)  SELECT * FROM foo
   WHERE id = 3
c) SELECT   * FROM foo WHERE id = 3
d)  – Das ist nur Deko
   SELECT * FROM foo WHERE id = 3

Obwohl offensichtlich alle drei Abfragen identisch sind, muss SQL Server jeweilige Speicherungen im Plancache vornehmen, da sich a von b z. B. durch den Zeilenumbruch unterscheidet während c Leerzeichen zwischen dem „*“ besitzt. Ganz besonders wichtig ist auch der Hinweis, dass selbst Kommentare einen neuen Abfrageplan erzwingen! Die Ausführung der obigen vier Abfragen wird wie folgt im Plancache gespeichert:

Wichtig: Bei den eigenen Tests bitte die drei Abfragen immer EINZELN ausführen oder ein GO zwischen die Anweisungen setzen, da bei Ausführung aller drei Statements ansonsten nur EIN Abfrageplan gespeichert wird! Das obige Wissen vorausgesetzt können wir uns nun dem eigentlichen Thema widmen, der Verwendung von voll qualifizierten Objekten und dessen Auswirkung auf den Plancache. Dazu wird eine IDENTISCHE Abfrage zunächst ohne die Verwendung von voll qualifizierten Objekten unter dem Kontext aller drei Benutzer der Datenbank ausgeführt (Bitte auch hier darauf achten, dass jede Zeile separat ausgeführt werden muss!)

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

EXECUTE AS User = 'demo_2' 

SELECT * FROM foo WHERE id = 3 

REVERT

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

Der obige Code wechselt zunächst den Kontext und führt anschließen die Abfrage unter dem Kontext des entsprechenden Benutzers aus. Anschließend wird der Kontext zum nächsten User gewechselt. Interessant hierbei ist zunächst, dass alle drei Benutzer exakt das gleiche SQL‑Statement ausführen. Somit sollte – gemäß der weiter oben ausgeführten Behauptung – auch nur EIN Abfrageplan für diese Abfrage vorhanden sein. Schaut man jedoch in den Plancache, wird man überrascht sein.

Schaut man sich das Ergebnis an, wird man überrascht sein, daß die IDENTISCHE Abfrage dennoch mehrmals im Plancache steht. Wie kann das sein? Wie natürlich schon aus dem Thema ersichtlich muß die Ursache in der Qualifizierung der Objekte liegen. Tatsächlich müssen sich für ein besseres Verständnis des Ergebnisses noch einmal die Benutzer der Datenbank näher angeschaut werden müssen. Während die Benutzer „demo_1“ und „demo_2“ als Standardschema [dbo] verwenden, verwendet der Benutzer „demo_3“ ein anderes Standardschema. Berücksichtigt man die Rolle des Standardschemas in Verbindung mit der ausgeführten Abfrage, wird schnell klar, dass es Unterschiede im Plancache geben muß, die nicht sofort offensichtlich sind. Um mehr Informationen über die Attribute zum Plancache zu erhalten, verwendet man die DMV sys.dm_exec_plan_attributes (http://msdn.microsoft.com/en-us/library/ms189472.aspx).
 

 Auf der linken Seite werden die Attribte des ersten Plans aufgezeigt; auf der rechten Seite finden sich die Informationen zum zweiten Plan. Offensichtlich sind alle Attribute (bis auf [user_id]) identisch. Leider ist der Eintrag [user_id] hier mißverständlich. Dieser 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, daß – 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. Schaut man sich die Einträge in sys.schemas etwas genauer an, wird man den Zusammenhang schnell erkennen können. 

Ausführung von 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“.

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

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() ANDst.text NOT LIKE '%exec_cached_plans%'

Analysiert man das Ergebnis des Plancaches, wird man feststellen, dass dieses Mal für alle drei Abfragen der gleiche Abfrageplan verwendet worden ist.

Ursächlich hierfür ist, wie schon im vorherigen Beispiel gezeigt, dass die 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!

Fazit

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.

Verweise

Eine sehr gute Demonstration für die oben gezeigten Zusammenhänge und einen viel tieferen Einblick in die Arbeit des Plancaches gibt ein Video von Bob Beauchemin.  http://technet.microsoft.com/en-us/sqlserver/gg545010.aspx