SQL Server Tips & Tricks

SQL Server 2014 : Updatable Clustered Columnstore Index

2013-12-05T23:00:00.000+01:00

Tra le svariate novità che introdurrà Sql Server 2014 vi sono anche i Clustered ColumnStored Index.
Sono uno strumento molto potente qualora si abbia la necessita di applicare aggregazioni e raggruppamenti su tabelle dei fatti molto grosse.
I Columnstore Index introdotti da SQL Server 2012 (ne ho parlato in un vecchio post) impedivano di aggiornare la tabella su cui erano stati creati mentre i Clustered ColumnStored Index non hanno più questa limitazione.
Per utilizzare i nuovi Clustered Columnstore Index è necessario che l'intera tabella sia convertita da Rowstore (Heap o Clustered che sia) a Columnstore.
Per eseguire questa operazione dobbiamo rimuovere tutti gli indici dalla tabella ed eseguire il comando :
Create Clustered Columnstore Index IdxName On MyTable.
Di fatto la tabella stessa è il Clustered ColumnStore Index.

La modalità di salvataggio dei dati in ColumnStore mode prevede che ogni colonna della tabella sia salvata in modalità compressa in una struttura fisicamente separata dalle altre colonne della tabella stessa.
Questa struttura fisica da un enorme vantaggio a quelle operazioni di aggregazione e raggruppamento su tabelle dei fatti di grosse dimensioni; separando fisicamente le colonne tra loro evito letture inutili che aumenterebbero la quantità di I/O necessario per reperire tutti i dati. Inoltre i dati stessi di ogni colonna sono salvati in un formato compresso. Ciò significa che anche in termini di allocazione di spazio su disco ottengo notevoli benefici(anche in termini di performance visto che riduco ulteriormente l'I/O necessario).

Vediamo un pò di terminologia:

Rowgroup

Gruppo di righe salvate in modalità compressa e in formato ColumnStore.

Column Segment

Gruppo di valori di una sola colonna salvati in modo compresso e contenuti nella medesima struttura.
Ogni colonna è salvata in strutture separate dalle altre e tutte sono suddivise in segmenti.
Ogni RowGroup contiene un Column Segment di tutte le colonne nella tabella.

Rowstore

I dati sono salvati su righe e colonne "contenute fisicamemente nella medesima struttura"
Area temporanea dove vengono inserite le nuove righe in attesa d'essere aggiunte al columnstore.

DeltaStore

Il DeltaStore è ciò che rende i Clustered Columnstore Index editabili....
E' composto da Rowstore contenenti le righe che nel tempo andrò ad inserire nella tabella.

Vediamo come funzionano in scrittura i Clustered ColumStore Index

Esecuzione di Insert

Quando eseguiamo un inserimento in una tabella ColumnStore la nuova riga viene salvata nel RowStore aperto nel DeltaStore. Se non esistono RowStore ne viene creato ed aperto uno. Solo quando il Rowstore raggiunge la soglia di 2^20 (1.048.576 record) verrà chiuso. A questo punto un background process chiamato "Tuple-Mover" cerca nel DeltaStore i RowStore chiusi, li comprime e li sposta nel ColumnStore.

Esecuzione di Delete

Se invece dobbiamo eliminare delle righe dalla tabella abbiamo 2 comportamenti distinti

Se la riga è nel DeltaStore verrà fisicamente eliminata.
Se la riga è nel ColumnStore verrà marcata come "cancellata" ma fisicamente rimarrà al suo posto. Sarà eliminata fisicamente solo durante la Rebuild della tabella.

Vediamo degli esempi.

1. INSERT

Creo un DB contenente una tabella con Clustered ColumnStore Index popolata con 100.000 Record

Use Master
Go

Create Database TestClusteredColumnStore
Go

Use TestClusteredColumnStore
Go

Create table TabClusteredColumnStore 
( 
 id int identity,
 valore1 int, 
 valore2 int, 
 valore3 int, 
 valore4 int, 
 valore5 int, 
) 
Go

/***************************************************************************************
ATTENZIONE : questa operazione trasforma la tabella da HEAP(Rowstore) a ColumnStore
***************************************************************************************/
create clustered columnstore index idxClustColStore on TabClusteredColumnStore
Go

--Popolo la tabella con 100000 Record
;With Cte as (Select 1 Riga union All Select 1)
 ,Cte4 as (Select A.Riga From Cte A, Cte B )
 ,Cte16 as (Select A.Riga From Cte4 A, Cte4 B )
 ,Cte256 as (Select A.Riga From Cte16 A, Cte16 B )
 ,Cte65536 as (Select A.Riga From Cte256 A, Cte256 B )
 ,cteMolti as (Select A.Riga From Cte65536 A, Cte65536 B )
 ,Results as (Select Row_number() Over(Order By Riga) Riga From cteMolti)
Insert into TabClusteredColumnStore
Select Riga,Riga,Riga,Riga,Riga from Results Where Riga <= 100000
Go

Eseguiamo questa vista di sistema :

--Ho un solo RowGroup aperto nel deltastore
select object_name(object_id)TabName
  ,* 
from sys.column_store_row_groups
Go

La sys.column_store_row_groups è una vista di sistema che mi restituisce informazioni su ogni Clustered ColumnStore Index presente nel database.

Il risultato è il seguente

Come vedete la tabella TabClusteredColumnStore ha un DeltaStore contenente un RowStore che a sua volta contiene 100.000 Record. Lo stato del RowStore è OPEN, quindi in grado di ricevere ulteriori record.
Provo adesso ad inserire abbastanza righe per raggiungere la soglia massima.
La soglia è 1048576 , quindi dato che ne ho già inserite 100000 devo aggiungerne altre 948576 + 1 in modo da causare la chiusura del RowStore.

--Arrivo alla soglia del Rowstore
;With Cte as (Select 1 Riga union All Select 1)
 ,Cte4 as (Select A.Riga From Cte A, Cte B )
 ,Cte16 as (Select A.Riga From Cte4 A, Cte4 B )
 ,Cte256 as (Select A.Riga From Cte16 A, Cte16 B )
 ,Cte65536 as (Select A.Riga From Cte256 A, Cte256 B )
 ,cteMolti as (Select A.Riga From Cte65536 A, Cte65536 B )
 ,Results as (Select Row_number() Over(Order By Riga) Riga Riga From cteMolti)
Insert into TabClusteredColumnStore
Select Riga,Riga,Riga,Riga,Riga from Results Where Riga <= 948577
Go

Rieseguo la select di prima su sys.column_store_row_groups.

Come vedete il RowStore è stato chiuso e contiene 1048576 righe. Inoltre è stato creato un RowStore nuovo contenente una sola riga, inserita dall'istruzione precedente.
Il Background process Tuple-Mover alla prima esecuzione troverà il RowStore in stato CLOSED e lo inserirà in modalità compressa nel ColumnStore index.

Rieseguo dopo qualche minuto la medesima query su sys.column_store_row_groups.

Lo stato ora è COMPRESSED, il che significa che le righe sono state inserite nel Clustered ColumnStore Index dal Tuple-Mover.

2. DELETE

Cosa accade quando eseguo la delete di una riga già nel Clustered ColumnStore Index ?

Delete From TabClusteredColumnStore Where id = 1
Go

Come detto prima, se la riga è nel ColumnStore verrà marcata come eliminabile ma non verrà eliminata fisicamente dalla struttura.

L'eliminazione fisica delle righe dal ColumnStore verrà eseguita quando sarà effettuato il rebuild del Clustered ColumnStore Index.

Cosa accade quando eseguo una Delete di una riga in RowStore in stato OPEN nel DeltaStore ?
(La riga con id 1048577 è nel RowStore OPEN)

Delete From TabClusteredColumnStore Where id = 1048577
Go

Come detto prima se la riga è in un RowStore in stato OPEN nel DeltaStore allora verrà eliminata definitivamente.

Ed effettivamente viene eliminata, la colonna Total_Rows restituita dalla vista mi dice che ora le righe sono 0 (Nella esecuzione precedente mi dava 1 Riga per il RowStore in stato OPEN).

3. Data Compression

I dati di ColumnStore Index e di Clustered ColumnStore Index vengono salvati in modalità compressa. Per farci un'idea del risparmio in termini di spazio e di I/O creo una tabelle esattamente uguale a TabClusteredColumnStore ma con la struttura standard a cui siamo abituati da anni e la popolo con lo stesso numero di righe

Create table TabStandardRowStore 
( 
 id int identity primary key clustered, 
 valore1 int, 
 valore2 int, 
 valore3 int, 
 valore4 int, 
 valore5 int, 
) 

;With Cte as (Select 1 Riga union All Select 1)
 ,Cte4 as (Select A.Riga From Cte A, Cte B )
 ,Cte16 as (Select A.Riga From Cte4 A, Cte4 B )
 ,Cte256 as (Select A.Riga From Cte16 A, Cte16 B )
 ,Cte65536 as (Select A.Riga From Cte256 A, Cte256 B )
 ,cteMolti as (Select A.Riga From Cte65536 A, Cte65536 B )
 ,Results as (Select Row_number() Over(Order By Riga) Riga From cteMolti)
Insert into TabStandardRowStore
Select Riga,Riga,Riga,Riga,Riga from Results Where Riga <= 1048576
Go

Vediamo quanto spazio abbiamo risparmiato

Exec sp_spaceused 'TabClusteredColumnStore'
Exec sp_spaceused 'TabStandardRowStore'

Il risparmio direi che è notevole, la tabella TabClusteredColumnStore alloca meno della metà dello spazio della sua gemella con la struttura tradizionale.

In SQL Server 2014 inoltre è stato aggiunto un nuovo tipo di compressione ancora più efficiente.
Lo applico alla tabella TabClusteredColumnStore

Alter table TabClusteredColumnStore rebuild with(Data_compression = ColumnStore_Archive)

Riverifico lo spazio allocato

che è ulteriormente calato.....

Per maggiori info sulla compressione dei dati nei ColumnStore Index vi consiglio di leggere il blog di Niko Nuegebauer.

N.B. La rebuild ha ricostruito il Clustered ColumnStore Index e ha anche eliminato la riga eliminata precedentemente.

Ciao

Luca

SQL Server 2012 Cumultive Update 7

2013-11-19T14:28:00.001+01:00

E' uscita la CU7 di SQL Server 2012.

Qui il link per il download.

Ciao

Luca

SQL Server 2014 Range Index Vs Hash Index

2013-11-12T17:35:00.004+01:00

SQL Server 2014 Ctp2 rende disponibile oltre alle In-Memory table con Hash Index anche le In-Memory Table dotate di Range Index.
La struttura di un Hash Index è assimilabile a questa

Il suo funzionamento è spiegato molto bene a questo link.

Una delle limitazioni che abbiamo con l'utilizzo di In-Memory table con indici Hash, ne ho parlato in questo post, è che per ciascun indice, dobbiamo necessariamente conoscere la cardinalità dei dati per poter definire la dimensione del Bucket_Count. In altre parole dobbiamo conoscere a priori quante righe conterrà la tabella...
Un altro limite degli Hash index è che non consentono accessi in Seek alla tabella per ricerche basate su range di valori. Ciò significa che in questi casi l'engine implementa una Scan della tabella intera.

Qualora non si conosca la cardinalità dei dati o si debbano effettuare ricerche basate su range di valori conviene utilizzare i Range index.

Questa tipologia di oggetti ha una struttura a Bw-Tree (Buzz Word Tree).

La struttura ricorda i B-Tree a cui siamo abituati, ma non lasciamoci trarre in inganno, sono differenti ed il loro funzionamento è ben spiegato a questo link.
La struttura del BW-Tree, così come quella degli Hash index è totalmente in memoria RAM, il che da ad entrambi tipi un incredibile vantaggio in termini di performance rispetto alle tabelle tradizionali.

Per creare In-Memory Table basate su Hash e/o Range Index qui trovate tutte le info.

Proviamo a creare 2 In-Memory table

USE MASTER
GO

CREATE DATABASE Hekaton_Test      
 ON PRIMARY(NAME = [PRIMARY],         
   FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Hekaton_Test\Hekaton_DiskBasedTable.mdf')    
,FILEGROUP [Hekaton_InMemory_FG] CONTAINS MEMORY_OPTIMIZED_DATA          
 (NAME = [Hekaton_InMemory_Dir],
  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Hekaton_Test\In_Memory_Table') 
LOG ON (name = [Hekaton_log], FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Hekaton_Test\Hekaton_Test_log.ldf')   
Go

USE Hekaton_Test
GO

--In-Mem with Hash Index
CREATE TABLE Hash_MemoryTable 
( 
 id INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH(BUCKET_COUNT = 65536)  
 ,Nome VARCHAR(25) COLLATE Latin1_General_100_BIN2 not null
 ,Cognome VARCHAR(50) COLLATE Latin1_General_100_BIN2 not null
 ,Indirizzo VARCHAR(50) COLLATE Latin1_General_100_BIN2 
 INDEX inMemoryIdx NONCLUSTERED HASH (Cognome,Nome) WITH(BUCKET_COUNT = 35000)  
)WITH 
 (
  MEMORY_OPTIMIZED = ON
  ,DURABILITY = SCHEMA_AND_DATA
 );  
GO

--In-Mem with Range Index
CREATE TABLE Range_MemoryTable 
( 
 id INT NOT NULL PRIMARY KEY NONCLUSTERED
 ,Nome VARCHAR(25) COLLATE Latin1_General_100_BIN2 not null
 ,Cognome VARCHAR(50) COLLATE Latin1_General_100_BIN2 not null
 ,Indirizzo VARCHAR(50) COLLATE Latin1_General_100_BIN2 
 INDEX inMemoryIdx NONCLUSTERED(Cognome,Nome)  
)WITH 
 (
  MEMORY_OPTIMIZED = ON
  ,DURABILITY = SCHEMA_AND_DATA
 );  
GO

--Popolo entrambe le 5tabelle con 65536 Record
;WITH Cte AS(SELECT 1 Riga UNION ALL SELECT 1) 
 ,Cte4 as (SELECT A.Riga FROM Cte A, Cte B) 
 ,Cte16 as (SELECT A.Riga FROM Cte4 A, Cte4 B) 
 ,Cte256 as (SELECT A.Riga FROM Cte16 A, Cte16 B) 
 ,Cte65536 as (SELECT A.Riga FROM Cte256 A, Cte256 B) 
 ,Results as (SELECT ROW_NUMBER()OVER(ORDER BY A.Riga) Riga FROM Cte65536 A,Cte65536 B) 
INSERT INTO Hash_MemoryTable(id, Nome, Cognome, Indirizzo) 
SELECT Riga 
  , 'Nome ' + Cast(Riga as char(8))  
  , 'Cognome ' + Cast(Riga as char(8))      
  , 'Indirizzo ' + Cast(Riga as char(8))  
FROM Results WHERE Riga <= 65536
GO

;WITH Cte AS(SELECT 1 Riga UNION ALL SELECT 1) 
 ,Cte4 as (SELECT A.Riga FROM Cte A, Cte B) 
 ,Cte16 as (SELECT A.Riga FROM Cte4 A, Cte4 B) 
 ,Cte256 as (SELECT A.Riga FROM Cte16 A, Cte16 B) 
 ,Cte65536 as (SELECT A.Riga FROM Cte256 A, Cte256 B) 
 ,Results as (SELECT ROW_NUMBER()OVER(ORDER BY A.Riga) Riga FROM Cte65536 A,Cte65536 B) 
INSERT INTO Range_MemoryTable(id, Nome, Cognome, Indirizzo)  
SELECT Riga 
  , 'Nome ' + Cast(Riga as char(8))  
  , 'Cognome ' + Cast(Riga as char(8))      
  , 'Indirizzo ' + Cast(Riga as char(8))  
FROM Results WHERE Riga <= 65536
GO

In entrambe le tabelle il campo id è Primary Key e nella Hash_MemoryTable è associato ad un indice Hash mentre nella Range_MemoryTable è creato come Range. Inoltre, su entrambe le tabelle è presente un indice sulle colonne Cognome e Nome.

Come si comportano le due tabelle in caso di ricerca puntuale ?

--Seek diretta
SELECT * FROM Hash_MemoryTable WHERE id = 65000
Go
SELECT * FROM Range_MemoryTable WHERE id = 65000
Go

Il piano d'esecuzione per le query è il medesimo.

Pur non essendo dei contatori particolarmente significativi guardiamo i "Query cost (Relative to the batch)" che danno l'idea del peso di ciascuna query nel batch...

Il piano d'esecuzione per la ricerca nella Hash_MemoryTable è estremamente performante nelle ricerche puntuali. Questo perché l'indice Hash, che di fatto è un array di puntatori, contiene l'indirizzo della riga. La query filtra direttamente sulla colonna di PK Id = 65000, quindi sul valore 65000 viene applicata una funzione di hash che restituisce il bucket contenente l'indirizzo della riga nell'array.

La query sulla Range_MemoryTable, a parità di piano d'esecuzione, è meno performante dato che l'accesso alla riga selezionata è fatto seguendo l'indice Bw-Tree.

Cosa accade invece se modifico le condizioni di ricerca?

SELECT * FROM Hash_MemoryTable WHERE id >= 65000
Go
SELECT * FROM Range_MemoryTable WHERE id >= 65000
Go

I piani d'esecuzione in questo caso sono differenti...

La ricerca su Hash_MemoryTable è decisamente la più pesante, per sua natura la struttura di un Hash index non consente l'uso del Seek in questi tipi di ricerche.
Il Range index, invece, utilizza un Seek risultando molto più performante.
Dopotutto è un Bw-Tree, quindi le ricerche di questo tipo sono il suo pane....

Quale tipo indice dunque è il migliore ?
Bhe... Dipende da cosa dobbiamo fare....
Per ricerche dirette l'Hash Index è la soluzione più performante.
Per ricerche su range di valori il Range Index è quello che fa per noi.

Dato che le In-Memory table possono avere fino ad un max di 8 Index possiamo creare delle strutture ibride che sfruttino sia gli indici Hash che i Range.
Sulle colonne che saranno accedute in modo diretto (Where id = 65000) potrei creare un indice Hash, per le colonne su cui effettuo ricerche di tipo range (Where id >= 65000) posso creare un Range Index.

Un vantaggio comune ad entrambi i tipi di indici, dato che puntano alla riga nella sua interezza, è che l'engine non ha più la necessità di effettuare Key o Rid lookup.

Creo una tabella tradizionale e la popolo con le solite 65536 righe

CREATE TABLE DiskTable  
( 
 id INT NOT NULL PRIMARY KEY CLUSTERED
 ,Nome VARCHAR(25) COLLATE Latin1_General_100_BIN2 not null
 ,Cognome VARCHAR(50) COLLATE Latin1_General_100_BIN2 not null
 ,Indirizzo VARCHAR(50) COLLATE Latin1_General_100_BIN2 
 INDEX DiskIdx NONCLUSTERED (Cognome,Nome) 
)
GO

;WITH Cte AS(SELECT 1 Riga UNION ALL SELECT 1) 
 ,Cte4 as (SELECT A.Riga FROM Cte A, Cte B) 
 ,Cte16 as (SELECT A.Riga FROM Cte4 A, Cte4 B) 
 ,Cte256 as (SELECT A.Riga FROM Cte16 A, Cte16 B) 
 ,Cte65536 as (SELECT A.Riga FROM Cte256 A, Cte256 B) 
 ,Results as (SELECT ROW_NUMBER()OVER(ORDER BY A.Riga) Riga FROM Cte65536 A,Cte65536 B) 
INSERT INTO DiskTable(id, Nome, Cognome, Indirizzo)  
SELECT Riga 
  , 'Nome ' + Cast(Riga as char(8))  
  , 'Cognome ' + Cast(Riga as char(8))      
  , 'Indirizzo ' + Cast(Riga as char(8))  
FROM Results WHERE Riga <= 65536
GO

Come vedete la tabella ha la medesima struttura delle In-Memory create precedentemente e le stesse colonne indicizzate.
Ovviamente in termini di performance è molto più lenta delle In-Memory table ma non solo, ha un'ulteriore handicap che è la struttura dei B-Tree tradizionali.
Le datapages che compongono un index non clustered cosa contengono ?
Contengono i valori delle colonne su cui ho creato l'indice ed eventualmente:

Se la tabella è dotata di chiave clustered il valore della chiave clustered stessa per la riga
Se la tabella è in Heap lo Heap Rid della riga

Cosa accade quindi se la query richiede altre colonne oltre a quelle contenute nell'indice ?

Prendiamo queste Select che richiedono tutte le colonne che compongono la tabella.

SELECT * FROM DiskTable WHERE Cognome = 'Cognome 65000' And Nome = 'Nome 65000'
GO
SELECT * FROM Hash_MemoryTable WHERE Cognome = 'Cognome 65000' And Nome = 'Nome 65000'
GO
SELECT * FROM Range_MemoryTable WHERE Cognome = 'Cognome 65000' And Nome = 'Nome 65000'
GO

Questi sono i piani d'esecuzione generati dall'engine

Accade che l'engine è costretto ad implementare un Key LookUp se la tabella è clustered oppure un RID LookUp se la tabella è Heap per reperire anche i valori delle colonne non presenti nell'indice.
Questi surplus di lavoro, sono comunque accessi ad altre strutture, diminuiscono le performance aumentando le risorse in uso ed i tempi di attesa.
Le select sulle In-Memory table non richiedono l'operatore di Key Lookup dato che gli indici, sia Hash che Range (seppur nel leaf level), hanno sempre disponibile il puntatore all'intera riga. La select sulla DiskTable invece è costretta ad utilizzare un Key LookUp per reperire le colonne richieste.

Ciao

Luca

SQL Saturday 257 Session Download

2013-11-12T12:21:00.000+01:00

Ciao,
Qui potete scaricare le slide e le demo della sessione sulla gestione degli errori nel codice T-SQL che ho tenuto Sabato 9/11/2013 al SQL Saturday 260 in Microsoft a Milano....

Ciao

Luca

SQL Server 2014 CTP2 disponibile.

2013-10-17T15:56:00.000+02:00

E' uscito SQL Server 2014 Ctp2 e qui potete farne il download !!!

Qui trovate le release notes...

Ciao

Luca

Extended Events : Leggere il contenuto del file .xel

2013-10-11T12:45:00.000+02:00

Come è possibile leggere il contenuto di un trace su file .Xel prodotto dagli Extended Events di SQL Server 2012 ?
Utilizzando il T-SQL ed un poco di sano XML !! ;-)
Facciamo un esempio.

Creo una sessione denominata [MySession] il cui target è il file E:\MySession.xel

USE MASTER
GO

CREATE EVENT SESSION [MySession] ON SERVER 
ADD EVENT sqlserver.query_post_execution_showplan
(
    ACTION
  (
   sqlserver.client_app_name
   ,sqlserver.client_hostname
   ,sqlserver.nt_username
   ,sqlserver.sql_text
  )
),
ADD EVENT sqlserver.sql_statement_completed
(
    ACTION
  (
   sqlserver.client_app_name
   ,sqlserver.client_hostname
   ,sqlserver.nt_username
   ,sqlserver.sql_text
  )
)
ADD TARGET package0.asynchronous_file_target
 (
  SET filename=N'E:\MySession.xel'
 )
WITH (STARTUP_STATE=ON)
GO

ALTER EVENT SESSION [MySession] ON SERVER STATE=START
GO

Il file .xel prodotto dalla trace ha questa struttura

Dato che il file è umanamente illeggibile possiamo consultarlo:

Utilizzando l'interfaccia grafica
Utilizzando T-SQL e XQuery

Se scelgo la prima opzione posso usufruire di un'interfaccia come la seguente dove nella parte superiore della finestra ho l'elenco degli eventi, nella parte inferiore il dettaglio del singolo evento selezionato.

Se però il contenuto della trace è molto grosso e ho necessità di effettuare filtri, ricerche e aggregazioni forse mi conviene scegliere la strada del T-SQL utilizzando la dmv sys.fn_xe_file_target_read_file.

SELECT 
    CAST(event_data AS XML) AS event_data 
 FROM 
    sys.fn_xe_file_target_read_file('E:\MySession*.xel', NULL, NULL, NULL)

Il carattere "*" nel nome del file indica alla dmv di leggere tutti i file xel denominati MySession presenti nella cartella. (Nella configurazione della session potremmo aver configurato anche il file rollover....)

La dmv trasforma il/i file da incomprensibili a quasi comprensibili.
Per ogni evento ne reperisce l'intero set di dati sottoforma di XML.

Ogni riga rappresenta un evento la cui struttura è indicativamente questa.

Il numero dei nodi associati all'evento dipende dal numero di Action e Fields selezionati per l'evento stesso....

Per reperire tutti i dati di un evento a questo punto devo conoscerne il path ed il tipo e creare una XQuery.
Esempio:
Per reperire il'id del database all'interno del quale s'è verificato l'esempio devo seguire il path "./data[@name="source_database_id]".
Per reperirne il valore utilizzo il metodo value o il metodo query.

Dovrei quindi scrivere la query seguente:

;WITH BaseXml AS --Leggo il file
( 
    SELECT 
        CAST(event_data AS XML) AS event_data 
    FROM 
        sys.fn_xe_file_target_read_file('E:\MySession*.xel', NULL, NULL, NULL) 
), 
XmlQuery AS --Leggo gli eventi
( 
 SELECT
  N.x.value('@name', 'varchar(250)') event_name
  ,N.x.value('@timestamp', 'datetime') event_time
  ,event_data 
    FROM 
        BaseXml    
    CROSS APPLY 
        event_data.nodes('/event') N(x) 
)
SELECT Row_number()Over(Order By event_time) Sequence
  ,event_time UTC
  ,dateadd(minute, datepart(TZoffset, sysdatetimeoffset()),event_time) event_time
  ,event_name
  ,event_data.value('(/event/action[@name=''client_app_name'']/value)[1]','varchar(max)') AS [client_app_name],
  event_data.value('(/event/action[@name=''client_hostname'']/value)[1]','varchar(max)') AS [client_hostname],
  event_data.value('(/event/action[@name=''cpu_id'']/value)[1]','bigint') AS [cpu_id],
  event_data.value('(/event/data[@name=''cpu_time'']/value)[1]','bigint') AS [cpu_time],
  event_data.value('(/event/action[@name=''database_id'']/value)[1]','bigint') AS [database_id],
  event_data.value('(/event/action[@name=''database_name'']/value)[1]','varchar(max)') AS [database_name],
  event_data.value('(/event/data[@name=''database_name'']/value)[1]','varchar(max)') AS [database_name],
  event_data.value('(/event/data[@name=''duration'']/value)[1]','bigint') AS [duration],
  event_data.value('(/event/data[@name=''estimated_cost'']/value)[1]','bigint') AS [estimated_cost],
  event_data.value('(/event/data[@name=''estimated_rows'']/value)[1]','bigint') AS [estimated_rows],
  event_data.value('(/event/data[@name=''last_row_count'']/value)[1]','bigint') AS [last_row_count],
  event_data.value('(/event/data[@name=''line_number'']/value)[1]','bigint') AS [line_number],
  event_data.value('(/event/data[@name=''logical_reads'']/value)[1]','bigint') AS [logical_reads],
  event_data.value('(/event/data[@name=''nest_level'']/value)[1]','bigint') AS [nest_level],
  event_data.value('(/event/action[@name=''nt_username'']/value)[1]','varchar(max)') AS [nt_username],
  event_data.value('(/event/data[@name=''object_id'']/value)[1]','bigint') AS [object_id],
  event_data.value('(/event/data[@name=''object_name'']/value)[1]','varchar(max)') AS [object_name],
  event_data.value('(/event/data[@name=''object_type'']/value)[1]','varchar(max)') AS [object_type],
  event_data.value('(/event/data[@name=''offset'']/value)[1]','bigint') AS [offset],
  event_data.value('(/event/data[@name=''offset_end'']/value)[1]','bigint') AS [offset_end],
  event_data.value('(/event/data[@name=''parameterized_plan_handle'']/value)[1]','varchar(max)') AS [parameterized_plan_handle],
  event_data.value('(/event/data[@name=''physical_reads'']/value)[1]','bigint') AS [physical_reads],
  event_data.value('(/event/data[@name=''row_count'']/value)[1]','bigint') AS [row_count],
  event_data.query('(/event/data[@name=''showplan_xml'']/value/*)[1]') AS [showplan_xml],
  event_data.value('(/event/data[@name=''source_database_id'']/value)[1]','bigint') AS [source_database_id],
  event_data.value('(/event/action[@name=''sql_text'']/value)[1]','varchar(max)') AS [sql_text],
  event_data.value('(/event/data[@name=''statement'']/value)[1]','varchar(max)') AS [statement],
  event_data.value('(/event/action[@name=''username'']/value)[1]','varchar(max)') AS [username],
  event_data.value('(/event/data[@name=''writes'']/value)[1]','bigint') AS [writes] 
FROM 
 XmlQuery
Order By 1

Il problema è che devo conoscere esattamente il path di ogni dato necessario e compilare il codice a mano per ogni colonna che desidero reperire..... Non è proprio molto comodo.

Fortunatamente, grazie al T-SQL dinamico posso risolvere questo inconveniente sfruttando CTE e tabelle temporanee per reperire, direttamente dall'XML la struttura ed il path di ogni singolo nodo.

DECLARE @FileName Varchar(256) = 'E:\MySession*.xel';

IF (OBJECT_ID('tempdb..#XQueryDef') IS NOT NULL) DROP TABLE #XQueryDef
IF (OBJECT_ID('tempdb..#XQuery') IS NOT NULL) DROP TABLE #XQuery

;WITH cteGetEventsFromFile AS --Leggo il file
( 
    SELECT 
        CAST(event_data AS XML) AS event_data 
    FROM 
        sys.fn_xe_file_target_read_file(@FileName, NULL, NULL, NULL) 
), 
cteGetEventsFather AS --Leggo gli eventi
( 
 SELECT
  N.x.value('@name', 'varchar(250)') event_name
  ,N.x.value('@timestamp', 'datetime') event_time
  ,event_data 
    FROM 
        cteGetEventsFromFile    
    CROSS APPLY 
        event_data.nodes('/event') N(x) 
)
,cteGetEventsFields AS --Leggo tutto ciò che riguarda ogni singolo evento
( 
    SELECT 
  L.event_name
        ,L.event_time
  ,N2.x.value('@name', 'varchar(100)') data_name
    FROM 
        cteGetEventsFather L 
    CROSS APPLY 
        L.event_data.nodes('event/*') N2(x) 
) 
,cteWhereToFind AS
(
SELECT 
 FL.event_name
 ,FL.event_time
 ,FL.data_name
 ,'''(/event/'+ ISNULL(C.column_type, O.object_type) +'[@name=''''' + FL.data_name + ''''']/value' +IIF(C.type_name like '%xml%','/*','') + ')[1]''' + 
   + IIF(C.type_name IS NULL
      ,CASE WHEN O.Type_name like '%string%' THEN ',''varchar(max)'''
        WHEN  O.type_name like '%int%' THEN ',''bigint'''
        WHEN  O.type_name like '%xml%' THEN ''
        ELSE ',''varchar(max)''' END 

      ,CASE WHEN C.type_name like '%string%' THEN ',''varchar(max)'''
        WHEN C.type_name like '%int%' THEN ',''bigint'''
        WHEN C.type_name like '%xml%' THEN ''
        ELSE ',''varchar(max)''' END 
  ) WhereToFind
FROM cteGetEventsFields FL
 left Join sys.dm_xe_object_columns C
  On FL.data_name = C.name 
    and FL.event_name = object_name
 left Join sys.dm_xe_objects O
  On FL.data_name = O.name 
   and O.object_type = 'Action'
)
SELECT DISTINCT * 
 INTO #XQueryDef
FROM cteWhereToFind

;WITH cteGetEventsFromFile AS --Leggo il file
( 
    SELECT 
        CAST(event_data AS XML) AS event_data 
    FROM 
        sys.fn_xe_file_target_read_file(@FileName, NULL, NULL, NULL) 
), 
cteGetEventsFather AS --Leggo gli eventi
( 
 SELECT
        N.x.value('@name', 'varchar(250)') event_name
  ,N.x.value('@timestamp', 'datetime') event_time
  ,event_data 
    FROM 
        cteGetEventsFromFile    
    CROSS APPLY 
        event_data.nodes('/event') N(x) 
)
SELECT * 
 INTO #XQuery
FROM cteGetEventsFather

DECLARE @strCmd VARCHAR(max)='SELECT Row_number()Over(Order By event_time) Sequence,dateadd(minute, datepart(TZoffset, sysdatetimeoffset()),event_time) event_time,event_name,'
SELECT 
 @strCmd+= 'event_data.' + IIF(data_name like '%xml%','query','value') + '(' + WhereToFind + ') AS ' + QUOTENAME(data_name) +  ',' + char(10)
FROM 
( 
 SELECT  DISTINCT  data_name, WhereToFind FROM #XQueryDef  
)A

SET @strCmd = reverse(stuff(reverse(Rtrim(@strCmd)), 1, 2, '')) + ' FROM #XQuery Order By Sequence'
EXEC(@strCmd)

Il risultato è una tabella composta da una colonna per ogni Action e Fields e una riga per ogni evento intercettato e contenuto nell'Xml.
A questo punto posso salvare il risultato in una tabella temporanea su cui fare, con codice T-SQL semplice tutte le query, le ricerche, le aggregazioni etc necessarie all'analisi della trace.

Ciao

Luca

SQL Saturday 260 Session Download

2013-10-09T11:24:00.000+02:00

Ciao,
Qui potete scaricare le slide e le demo della sessione sugli Extended Events che ho tenuto ieri al SQL Saturday 260 in Microsoft a Milano....

Ciao

Luca

Spostare una tabella da un FileGroup ad un altro

2013-10-08T23:25:00.000+02:00

Mai avuto la necessità di spostare una tabella da un filegroup ad un altro ?
Mi è giusto capitato pochi giorni fa e
per risolvere il problema mi sono creato una Stored Procedure che esegue :

Salvataggio metadati di tutte le Fk della tabella
Drop di tutte le Fk dalla tabella
Disable di tutti gli indici
Salvataggio metadati indice clustered
Drop Indice clustered dalla tabella
Create indice clustered con i metadati salvati precedentemente sul nuovo filegroup
Rebuild Indici non-clustered
Create FK con i metadati salvati precedentemente

Ecco lo script :

--N.B. MODIFICARE IL NOME DEL DB PRIMA DI USARE LO SCRIPT
Use TestMoveTable
Go
--N.B. MODIFICARE IL NOME DELLA TABELLA E DEL FILEGROUP
Declare @TabToMove varchar(128) = '[dbo].[TableFK]'
Declare @FileGroup varchar(128) = 'NEWFG'

Declare @Disable_Index varchar(max) = 'Alter index ALL on ' + @TabToMove + ' Disable;'
Declare @Drop_Clustered_Index varchar(max) = ''
Declare @Create_Clustered_Index varchar(max) = ''
Declare @Rebuild_NonClustered_Index varchar(max) = ''
Declare @Create_Fk varchar(max) = ''
Declare @Drop_Fk varchar(max) = ''
Select @Drop_Fk+= drop_command +';' + char(13) + char(10) From 
(

 select 
  'ALTER TABLE '
  + QUOTENAME(Object_Name(FKC.Parent_object_id)) +  ' DROP CONSTRAINT '
  + QUOTENAME(Object_name(FKC.constraint_object_id)) DROP_COMMAND
 from sys.foreign_key_columns FKC
  Inner Join sys.columns CP
   ON CP.object_id = FKC.parent_object_id 
    AND CP.column_id = FKC.parent_column_id
  Inner Join sys.columns RCP
   ON RCP.object_id = FKC.Referenced_object_id 
    AND RCP.column_id = FKC.Referenced_column_id
  Inner join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS Const
   ON Const.CONSTRAINT_NAME = Object_name(FKC.constraint_object_id)
 Where FKC.referenced_object_id = Object_ID(@TabToMove)
)Drop_Command


Select @Create_Fk+= create_command +';' + char(13) + char(10) From 
(
 select 
  'ALTER TABLE '
  + QUOTENAME(Object_Name(FKC.Parent_object_id)) +  ' WITH CHECK ADD  CONSTRAINT '
  + QUOTENAME(Object_name(FKC.constraint_object_id)) + ' FOREIGN KEY (' 
  + QUOTENAME(CP.name) + ') REFERENCES ' + QUOTENAME(Object_Name(FKC.Referenced_object_id)) 
  + '(' + QUOTENAME(RCP.name) + ')'
   + CASE WHEN Const.UPDATE_RULE = 'CASCADE' THEN ' ON UPDATE CASCADE ' ELSE '' END
   + CASE WHEN Const.UPDATE_RULE = 'CASCADE' THEN ' ON DELETE CASCADE ' ELSE '' END CREATE_COMMAND

 from sys.foreign_key_columns FKC
  Inner Join sys.columns CP
   ON CP.object_id = FKC.parent_object_id 
    AND CP.column_id = FKC.parent_column_id
  Inner Join sys.columns RCP
   ON RCP.object_id = FKC.Referenced_object_id 
    AND RCP.column_id = FKC.Referenced_column_id
  Inner join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS Const
   ON Const.CONSTRAINT_NAME = Object_name(FKC.constraint_object_id)
 Where FKC.referenced_object_id = Object_ID(@TabToMove)
)Create_Command

Select @Rebuild_NonClustered_Index = @Rebuild_NonClustered_Index + char(13) +char(10) + Rebuild_Index + ';' From
(
select 'Alter Index '+ Quotename(i.name)  + ' on ' + @TabToMove + ' Rebuild' 
   Rebuild_Index
from sys.indexes as i
  inner join sys.objects obj
    on i.object_id=obj.object_id
  inner join sys.schemas sch
    on obj.schema_id=sch.schema_id
  inner join sys.partitions part
    on i.object_id=part.object_id
     and i.index_id=part.index_id
  inner join sys.stats stats
    on i.object_id=stats.object_id
     and i.index_id=stats.stats_id
where i.object_id = object_id(@TabToMove)
 And I.type_desc = 'NONCLUSTERED'
)A

Select @Drop_Clustered_Index = Drop_Index From
(
 select Case When is_primary_key = 1 Or is_unique_constraint = 1 Then 'Alter Table ' + @TabToMove + ' Drop constraint ' + Quotename(i.name) 
    else 'Drop index ' + Quotename(i.name) + ' on ' + @TabToMove  + ';'
   End  Drop_Index
 from sys.indexes as i
     inner join sys.objects obj
     on i.object_id=obj.object_id
     inner join sys.schemas sch
     on obj.schema_id=sch.schema_id
     inner join sys.partitions part
     on i.object_id=part.object_id
      and i.index_id=part.index_id
     inner join sys.stats stats
     on i.object_id=stats.object_id
      and i.index_id=stats.stats_id
 where i.object_id = object_id(@TabToMove)
  And I.type_desc = 'CLUSTERED'
)A


Select @Create_Clustered_Index = Create_Index from
(
Select Case is_primary_key When 1 Then 'Alter Table ' + @TabToMove + ' Add constraint ' + index_name + ' Primary Key Clustered (' + reverse(stuff(reverse(index_columns), 1, 1, '')) + ') on ' + @FileGroup + ';'
   else 
    Case is_unique_constraint when 1
     Then 'Alter Table ' + @TabToMove + ' Add constraint ' + index_name + ' Unique Clustered (' + reverse(stuff(reverse(index_columns), 1, 1, '')) + ') on ' + @FileGroup + ';'
    else
     Case Is_unique When 1 
      Then 'Create Unique Clustered index ' + Quotename(index_name) + ' on ' + @TabToMove + '(' + reverse(stuff(reverse(index_columns), 1, 1, '')) + ') on ' + @FileGroup + ';'
     Else
      'Create Clustered index ' + Quotename(index_name) + ' on ' + @TabToMove + '(' + reverse(stuff(reverse(index_columns), 1, 1, '')) + ') on ' + @FileGroup + ';'
     End
    End
  End Create_Index 
from
(
 select
 quotename(sch.name)+'.'+quotename(obj.name) [table_name],
 quotename(i.name) [index_name],
 i.type_desc,
 (
     select '['+col.name+'] ' 
          + Case is_descending_key When 1 then ' Desc,' 
           Else 'Asc,'
         End as [data()]
     from sys.index_columns as k
      inner join sys.columns col
          on k.object_id=col.object_id
           and k.column_id=col.column_id
     where k.object_id = i.object_id
      and k.index_id = i.index_id
      and k.index_id<>0
      and k.is_included_column<>1
      order by key_ordinal, k.column_id
     for xml path('')
 )
 as [index_columns],
 is_unique,
 is_primary_key,
 is_unique_constraint,
 fill_factor,
 is_padded
 from sys.indexes as i
     inner join sys.objects obj
     on i.object_id=obj.object_id
     inner join sys.schemas sch
     on obj.schema_id=sch.schema_id
     inner join sys.partitions part
     on i.object_id=part.object_id
      and i.index_id=part.index_id
     inner join sys.stats stats
     on i.object_id=stats.object_id
      and i.index_id=stats.stats_id
 where i.object_id = object_id(@TabToMove)
  And I.type_desc = 'CLUSTERED' 
 )A
)B

Print @Drop_Fk
Print @Disable_Index
Print @Drop_Clustered_Index
Print @Create_Clustered_Index
Print @Rebuild_NonClustered_Index
Print @Create_Fk

Lo script genera un risultato simile a questo nella finestra Messages :

Attenzione, non eseguire lo script su tabelle partizionate....

Ciao

Luca

Backup/Restore come Initial Snapshot di una replica transazionale

2013-09-28T18:46:00.000+02:00

In certi scenari di replica, dove il DB da pubblicare è di grosse dimensioni, potrebbe essere conveniente configurare la/le sottoscrizioni partendo da un Backup\Restore anziché da un Initial Snapshot come prevede la procedure standard.
La creazione dell'Initial Snapshot potrebbe essere molto lunga e consumare molte risorse che in questo modo risparmiamo.
Vediamo un esempio per la creazione di una sottoscrizione da un backup....

Voglio creare una sottoscrizione del Db DBPublisher del server Win12Node1 sul database DBSubscriber del server Win12Node2.
Su Win12node1 è già configurata la pubblicazione del DBPublisher denominata TestPub.

Step 1

Sul Publisher la prima cosa da fare è verificare se la pubblicazione in essere accetta sottoscrizioni inizializzate da backup.
Per verificare basta eseguire la sp_helppublication e controllare il setting allow_initialize_from_backup.
Questo setting può assumere 2 valori:
          1.   0 non abilitato
          2.   1 abilitato.

--Da eseguire sul publisher
Exec sp_helppublication 'TestPub'
GO

Nel mio caso devo abilitare l'inizializzazione da Backup, quindi devo eseguire la sp_changepublication.

--Da eseguire sul publisher
Exec sp_changepublication @publication = 'TestPub'
                      , @property = 'allow_initialize_from_backup'
                      , @value  = 'true'
GO

Step 2

Eseguire il Restore del DB sul server sottoscrittore, nel mio caso lo chiamerò DBSubScriber partendo dall'ultimo set di backup disponibile.

N.B. Se l'ultimo backup disponibile è più vecchio del periodo di Retention del distributor allora sarà necessario creare un nuovo set di backup.

--Da eseguire sul subscriber
USE [master]
Go
Restore Database [DBSubScriber] From Disk = '\\MyFileServer\SharedFolder\init.bak' 
GO

Step 3

Creiamo la sottoscrizione dal server publisher utilizzando la sp_addsubscription e la sp_addpushsubscription_agent.

La sp_addsubscription deve essere eseguita specificando:
@subscription_type = 'Push'
@sync_type = 'initialize with backup'
@backupdevicetype = 'disk'
@backupdevicename = MyPath
Dove MyPath è il path al backup differenziale appena creato che verrà utilizzato per allineare i dati.

La sp_addpushsubscription_agent deve essere eseguita specificando:
@job_login = MyLogin
@job_password = MyPassword
Dove MyLogin e MyPassword sono Username e Pwd che l'agent utilizzerà per le repliche.

--Da eseguire sul publisher
use DBPublisher
GO

exec sp_addsubscription 
  @publication = N'TestPub'
  , @subscriber = N'WIN12NODE2'
  , @destination_db = N'DBSubScriber'
  , @subscription_type = N'Push'
  , @sync_type = N'initialize with backup'
  , @backupdevicetype = N'disk'
  , @backupdevicename = N'\\MyFileServer\SharedFolder\init.bak'
  , @article = N'all'
  , @update_mode = N'read only'
  , @subscriber_type = 0

exec sp_addpushsubscription_agent @publication = N'TestPub'
  , @subscriber = N'WIN12NODE2'
  , @subscriber_db = N'DBSubScriber'
  , @job_login = MyLogin
  , @job_password = MyPassword
  , @subscriber_security_mode = 1
  , @frequency_type = 64
  , @frequency_interval = 0
  , @frequency_relative_interval = 0
  , @frequency_recurrence_factor = 0
  , @frequency_subday = 0
  , @frequency_subday_interval = 0
  , @active_start_time_of_day = 0
  , @active_end_time_of_day = 235959
  , @active_start_date = 20130919
  , @active_end_date = 99991231
  , @enabled_for_syncmgr = N'False'
  , @dts_package_location = N'Distributor'
GO

Se abbiamo fatto tutto correttamente la replica è già operativa e tutte le transazioni applicate al DBPublisher saranno replicate anche sul DBSubscriber.
Per essere certi che tutto funzioni correttamente possiamo utilizzare il tool di monitoring della replica per risolvere eventuali problemi.

Ciao

Luca

T-SQL e caricamento dati differenziale..... E le performance ?

2013-09-22T09:30:00.000+02:00

Immaginiamo questo scenario :
DBTarget è un DB OLAP che contiene una tabella TargetTable ed è utilizzato per effettuare Business Intelligence
DBSource è il DB OLTP che contiene una tabella SourceTable ed è utilizzato dai programmi aziendali di gestione.
Abbiamo la necessità di allineare il contenuto di TargetTable con quello di SourceTable utilizzando T-SQL.

Ho a disposizione 3 strade

Soluzione 1

Truncate/Delete di TargetTable
Ri-popolamento totale partendo dai dati di SourceTable

Soluzione 2

Cerco le righe presenti in TargetTable ma non in SourceTable e se necessario le elimino
Cerco le righe differenti tra TargetTable e SourceTable e le aggiorno con i dati di SourceTable
Cerco le righe presenti in SourceTable ma non in TargetTable e le inserisco

Soluzione 3

Eseguo il Merge delle tabelle

Il costrutto Merge, introdotto dall'edizione SQL Server 2008 (ben 5 anni fa ma che purtroppo non vedo molto utilizzato) consente, con un unico comando, di effettuare Delete, Update ed Insert in una tabella con un solo comando.

Vediamo un paio di esempi.

Creo DBSource con la Sp di popolamento iniziale e quella di simulazione modifica dati

Create Database DBSource
Go

Use DBSource
Go


Create table SourceTable
(
 id int primary key clustered
 ,campo1 varchar(25)
 ,campo2 varchar(25)
 ,campo3 varchar(25)
)
GO


--Popolo la tabella con circa 16 Milioni di righe
Create Procedure CreateSourceData
as

Truncate table SourceTable

--Popolamento iniziale
;With Cte as (Select 1 Riga Union All Select 1)
 ,Cte4 as (Select A.Riga From Cte A, Cte B)
 ,Cte16 as (Select A.Riga From Cte4 A, Cte4 B)
 ,Cte256 as (Select A.Riga From Cte16 A, Cte16 B)
 ,Cte65536 as (Select A.Riga From Cte256 A, Cte256 B)
 ,CteManyRows as (Select A.Riga From Cte65536 A, Cte256 B)
 ,Results as (Select ROW_NUMBER() Over(Order By Riga)Riga From CteManyRows)
Insert into SourceTable (id, campo1, campo2, campo3)
Select 
 Riga
 ,'Campo1 ' + Cast(Riga as Varchar(19)) 
 ,'Campo2 ' + Cast(Riga as Varchar(19)) 
 ,'Campo3 ' + Cast(Riga as Varchar(19)) 
from Results
Go

--Cancello, Modifico, inserisco il numero di righe
--specificato nel parametro @RowNum
--Le righe coinvolte sono reperite casualmnte grazie all'Order by NewId()
Create Procedure ModifySourceData
@RowNum int
as
--Cancellazione Random
Delete A From SourceTable A
 Inner Join
  (Select Top(@RowNum) id from SourceTable
   Order By NEWID()) B
 On A.id = B.id

--Modifica Random
Update A Set Campo2 = A.campo3
 From SourceTable A
  Inner Join
   (Select Top(@RowNum) id from SourceTable
    Order By NEWID()) B
  On A.id = B.id

--Inserimento di nuove righe
;With Cte as (Select 1 Riga Union All Select 1)
 ,Cte4 as (Select A.Riga From Cte A, Cte B)
 ,Cte16 as (Select A.Riga From Cte4 A, Cte4 B)
 ,Cte256 as (Select A.Riga From Cte16 A, Cte16 B)
 ,Cte65536 as (Select A.Riga From Cte256 A, Cte256 B)
 ,CteManyRows as (Select A.Riga From Cte65536 A, Cte256 B)
 ,Results as (Select ROW_NUMBER() Over(Order By Riga)Riga From CteManyRows)
Insert into SourceTable (id, campo1, campo2, campo3)
Select 
 Riga + 1000000000
 ,'Campo1 ' + Cast(Riga + 1000000000 as Varchar(19)) 
 ,'Campo2 ' + Cast(Riga + 1000000000 as Varchar(19)) 
 ,'Campo3 ' + Cast(Riga + 1000000000 as Varchar(19)) 
from Results
 Where Riga <= @RowNum
Go

Creo ora DBTarget con le SP di popolamento iniziale e di allineamento

Create Database DBTarget
Go

Use DBTarget
Go

Create table TargetTable
(
 id int primary key clustered
 ,campo1 varchar(25)
 ,campo2 varchar(25)
)
GO

--popola la tabella TargetTable con circa 16 milioni
Create Procedure dbo.ResetTable
as
Truncate table TargetTable

;With Cte as (Select 1 Riga Union All Select 1)
 ,Cte4 as (Select A.Riga From Cte A, Cte B)
 ,Cte16 as (Select A.Riga From Cte4 A, Cte4 B)
 ,Cte256 as (Select A.Riga From Cte16 A, Cte16 B)
 ,Cte65536 as (Select A.Riga From Cte256 A, Cte256 B)
 ,CteManyRows as (Select A.Riga From Cte65536 A, Cte256 B)
 ,Results as (Select ROW_NUMBER() Over(Order By Riga)Riga From CteManyRows)
Insert into TargetTable (id, campo1, campo2)
Select 
 Riga
 ,'Campo1 ' + Cast(Riga as Varchar(19)) 
 ,'Campo2 ' + Cast(Riga as Varchar(19)) 
from Results
Go

--Allinea la tabella TargetTable facendone la truncate e ripopolandola totalmente da SourceTable
Create Procedure dbo.Ripopola
as
Truncate table TargetTable

Insert into TargetTable
Select id, Campo1, Campo2 From [DBSource].[dbo].[SourceTable]
Go


--Allinea la tabella TargetTable effettuando
--1 Delete righe non più presenti in Source
--2 Update Righe modificate in Source
--3 Insert nuove righe da Source
Create Procedure dbo.AllineaSenzaMerge
as

Delete T from TargetTable T
 Where T.id not in (Select id from [DBSource].[dbo].[SourceTable])

--Cerco presenti ma differenti
Update T Set 
 T.campo1 = S.campo1 
 ,T.campo2 = S.campo2
from TargetTable T
 Inner join [DBSource].[dbo].[SourceTable] S
  On T.id = S.id
Where T.campo1 <> S.campo1
 Or T.campo2 <> S.campo2

--Cerco nuovi
Insert Into TargetTable
Select S.id
  ,S.campo1
  ,S.campo2 
from [DBSource].[dbo].[SourceTable] S
 Where S.id not in (Select id from TargetTable)
GO

--Allinea la tabella TargetTable con il solo 
-- omando MERGE
Create Procedure dbo.AllineaConMerge
as

Merge Into TargetTable Target
Using 
(Select id,Campo1,campo2 from [DBSource].[dbo].[SourceTable]) Source
 On Target.id = Source.id
When not matched by Source then
 Delete
When Matched and (target.campo1 != source.campo1 Or target.campo2 != source.campo2) Then
 Update Set target.campo1 = source.campo1
    ,target.campo2 = source.campo2
When not Matched by target then
 Insert (id,campo1,campo2) values(source.id, source.campo1, source.campo2);

GO

Ho creato nel DB 4 SP

dbo.ResetTable= Ripristina la situazione iniziale del test
dbo.Ripopola = Allina la tabella con un re-inserimento totale dei dati
dbo.AllineaSenzaMerge = Allinea la tabella eliminando, modificando, inserendo solo le righe necessarie in 3 step T-SQL differenti (Delete, Update, Insert)
dbo.AllineaConMerge = Allinea la tabella eliminando, modificando, inserendo solo le righe necessarie con il solo comando merge.

Popoliamo la tabella SourceTable di DBSource con circa 16 milioni di Righe.
N.B. Il caricamento potrebbe essere lento....

Use DBSource
Go
Exec CreateSourceData 
Go

Esempio 1 : Popolamento TargetTable da zero

Proviamo ad allineare Target con la SP dbo.Ripopola

Use DBTarget
Go
Set statistics Io On
Set Statistics Time On
Go

Exec dbo.Ripopola
Go

Set statistics time off
Set statistics io Off
GO

La Sp impiega circa 30 secondi per inserire circa 16 milioni di record (Il disco della macchina è un SSD ;-) )

Proviamo ad allinearla con la SP dbo.AllineaSenzaMerge

Use DBTarget
Go
Truncate table TargetTable
Go
Set statistics Io On
Set statistics Time On
GO
Exec dbo.AllineaSenzaMerge
GO
Set statistics time off
Set statistics io Off
GO

Il tempo impiegato è di circa 35 secondi...

Proviamo ora con il comando Merge

Use DBTarget
Go
Truncate table TargetTable
Go
Set statistics Io On
Set statistics Time On
GO
Exec dbo.AllineaConMerge
GO
Set statistics time off
Set statistics io Off
GO

Il Merge impiega circa 44 Secondi.......

In una situazione come questa è ovviamente meglio effettuare il popolamento utilizzando un Insert into dalla tabella sorgente.
Gli step di Delete e di Update sono inutili quindi è ovvio che le Sp AllineaSenzaMerge e AllineaConMerge siano meno performanti nonostante il numero di operazioni I/O sia lo stesso per tutte 3 le Sp......

Esempio 2 : Allineamento TargetTable con SourceTable ( 3000000 righe modificate)

Ora che le tabelle Source e Target sono allineate, simuliamo un utilizzo della tabella Source con operazioni di Delete, Update e Insert.
Simulo la cancellazione, la modifica e l'inserimento di 1000000 di record nella tabella SourceTable.
In tutto sono 3000000 le righe "modificate".

Use DBSource
Go
Exec ModifySourceData 1000000
Go

Allineamento con Truncate e Re-inserimento massivo

Use DBTarget
Go
Set statistics Io On
Set Statistics Time On
Go
Exec Ripopola
Go
Set statistics time off
Set statistics io Off
GO

Dato che il numero di righe in SourceTable non è cambiato il tempo d'esecuzione della Sp è praticamente il medesimo del test precedente, circa 30 secondi.

Proviamo con la SP dbo.AllineaSenzaMerge
N.B. La tabella viene riportata alla condizione precedente con la sp dbo.ResetTable

Use DBTarget
Go
Exec dbo.ResetTable
Go
Set statistics Io On
Set statistics Time On
GO
Exec dbo.AllineaSenzaMerge
GO
Set statistics time off
Set statistics io Off
GO

Stavolta ha impiegato circa 32 secondi...

Proviamo il Merge

Use DBTarget
Go
Exec dbo.ResetTable
Go
Set statistics Io On
Set statistics Time On
GO
Exec dbo.AllineaConMerge
GO
Set statistics time off
Set statistics io Off
GO

Il Merge ha girato in 24 secondi.....

La spiegazione sta nel numero di operazioni di I/O effettuate.
La sp dbo.Ripopola deve ricostruire da zero la tabella, quindi implementa moltissimo I/O.
La sp dbo.AllineaSenzaMerge per ognuno dei 3 step deve cercare le righe coinvolte e modificarle, quindi moltissimo I/O....
Il Merge implementa un solo passaggio e quindi una sola lettura delle tabelle, mentre Update, Delete ed Insert vengono implementati all'unisono....
Dopotutto meno I/O faccio più veloce vado ed il Merge in questo caso ci aiuta...

Ciao

Luca

Script Rebuild o Reoganize ?

2013-09-14T10:49:00.000+02:00

E' sempre necessario fare la rebuild di un indice o posso farne la reorganize ?
Per dirla in modo estrememente sintetico e semplificato, la Rebuild è molto efficiente ma pesante , la Reorganize meno efficace ma più leggera...
La prima è utile quando è necessario ricostruire la struttura al fine di ripristinare il livello di performance adeguato, la seconda è utile quando la struttura non è molto frammentata e le performance sono ancora accettabili.

Qui trovate un articolo dettagliato su come determinare la frammentazione di un indice e come intervenire.

Lo script, trasformabile in stored procedure fa riferimento a :

@Pages_Limits = numero minimo di datapages che compone l'indice. se l'indice è composto da un numero di data pages inferiore non viene preso in considerazione.
@Fragmentation_Limits = % di frammentazione sotto la quale viene eseguita la Reorganize. Se la percentuale di frammentazione dell'indice è superiore al valore della variabile viene eseguita la Rebuild.

DECLARE @Pages_Limits INT = 1500
DECLARE @Fragmentation_Limits TINYINT = 30
DECLARE @StrCmd VARCHAR(max) = ''

SELECT @StrCmd = @StrCmd +
 'ALTER INDEX ' + QUOTENAME(I.name) + ' ON ' + QUOTENAME(S.name) + '.' + QUOTENAME(O.name) 
 + IIF(F.avg_fragmentation_in_percent > 10 AND F.avg_fragmentation_in_percent < @Fragmentation_Limits,' REORGANIZE',' REBUILD')
 + IIF(ISNULL(P.[Partitions],0) > 1,' PARTITION = ' + CAST(F.partition_number AS VARCHAR(5)),'')+ Char(13)
FROM sys.dm_db_index_physical_stats(DB_ID() , NULL, NULL , NULL , 'LIMITED') F
 LEFT JOIN 
        (
     SELECT Object_id
     ,index_id
     ,Count(*) [Partitions]  
     FROM sys.partitions
      GROUP BY Object_id,index_id
        ) P ON F.object_id = P.object_id and F.index_id = P.index_id
 INNER JOIN sys.indexes I
  ON F.object_id = I.object_id and F.index_id = I.index_id
 INNER JOIN sys.all_objects O
  ON F.object_id = O.object_id
 INNER JOIN sys.schemas S
  ON O.schema_id = S.schema_id
WHERE I.type != 0 and F.page_count >= @Pages_Limits
ORDER BY page_count DESC

PRINT(@StrCmd)

Lo script genera il comando di ALTER INDEX per tutte le strutture coinvolte
(Nell'esempio è modificato per monitorare una tabella specifica chiamata SlowQueries")

Se si desidera eseguirlo senza preview basta aggiungere il comando di EXEC(@StrCmd)

N.B.1.
Eseguire Rebuild in ambiente di produzione può impedire l'uso della tabella/indice su cui si sta eseguendo l'operazione con ripercussioni su tutte le connessioni e le attività che tentano di accedervi !!!!!! Da fare con molta cautela !!!

N.B.2. Lo script è per SQL Server 2012.
Per renderlo compatibile con SQL Server 2005,2008,2008R2 è necessario convertire l'operatore ternario IIF in CASE WHEN.

Ciao

Luca

SQL Saturday : Milano 08-10-2013

2013-09-08T14:45:00.001+02:00

L'8 ottobre 2013 presso l'Innovation Campus di Microsoft in Via Lombardia, 2, Milano si terrà il SQL Saturday con sessioni molto interessanti e speaker italiani ed internazionali.

Qui trovate l'agenda.
Qui potete registrarvi.

Ciao

Luca

SQL Server 2014 CTP1 : In-Memory Optimized Tables, No Lock and Latch !!!

2013-09-08T14:29:00.001+02:00

Fino all'edizione 2012 il meccanismo di controllo delle transazioni faceva largo uso di Lock e di Latch, con tutti i problemi derivanti alla loro gestione
(Consumo di risorse, low performance, etc.... )
Le In-Memory Optimized Tables introdotte da SQL Server 2014 CTP1 utilizzano un meccanismo di gestione accessi concorrenziali di tipo Optimistic Multiversion,
che consente alle transazioni di essere Lock e Latch Free.

Ciò non significa assolutamente che le transazioni implementate sulle in-Memory Optimized Tables non siano
Atomic
Consistency
Isolation
Durability

Vediamo un esempio di funzionamento.

Creo un Database di test

Use Master
Go

CREATE DATABASE Hekaton_Test     
ON PRIMARY(NAME = [PRIMARY],     
   FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Hekaton_Test\Hekaton_DiskBasedTable.mdf')
   ,FILEGROUP [SampleDB_mod_fg] 
    CONTAINS MEMORY_OPTIMIZED_DATA    
     (NAME = [HKDB_mod_dir],FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Hekaton_Test\In_Memory_Table')
LOG ON (name = [SampleDB_log], Filename='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Hekaton_Test\Hekaton_Test_log.ldf')  
Go

Use Hekaton_Test
Go

Create table MemoryTable
(
 id int not null PRIMARY KEY NONCLUSTERED HASH WITH(BUCKET_COUNT = 1024)
 ,Campo char(30) not null
)WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA); 
GO

;With Cte as(Select 1 Riga union All Select 1)
 ,Cte4 as (Select A.Riga From Cte A, Cte B)
 ,Cte16 as (Select A.Riga From Cte4 A, Cte4 B)
 ,Cte256 as (Select A.Riga From Cte16 A, Cte16 B)
 ,Cte65536 as (Select A.Riga From Cte256 A, Cte256 B)
 ,Results as (Select ROW_NUMBER()Over(Order by Riga) Riga From Cte65536)
Insert into MemoryTable(id, Campo)
Select Riga
       ,Riga
from Results where Riga <= 1024

Simulo l'accesso di due connessioni alla medesima tabella, la prima eseguirà una transazione in update di tutte le righe, la seconda contemporaneamente tenterà la lettura dell'intera tabella.

Questa la transazione

USE Hekaton_Test

BEGIN TRANSACTION

UPDATE MemoryTable WITH(SNAPSHOT) SET campo = 'T1 ' + campo 

SELECT * FROM MemoryTable WITH(SNAPSHOT)

L'aggiornamento è andato a buon fine

e la select all'interno della prima connessione ha ovviamente restituito i valori modificati.

N.B. Senza WITH(SNAPSHOT) l'update e la select della prima connessione restituirebbero un errore.
Nella MSDN trovate tutte le info sull'utilizzo degli Isolation Level con le In-Memory Optimized Tables.

La prima transazione è attualmente attiva dato che il COMMIT ancora non è stato implementato.
Cosa succede se la seconda connessione tenta la lettura della tabella ?

SELECT * FROM MemoryTable

Dato che le In-Memory Optimized tables utilizzano un sistema Optimistic Multiversion non ho lock e di conseguenza no wait, quindi i dati vengono restituiti senza attesa nella loro versione pre-modifica.

N.B. Il multiversioning per le In-Memory Optimized Tables non utilizza il TempDb ma la RAM.

Ovviamente chiudendo con il COMMIT la transazione che ha implementato l'update tutte le successive letture restituiranno i dati modificati.

Ma cosa accade quando 2 transazioni tentano di modificare i medesimi dati contemporaneamente ?

Non avendo ancora effettuato il commit posso eseguire subito una nuova transazione che implementerà una modifica.

USE Hekaton_Test

BEGIN TRANSACTION

UPDATE MemoryTable WITH(SNAPSHOT) SET campo = 'T2 ' + campo 

SELECT * FROM MemoryTable WITH(SNAPSHOT)

ed ecco il risultato

In questo caso si verifica un conflitto e la transazione viene conclusa con errore.
In casi come questo è necessario ritentare la transazione.
Qui trovate le linee guida per il retry delle transazioni.
Qui trovate le linee guida sulla selezione dell'isolation level.

Ciao

Luca

SQL Server 2014 CTP1 : Natively compiled stored procedures

2013-08-21T02:46:00.001+02:00

Un'altra novità introdotta da SQL Server 2014 CTP1 sono le Natively compiled stored procedures.
Queste SP altro non sono che codice T-SQL compilato nativamente che può essere eseguito dalla CPU senza alcun passaggio intermedio, risparmiando tempo e risorse.

Dato che non sono necessari passaggi intermedi per eseguire una Natively Compiled Stored Procedure è evidente che i miglioramenti in termini di performance possono essere moltissimi.

Facciamo un esempio

Creo un database contenente una In-Memory Table

Use Master
Go

--Creo il DB
CREATE DATABASE Hekaton_Test     
ON PRIMARY(NAME = [PRIMARY],     
   FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Hekaton_Test\Hekaton_DiskBasedTable.mdf')
   ,FILEGROUP [Hekaton_InMemory_FG] 
    CONTAINS MEMORY_OPTIMIZED_DATA    
     (NAME = [Hekaton_InMemory_Dir],FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Hekaton_Test\In_Memory_Table')
LOG ON (name = [Hekaton_log], Filename='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Hekaton_Test\Hekaton_Test_log.ldf')  
Go

USE Hekaton_Test
Go

--Creo la tabella
CREATE TABLE dbo.MemoryTable
(
 id int not null PRIMARY KEY NONCLUSTERED HASH WITH(BUCKET_COUNT = 65536)
 ,campo varchar(25) COLLATE Latin1_General_100_BIN2 not null
)WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA); 
GO

--La popolo
With Cte as (Select 1 Riga union All Select 1)
 ,Cte4 as (Select A.Riga from Cte A, cte B)
 ,Cte16 as (Select A.Riga from Cte4 A, Cte4 B)
 ,Cte256 as (Select A.Riga from Cte16 A, Cte16 B)
 ,Cte65536 as (Select A.Riga from Cte256 A, Cte256 B)
 ,results as (Select Row_number() over(Order by Riga) Riga from Cte65536)
insert into MemoryTable 
Select Riga, 'Valore ' + Cast(Riga as varchar(5)) From results
GO

Creo ora una Stored Procedure tradizionale e la Natively Stored Procedure

--Creo una Stored Procedure tradizionale
CREATE PROCEDURE Dbo.SP_TSQL_MEMORY_TAB
 @id int
AS
 declare @c varchar(25)

 Select @c = A.campo from dbo.Memorytable A Where A.id = @id

 Update dbo.Memorytable Set campo = campo + 'AAAA' Where id = @id
 
 Delete from dbo.Memorytable where id = @id
 Insert into dbo.Memorytable values(@id, @c)
GO

--Creo una Natively Compiled Stored Procedure
CREATE PROCEDURE Dbo.SP_NATIVE_COMPILATION_MEMORY_TAB
 @id int not null
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(
 TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'us_english'
)
 declare @c varchar(25)

 Select @c = A.campo from dbo.Memorytable A Where A.id = @id

 Update dbo.Memorytable Set campo = campo + 'AAAA' Where id = @id
 
 Delete from dbo.Memorytable where id = @id
 Insert into dbo.Memorytable values(@id, @c)

END
GO

Escludendo alcune Keyword particolari la sintassi non è molto dissimile da quella necessaria alla creazione di SP tradizionali.

WITH NATIVE_COMPILATION : Identifica che stiamo creando una SP Natively compiled
SCHEMABINDING : E' obbligatorio che la SP sia creata con lo SCHEMABINDING
EXECUTE AS : E' obbligatorio e supporta i valori OWNER, SELF o user. CALLER non è supportato.
BEGIN ATOMIC : Identifica il corpo della SP che sarà eseguito come un unico blocco atomico.

Il blocco Atomic dovrà inoltre avere specificato anche
TRANSACTION ISOLATION LEVEL :E' obbligatorio e specifica il Transaction Isolation Level utilizzato dal body della Sp
LANGUAGE : E' obbligatorio e specifica il Language che sarà utilizzato dalla sp.

Eseguo ora la stored procedure tradizionale per ogni riga contenuta nella in-memory table e mi faccio restituire il tempo di esecuzione in millisecondi.

DECLARE @t datetime;
SET @t = GETDATE();

declare @i int = 1
While @i <= 65536
begin

 
 --Exec sp_executesql N'Dbo.SP_NATIVE_COMPILATION_MEMORY_TAB @i',N'@i int',@i=@i
 Exec sp_executesql N'Dbo.SP_TSQL_MEMORY_TAB @i',N'@i int',@i=@i
 Set @i+=1

end
select DATEDIFF( MILLISECOND , @t , current_timestamp );

Ed ecco il risultato

Ora eseguo la Natively Compiled Stored Procedure e mi faccio restituire il tempo di esecuzione in millisecondi

DECLARE @t datetime;
SET @t = GETDATE();

declare @i int = 1
While @i <= 65536
begin

 
 Exec sp_executesql N'Dbo.SP_NATIVE_COMPILATION_MEMORY_TAB @i',N'@i int',@i=@i
 --Exec sp_executesql N'Dbo.SP_TSQL_MEMORY_TAB @i',N'@i int',@i=@i
 Set @i+=1

end
select DATEDIFF( MILLISECOND , @t , current_timestamp );

Ed ecco il risultato

Passare da circa 50 secondi a meno di 20 solo compilando nativamente la stored procedure è un ottimo guadagno.
Peccato non sia così facile trasformare le Stored procedure tradizionali in Natively Compiled.
In primo luogo queste nuove SP possono accedere solo a In-Memory Table(non supportano l'accesso a Disk-Based Table) e non tutti i comandi T-SQL sono supportati.
Per esempio non è possibile farne l'alter.
Qui trovate la lista dei cosa è supportato e cosa non lo è.

Ma effettivamente, dove viene salvato il codice ?
Se guardiamo l'INFORMATION_SCHEMA.ROUTINES vediamo che entrambe le SP sono salvate come metadati nelle tabelle di sistema.

Select SPECIFIC_CATALOG
  ,SPECIFIC_SCHEMA 
  ,SPECIFIC_NAME
  ,ROUTINE_TYPE
  ,ROUTINE_DEFINITION  
from Information_Schema.ROUTINES

Se però la Natively Compiled sp fosse disponibile solo nei metadati i vantaggi in performance che abbiamo appena visto non li avremmo ottenuti.
SQL Server, all'atto della creazione della Sp, oltre ad inserirla nei metadati, crea un modulo compilato nativamente e lo carica in memoria rendendolo istantaneamente disponbile, non solo, salva anche un file .c sul disco.
Questo file contiene la definizione del modulo nativo che sql ha generato e che ha caricato in memoria.

E' possibile reperire la lista di tutti i moduli creati dagli utenti e i loro file .c con una nuova DMV, la sys.dm_os_loaded_modules.

SELECT * FROM sys.dm_os_loaded_modules
WHERE DESCRIPTION = 'XTP Native DLL'

E questo è un estratto del contenuto del file .c

struct hkt_485576768
{
 long hkc_1;
 unsigned short hkvdo[2];
};
struct hkis_48557676800002
{
 long hkc_1;
};
struct hkif_48557676800002
{
 long hkc_1;
};
unsigned short GetSerializeSize_485576768(
 struct HkRow const* hkRow)
{
 struct hkt_485576768 const* row = ((struct hkt_485576768 const*)hkRow);
 return ((row->hkvdo)[1]);
}
HRESULT Serialize_485576768(
 struct HkRow const* hkRow,
 unsigned char* buffer,
 unsigned short bufferSize,
 unsigned short* copySize)
{
 return (RowSerialize(hkRow, (GetSerializeSize_485576768(hkRow)), buffer, bufferSize, copySize));
}
HRESULT Deserialize_485576768(
 struct HkTransaction* tx,
 struct HkTable* table,
 unsigned char const* data,
 unsigned short datasize,
 struct HkRow** hkrow)
{
 return (RowDeserialize(tx, table, data, datasize, sizeof(struct hkt_485576768), (sizeof(struct hkt_485576768) + 25), hkrow));
}
unsigned short GetSerializeRecKeySize_485576768(
 struct HkRow const* hkRow)
{
 struct hkt_485576768 const* row = ((struct hkt_485576768 const*)hkRow);
 unsigned short size = sizeof(struct hkif_48557676800002);
 return size;
}

Questo file serve solo per troubleshooting dato che il modulo è già in memoria.

Attenzione : Dato che queste SP non verranno più Ricompilate se non a seguito di Restart del server, è indispensabile crearle quando la/le tabelle che andranno ad utilizzare conterranno un campione rappresentativo dei dati e le statistiche dovranno essere aggiornate.
Qui trovate maggiori dettagli.

I File risultanti dalla compilazione di queste SP non sono compresi nel backup del database perché non sono necessari.
Al momento del restore le Natively compiled Stored Procedure saranno ricompilate, dopotutto nei metadati è presente la loro definizione.

Ciao

Luca

SQL Server 2014 CTP1 : Buffer Pool Extension

2013-07-21T23:10:00.002+02:00

Le attività di read & write da e per i dischi sono le più pesanti che un RDBMS deve sostenere.
La Buffer Pool Extension di SQL 2014 alleggerisce il carico di I/O mantenendo elevato il throughput dei dischi.

SQL Server carica in un'area di memoria RAM detta Buffer Pool tutte le datapages richieste dalle nostre operazioni.
Quelle che hanno subito modifiche (dirty pages) verranno riportate sul disco con una operazione asincrona.
Maggiore è il tempo in cui le pagine risiedono nel buffer pool (page lifetime expetancy) migliori saranno le performance.
Se l'istanza di sql però soffre di memory pressure (Page lifetime molto basso) allora sarà costretta a continue operazioni di lettura dal/scrittura sul disco
delle data pages , siano esse Dirty pages o Clean pages(Pagine che NON hanno subito modifiche).

Per risolvere il problema si potevano seguire 2 strade :
1) Aggiungere RAM
2) Aggiungere dischi ad alta velocità

Ora possiamo anche scegliere la via della Buffer Pool Extension.
In pratica diciamo al buffer pool standard di utilizzare oltre alla Ram anche un file su disco SSD per contenere le datapages richieste dalle operazioni degli utenti.

Con questo comando possiamo abilitarla:

USE MASTER
GO

ALTER SERVER CONFIGURATION 
SET BUFFER POOL EXTENSION ON
    (FILENAME = 'E:\Extension.BPE', SIZE = 32 GB);

Il disco E:\ è un SSD, Extension.BPE è il file contenente l'estensione del buffer pool ed è allocato in modo statico a 32 GB.
La dimensione del file dipende dalla dimensione Max della ram associata a SQL Server.
Microsoft consiglia di rispettare un rapporto di 1:16 tra Max Memory e l'estensione del buffer pool.

D'ora in poi SQL Server utilizzerà questo file in ausilio al buffer pool in RAM.
Per onor di cronaca solo le clean pages, cioè pagine che non hanno subito modifiche, vengono scritte nella buffer pool extension.
Le dirty pages risiedono solo nel buffer pool in ram, questo rende le operazioni di checkpoint più rapide.
Lo spostamento tra il buffer pool e la sua estensione delle clean page viene eseguito dal Buffer Manager.
Questa gestione ibrida del buffer pool consente di migliorare parecchio le operazioni di lettura in ambienti a rischio di memory pressure.

Non si dovrebbe modificare la configurazione del buffer pool extension una volta attivato, soprattutto in produzione.
Disabilitarlo o rimuoverlo potrebbe penalizzare in modo drastico le performance.

Ciao

Luca

SQL Server 2014 CTP1 : Rebuild Index Online

2013-06-30T10:30:00.000+02:00

Una delle novità introdotta da SQL Server 2014 Ctp1 è quella di poter specificare il comportamento che deve intraprendere la rebuild con opzione online qualora una risorsa della tabella stessa fosse in uso da un'altra transazione.

Ricostruire gli indici è un'operazione che andrebbe effettuata in modo frequente soprattutto nei database OLTP che subiscono moltissime scritture ed allo stesso tempo moltissime letture al fine di mantenere elevato il grado delle performance nelle fasi di ricerca.
Grazie all'opzione ONLINE = ON possiamo ricostruire gli indici e anche durante questa fase assicurare alle altre connessioni una normale operatività.
Se però una transazione dovesse rimanere in esecuzione per lungo tempo la rebuild non proseguirebbe la sua attività fino a che le risorse in uso dalla transazione non torneranno disponibili.

Fino alla versione 2012 era possibile eseguire il rebuild online di una tabella senza specificare per quanto tempo l'operazione avesse dovuto attendere per accedere ad una risorsa in uso da un'altra connessione. In pratica non era possibile decidere se dare "precedenza" all'operazione di rebuild o alla query.
Inoltre la rebuild di una singola partizione di una tabella non era supportata.

Con l'attuale versione durante un'operazione di rebuild on line, qualora una transazione blocchi delle risorse nella tabella, la rebuild stessa viene sospesa in attesa che la transazione bloccante rilasci le risorse. Se la transazione, per un qualche errore dovesse rimanere appesa per un lungo periodo, la rebuild non si potrebbe completare in tempo utile.

Vediamo cosa accade

Su un'istanza SQL Server 2012 Sp1 Enterprise creo un database contenente una tabella partizionata [TestTable] e la popolo con 4 milioni di righe.

USE MASTER
GO

CREATE DATABASE TestRebuild2012 
Go 
ALTER DATABASE TestRebuild2012 
ADD FILEGROUP [PartizioneUno] 
GO 

ALTER DATABASE TestRebuild2012 
ADD FILEGROUP [PartizioneDue] 
GO 

ALTER DATABASE TestRebuild2012 
ADD FILEGROUP [PartizioneTre] 
GO 

ALTER DATABASE TestRebuild2012 
ADD FILEGROUP [PartizioneQuattro] 
GO   
ALTER DATABASE TestRebuild2012 
ADD FILE ( NAME = N'p1', FILENAME = N'E:\SQL2012_MSSQLSERVER\User Database\p1.ndf') 
TO FILEGROUP [PartizioneUno] 
GO 

ALTER DATABASE TestRebuild2012 
ADD FILE ( NAME = N'p2', FILENAME = N'E:\SQL2012_MSSQLSERVER\User Database\p2.ndf') 
TO FILEGROUP [PartizioneDue] 
GO 

ALTER DATABASE TestRebuild2012 
ADD FILE ( NAME = N'p3', FILENAME = N'E:\SQL2012_MSSQLSERVER\User Database\p3.ndf') 
TO FILEGROUP [PartizioneTre] 
GO 

ALTER DATABASE TestRebuild2012 
ADD FILE ( NAME = N'p4', FILENAME = N'E:\SQL2012_MSSQLSERVER\User Database\p4.ndf') 
TO FILEGROUP [PartizioneQuattro] 
GO   

USE TestRebuild2012 
Go 


CREATE PARTITION FUNCTION testPF (int)
AS RANGE LEFT FOR VALUES (1000000, 2000000, 3000000);
Go

CREATE PARTITION SCHEME testScheme
AS PARTITION testPF
TO ( [PartizioneUno],[PartizioneDue],[PartizioneTre],[PartizioneQuattro] );
Go

CREATE TABLE TestTable
(
 id INT
 ,campo VARCHAR(25)
 ,valore VARCHAR(25)
)
ON testScheme(id)

CREATE CLUSTERED INDEX idxClustered on TestTable(id) 


;WITH Cte as (SELECT 1 Riga UNION ALL SELECT 1)
 ,Cte4 as (SELECT A.Riga FROM Cte A, Cte B)
 ,Cte16 as (SELECT A.Riga FROM Cte4 A, Cte4 B)
 ,Cte256 as (SELECT A.Riga FROM Cte16 A, Cte16 B)
 ,Cte65536 as (SELECT A.Riga FROM Cte256 A, Cte256 B)
 ,CteBig as (SELECT A.Riga FROM Cte65536 A, Cte65536 B)
 ,Results as (SELECT ROW_NUMBER()OVER(ORDER BY Riga) Riga FROM CteBig)
INSERT INTO TestTable
SELECT TOP 4000000 
 Riga
 ,'Campo ' + Cast(Riga AS VARCHAR(10)) 
 ,'valore ' + Cast(Riga AS VARCHAR(10)) 
FROM Results

Apro una connessione ed eseguo la rebuild online dell'indice clustered, cioè della tabella.

ALTER INDEX idxClustered ON TestTable
REBUILD 
WITH (ONLINE = ON)

Da un'altra connessione eseguo la modifica di una sola riga ma non ne faccio il commit (In questo modo mantengo per lungo tempo i Lock sulla risorsa)

BEGIN TRANSACTION
UPDATE TestTable SET Valore = 'Mod ' + Valore
 WHERE id = 1500000

L'activity monitor mostra cosa sta accadendo....

Come potete vedere la rebuild è in stato SUSPENDED, sta attendendo di accedere alla risora in uso dall'update.
Se eseguo il COMMIT o la ROLLBACK dell'operazione di update la rebuild entra in RUNNING e può essere completata.

Con SQL Server 2014 CTP1 è stata introdotta la possibilità di specificare come un'operazione di rebuild online di un indice si deve comportare qualora un'altra transazione stia elaborando una o più righe della tabella stessa.

Su un'istanza SQL Server 2014 CTP1 creo un database contenente una tabella partizionata [TestTable] e la popolo con 4 milioni di righe.
N.B. Il Database ha la stessa identica struttura del suo gemello in versione SQL 2012. Cambia solo il nome ed il path dei file.

CREATE DATABASE TestRebuild2014 
Go 

ALTER DATABASE TestRebuild2014 
ADD FILEGROUP [PartizioneUno] 
GO 

ALTER DATABASE TestRebuild2014 
ADD FILEGROUP [PartizioneDue] 
GO 

ALTER DATABASE TestRebuild2014 
ADD FILEGROUP [PartizioneTre] 
GO 

ALTER DATABASE TestRebuild2014 
ADD FILEGROUP [PartizioneQuattro] 
GO   

ALTER DATABASE TestRebuild2014 
ADD FILE ( NAME = N'p1', FILENAME = N'E:\Data\p1.ndf') 
TO FILEGROUP [PartizioneUno] 
GO 

ALTER DATABASE TestRebuild2014 
ADD FILE ( NAME = N'p2', FILENAME = N'E:\Data\p2.ndf') 
TO FILEGROUP [PartizioneDue] 
GO 

ALTER DATABASE TestRebuild2014 
ADD FILE ( NAME = N'p3', FILENAME = N'E:\Data\p3.ndf') 
TO FILEGROUP [PartizioneTre] 
GO 

ALTER DATABASE TestRebuild2014 
ADD FILE ( NAME = N'p4', FILENAME = N'E:\Data\p4.ndf') 
TO FILEGROUP [PartizioneQuattro] 
GO   

USE TestRebuild2014 
Go 


CREATE PARTITION FUNCTION testPF (int)
AS RANGE LEFT FOR VALUES (1000000, 2000000, 3000000);
Go

CREATE PARTITION SCHEME testScheme
AS PARTITION testPF
TO ( [PartizioneUno],[PartizioneDue],[PartizioneTre],[PartizioneQuattro] );
Go

CREATE TABLE TestTable
(
 id INT
 ,campo VARCHAR(25)
 ,valore VARCHAR(25)
)
ON testScheme(id)

CREATE CLUSTERED INDEX idxClustered on TestTable(id) 


;WITH Cte as (SELECT 1 Riga UNION ALL SELECT 1)
 ,Cte4 as (SELECT A.Riga FROM Cte A, Cte B)
 ,Cte16 as (SELECT A.Riga FROM Cte4 A, Cte4 B)
 ,Cte256 as (SELECT A.Riga FROM Cte16 A, Cte16 B)
 ,Cte65536 as (SELECT A.Riga FROM Cte256 A, Cte256 B)
 ,CteBig as (SELECT A.Riga FROM Cte65536 A, Cte65536 B)
 ,Results as (SELECT ROW_NUMBER()OVER(ORDER BY Riga) Riga FROM CteBig)
INSERT INTO TestTable
SELECT TOP 4000000 
 Riga
 ,'Campo ' + Cast(Riga AS VARCHAR(10)) 
 ,'valore ' + Cast(Riga AS VARCHAR(10)) 
FROM Results

Provo ad eseguire il rebuild online dell'intera tabella.

ALTER INDEX idxClustered ON TestTable
REBUILD 
WITH (
  ONLINE = ON 
  (
   WAIT_AT_LOW_PRIORITY 
    (MAX_DURATION = 1
    , ABORT_AFTER_WAIT = BLOCKERS)
  )
 )

La sintassi prevede delle nuove opzioni :
MAX_DURATION : Tempo massimo (in minuti) d'attesa per accedere alla risorsa.
ABORT_AFTER_WAIT : Al termine del periodo di attesa specificato in MAX_DURATION viene eseguito il Kill dell'operazione bloccante se il parametro è BLOCKERS o della rebuild stessa se il parametro è SELF.

Apro una connessione ed eseguo la modifica di una sola riga ma non ne faccio il commit (In questo modo mantengo per lungo tempo i Lock sulla risorsa)

BEGIN TRANSACTION
UPDATE TestTable SET Valore = 'Mod ' + Valore
WHERE id = 1500000

Come si comporta quindi l'engine ?
Inizialmente esattamente come se ci trovassimo in SQL Server 2012, la rebuild viene messa in suspend....

ma dato che abbiamo specificato il parametro MAX_DURATION = 1 la rebuild attende per il tempo max specificato, nel nostro esempio un minuto, scaduto il quale implementa il Kill della/delle transazioni bloccanti. In questo modo la rebuild potrà continuare le sue attività.

Cosa succede alla transazione dell'update se tento di dare il commit ?

Dato che la rebuild ha fatto il Kill della connessione riceve un'errore.
(Automaticamente l'engine ha inoltre implementato la rollback dell'update)

Se avessi specificato il parametro ABORT_AFTER_WAIT = SELF il comportamento sarebbe stato l'opposto, la transazione avrebbe avuto priorità e l'engine avrebbe effettuato la kill della rebuild.

Dato che la tabella è stata creata come partizionata potrei voler effettuare il rebuild online di una sola partizione ma......
L'operazione di rebuild di una sola partizione con Sql Server 2012 non è supportata.

Mentre in Sql Server 2014 CTP1 è possibile fare il rebuild online di una sola partizione

ALTER INDEX idxClustered ON TestTable
REBUILD PARTITION = 2
WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1, ABORT_AFTER_WAIT = BLOCKERS)))

