Verwendung eines Schema-only Klons

In einem früheren Artikel haben wir Ihnen bereits erklärt, wie eine Datenbank für Optimierungszwecke geklont werden kann. Benutzt wurde dafür DBCC CLONEDATABASE. Was man nun mit der geklonten Datenbank machen kann, wollen wir uns in diesem Artikel anschauen.

Kurze Wiederholung

Bevor wir uns verschiedene Anwendungsszenarien anschauen, raten wir Ihnen dazu, diesen Artikel zu lesen. Wie bereits erwähnt, sprechen wir hier über verschiedene Wege eine Datenbank zu klonen. Dabei wollen wir uns mit dem Befehl DBCC CLONEDATABASE beschäftigen. Mithilfe dieses Befehls wird ein Schema-only Klon erstellt. Das bedeutet, dass keine Daten der DB kopiert werden, die Statistiken und somit die Execution Pläne werden dagegen gespeichert. Doch was nutzen uns diese Statistiken?

Optimierung von Queries

Weist eine Datenbank schlechte Performance auf, kann dies an schlechten Queries liegen. Jedoch ist das Troubleshooting für mögliche Ursachen mühsam und aufwendig. Damit nicht die Produktionsdatenbank beim Untersuchen der Daten beansprucht wird, kommt an dieser Stelle der Schema-only Klon zum Einsatz. Man kann sich ohne Probleme die tatsächlichen Execution Pläne anschauen und nicht nur geschätzte. Auch Änderungen an Queries und somit der Pläne können vorgenommen werden. Denn da der Klon keine Daten beinhaltet, wird das System nicht durch neue Berechnungen belastet. Stattdessen werden die bereits vorhandenen Statistiken verwendet. Zudem können auch DML Queries wie Insert, Update und Delete analysiert werden, da die Gefahr der Veränderung von tatsächlichen Daten nicht vorhanden ist. Der Nachteil ist jedoch, dass keine Veränderungen an CPU Zeit und I/O beobachtet werden können. Auch Änderungen an Indizes führt zu keinem Ergebnis, da hier eine neue Berechnung mit realen Daten erforderlich ist.

Kompatibilität prüfen

Sollte sich eine Datenbank noch auf einer älteren Version befinden, gibt es noch eine weitere Funktion des Klons. Denn hier kann ohne Probleme die Kompatibilität von anderen Versionen geprüft werden. Zum Vergleich kann eine Query auf der aktuellen Version ausgeführt werden und im Anschluss auf einer Neuen. Um die Kompatibilität zu ändern, muss sich die Datenbank jedoch in einem read-write Modus befinden. Dieser lässt sich mit folgender Zeile ändern.

ALTER DATABASE YourDBClone SET read_write WITH NO_WAIT

Es ist jedoch wichtig zu beachten, dass die Statistiken nicht automatisch aktualisiert werden. Würde man an dieser Stelle eine Query ausführen, werden diese stattdessen überschrieben. Darum sollte im Anschluss unbedingt diese Zeile ausgeführt werden.

ALTER DATABASE YourDBClone SET auto_update_statistics OFF WITH NO_WAIT

Nun können ohne Probleme die Execution Pläne in verschiedenen Versionen miteinander verglichen werden.

Erstellen einer leeren Datenbank

Eine weitere nützliche Funktion ist das Erstellen einer leeren Datenbank nach Vorlage einer bereits bestehenden. Dabei wird kein Source Code oder Reverse Engineering benötigt. Von der geklonten Datenbank wird im read-write Modus ein Backup erstellt. Dieses Backup kann in der gewünschten Zielumgebung wiederhergestellt werden. Schon hat man an dieser Stelle eine leere Datenbank mit den Objekten des Originals.

Ein kleines Beispiel

Am Ende wollen wir einmal beispielhaft durchgehen, wie man eine Query mithilfe von DBCC CLONEDATABASE anpassen kann. Dazu nehmen wir uns eine Demo Datenbank. Wie bereits in unserem vorherigen Artikel erklärt, können wir an dieser Stelle nicht die AdventureWorks2022 Datenbank nutzen. Warum das nicht geht, bzw. welcher Fehler auftritt, haben wir dort ausführlich erklärt. Darum haben wir uns mithilfe dieses Codes eine eigene Datenbank erstellt. Diese wird mit 1000000 Reihen gefüllt. Falls das zu viel sein sollte, kann das gerne im Code angepasst werden.

/* Change file locations as appropriate */

CREATE DATABASE [CustomerDB] 
 ON  PRIMARY 
 (
   NAME = N'CustomerDB', FILENAME = N'C:\Databases\CustomerDB.mdf' , 
   SIZE = 512MB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB 
 )
 LOG ON 
 ( 
   NAME = N'CustomerDB_log', FILENAME = N'C:\Databases\CustomerDB_log.ldf' , 
   SIZE = 512MB , MAXSIZE = UNLIMITED , FILEGROWTH = 65536KB 
 );
GO

ALTER DATABASE [CustomerDB] SET RECOVERY SIMPLE;
Now, create a table and add some data:

