UGISS User Group Italiano SQL Server
Durante la SQL Conference 2011, parlando con alcuni di voi sono emerse domane interessanti.
In questo articolo cercheremo di rispondere ad una di esse: Vorrei simulare off-line le query che vengono eseguite su un DB in produzione senza però fare il restore del DB in ambiente di test. E’ possibile esportare le Statistiche di un database in produzione ed importarle in un database di test? Simulando quindi la densità e la distribuzione dei dati nelle tabelle in produzione?
Prima di descrivere la soluzione, pensiamo a quello che avviene dietro le quinte quando chiediamo a SQL Server di eseguire una query; chi ha seguito la mia sessione SQL204 – SQL Server Execution Plans alla SQL Conference si ricorderà le tre fasi fondamentali dell’esecuzione di una query, ovvero:
-
Query Parsing
-
Query Optimizer
-
Query Execution
Il Query Parsing verifica la sintassi del comando T-SQL, controlla che tutti gli elementi del linguaggio (SELECT, FROM, WHERE, UPDATE ecc…) siano stati scritti e specificati correttamente, l’output di questa fase è noto come Logical Operator Tree e rappresenta l’input per la fase successiva ovvero per il Query Optimizer che trasforma una query T-SQL in un piano di esecuzione.
Query Optimizer genera e valuta molteplici piani di esecuzione con l’obiettivo di sceglierne uno, quello che meglio permette di raggiungere i dati!! La scelta del miglior piano di esecuzione, e vi assicuro che è implementata in un algoritmo davvero complesso, valuta anche le statistiche per conoscere la selettività (valore di densità) degli indici definiti nelle tabelle a cui SQL Server dovrà accedere. L’output di questa fase è noto come Estimated Execution Plan e rappresenta l’input per il Query Execution che esegue la nostra query nello storage engine in accordo con il piano di esecuzione stimato.
Le Statistiche influenzano quindi il Query Optimizer nella scelta del piano di esecuzione… ed è per questo motivo che vogliamo importarle dal DB di produzione al DB di test, per far credere a SQL Server che le tabelle in test contengano effettivamente i dati.
Ipotizziamo che il database di produzione si chiami db_export_stats e che contenga, per semplicità, solo la tabella dbo.Contact, che memorizza i contatti. Il seguente frammento di codice in linguaggio T-SQL permette di eseguire il setup del database db_export_stats:
————————————————————————
— Setup DB for export statistics
————————————————————————
use [master];
go
if exists(select * from sys.databases where name = ‘db_export_stats’)
begin
alter database [db_export_stats]
set single_user with rollback immediate;
drop database [db_export_stats];
end
go
— Create database
create database [db_export_stats];
go
Dopo aver creato il database, eseguiamo il seutp e il caricamento di 1.000.000 di record nella tabella dbo.Contact utilizzando il seguente codice T-SQL:
————————————————————————
— Setup table and insert data
————————————————————————
— Change database context
use [db_export_stats];
go
— Create table dbo.Contact (drop if exists)
if object_id(‘dbo.Contact’, ‘U’) is not null
drop table dbo.Contact;
go
create table dbo.Contact
(
ContactID int identity(1, 1) not null
,FirstName varchar(128) not null
,LastName varchar(128) not null
,AddressLine varchar(40) default ‘AddressLine’
,City varchar(40) default ‘City’
,PostalCode varchar(5) default ‘PCode’
,Phone varchar(20) not null
,ModifiedDate datetime default getdate()
);
go
— Create function dbo.udf_Nums
if (object_id(‘dbo.udf_Nums’) is not null)
drop function dbo.udf_Nums;
go
create function dbo.udf_Nums(@m as bigint) returns table
as
return
with
t0 as (select n = 1 union all select n = 1),
t1 as (select n = 1 from t0 as a, t0 as b),
t2 as (select n = 1 from t1 as a, t1 as b),
t3 as (select n = 1 from t2 as a, t2 as b),
t4 as (select n = 1 from t3 as a, t3 as b),
t5 as (select n = 1 from t4 as a, t4 as b),
result as (select row_number() over (order by n) as n from t5)
select n from result where n <= @m;
go
with cte_rows as
(
select
n
from
dbo.udf_Nums(1000000)
)
insert into dbo.Contact
(
FirstName,
LastName,
Phone
)
select
‘Mario_’ + ltrim(str(n)),
‘Rossi_’ + ltrim(str(n)),
‘059/’ + ltrim(str(n))
from
cte_rows;
go
Prendiamo confidenza con i dati inseriti nella tabella dbo.Contact, la figura 1 illustra l’output che si ottiene eseguendo la query seguente:
select * from dbo.Contact;
go
Figura 1 – Contenuto della tabella dbo.Contact
Applichiamo ora la primary key alla tabella dbo.Contact basata sulla colonna ContactID, la creazione di questo constraint sulla tabella (HEAP) dbo.Contact ha come effetto la creazione dell’indice cluster sulla tabella stessa. Di seguito eseguiamo la creazione di un indice non cluster per migliorare le performance delle query che interrogano i contatti filtrandoli per la colonna Phone. Osserviamo anche l’utilizzo della stored procedure di sistema sp_createstats che permette di creare le statistiche per ogni colonna nelle tabelle utente del database corrente.
————————————————————————
— Setup constraint and statistics
————————————————————————
alter table dbo.Contact
add constraint PK_Contact_ContactID primary key (ContactID);
go
create index NCI_Contact_FirstName_LastName on dbo.Contact
(
[Phone]
)
include
(
[FirstName]
,[LastName]
);
go
exec sp_createstats;
go
Consultiamo ora le statistiche collegate alla tabella dbo.Contact, l’output è illustrato in figura 2:
@objname = ‘dbo.Contact’,
@results = ‘ALL’;
go
Figura 2 – Statistiche per la tabella dbo.Contact
Abbiamo allestito il nostro database di produzione, ipotizziamo ora ci siano diversi utenti pronti ad interrogare i dati attraverso una applicazione, uno di loro desidera selezionare i contatti filtrandoli per la colonna Phone attraverso l’operatore LIKE ed invia a SQL Server la query riportata di seguito. In figura 3 osserviamo il piano di esecuzione effettivo generato per la query; consultandolo si osserva la parallelizzazione degli operatori.
select
FirstName
,LastName
,Phone
,AddressLine
from
dbo.Contact
where
(Phone like ‘059/21%’);
go
Figura 3 – Piano di esecuzione effettivo in ambiente di produzione
La query precedente rappresenta un esempio; nel mondo reale alla nostra istanza SQL Server verranno rivolte migliaia di query al minuto. Le attività di monitoraggio e di performance tuning (che dobbiamo eseguire) vorremmo venissero fatte off-line per non stressare ulteriormente la macchina e per lavorare in sicurezza su un DB di test. Come potrei simulare, in ambiente di test, la precedente query su dbo.Contact senza disporre dei dati veri e propri? Alcuni clienti, giustamente, non hanno piacere vengano presi i backup dei DB in produzione, oppure semplicemente il backup potrebbe essere centinaia di gigabyte…
Una soluzione è rappresentata dall’esportazione delle statistiche presenti sulla tabella dbo.Contact per la successiva importazione su un DB vuoto avente lo stesso schema del database db_export_stats.
Procediamo quindi con il seutp del database db_stats_only che conterrà la tabella dbo.Contact con la stessa struttura dell’omologa tabella sul database db_export_stats, unica differenza, sul DB db_stats_only non verrà eseguito il caricamento dei dati.
————————————————————————
— Setup DB with statistics only
————————————————————————
use [master];
go
if exists(select * from sys.databases where name = ‘db_stats_only’)
begin
alter database [db_stats_only]
set single_user with rollback immediate;
drop database [db_stats_only];
end
go
— Create database
create database [db_stats_only];
go
————————————————————————
— Setup table dbo.Contact
————————————————————————
— Change database context
use [db_stats_only];
go
— Create table dbo.Contact
if object_id(‘dbo.Contact’, ‘U’) is not null
drop table dbo.Contact;
go
create table dbo.Contact
(
ContactID int identity(1, 1) not null
,FirstName varchar(128) not null
,LastName varchar(128) not null
,AddressLine varchar(40) default ‘AddressLine’
,City varchar(40) default ‘City’
,PostalCode varchar(5) default ‘PCode’
,Phone varchar(20) not null
,ModifiedDate datetime default getdate()
);
go
————————————————————————
— Setup constraint
————————————————————————
alter table dbo.Contact
add constraint PK_Contact_ContactID primary key (ContactID);
go
create index NCI_Contact_FirstName_LastName on dbo.Contact
(
[Phone]
)
include
(
[FirstName]
,[LastName]
);
go
Eseguiamo, in ambiente di test, la stessa query lanciata in produzione (piano di esecuzione in figura 3), come si può facilmente intuire il piano di esecuzione sarà diverso, il Query Optimizer, in questo caso (no record in tabella), sceglierà di eseguire una scansione dell’indice cluster senza parallelizzazione degli operatori.
Il piano di esecuzione effettivo è illustrato in figura 3.1.
select
FirstName
,LastName
,Phone
,AddressLine
from
dbo.Contact
where
(Phone like ‘059/21%’);
go
Figura 3.1 – Piano di esecuzione effettivo in ambiente di test
L’esportazione delle statistiche può essere eseguita utilizzando il wizard "Generazione guidata script di SQL Server" raggiungibile con un click destro del mouse sul database db_export_stats, accedendo al menù "Attività" e successivamente al wizard "Genera script…".
Con alcuni semplici click, dopo aver selezionato il database, si accede alla finestra illustrata in figura 5 dove si dovrà selezionare, per l’opzione "Script Statistiche" il valore "Genera script per statistiche e istogrammi".
Figura 4 – Selezione database di riferimento
Figura 5 – Selezione del valore "Genera script per statistiche e istogrammi" per l’opzione "Script Statistiche"
Procedendo con il wizard sarà possibile specificare per quali tabelle si desidera esportare le statistiche, nel nostro esempio selezioneremo la tabella dbo.Contact come illustrato in figura 6.
Figura 6 – Finestre di selezione per oggetti e tabelle (wizard)
Scegliendo di ottenere l’output del wizard in una nuova finestra di query, SQL Server produrrà uno script simile a quello riportato di seguito:
SET ANSI_PADDING OFF
GO
CREATE STATISTICS [AddressLine] ON [dbo].[Contact]([AddressLine]) WITH STATS_STREAM = 0x01000…0F0000000000
GO
CREATE STATISTICS [City] ON [dbo].[Contact]([City]) WITH STATS_STREAM = 0x01000…000000000
GO
CREATE STATISTICS [FirstName] ON [dbo].[Contact]([FirstName]) WITH STATS_STREAM = 0x010000000100…00000000
GO
CREATE STATISTICS [LastName] ON [dbo].[Contact]([LastName]) WITH STATS_STREAM = 0x010000…40420F0000000000
GO
CREATE STATISTICS [ModifiedDate] ON [dbo].[Contact]([ModifiedDate]) WITH STATS_STREAM = 0x01000000010…000000000
GO
UPDATE STATISTICS [dbo].[Contact]([NCI_Contact_FirstName_LastName]) WITH STATS_STREAM = 0x010000000200…000040420F0000000000, ROWCOUNT = 1000000, PAGECOUNT = 6025
GO
UPDATE STATISTICS [dbo].[Contact]([PK_Contact_ContactID]) WITH STATS_STREAM = 0x0100000001…0F0000000000, ROWCOUNT = 1000000, PAGECOUNT = 11061
GO
CREATE STATISTICS [PostalCode] ON [dbo].[Contact]([PostalCode]) WITH STATS_STREAM = 0x010000000…420F0000000000
GO
Interessante l’opzione non documentata STATS_STREAM:
[ STATS_STREAM = stats_stream ] [ ROWCOUNT = numeric_constant ] [ PAGECOUNT = numeric contant ]
L’opzione STATS_STREAM è implementata a partire dalla versione 2005 di SQL Server (Microsoft non garantisce la compatibilità con le versioni future) e accetta in input un valore esadecimale (troncato per semplicità nello script), eseguendo i precedenti CREATE e UPDATE STATISTICS, SQL Server sarà in grado di includere nelle statistiche (create o aggiornate), gli istogrammi che descrivono la distribuzione dei dati, il numero di righe contenute nelle tabelle ed il numero di pagine necessarie per la memorizzazione dei dati.
Dopo aver applicato le statistiche e ripulito la plan cache, rieseguiamo la query d’interrogazione; il Query Optimizer determinerà il piano di esecuzione sub-ottimale in funzione delle statistiche importate dal DB db_export_stats… deciderà quindi di parallelizzare gli operatori esattamente come avviene in produzione, lo illustra la figura 7 con i piani di esecuzione in formato grafico e XML.
DBCC FREEPROCCACHE;
go
— Query data
select
FirstName
,LastName
,Phone
,AddressLine
from
dbo.Contact
where
(Phone like ‘059/21%’);
go
Figura 7 – Piano di esecuzione effettivo (grafico e XML) in ambiente di test
Conclusioni
In questo articolo abbiamo descritto come sia possibile riprodurre, in ambiente di test, il piano di esecuzione effettivo di una query (eseguita in produzione), senza copiare svariati GB se non TB di dati dal DB in produzione al DB in test.
Pulizia DB
————————————————————————
— Cleanup DB
————————————————————————
use [master];
go
if exists(select * from sys.databases where name = ‘db_export_stats’)
begin
alter database [db_export_stats]
set single_user with rollback immediate;
drop database [db_export_stats];
end
go
if exists(select * from sys.databases where name = ‘db_stats_only’)
begin
alter database [db_stats_only]
set single_user with rollback immediate;
drop database [db_stats_only];
end
go