Se non vengono specificate le opzioni MAX_DURATION e ABORT_AFTER_WAIT il comportamento implementato è quello descritto per SQL Server 2012.

Ciao

Luca

Sql Server 2014 CTP1 : Un vero In-Memory Oltp Database management System

2013-06-26T02:47:00.002+02:00

Oggi è uscito SQL Server 2014 CTP1 !!!
Era necessario visto che SQL 2012 è poco più di anno che è uscito in RTM ? Cioè... ancora non abbiamo apprezzato tutte le sue potenzialità che Microsoft ha pensato bene di rilasciare, seppur in CTP il suo successore.....
Che cosa avranno mai sviluppato per giustificare un così repentino rilascio ?!?!?
Una cosa su tutte.... Hekaton, o nel suo nome definitivo In-Memory OLTP !!!!

In-Memory OLTP è tecnologia tutta nuova che anche grazie alla diffusione dei sistemi HW a 64 bit, consente di sfruttare in modo impressionante la RAM della macchina, migliorando di 10x,o addirittura 100x le performance dei nostri processi sfruttando quelle che sono definite "In-Memory optimized Tables" e le "Natively compiled stored procedures".

Le In-Memory Optimized Table non sono altro che delle tabelle che risiedono interamente in memoria con i loro indici.

Le Natively compiled stored procedures sono degli oggetti compilati in "Machine Code" che di conseguenza potrebbero ulteriormente migliorare le perfomance nel process dei dati . Questi oggetti li affronterò in un post successivo.

