Archivi categoria: Scripts

Esempi di SQL Graph Database su GitHub

Facebooktwittergoogle_plusredditlinkedin

SQL Server offre da sempre gli strumenti per gestire i dati gerarchici e le relazioni tra le entità. A volte, però, le relazioni tra le entità possono diventare complesse. Pensiamo ad una relazione molti-a-molti, i database relazionali non dispongono di soluzioni native per questo tipo di legame, che viene comunemente implementato con una tabella di associazione.

SQL Server 2017, grazie all’introduzione di Graph Database, permette di esprimere certi tipi di query più facilmente rispetto ad un database relazionale puro.

Alcuni giorni fa, Perry Skountrianos (@pskountrianos) ha confermato il “pull request” che ho fatto sul repository Microsoft/sql-server-samples di GitHub. Questo repository contiene esempi di codice che spiegano come utilizzare alcune funzionalità di SQL Server, SQL Azure e Azure Data Warehouse. Ogni esempio include il relativo file README.md che spiega come eseguire ed utilizzare il codice.

Nella directory relativa a SQL Graph (sql-server-samples/samples/demos/sql-graph/recommendation-system) troverete tre demo.

La prima spiega come creare e popolare i nuovi oggetti “Graph” ovvero due nuovi tipi di tabelle “Node” e “Edge”. La seconda demo ha l’obiettivo di introdurre la nuova clausola MATCH (T-SQL) usata per eseguire query su Graph DB attraversando i nodi e le relazioni creati nella prima demo. Nell’ultima demo verrà mostrato come costruire un sistema di raccomandazione per le offerte di vendita (basato sul database WideWorldImporters), in grado di determinare i prodotti consigliati sulla base del prodotto che il cliente sta acquistando oppure ha appena acquistato sul nostro ipotetico e-commerce, uno scenario tipico di utilizzo di SQL Graph.

Hai sentito parlare di SQL Graph e vuoi saperne di più? Questi esempi, disponibili su GitHub, sono un ottimo punto di partenza!

Buon divertimento!

Conversioni implicite: La plan cache ci svela quante sono e dove avvengono!

Facebooktwittergoogle_plusredditlinkedin

Nell’articolo Conversioni implicite: cosa sono e che impatto hanno sulle nostre query, Luca Bruni (@mrg3d) ci ha parlato delle conversioni implicite che avvengono, a nostra insaputa, all’interno dell’Engine di SQL Server. Tali conversioni, possono costringere il Query Optimizer ad accedere alle tabelle con operazioni di scansione (Table Scan, Index Scan) in alternativa alle più efficienti operazioni di Seek (Index Seek), e quando una parte significativa del carico di lavoro è interessata da conversioni implicite, le performance degradano visibilmente!

Una conversione implicita tra due tipi di dato differenti avviene sempre dal tipo di dato meno preciso al tipo di dato più preciso. Per ovvie ragioni, SQL Server non può perdere precisione nei dati a causa di una conversione implicita, parlando quindi di tipi di dato testo, una conversione implicita potrà avvenire, ad esempio, dal tipo VARCHAR (meno preciso) al tipo NVARCHAR (più preciso), mai nella direzione opposta.

Probabilmente vi starete chiedendo se il vostro carico di lavoro è affetto da conversioni implicite e in che percentuale.

Le conversioni implicite, all’interno del piano di esecuzione, vengono segnalate con un warning. Il Query Optimizer può segnalare i seguenti tipi di warning:

  • No statistics (ColumnsWithNoStatistics)
  • No join predicate (NoJoinPredicate)
  • Implicit conversions (PlanAffectingConvert)
  • Sort on tempdb (SpillToTempDb)
  • No matched indexes (UnmatchedIndexes)
  • Wait (WaitWarningType)

..tra cui troviamo l’Implicit Conversions Warning (PlanAffectingConvert) di cui parleremo in questo articolo.

Il warning PlanAffectingConvert viene segnalato proprio in presenza di conversioni implicite, quando viene utilizzata la funzione CONVERT_IMPLICIT. È quindi possibile fare indagini sulla presenza di conversioni implicite nel carico di lavoro analizzando la plan cache, ovvero l’area di memoria utilizzata da SQL Server per memorizzare i piani di esecuzione delle query.

Utilizzando la metodologia “top-down” per analizzare il problema, il primo dato che vorremmo avere è la percentuale dei piani di esecuzione in cache affetti da conversioni implicite. Per ottenerlo dovremo contare i piani di esecuzione in cache, e di questi, quelli affetti da conversioni implicite… la seguente CTE estrae le informazioni che cerchiamo: il numero dei piani di esecuzione in cache, il numero dei piani di esecuzione in cui viene utilizzata la funzione CONVERT_IMPLICIT ed il calcolo della percentuale.

