Microsoft SQL Server 2016, usages de dbcc clonedatabase

Logo

Introduction

Une nouvelle fonctionnalité a été introduite avec SQL Server 2012 : dbcc clonedatabase. Cette nouveauté a été améliorée depuis SQL Server 2016 avec la quasi totalité des principaux objets désormais supportée.

Comment cela fonctionne et quel est le but de cette nouvelle fonctionnalité ? dbcc clonedatabase est très utile pour diagnostiquer l’impact du niveau de compatibilité (compatibility level) sur les requêtes dans un environnement de production avec des statistiques temps réel sans modifier directement et immédiatement le niveau de compatibilité de la base de données source, modification qui peut générer des effets de bord et qui doit être menée prudemment.

Clônage d’une base de données

Le clônage d’une base de données avec dbcc clonedatabase est très facile : 

dbcc clonedatabase ('AdventureWorks','CloneAdventureWorks')
go
          
Database cloning for 'AdventureWorks' has started with target as 'CloneAdventureworks'.
Database cloning for 'AdventureWorks' has finished. Cloned database is 'CloneAdventureworks'.
Database 'CloneAdventureworks' is a cloned database.
This database should be used for diagnostic purposes only and is not supported for use
in a production environment.

Que se passe-t-il avec cette nouvelle commande ?

  • Seuls les schémas et statistiques sont copiés vers la base de données clônée, les données ne sont pas copiées.
  • La base de données clônée est mise en lecture seule.

Les fichiers de bases de données sont créés avec un nombre aléatoire dans les répertoires de données et de log par défaut : 

use CloneAdventureworks
go
select physical_name,
   size
from sys.database_files
go
          
physical_name                                           size
-----------------------------------------------------   ----
/sqlpac/mssql/dbfiles/AdventureWorks_2621330649.mdf	    9216
/sqlpac/mssql/tlogfiles/AdventureWorks_1792103105.ldf   9216

Les données ne sont pas copiées, les tables sont vides mais toutes les statistiques lors du clônage ont été copiées, ainsi l’optimiseur choisira le même plan d’exécution que celui qui serait élaboré pour les tables remplies.

use CloneAdventureworks
go
exec sp_spaceused
go
database_name            database_size      unallocated space
------------------------ ------------------ ------------------
CloneAdventureworks      249.00 MB          103.00 MB

reserved           data               index_size         unused
------------------ ------------------ ------------------ ------------------
303656 KB          162304 KB          114072 KB          27280 KB
exec sp_spaceused "HumanResources.Employee"
go
name          rows       reserved           data               index_size    unused
------------- ---------- ------------------ ------------------ ------------- ------------
Employee      290        200 KB             56 KB              136 KB        8 KB
dbcc show_statistics("HumanResources.Employee", BusinessEntityID) with histogram
go
RANGE_HI_KEY RANGE_ROWS     EQ_ROWS        DISTINCT_RANGE_ROWS  AVG_RANGE_ROWS
------------ -------------- -------------- -------------------- --------------
           1            0.0            1.0                    0            1.0
           3            1.0            1.0                    1            1.0
           4            0.0            1.0                    0            1.0

Usage

Les plans d’exécution sont les mêmes avec ou sans données. Ceci peut être très pratique pour dépanner une requête avec des performances faibles, en effet l’option "Include Actual Execution Plan" avec des statistiques récentes mais sans rapatrier de données peut être utilisée dans la base de données clônée au lieu de l’option "Display Estimated Execution Plan" qui n’est pas toujours pertinente.

select BusinessEntityID,LoginID from [HumanResources].[Employee]
order by BusinessEntityID
offset 10 rows
fetch next 20 rows only
go
Demo Exec plan

Par ailleurs, les bons plans d’exécution pour les requêtes DML (insert, update, delete) peuvent être diagnostiqués en toute sécurité dans la base clônée sans modifier une quelconque donnée. Même si la base clônée est en mode lecture seule, les requêtes de mises à jour peuvent être lancées puisqu’aucune donnée n’est impactée.

Requêtes DML

Les recommandations sur les indexes manquants sont disponibles dans la base clônée pour à la fois les commandes SELECT et INSERT/UPDATE/DELETE :