Ovviamente ciò non significa che questi nuovi oggetti siano la panacea per tutti i mali dei nostri DB....
In-Memory OLTP è una tecnologia che va sfruttata con raziocinio per poter ottenere il top delle performance senza "sprechi" inutili di RAM.

Ovviamente non sono utilizzabili su piattaforme 32 bit....

Fino a SQL Server 2012 l'utilizzo della RAM da parte dell'engine era esclusivamente fatto per la gestione dei dati in fase di processo sotto forma di datapage. Mano a mano che essi "invecchiavano" venivano scaricati senza troppi riguardi dalla RAM.... Se poi una query ne avesse nuovamente fatto esplicita richiesta essi sarebbero stati riletti dal disco, con ovvie ripercussioni sulle performance dovute alle problematiche di accesso allo storage, di sincronizzazione delle transazioni, di allocazione della RAM, di utilizzo della CPU etc... Il tutto si traduce in performance non sempre all'altezza soprattutto in sistemi non particolarmente dotati in quanto a memoria ma che subiscono moltissime transazioni contemporanee.

Con SQL Server 2012 sono stati inoltre introdotti i ColumnStore Index e xVelocity che hanno migliorato le performance in modo incredibile semplicemente sfruttando in modo più intensivo la RAM.
Column Store Index ed xVelocity hanno mostrato in minima parte quale vantaggio comporta un migliore utilizzo della RAM, seppur con qualche limitazione(Per esempio una tabella dotata di columnstore index non poteva più subire scritture).