WITH XMLNAMESPACES
(
 DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
),
Cached_Plans AS
(
 SELECT
 COUNT(cp.plan_handle) AS Total_Plan_Cached
 FROM
 sys.dm_exec_cached_plans AS cp
),
Cached_Plans_with_Convert AS
(
 SELECT
 COUNT(cp.plan_handle) AS Total_Plan_Cached_with_Convert
 FROM
 sys.dm_exec_cached_plans AS cp
 CROSS APPLY
 sys.dm_exec_query_plan(cp.plan_handle) AS qp
 CROSS APPLY
 sys.dm_exec_sql_text(cp.plan_handle) AS qt
 WHERE
 (qp.query_plan.exist('//Warnings') = 1)
 AND CAST(qp.query_plan.query('//Warnings') AS VARCHAR(MAX)) LIKE '%CONVERT_IMPLICIT%'
)
SELECT
 Total_Plan_Cached
 ,Total_Plan_Cached_with_Convert = (SELECT Total_Plan_Cached_with_Convert FROM Cached_Plans_with_Convert)
 ,CAST(CAST((SELECT Total_Plan_Cached_with_Convert FROM Cached_Plans_with_Convert) AS DECIMAL(10,3)) / 
 CAST(Total_Plan_Cached AS DECIMAL(10,3)) AS DECIMAL(6,3)) * 100 [% Plan_Cached_with_Convert]
FROM
 Cached_Plans;

L’output di esempio è illustrato nelle figura seguente, dove circa il 4% del carico di lavoro è affetto da conversioni implicite.

Qualora venisse rilevato un numero considerevole di piani di esecuzione affetti da conversioni implicite, la seguente CTE ci potrà aiutare nell’identificazione delle query specifiche in cui avvengono le conversioni implicite, tra le colonne estratte vi è il testo della query, il piano di esecuzione completo e la porzione relativa ai warning di tipo PlanAffectingConvert in cui è stata utilizzata la funzione CONVERT_IMPLICIT.

WITH XMLNAMESPACES
(
 DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
),
PlanAffectingConvert AS
(
 SELECT
 query_text = qt.text
 ,cp.objtype
 ,cp.refcounts
 ,cp.usecounts
 ,Plan_Warnings = qp.query_plan.query('//Warnings')
 ,qp.query_plan
 FROM
 sys.dm_exec_cached_plans AS cp
 CROSS APPLY
 sys.dm_exec_query_plan(cp.plan_handle) AS qp
 CROSS APPLY
 sys.dm_exec_sql_text(cp.plan_handle) AS qt
 WHERE
 (qp.query_plan.exist('//Warnings') = 1)
)
SELECT
 *
FROM
 PlanAffectingConvert
WHERE
 CAST(Plan_Warnings AS VARCHAR(MAX)) LIKE '%CONVERT_IMPLICIT%';

Concludendo, se avete il dubbio che il vostro carico di lavoro sia affetto da conversioni implicite, un buon punto di partenza è l’utilizzo delle query descritte in questo articolo… se pensate di non avere conversioni implicite nelle vostre query, fate lo stesso un controllo… non costa nulla!

Buon divertimento!

Manutenzione indici colonnari: una ricostruzione accorta

Facebooktwittergoogle_plusredditlinkedin

Avete presente le procedure di manutenzione degli indici di Hola Hallengren?

Se no, vi consiglio di guardarle perché sono molto utili. Ecco il link: https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Se le conoscete, magari vi è capitato di notare che, nelle versioni più recenti di SQL Server, la manutenzione degli indici colonnari richiede uno sforzo ulteriore.

A questo proposito vi segnalo un articolo di dal blog Microsoft:

SQL 2014 Clustered Columnstore index rebuild and maintenance considerations

Quello che occorre ricordare è che la ricostruzione dell’indice è più pesante rispetto ad un B-Albero e si compone di una fase iniziale di costruzione del dizionario che non è parallelizzabile. Inoltre la dimensione del dizionario e il grado di parallelismo durante la creazione/manutenzione dell’indice influiscono sulla forma finale dell’indice colonnare.

Nell’articolo si propone uno script per ricostruire gli indici (partizionati se necessario) sulla base dei seguenti criteri (in OR):

  1. La percentuale di righe cancellate è superiore ad una soglia fissata (10% nello script)
  2. Il dizionario non è pieno (ovvero è più piccolo di 16MB) e il numero medio di righe per gruppo di righe (RowGroup) è inferiore alla soglia fissata (500k righe) e ci sono più di 500k righe totali.

La prima condizione deriva dal fatto che una cancellazione negli indici colonnari è solo logica e diventa effettiva (quindi lo spazio si libera) solo dopo una ricostruzione dell’indice.