USE [CustomerDB];
CREATE TABLE [dbo].[Customers]
(
	[CustomerID] [int] NOT NULL,
	[FirstName] [nvarchar](64) NOT NULL,
	[LastName] [nvarchar](64) NOT NULL,
	[EMail] [nvarchar](320) NOT NULL,
	[Active] [bit] NOT NULL DEFAULT 1,
	[Created] [datetime] NOT NULL DEFAULT SYSDATETIME(),
	[Updated] [datetime] NULL,
  CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED ([CustomerID])
);

INSERT dbo.Customers WITH (TABLOCKX) 
  (CustomerID, FirstName, LastName, EMail, [Active])
  SELECT rn = ROW_NUMBER() OVER (ORDER BY n), fn, ln, em, a
  FROM 
  (
  /* change here, if you want less rows */
    SELECT TOP (1000000) fn, ln, em, a = MAX(a), n = MAX(NEWID())
    FROM
    (
      SELECT fn, ln, em, a, r = ROW_NUMBER() OVER (PARTITION BY em ORDER BY em)
      FROM
      (
        SELECT TOP (20000000)
          fn = LEFT(o.name,  64), 
          ln = LEFT(c.name,  64), 
          em = LEFT(o.name,  LEN(c.name)%5+1) + '.' 
             + LEFT(c.name,  LEN(o.name)%5+2) + '@' 
             + RIGHT(c.name, LEN(o.name + c.name)%12 + 1) 
             + LEFT(RTRIM(CHECKSUM(NEWID())),3) + '.com', 
          a  = CASE WHEN c.name LIKE '%y%' THEN 0 ELSE 1 END
        FROM sys.all_objects AS o CROSS JOIN sys.all_columns AS c 
        ORDER BY NEWID()
      ) AS x
    ) AS y WHERE r = 1 
    GROUP BY fn, ln, em 
    ORDER BY n
  ) AS z 
  ORDER BY rn;

Sobald wir unsere Testdaten in der Datenbank haben, wollen wir noch eine Prozedur schreiben. In dieser geben wir einfach einige Daten aus unserer Datenbank aus. Im konkreten Beispiel geben wir die Daten zu einem Customer aus, der den Nachnamen “name” hat.

CREATE OR ALTER PROCEDURE [dbo].[usp_GetCustomerInfo] (@LastName [nvarchar](64))
AS

  SELECT 
    [CustomerID],
    [FirstName],
    [LastName],
    [Email],
    CASE WHEN [Active] = 1 THEN 'Active'
      ELSE 'Inactive' END [Status]
  FROM [dbo].[Customers]
  WHERE [LastName] = @LastName;
  
EXEC [dbo].[usp_GetCustomerInfo] 'name'

Der Execution Plan in der richtigen Datenbank sieht dementsprechend so aus.

Als nächstes wollen wir uns mit dem Klonen beschäftigen. Um den Klon zu erstellen, führen wir den Befehl DBCC CLONEDATABASE aus.

DBCC CLONEDATABASE ('CustomerDB','CustomerDBClone')

Es sollte eine neue Datenbank erstellt werden, die sich in einem read-only Modus befindet. Nun wollen wir auch hier die Prozedur von oben in dem Klon ausführen. Der Execution Plan sieht nun so aus.

Im Vergleich zu dem Plan der originalen Datenbank, können hier keine tatsächlichen Werte für die Zeit angegeben werden. Dafür wurden die Gesamtkosten und die Zahl der Reihen übernommen.

Den letzten Schritt, den wir noch machen wollen, ist eine Veränderung der Prozedur zu betrachten. Dafür lassen wir uns das Attribut ‘Active’ nicht mehr ausgeben. Die Prozedur sollte nun so aussehen.

CREATE OR ALTER PROCEDURE [dbo].[usp_GetCustomerInfo] (@LastName [nvarchar](64))
AS

  SELECT 
    [CustomerID],
    [FirstName],
    [LastName],
    [Email]
  FROM [dbo].[Customers]
  WHERE [LastName] = @LastName;
  
EXEC [dbo].[usp_GetCustomerInfo] 'name'

Führen wir das ganze nun wieder auf dem Klon aus, erhalten wir folgenden Plan.

Es wurde ein neuer Execution Plan erstellt mithilfe der Daten, die wir aus dem Ersten haben. Natürlich ist ein richtiges anpassen von Queries nicht ganz so einfach, aber der Ablauf wird auch hier ein ähnlicher sein.

Fazit

Zusammenfassend lässt sich sagen, dass sich mit einem Schema-only Klon neue Möglichkeiten ergeben, Queries anzupassen, ohne hohe Kosten an Performance zu verursachen. Besonders DBCC CLONEDATABASE stellt dabei eine schnelle und einfache Lösung dar. Zudem hoffen wir, konnten wir anschaulich erklären, wie eine mögliche Benutzung des Befehls aussehen kann. Sollten sich weitere Fragen ergeben, können Sie sich gerne über unser Kontaktformular mit uns in Verbindung setzen.