Ok bello.... Ma come funzionano le In-Memory Optimized Table ?
Partiamo dal fatto che NON funzionano come le tabelle tradizionali che possiamo d'ora in poi definire Disk-based tables. A differenza di queste ultime, le In-Memory Tables non hanno la classica allocazione basata su datapages così come gli indici non hanno la classica struttura B-Tree. Sono strutturalmente concepite in modo totalmente differente. Queste tabelle mantengono costantemente la loro struttura ed i dati in RAM. La loro creazione ha una sintassi leggermente differente rispetto a quella delle tabelle tradizionali.

Il vantaggio prestazionale è dato dal fatto che l'engine non ha la necessità di leggere le datapages contenenti i dati dal disco e di posizionarle nella buffer cache perché i dati sono SEMPRE presenti in ram. Ogni operazione su queste tabelle è implementata in RAM. Queste tabelle sono poi corredate di un'insieme di file di Checkpoint che sì risiedono sul disco, ma sono utilizzati solo ed esclusivamente nelle fasi di recovery. Questi file risiedono in un filestream filegroup
N.B. Non è necessario abilitare la feature del Filestream dal Configuration Manager per poter sfruttare le In-Memory Tables.

Il Database deve essere quindi creato specificando un apposito Filegroup repository dei file di checkpoint.