La seconda condizione ci dice di ricostruire l’indice se il numero medio di righe per gruppo è inferiore a 500k righe, ovvero metà del numero di righe massimo per gruppo per cui il gruppo è considerato chiuso e quindi compresso in background dal thread preposto (‘Tuple-Mover’).

Entrambe le condizioni sono applicate alle singole partizioni, se presente un partizionamento della tabella.

Successivamente, lo script produce i comandi di ALTER INDEX con un MAXDOP calcolato secondo la seguente logica:

  • Se il numero totale di righe è inferiore a un milione (quindi un singolo rowgroup non pieno) allora MAXDOP = 1.
  • Se il numero totale di ‘milionate’ di righe è inferiore al MAXDOP possibile (in base alle impostazioni Resource Governor) allora il MAXDOP è pari al numero di ‘milionate’ effettive (quindi un processore per ogni RowGroup).
  • Se abbiamo più ‘milionate’ dei processori disponibili allora MAXDOP = 0 (li usiamo tutti).

Bene, lo script ci può essere utile nella manutenzione dei nostri indici colonnari, ma gli script di Ola Hallengren hanno molte utili funzionalità come il log dei comandi, i filtri su db o indici specifici, i limiti di tempo, etc…

Quello che ho voluto fare è prendere la stored procedure di Ola ‘dbo.IndexOptimize’ e farne una versione che comprende la metodologia di manutenzione descritta sopra.

Eccola. Si chiama “dbo.ColumnStoreOptimize”.

Ho aggiunto alcuni nuovi parametri:

/* new parameters*/

@DeletedRowsPercent Decimal(5,2) = 10,

@RGQuality int = 500000,

@PercentageRGQualityPassed smallint = 20,

@IndexesTypeFilter varchar(6) = ‘Column’, — Row, NULL

/* end new parameters */

Il filtro per tipo di indice ci permette di lavorare solo su indici colonnari o per righe o entrambi in caso di NULL.

Tutte le modifiche che ho apportato alla procedura originale sono evidenziate dalla coppia di commenti:

/* news */

/* end news */

Ho dovuto applicare le modifiche solo alle versioni di SQL Server >= 2012 (tramite apposito filtro) perché manca una tabella di sistema usata per reperire informazioni sui gruppi di righe (la sys.column_store_row_groups).

Spero vi possa essere utile. Provare sempre in ambiente di Test prima di usare in Produzione.

*con milionate di righe intendo numero totale di righe diviso un milione

SQL Azure DTU Calculator

Facebooktwittergoogle_plusredditlinkedin

Una delle domande più comuni quando si inizia ad utilizzare SQL Azure è legata alla scelta del livello di servizio necessario per la proprie necessità. Dato che sul Cloud, ogni risorsa sprecata è un tangibile costo aggiuntivo, è bene scelgliere il livello di servizio migliore. Il “problema” è che il livello è misurato in DTU – Database Transaction Units – ossia un valore che rappresenta un mix di CPU, Memoria ed I/O. Il problema è che è molto difficile, se non impossibile, calcolare questo valore per un server on-premises già esistente, in modo da poter aver un paragone con dei dati conosciuti.

Fortunatamente ora è possibile, grazie a questo strumento:

Azure SQL Database DTU Calculator

sviluppato da Justin Henriksen, un Solution Architect specializzato su Azure, che rende molto più semplici le cose. Dopo aver eseguito uno script PowerShell per rilevare alcune metriche sul server On-Premises, è sufficiente farne l’upload sul suddetto sito per avere idea di che livello di DTU sia ottimale nel caso si volesse spostare il database sul cloud. E’ chiaro che è bene campionare le metriche usando un carico di lavoro rappresentivo per esser sicuri di avere dati corretti. Tenetelo a mente prima di prendere qualsiasi decisione in merito.

Oltre al suddetto sito, segnalo anche due link molto utili per capire al meglio quale livello di servizio è più adatto alla propria situazione:

Slide e demo della sessione “SQL Server 2016 What’s new for Developers”

Facebooktwittergoogle_plusredditlinkedin

Sono online le slide e le demo della sessione fatta all’evento “Buon Compleanno UgiDotNet” tenutosi qualche giorno fa presso la sede di Microsoft Italia.

SQL Server 2016: novità per gli sviluppatori (Davide Mauri – MVP)

Supporto Nativo a JSON, Tabelle temporali, Live Query Statistics, Stretch Database e Polybase sono feature che senza dubbio sono di sicuro interesse per chi sviluppa app, di qualsiasi genere. In questa sessione vedremo le prime tre feature con un certo dettaglio ed introdurremo le potenzialità delle ultime citate per poter dare un quadro completo di SQL Server 2016 che è una della release più ricche ed importanti, per quanto riguarda lo sviluppo, da SQL Server 2005 in avanti. Non mancherà, ovviamente, una “piccola” citazione a SQL Server per Linux.