delete from Sales.SalesOrderDetail where Unitprice > 10
DML queries, index recommendations

La meilleure caractéristique du clônage de base avec uniquement les statistiques consiste à pouvoir comparer des plans d’exécution lors du changement du niveau de compatibilité, très très utile pour des tests de régressions dûes au niveau de compatibilité.

La base de données clônée est en lecture seule, aussi le niveau de compatibilité ne peut pas être modifié :

alter database CloneAdventureworks set compality_level=110
go
        
Msg 3906, Level 16, State 1, Server vps650326, Line 1
Failed to update database "CloneAdventureworks" because the database is read-only.
Msg 5069, Level 16, State 1, Server vps650326, Line 1
ALTER DATABASE statement failed.

Altérer la base en mode read-write :

alter database CloneAdventureworks set read_write with no_wait
go

TRÈS IMPORTANT : désactiver l’option auto update statistics, sinon l’instantané des statistiques généré avec la commande dbcc clonedatabase pourrait être détérioré. Bien sûr, ne pas recréér ou altérer des indexes, les statistiques seront sinon également perdues.

alter database CloneAdventureworks set auto_update_statistics off with no_wait
go

À présent le niveau de compatibilité de la base clônée peut être modifié : ça ne sera pas très utile pour les statistiques I/O et CPU mais nous pouvons maintenant diagnostiquer les modifications de plans d’exécution lorsque le niveau de compatibilité est changé. Dans cet exemple, une requête s’exécutant dans une base clônée de la base de démo AdventureworksDW : 

use CloneAdventureworksDW
go
SELECT c.CalendarYear
	,b.SalesTerritoryRegion
	,FirstName + ' ' + LastName AS FullName
	,count(SalesOrderNumber) AS NumSales
	,sum(SalesAmount) AS TotalSalesAmt
	,Avg(SalesAmount) AS AvgSalesAmt
	,count(DISTINCT SalesOrderNumber) AS NumOrders
	,count(DISTINCT ResellerKey) AS NumResellers
FROM FactResellerSalesXL_PageCompressed a
INNER JOIN DimSalesTerritory b ON b.SalesTerritoryKey = a.SalesTerritoryKey
INNER JOIN DimEmployee d ON d.Employeekey = a.EmployeeKey
INNER JOIN DimDate c ON c.DateKey = a.OrderDateKey
WHERE b.SalesTerritoryKey = 3
	AND c.FullDateAlternateKey BETWEEN '1/1/2006' AND '1/1/2010'
GROUP BY b.SalesTerritoryRegion,d.EmployeeKey,d.FirstName,d.LastName,c.CalendarYear
GO
go

Avec le niveau de compatibilité 130 (SQL Server 2016) :

Exec plan compatibility_level 130

Lorsque c’est modifié au niveau de compatibilité 150 (SQL Server 2019) : 

alter database CloneAdventureworksDW set compatibility_level=150
go
Exec plan compatibility_level 150

Préparer une base de données vide sans reverse engineering

Un autre usage intéressant de dbcc clonedatabase est la possibilité de préparer une base de données ne contenant que les objets, sans données ni statistiques, pratique quand il n’existe aucun code source de recréation d’une base avec seulement les objets autrement qu’avec les options de reverse engineering de Management Studio, PowerDesigner… : 

dbcc clonedatabase('Adventureworks','CloneAdventureworks') with no_statistics
go
alter database CloneAdventureworks set read_write with no_wait
go
backup database CloneAdventureworks to disk='CloneAdventureworks.bak'
go

Puis dans l’environnement cible :

restore database Adventureworks from  disk='CloneAdventureworks.bak'
with move 'AdventureWorks_Data' to '/sqlpac/mssql/dbfiles/AdventureWorks.mdf',
move 'AdventureWorks_Log' to '/sqlpac/mssql/tlogfiles/AdventureWorksldf',
replace
go

Conclusion

dbcc clonedatabase est un compagnon utile pour diagnostiquer des problèmes dûs au niveau de compatibilité dans un environnement de production sans devoir dupliquer la base dans son intégralité vers un autre environnement, lequel peut ne pas avoir le même profil matériel (CPU, disques…).