Use Master
Go

CREATE DATABASE Hekaton_Test     
ON PRIMARY(NAME = [PRIMARY],     
   FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Hekaton_Test\Hekaton_DiskBasedTable.mdf')
   ,FILEGROUP [Hekaton_InMemory_FG] 
    CONTAINS MEMORY_OPTIMIZED_DATA    
     (NAME = [Hekaton_InMemory_Dir],FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Hekaton_Test\In_Memory_Table')
LOG ON (name = [Hekaton_log], Filename='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Hekaton_Test\Hekaton_Test_log.ldf')  
Go

L'opzione CONTAINS MEMORY_OPTIMIZED_DATA identifica il/i filegroup che conterranno i file di checkpoint.

Prima di creare la tabella In-Memory è bene sapere che NON tutti i tipi dato sono supportati.
I tipi di dati supportati sono :
bit
inyint
smallint
int
bigint
money
smallmoney
float
real
datetime
smalldatetime
datetime2
date
time
char(n)
varchar(n)
nchar(n)
nvarchar(n)
sysname
binary(n)
varbinary(n)
Uniqueidentifier

Non sono supportati i tipi di dati LOB, Xml, CLR e il (max).

Una riga non deve assolutamente superare la lunghezza di 8060 byte. Non sono previste ROW_OVERFLOW Allocation Unit.
La tabella deve avere almeno un indice e non può averne più di 8.
Tutte le tabelle, escludendo quelle create con l'opzione SCHEMA_ONLY devono avere una Primary Key definita.
Tutti gli indici necessari devono essere creati al momento della create della tabella.
In questo momento sono supportate le sole Windows Collate BIN2.

Non è possibile definire Foreign Key, Check, DML Trigger, Unique Index oltre alla Pk, identity....
Non posso modificarne lo schema e non posso aggiungere indici dopo la creazione della tabella.

Se per ottenere delle performance eccezionali siamo disposti a rinunciare a quanto detto allora possiamo procedere, altrimenti meglio optare per le tabelle tradizionali.

Creo la tabella In-Memory Table

Use Hekaton_Test  
Go
Create table MemoryTable
(
 id int not null PRIMARY KEY NONCLUSTERED HASH WITH(BUCKET_COUNT = 5000000)
 ,Nome varchar(25) COLLATE Latin1_General_100_BIN2 not null
 ,Cognome varchar(50) COLLATE Latin1_General_100_BIN2 not null
 ,Indirizzo varchar(50) COLLATE Latin1_General_100_BIN2
 INDEX inMemoryIdx NONCLUSTERED HASH (Cognome,Nome) WITH(BUCKET_COUNT = 5000000) 
)WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY); 
GO

Come vedete la sintassi differisce di poco dal comando DDL tradizionale.
Ho il solito Create table seguito dalla definizione dei campi.
NONCLUSTERED HASH WITH(BUCKET_COUNT = 1024) identifica il tipo di indice che vado a creare.

La CTP1 supporta solo indici HASH (Nel Blog di Kalen Delaney trovate un'ottima descrizione di come funzionano)
Detto per sommicapi... Un Hash index altro non è che un array di puntatori che consentono di accedere alle righe della tabella grazie ad una funzione di hash sulla chiave dell'indice stesso, ogni elemento dell'array è chiamato Hash Bucket. E' indispensabile selezionare un BUCKET_COUNT adeguato alla cardinalità attesa dei dati contenuti nella tabella. Come spiegato bene da Kalen Delaney un valore sottostimato causa la crescita della row chain che diminuisce le performance, un valore troppo elevato spreca memoria.

Se non si conosce la cardinalità è bene ripiegare su Range index che saranno disponibili dalla CTP2.

L'opzione MEMORY_OPTIMIZED = ON identifica che la tabella è di tipo in-memory
L'opzione DURABILITY può assumere 2 valori :
1) SCHEMA_AND_DATA = Significa che la definizione della tabella ed i suoi dati dovranno SEMPRE risiedere in memoria ed in caso di restart del server essi dovranno essere ricostruiti partendo dai file di checkpoint e dal T-Log
2) SCHEMA_ONLY = Le modifiche ai dati non vengono loggate, ciò significa che a seguito del shutdown dell'istanza i dati eventualmente contenuti saranno irrimediabilmente persi. Il restart dell'istanza comporta la ricreazione della tabella totalmente priva di dati. Questa modalità la trovo utile per aumentare ulteriormente le performance durante le fasi di ETL che necessitano di tabelle di staging.

Per fare un raffronto di performance creo la medesima tabella anche in modo tradizionale

Use Hekaton_Test  
Go
Create Table DiskTable
(
 id int Primary Key Clustered
 ,Nome varchar(25)
 ,Cognome varchar(50)
 ,Indirizzo varchar(50)
 INDEX OnDiskIdx NONCLUSTERED (Cognome,Nome)
)
ON [PRIMARY]
GO

Popolo Entrambe le tabelle con 5000000 di record

Use Hekaton_Test  
Go

--Tabella Disk Based

Set statistics Io On
Set statistics Time On
;With Cte as(Select 1 Riga union All Select 1)
 ,Cte4 as (Select A.Riga From Cte A, Cte B)
 ,Cte16 as (Select A.Riga From Cte4 A, Cte4 B)
 ,Cte256 as (Select A.Riga From Cte16 A, Cte16 B)
 ,Cte65536 as (Select A.Riga From Cte256 A, Cte256 B)
 ,CteMany as (Select A.Riga From Cte65536 A, Cte65536 B)
 ,Results as (Select ROW_NUMBER()Over(Order by Riga) Riga From CteMany)
Insert into DiskTable(id, nome, Cognome, indirizzo)
Select Riga
  , 'Nome ' + Cast(Riga as char(8)) 
  , 'Cognome ' + Cast(Riga as char(8))     
  , 'Indirizzo ' + Cast(Riga as char(8)) 
from Results where Riga <= 5000000
Set statistics Io Off
Set statistics Time Off

--Tabella In-Memory

Set statistics Io on
Set statistics Time on

;With Cte as(Select 1 Riga union All Select 1)
 ,Cte4 as (Select A.Riga From Cte A, Cte B)
 ,Cte16 as (Select A.Riga From Cte4 A, Cte4 B)
 ,Cte256 as (Select A.Riga From Cte16 A, Cte16 B)
 ,Cte65536 as (Select A.Riga From Cte256 A, Cte256 B)
 ,CteMany as (Select A.Riga From Cte65536 A, Cte65536 B)
 ,Results as (Select ROW_NUMBER()Over(Order by Riga) Riga From CteMany)
Insert into MemoryTable(id, nome, Cognome, indirizzo)
Select Riga
  , 'Nome ' + Cast(Riga as char(8)) 
  , 'Cognome ' + Cast(Riga as char(8))     
  , 'Indirizzo ' + Cast(Riga as char(8)) 
from Results where Riga <= 5000000
Set statistics Io Off
Set statistics Time Off
GO

Ecco qui i risultati

Come potete vedere per inserire 5000000 Record la tabella tradizionale ha impiegato 2 minuti e mi restituisce il numero di datapages coinvolte dall'operazione. Tengo a precisare che sto utilizzando un disco SSD per la virtual Machine, quindi le performance di I/O sono ottime anche per la disk-based Table

L'inserimento nella tabella In-Memory non restituisce nessun valore relativo all'I/O dato che NON utilizza l'allocazione tradizionale a datapages e come tempo d'esecuzione restituisce 12 secondi......

Non c'è che dire.... Un guadagno notevolissimo in termini di performance !!! Dopotutto la Ram è sempre molto più veloce del disco....
Anche di un disco SSD ;-)

Direi che la tecnologia In-Memory sia davvero il nuovo punto di svolta per ottenere performance sempre migliori.

Ciao

Luca

Windows Server R2 e SQL Server 2014 Ctp1

2013-06-25T21:01:00.002+02:00

Qui potete fare il download della CTP1 di SQL Server 2014
Qui potete scaricare la preview di Windows Server 2012 R2

Ciao

Luca

WPC 2013

2013-06-19T14:32:00.000+02:00

Sono aperte le iscrizioni alla WPC 2013, la più importante conference italiana per quanto riguarda le tecnologie Microsoft !!!

Qui trovate tutte le info...

Ciao

Luca

SQL Server 2014 !!!!

2013-06-19T13:50:00.002+02:00

Non abbiamo ancora sfruttato appieno le potenzialità di SQL Server 2012 che è già in preparazione la release della Ctp di SQL Server 2014.....

Qui il link ufficiale...
Qui trovate ulteriori info sulle novità della nuova edizione.....
Qui potete iscrivervi per ricevere la notifica della Ctp che dovrebbe uscire a breve....

Ciao

Luca

SQL Server 2012 : Viste, problemi e soluzioni....

2013-05-26T02:03:00.000+02:00

Comodissime le viste, non c'è che dire, salvo il caso in cui vadano in errore, non ci restituiscano tutti i campi che ci aspetteremmo o peggio ancora, ci restituiscano campi con valori incoerenti.
Se create correttamente sono un ausilio perfetto per reperire i dati in modo semplice e performante.... Sempre che si mettano in opera alcuni semplicissimi accorgimenti.

Creo un db di esempio

Use Master
Go

create database TestMetaData
Go 

Use TestMetaData
Go


Create table TestTable1
(
 T1id int
 ,T1campo1 varchar(25)
 ,T1campo2 varchar(25)
 ,T1campo3 varchar(25)
)

Create table TestTable2
(
 T2id int
 ,T2campo1 varchar(25)
 ,T2campo2 varchar(25)
 ,T2campo3 varchar(25)
)


;with cte as (Select 1 Riga union all Select 1)
 ,cte4 as (select A.Riga from cte A, Cte B)
 ,results as (select Row_number() Over(Order by Riga)Riga from cte4)
Insert into TestTable1 
Select 
 Riga
 ,'Campo1 - ' + Cast(Riga as varchar(5)) 
 ,'Campo2 - ' + Cast(Riga as varchar(5))
 ,'Campo3 - ' + Cast(Riga as varchar(5))
from results

;with cte as (Select 1 Riga union all Select 1)
 ,cte4 as (select A.Riga from cte A, Cte B)
 ,results as (select Row_number() Over(Order by Riga)Riga from cte4)
Insert into TestTable2 
Select 
 Riga
 ,'Campo1 - ' + Cast(Riga as varchar(5)) 
 ,'Campo2 - ' + Cast(Riga as varchar(5))
 ,'Campo3 - ' + Cast(Riga as varchar(5))
from results

Create View TestView
as
Select * from dbo.TestTable1
Go

Caso 1 : La vista va in errore dopo avere modificato una tabella

Creo ora una vista denominata TestViewDropColumn che restituisce tutte le colonne e tutte le righe della tabella TestTable1

Create View TestViewDropColumn
as
Select * from dbo.TestTable1
Go

Come vedete l'ho creata con una Select *, mi aspetto quindi che tutte le colonne della tabella vengano restituite dalla vista, ed in effetti è così...

Cosa succede se elimino una colonna dalla tabella TestTable1 dopo aver creato la vista e faccio una query sulla vista stessa?

Alter table dbo.TestTable1 drop column T1campo3
GO
Select * from TestViewDropColumn

Succede che la vista va in errore.....

Caso 2 : La vista non restituisce tutte le colonne contenute in una tabella

Creo una vista sulla tabella dbo.TestTable2

Create View TestViewAddColumn
as
Select * from dbo.TestTable2
Go

Anche in questo caso la vista è creata con Select *.
Aggiungo ora una colonna di tipo int e autoincrementate alla tabella dbo.TestTable2

Alter table dbo.TestTable1 add NewField int identity

Vediamo cosa succede se eseguo una select sulla vista e poi sulla tabella

In questo caso la vista non restituisce la nuova colonna mentre la seconda select, direttamente sulla tabella, funziona alla perfezione.....

Caso 3 : La vista restituisce dati incongruenti

In questo caso creo una vista contenente una join tra dbo.TestTable1 e dbo.TestTable2

Create View TestViewJoin
as
Select T1.*, T2.* 
 from dbo.TestTable1 T1
 inner join dbo.TestTable2 T2 on T1.T1id = T2.T2id 
Go

Aggiungo una nuova colonna alla tabella dbo.TestTable1

Alter table dbo.TestTable1 add ComputedColumn as (T1campo1 + T1Campo2)

Eseguo la vista

Select * from TestViewJoin

ed ecco il risultato

Come vedete la vista non da errore ma restituisce i valori in modo totalmente errato.
Questo secondo me è il caso peggiore perché rischia di compromettere in modo devastante e totalmente imprevedibile le applicazioni che fanno riferimento alla vista.
Il valore "Campo1 - 4Campo2 - 4" è il valore relativo alla colonna "ComputedColumn" aggiunta alla tabella dbo.TestTable1, non è di certo il valore della colonna T2id della tabella dbo.TestTable2.

Come facciamo a porre rimedio ai problemi ?

In modo semplice, richiamando la stored procedure di sistema sp_refreshview che aggiorna i metadati della vista specificata.
Nel mio caso devo aggiornare tutte 3 le viste create per risolvere i problemi

Exec sp_refreshview TestViewDropColumn
Exec sp_refreshview TestViewAddColumn
Exec sp_refreshview TestViewJoin

Se rieseguo le select dalle viste tutto funzionerà in modo corretto

Select * from TestViewDropColumn
Select * from TestViewAddColumn
Select * from TestViewJoin

Come vedete ora le tre viste restituiscono i risultati aspettati.

Per evitare che i casi d'esempio si verifichino abbiamo 3 strade:

1. Eseguire una ALTER VIEW a seguito di aggiornamenti delle tabelle referenziate
2. Eseguire un refresh con la sp_refreshview a seguito di aggiornamenti delle tabelle referenziate
3. Creare le viste con l'opzione SCHEMABINDING che impedisce qualunque modifica strutturale alle tabelle referenziate dalla vista.

Ciao

Luca

SQL Server Express Edition : Schedulare jobs

2013-03-21T17:32:00.004+01:00

Ok, SQL Server Express non ha il SQL Server Agent, ma questo non è una buona scusa per non implementare backup e attività di manutenzione regolari dei Database.

Anche senza SQL Server Agent è possibile, con un pizzico di lavoro in più, schedulare tutte le attività necessarie al mantenimento dei DB.
Come ? Usando lo Schedule Task del sistema operativo e un poco di sano T-SQL !!

Immaginiamo di voler fare un backup ed il rebuild degli indici del Db collegato a Sql Express

Creo un DB di test di cui effettuare la manutenzione

Use Master
Go

Create database TestJob
Go

Use TestJob
Go


create table TestTab
(
 id int
 ,campo varchar(25)
)

create clustered index idx on TestTab(id)
Go

;with cte as (Select 1 Riga union all Select 1)
 ,cte4 as (Select A.Riga From cte A, cte B)
 ,cte16 as (Select A.Riga From cte4 A, cte4 B)
 ,cte256 as(Select A.Riga From cte16 A, cte16 B)
 ,cte65536 as(Select A.Riga From cte256 A, cte256 B)
 ,results as(Select Row_number()Over(Order By Riga)Riga from cte65536)
Insert into TestTab 
Select Riga,riga from Results
Go 2

Verifichiamo subito se c'è frammentazione per avere un raffronto post manutenzione.

Use TestJob
Go
select * from sys.dm_db_index_physical_stats(db_id(),null,null,null,'Detailed')
Go

Ok.... Frammentazione al 98% nella tabella...

Mi serve poi un DB di Log all'interno del quale salvare gli esiti delle operazioni pianificate.

Use Master
Go

create database LogJob
Go

Use LogJob
Go

Create table TabLogJob
(
 Data datetime default getdate()
 ,Errore bit
 ,Messaggio varchar(max)
)
Go

Preparo uno Script di backup e di Rebuild degli indici.
Ovviamente il backup deve essere implementato post Check d'integrità del database.
Quindi gli step sono
1: Check Integrity
2: Backup
3: Rebuild

In caso d'errore log nella tabella TabLogJob del DB LogJob

Use TestJob
Go

CREATE TABLE #DBCC_CHECKDB 
(   
 Error int NULL,
 [Level] int NULL,
 State int NULL,
 MessageText nvarchar(2048) NULL,
 RepairLevel nvarchar(22) NULL,
 Status int NULL,
 DbId int NULL,
 DbFragId int NULL,
 ObjectId int NULL,
 IndexId int NULL,
 PartitionId bigint NULL,
 AllocUnitId bigint NULL,
 RidDbId smallint NULL,
 RidPruId smallint NULL,
 [File] smallint NULL,
 Page int NULL,
 Slot int NULL,
 RefDbId smallint NULL,
 RefPruId smallint NULL,
 RefFile smallint NULL,
 RefPage int NULL,
 RefSlot int NULL,
 Allocation smallint NULL 
)    

INSERT INTO #DBCC_CHECKDB 
(
 Error,
 [Level], 
 State,
 MessageText,
 RepairLevel,
 Status,
 DbId,
 DbFragId,
 ObjectId,
 IndexId,
 PartitionId,
 AllocUnitId,
 RidDbId,
 RidPruId,
 [File],
 Page,
 Slot,
 RefDbId,
 RefPruId,
 RefFile,
 RefPage,
 RefSlot,
 Allocation
) 
EXEC('DBCC CHECKDB(TestJob) WITH TABLERESULTS, ALL_ERRORMSGS ')   
If ( Select count(*) from #DBCC_CHECKDB Where Error >= 8000 and level >= 16 ) = 0  
Begin 
 
 begin try

  backup Database TestJob to disk = 'C:\SQL 2012\Backup\Full.bak'
  Insert into LogJob.dbo.TabLogJob (Errore,Messaggio)
          values(0,'Backup Effettuato Correttamente')

  exec sp_MSforeachtable 'ALTER INDEX ALL ON ? REBUILD'
  Insert into LogJob.dbo.TabLogJob (Errore,Messaggio)
   values(0,'Rebuild Effettuata Correttamente')

 end try
 begin catch

         Insert into LogJob.dbo.TabLogJob (Errore,Messaggio)
   Select 1,ERROR_MESSAGE()

 end catch
end
Else 
begin
 
 Insert into LogJob.dbo.TabLogJob (Errore,Messaggio)
 Select 1,MessageText from #DBCC_CHECKDB Where Error >= 8000 and level >= 16
 
end 
 
Drop table #DBCC_CHECKDB

Salvo lo script e mi preparo ad eseguirlo con l'utility sqlcmd.exe.
Creo un file Batch contenente il seguente comando :

sqlcmd.exe -Slocalhost\SqlExpress -E -i"C:\SQL 2012\Backup\Maintenance.sql"

Provo il file e verifico se la frammentazione è calata e se nella tabella di Log è presente l'avvenuta esecuzione

Use TestJob
Go
select * from sys.dm_db_index_physical_stats(db_id(),null,null,null,'Detailed')
Go

Frammentazione inesistente....

Verifico il Backup

Backup effettuato....

Verifico il Log

Logging corretto !!!

Perfetto !! Funziona tutto....

Ora non mi resta che schedularlo per essere eseguito ogni giorno alle 16:45

Step1 Creo il Task

Step2 Tipo di schedulazione

Step3 Seleziono l'attività

Step4 Seleziono il batch

Step5 Imposto l'ora di esecuzione

Step6 Finish con apertura eventuale delle proprietà per i setting relativi alla Security

Non mi resta che attendere le 16 e 45 per vedere se ha funzionato....

tic tac... tic tac.... ;-)

Ok... 16 e 45... Vediamo cosa è successo....

Ok, backup effettuato in append al file preesistente.....

Verifico il Log

Log ok...

Benissimo... I nostri DB ora saranno contenti !!!

Immaginiamo però che a causa di errori del disco vi sia una datapage corrotta.
Ne danneggio una a caso....

Alter Database TestJob set single_user with rollback immediate 
Go 
Dbcc writepage(TestJob,1,125,1,1,0x66,1) 
Go 
Alter Database TestJob set multi_user 
Go

Cosa accade se parte il batch ?
bhe... Parte la Dbcc checkdb() che restituisce errore,il backup e la rebuild non vengono implementati, e viene eseguito il log.

Con poco sforzo si può modificare lo script in modo che crei un file di backup giornaliero e si può modificare il batch in modo che elimini i backup che non si desidera più mantenere on line....

OVVIAMENTE FARE IL BACKUP SULLA STESSA MACCHINA COME NEL MIO ESEMPIO NON E' UNA BEST PRACTICE.
FARE IL BACKUP SULLA STESSA MACCHINA = NON FARE IL BACKUP !!!!

Ciao

Luca

SQL Server 2012 : LocalDB

2013-03-07T18:06:00.001+01:00

Si sa dai....
Ai Dev non piace assolutamente configurare e manutenere un RDBMS... :-)
SQL Server 2012 LocalDb esaudisce i desideri degli sviluppatori...
Perché installare SQL Server Express per avere un servizio RDBMS se poi l'utilizzo che ne si fa è saltuario e molto limitato ?

LocalDb è una modalità particolare d'esecuzione di SQL Server Express rivolta agli sviluppatori per le attività di programmazione e debug ma che potrebbe essere anche utilizzata da applicazioni single user che necessitano di una base dati per poter funzionare.
Il vantaggio rispetto alla edizione SQL Server Express ??
Bhè... L'utilizzatore non deve preoccuparsi dell'avvio del servizio, della configurazione etc...

Se si sviluppa utilizzando Visual Studio 2012 viene installata automaticamente, altrimenti è necessario scaricare il setup ed eseguirne l'installazione.

Il Database poi sarà un vero e proprio DB Sql Server a tutti gli effetti. Sarà composto da file dati e file Log, potremo usare viste, stored procedure, functions, trigger etc...

Ma le applicazioni come fanno a connettersi al Db se non ho a disposizione un'istanza Express ?
Quando l'applicazione che necessita dei dati invoca una connessione viene avviato un processo di SQL Server LocalDb che farà da "gestore" per il Db.
Quando poi l'applicazione chiamante sarà chiusa dall'utente, automaticamente, dopo qualche minuto verrà terminato anche il processo di SQL Server LocalDb.

Vediamo un esempio.

Utilizzando Visual Studio 2012 genero un nuovo progetto Windows form.

Ho bisogno ora di creare un Db a cui la mia applicazione possa fare riferimento, quindi:
View->Sql Server Object Explorer

Il mio progetto ha già a disposizione un'istanza su cui appoggiarsi per reperire i dati del Db.
L'istanza si chiama (localdb)\Projetcs e viene creata con il setup di Visual Studio 2012 SSDT.

Ora devo creare il Db, Tasto Dx sulla folder Databases e Add New Database

Devo solo specificare il nome e la locazione dei file *.mdf e *.ldf

Ora devo generare la struttura delle tabelle, delle view, delle stored procedure etc... all'interno del Db.
Tasto Dx su Tables e Add New Table

Una volta definita la struttura devo fare l'Upload dell'oggetto nel DB (tasto Upload in alto a sx) e mi appare una finestra di preview delle modifiche.

Confermiamo l'update e dell'oggetto viene fatto il deploy nel DB.

Possiamo anche inserire dei dati nella tabella appena creata
Tasto Dx sulla tabella Customers e View Data

Il nostro Db è ora pronto all'uso !!!

Se proprio si vogliono fare le cose in modo preciso sarebbe stato ottimale utilizzare un progetto di tipo SQL Server Database Project in modo da poter gestire il versioning degli oggetti etc...

Ora possiamo occuparci dell'applicazione Windows Form che dovrà leggere ed aggiornare i dati della tabella Customers.
Il codice dell'applicazione lo potete trovare qui. E' un esempio della msdn.

L'unica modifica che ho apportato è il salvataggio della connectionstring nell'app.config dell'applicazione.
Per il mio progetto in fase di debug sarà :

E qui dove la passo all'applicazione per connettermi al DB e mostrare i dati

String connectionString = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"].ToString();

Proviamo ad eseguirla ed a leggere i dati dal nostro Db agganciato all'istanza LocalDb\Projects

Inserisco una riga

Perfetto... Funziona...
Ora la devo poter utilizzare su un'altra macchina che non dispone ne di SQL Server ne di Visual Studio.
Come dicevo prima è necessario scaricare il package di setup del localDb ed eseguirlo sul client di destinazione

Il secondo step è copiare l'applicazione, i file del Db sul client ed infine configurare l'app.config facendo in modo che utilizzi l'istanza LocalDb

Come vedete si fa riferimento all'istanza (Localdb)\v11.0 e viene anche fornito il path al file mdf.
In questo modo viene avviato il processo di SQL Server LocalDb e viene creato il DB ApplicationDb agganciando il file mdf specificato.

Eseguo l'applicazione.

Avendo copiato il db dalla macchina di sviluppo ho a disposizione anche le righe inserite a scopo di test.

Ne inserisco una nuova

Sul Client, quando si apre la connessione al Db viene attivato un processo di sqlservr.exe

Il bello è che non ci si deve preoccupare della sua chiusura !!! Dopo pochi minuti dalla chiusura della connessione anche il processo sqlservr.exe sarà automaticamente terminato.
Penso che LocalDb sia un ottima opzione per quelle piccole applicazioni single user che vengono utilizzate saltuariamente.
Ciao

Luca

SQL Server 2012 : Datapage Repair

2013-03-06T13:25:00.011+01:00

A causa di errori del disco, del controller, sbalzi di tensione o altri casi particolari i dati contenuti nei DB potrebbero danneggiarsi e diventare inutilizzabili. Il problema è che potremmo non accorgerci del danno fino a che quei dati precisi non ci servono.
Cosa dobbiamo fare in questo malaugurato caso ?

Dobbiamo ripristinare la/le data pages danneggiate, semplice no ?!?!

Creo un DB di esempio e lo popolo...

Use Master
Go

Create database RestorePage
Go

Alter database RestorePage Set Recovery Full
Go

Use RestorePage
Go

Create table DamagePage
(
 id int primary key clustered
 ,valoreA varchar(25)
)
Go

;With Cte as (Select 1 Riga Union all Select 1) 
 ,Cte4 as (Select A.Riga from Cte A, Cte B)  
 ,Cte16 as (Select A.Riga from Cte4 A, Cte4 B)  
 ,Cte256 as (Select A.Riga from Cte16 A, Cte16 B)  
 ,Results as (Select Row_Number() Over(Order by riga)Riga From Cte256) 
insert into DamagePage 
Select Riga
 , 'Valore ' + Cast(Riga as Varchar(5))  
from Results
Go

Simulo un'attività di Backup composta da Backup Full e T-Log

Backup database RestorePage to disk = 'C:\SQL 2012\Backup\BckFull.bak' With compression
GO

Backup Log RestorePage to disk = 'C:\SQL 2012\Backup\Log1.trn' With compression
GO

Backup Log RestorePage to disk = 'C:\SQL 2012\Backup\Log2.trn' With compression
GO

Recupero l'id pagina in cui risiede la riga con id 128

Select *,sys.fn_PhysLocFormatter( %%Physloc%% ) from DamagePage
Where id = 128
Go

Danneggio la data page contenente la riga con id = 128

Alter Database RestorePage set single_user with rollback immediate
Go
Dbcc writepage(RestorePage,1,217,1,1,0x66,1)
Go
Alter Database RestorePage set multi_user
Go

Ok.... Il danno è fatto...
Come faccio ad accorgermene ?

Se non uso strumenti di diagnosi me ne accorgo solo quando una applicazione deve reperire la riga con id 128 dalla tabella

Select * from DamagePage
Where id = 128
Go

Otterrà questo errore

Quindi cosa posso fare ora ?
Avendo i backup del Db posso fare il restore della singola data page corrotta

Gli step da attuare sono
1. Restore della data page con il comando Restore lasciando il db in recovery(Il DB resta accessibile, solo la data page oggetto del restore è inaccessibile....)
2. Backup del tail log
3. Restore di tutti i t-log backup necessari lasciando il db in recovery
4. Restore del tail-log backup riportando il db on line

Use master
Go

--Restore data page e DB in recovery
restore database [RestorePage] page='1:217' from disk = 'C:\SQL 2012\Backup\BckFull.bak' with norecovery
Go

--Backup del tail-log
backup log [RestorePage] to disk =  'C:\SQL 2012\Backup\Tail_log.trn' with No_truncate
Go

--Restore dei T-Log
restore log [RestorePage] from disk = 'C:\SQL 2012\Backup\Log1.trn' with norecovery
Go

restore log [RestorePage] from disk = 'C:\SQL 2012\Backup\Log2.trn' with norecovery
Go

--Restore del tail-log
restore log [RestorePage] from disk = 'C:\SQL 2012\Backup\Tail_log.trn' 
Go

Riproviamo ora a reperire la riga con id 128 dalla tabella

Select * from DamagePage
Where id = 128
Go

Come vedete il problema è risolto...

Semplicissimo vero ??
Ma siamo certi che i nostri backup non contengano errori ?
Se il backup è perfettamente integro siamo in grado di risolvere il problema, altrimenti la questione si complica, e di parecchio....
Immaginiamo che il backup dell'esempio precedente non fosse stato integro... Avremmo fatto il restore della data page già danneggiata, complicando ancora di più le cose....

Cosa potremmo tentare allora ?

Possiamo tentare di aggiustare il Db con il comando Dbcc CheckDb() che mette a disposizione 2 opzioni di "riparazione"
1. REPAIR_REBUILD
2. REPAIR_ALLOW_DATA_LOSS
L'opzione REPAIR_FAST è mantenuta per retro compatibilità ma di fatto non implementa alcunché....

Proviamo a ridanneggiare la data page con lo script usato poco fa e vediamo cosa accade se tentiamo di ripararla con la dbcc checkDb()

Provo prima l'opzione REPAIR_REBUILD che non comporta perdita di dati

Alter Database RestorePage set single_user with rollback immediate
Go
dbcc checkdb(RestorePage,REPAIR_REBUILD )
Go
Alter Database RestorePage set multi_user
Go

Ed ecco il risultato

Non ha fatto nulla.....

Proviamo con l'opzione REPAIR_ALLOW_DATA_LOSS

Alter Database RestorePage set single_user with rollback immediate
Go
dbcc checkdb(RestorePage,REPAIR_ALLOW_DATA_LOSS )with no_infomsgs
Go
Alter Database RestorePage set multi_user
Go

Ha corretto gli errori... benissimo !!!!

Proviamo a verificare se la ricerca della riga con id 128 funziona

In effetti la query funziona, ma non mi restituisce i dati perché per riparare la data page la dbcc checkdb() l'ha definitivamente eliminata !!!
Addio integrita dei dati quindi.....

Per evitare questi spiacevoli inconvenienti prima di ogni Backup full o differenziale è indispensabile effettuare un'operazione di diagnosi utilizzando la
Dbcc CheckDb() che verifica l'integrità strutturale, logica e fisica del Db.

Se la diagnosi non evidenzia errori posso effettuare il backup, se invece vengono segnalate anomalie che senso ha fare il backup ?
Senza il check del Db faremmo il backup anche degli errori e un backup contenente errori non serve a nulla visto che il nostro obiettivo è di avere i db in piena efficienza.

Se i nostri backup sono creati utilizzando i maintenance plan possiamo avvalerci del Check Database Integrity Task ed eseguire il backup solo se il primo task si è concluso senza errori, magari inviando una mail in caso di esito negativo.....

Se il backup lo gestiamo attraverso il t-sql possiamo utilizzare questo script

CREATE TABLE #DBCC_CHECKDB
( 
 Error int NULL, 
 [Level] int NULL, 
 State int NULL, 
 MessageText nvarchar(2048) NULL, 
 RepairLevel nvarchar(22) NULL, 
 Status int NULL, 
 DbId int NULL, 
 DbFragId int NULL,  
 ObjectId int NULL, 
 IndexId int NULL, 
 PartitionId bigint NULL, 
 AllocUnitId bigint NULL, 
 RidDbId smallint NULL,  
 RidPruId smallint NULL, 
 [File] smallint NULL, 
 Page int NULL, 
 Slot int NULL, 
 RefDbId smallint NULL,  
 RefPruId smallint NULL, 
 RefFile smallint NULL,  
 RefPage int NULL, 
 RefSlot int NULL, 
 Allocation smallint NULL 
) 

INSERT INTO #DBCC_CHECKDB 
(
 Error,
 [Level],
 State,
 MessageText,
 RepairLevel,
 Status, 
 DbId,
 DbFragId,
 ObjectId,
 IndexId,
 PartitionId,
 AllocUnitId,
 RidDbId,
 RidPruId,
 [File],
 Page,
 Slot,
 RefDbId,
 RefPruId,
 RefFile,
 RefPage,
 RefSlot,
 Allocation 
)
EXEC('DBCC CHECKDB(RestorePage) WITH TABLERESULTS, ALL_ERRORMSGS ')

If ( Select count(*) from #DBCC_CHECKDB Where Error >= 8000 and level >= 16 ) = 0
 Backup Database RestorePage To disk = 'C:\SQL 2012\Backup\BackupFull.bak'
Else
 throw 50000,'Errori!! Impossibile implementare il backup!!!',1

Drop table #DBCC_CHECKDB

NB: Lo script l'ho creato per SQL Server 2012.

Facciamo sempre un check dei nostri db, almeno prima dei backup full, se vogliamo evitare spiacevoli sorprese !!!

Ciao

Luca

Sql Server 2012 : Extended Events. Cosa Sono ?

2013-03-05T15:14:00.002+01:00

RIP SQL server Profiler.. !?!?!?!?!?!?!?! Eh ?!?!?!?! Ma come !?!?!?!??!
Ebbene si, dopo anni di onoratissimo servizio anche per Sql Server Profiler è giunta l'ora della meritata pensione...
Ma chi lo sostituirà !?!?! Gli Extended Events !!!
Gli Extended Events sono stati introdotti sin dall'edizione 2008 ma il loro utilizzo era un tantino macchinoso dato che nel Management studio 2008 non era presente nessun tool di configurazione e a meno che non si installasse questo Addin la configurazione doveva essere implementata via T-SQL.

Ma cosa sono questi Extended Events (d'ora in poi EE) ? Sono la nuova architettura che consente ai DBA di raccogliere una vastissima gamma di informazioni relative all'Engine di SQL Server per operazioni di monitoring, debug e troubleshooting.
Sono molto facili da comprendere e da utilizzare e nel Management Studio di SQL 2012 è integrata anche la loro configurazione attraverso una comoda UI.

Ma perché mettere in pensione SQL Server Profiler ?
Perché è molto invasivo e molto pesante mentre gli extended events sono estremamente leggeri e scalabili.

A dirla tutta SQL Server profiler non va totalmente in pensione, resterà il tool di Trace e Replay per Analysis Services.

Ma cosa posso implementare con gli EE ?
Praticamente tutto ciò che era implementabile con SQL Profiler....

Facciamo un esempio.... Come identificare un problema di DeadLock con gli EE.

Genero un DB di test su cui causerò un DeadLock

Use Master
Go

Create Database ExtEvnDb
Go

Use ExtEvnDb
Go

Create table Tab_A
(
 idA int
)
Go

Create table Tab_B
(
 idB int
)
Go

Insert into Tab_A values(1),(2),(3)
Insert into Tab_B values(4)
Go

Select * from Tab_A
Select * from Tab_B
Go

Prepariamo ora la sessione di monitoring utilizzando gli EE.
Dal management studio ci connettiamo all'istanza di SQL che vogliamo monitorare e selezioniamo la folder Management, all'interno della quale troviamo
il configuratore degli EE

Nella page General del configuratore diamo un nome alla sessione, nel mio caso DeadLockMonitor.
Possiamo configurare se l'event session deve essere avviata automaticamente allo start dell'engine e/o al termine della configurazione.

Nella page Events possiamo selezionare gli eventi necessari alla sessione di monitoring.
Un Event altro non è che un'azione, un'attività scatenata da un'operazione all'interno dell'engine di SQL Server.
Utilizzando la textbox Event Library ricerchiamo gli eventi adeguati alle nostre necessità, digitando il nome dell'evento stesso o parte di esso.
Per ogni evento selezionato il configuratore mette a diposizione una descrizione e la lista dei campi ad esso associati, cioè quali informazioni possiamo estrarre dall'evento stesso.
Per monitorare un DeadLock ci possono essere utili i seguenti eventi:
1. xml_deadlock_report = Grafico che rappresenta il deadlock
2. lock_deadlock = Evento di deadlock
3. sql_batch_starting = Avvio di un batch T-Sql
4. sql_batch_completed = Conclusione di un batch T-Sql

Una volta selezionati gli Eventi necessari passiamo alla loro configurazione premendo il button Configure
Il tab Global Fields(Actions) consente di selezionare i campi che devono essere restituiti dall'evento selezionato.
Per monitorare un DeadLock ci possono essere utili i seguenti campi:
1. client_app_name = Nome dell'applicazione che si connette a SQL Server
2. event_sequence = Numero sequenziale che identifica l'ordine delle operazioni
3. session_id = Id della sessione
4. transaction_id = Id della transazione

Possiamo poi filtrare quali eventi tracciare. Per esempio vogliamo monitorare gli eventi selezionati ma solo quelli che si verificano in un determinato Db o che nel testo del comando hanno una stringa particolare.
NB: Non impostare il filtro sull'evento xml_deadlock_graph, lo perderemmo....

Nella page Data Storage configuriamo dove gli eventi debbano essere collezionati.
Un target è un consumatore di eventi, una locazione contenente gli eventi che si sono verificati. Possiamo collezionare gli eventi su file, in un buffer etc... e possiamo avere più target per ogni sessione di EE.
Un target può processare gli eventi in modo Sincrono o Asincrono.

A questo punto la configurazione è terminata, Click su OK e l'EE Session verrà avviata e comincerà a monitorare gli eventi selezionati.
NB: la session verrà avviata se abbiamo messo il flag di auto start nella configurazione.
In caso contrario click dx del mouse sulla sessione di EE e selezioniamo Start o Stop Session.

Vediamo di causare un dead lock
Dal management studio apro 2 connessioni avendo cura di utilizzare la Keyword Application Name per simulare l'accesso da parte di una applicazione utente.
La prima connessione si chiamerà Application_1, la seconda Application_2

Ecco il command di Application_1

Use [ExtEvnDb]
Go

Begin tran 

Update Tab_A set ida = idA + 10

--Eseguire la prima parte del command di application_2

Update Tab_B Set idb = idb + 10

--Eseguire la seconda parte del command di application_2

commit

mentre questo è il command di Application_2

Use [ExtEvnDb]
Go

Begin tran 

Update Tab_B Set idb = idb + 30

--Eseguire la seconda parte del command di application_1

Update Tab_A set ida = idA + 30

--DeadLock Error

commit

La seguenza di operazioni sarà:
1. Application_1 : Begin tran Update Tab_A set ida = idA + 10
2. Application_2 : Begin tran Update Tab_B Set idb = idb + 30
3. Application_1 : Update Tab_B Set idb = idb + 10
4. Application_2 : Update Tab_A set ida = idA + 30

Ecco il DeadLock

Come facciamo a verificare la nostra session di EE per reperire le info relative al DeadLock?
Utilizzando management studio...

Ed ecco che si aprono gli eventi consumati dal target

L'evento lock_deadlock mi indica che si è verificato un deadlock e l'evento xml_deadlock_graph mostra il grafico

Il grafico indica che la session id 53 è stata killata per risolvere un deadlock con la session id 51. (SQL fa il Kill della sessione per cui è più semplice effettuare il rollback, sostanzialmente fa il kill della transazione più piccola)

Quale è il comando che ha scatenato il deadlock ?

Grazie agli eventi ed ai fields selezionati in fase di configurazione possiamo risalire alla sequenza di operazioni e alle applicazioni che hanno causato il deadlock.

Così come con il profiler potevamo creare delle trace da inviare al cliente, lo stesso lo possiamo implementare con gli EE.
Generiamo lo script di configurazione da inviare al cliente ed il gioco è fatto....
Il cliente poi non farà altro che inviarci uno o più file *.xel che potremo leggere facendo drag & drop in Management Studio.

Possiamo attivare più session EE contemporaneamente e per ciascuna di esse selezionare Events, Actions, Filter e Targets personalizzati con un overhead per l'engine di molto inferiore a quello che avremmo utilizzando SQL Trace o addirittura il SQL Server Profiler.
Qui trovate un interessante post sull'overhead dato da EE e SQL Server Profiler.

Quindi buon troubleshooting con gli Extended Events !!!

Ciao

Luca