Archivi categoria: Scripts

Statistiche di utilizzo e performance delle viste in un database SQL Server

Facebooktwittergoogle_plusredditlinkedin

Le performance di una soluzione database sono spesso oggetto di diatriba tra chi fornisce la soluzione e chi la personalizza. Scrivere codice T-SQL ottimizzato, in grado di scalare all’aumentare dei dati e degli utenti, non è affatto semplice e quando la complessità aumenta, le attività di manutenzione del codice diventano difficili da attuare anche per l’autore stesso.

In questo articolo, condivido la metodologia di tuning e alcuni script che utilizzo per ottenere informazioni sulle performance delle query che utilizzano le viste presenti nel database oggetto dell’analisi. La presenza di viste nidificate contenenti query non ottimizzate può diventare oggetto di analisi specifica, gli script contenuti in questo articolo hanno l’obiettivo di fornire alcuni indicatori sull’utilizzo e sulle performance delle viste di un DB.

Alcuni indicatori sulle performance delle viste in SQL Server

Il primo dato interessante è stato ottenuto interrogando la DMV sys.dm_exec_query_optimizer_info che espone le statistiche sulle ottimizzazioni eseguite dal Query Optimizer dall’avvio dell’istanza SQL Server; i valori sono quindi cumulativi.

La CTE riportata di seguito, basata sulla DMV sys.dm_exec_query_optimizer_info, fornisce informazioni sul carico di lavoro. Il dato interessante che si ottiene è il numero (in percentuale) di query che referenziano una vista. Ho avuto l’opportunità di esaminare casi dove circa l’85% delle query eseguite referenziava una vista. Il dato puro, di per sé, non necessariamente è sintomo di un problema di performance, ma se associato alle lamentele degli utenti circa la lentezza del sistema, ci suggerisce quantomeno un approfondimento.

WITH CTE_QO AS
(
  SELECT
    occurrence
  FROM
    sys.dm_exec_query_optimizer_info
  WHERE
    ([counter] = 'optimizations')
),
QOInfo AS
(
  SELECT
    [counter]
    ,[%] = CAST((occurrence * 100.00)/(SELECT occurrence FROM CTE_QO) AS DECIMAL(5, 2))
  FROM
    sys.dm_exec_query_optimizer_info
  WHERE
    [counter] IN ('optimizations'
                  ,'trivial plan'
                  ,'no plan'
                  ,'search 0'
                  ,'search 1'
                  ,'search 2'
                  ,'timeout'
                  ,'memory limit exceeded'
                  ,'insert stmt'
                  ,'delete stmt'
                  ,'update stmt'
                  ,'merge stmt'
                  ,'contains subquery'
                  ,'view reference'
                  ,'remote query'
                  ,'dynamic cursor request'
                  ,'fast forward cursor request'
                 )
)
SELECT
  [optimizations] AS [optimizations %]
  ,[trivial plan] AS [trivial plan %]
  ,[no plan] AS [no plan %]
  ,[search 0] AS [search 0 %]
  ,[search 1] AS [search 1 %]
  ,[search 2] AS [search 2 %]
  ,[timeout] AS [timeout %]
  ,[memory limit exceeded] AS [memory limit exceeded %]
  ,[insert stmt] AS [insert stmt %]
  ,[delete stmt] AS [delete stmt]
  ,[update stmt] AS [update stmt]
  ,[merge stmt] AS [merge stmt]
  ,[contains subquery] AS [contains subquery %]
  ,[view reference] AS [view reference %]
  ,[remote query] AS [remote query %]
  ,[dynamic cursor request] AS [dynamic cursor request %]
  ,[fast forward cursor request] AS [fast forward cursor request %]
FROM
  QOInfo
PIVOT (MAX([%]) FOR [counter] 
  IN ([optimizations]
      ,[trivial plan]
      ,[no plan]
      ,[search 0]
      ,[search 1]
      ,[search 2]
      ,[timeout]
      ,[memory limit exceeded]
      ,[insert stmt]
      ,[delete stmt]
      ,[update stmt]
      ,[merge stmt]
      ,[contains subquery]
      ,[view reference]
      ,[remote query]
      ,[dynamic cursor request]
      ,[fast forward cursor request])) AS p;

L’approfondimento può essere eseguito mettendo in relazione la vista di sistema sys.views con le DMV che espongono le statistiche delle query recenti, il testo della query ed il relativo piano di esecuzione in cache.

La seguente CTE fornisce informazioni statistiche dettagliate sulle query in cache che utilizzano le viste presenti nel database corrente. I valori relativi a numero di esecuzioni, tempo totale di esecuzione, pagine di memoria lette e le altre informazioni dipendenti dalla versione di SQL Server in uso, forniscono una chiara indicazione sulle query da controllare/ottimizzare; analisi supportata dalla visualizzazione del piano di esecuzione in cache.

WITH CTE_VW_STATS AS
(
  SELECT
    SCHEMA_NAME(vw.schema_id) AS schemaname
    ,vw.name AS viewname
    ,vw.object_id AS viewid
  FROM
    sys.views AS vw
  WHERE
    (vw.is_ms_shipped = 0)
  INTERSECT
  SELECT
    SCHEMA_NAME(o.schema_id) AS schemaname
    ,o.Name AS name
    ,st.objectid AS viewid
  FROM
    sys.dm_exec_cached_plans cp
  CROSS APPLY
    sys.dm_exec_sql_text(cp.plan_handle) st
  INNER JOIN
    sys.objects o ON st.[objectid] = o.[object_id]
  WHERE
    st.dbid = DB_ID()
)
SELECT
  vw.schemaname
  ,vw.viewname
  ,vw.viewid
  ,DB_NAME(t.databaseid) AS databasename
  ,t.databaseid
  ,t.*
FROM
  CTE_VW_STATS AS vw
CROSS APPLY
  (
   SELECT
     st.dbid AS databaseid
     ,st.text
     ,qp.query_plan
     ,qs.*
   FROM
     sys.dm_exec_query_stats AS qs
   CROSS APPLY
     sys.dm_exec_sql_text(qs.plan_handle) AS st
   CROSS APPLY
     sys.dm_exec_query_plan(qs.plan_handle) AS qp
   WHERE
     (CHARINDEX(vw.schemaname, st.text, 1) > 0)
     AND (st.dbid = DB_ID())
  ) AS t;

L’ultima query fornisce informazioni sulle viste non utilizzate, fate molta attenzione ai dati che estrae, è basata sulla DMV sys.dm_exec_cached_plans che è soggetta al fluttuare dei piani di esecuzione all’interno della plan cache. Se una vista non è in cache nel momento in cui eseguite la query, non è detto che tale vista sia da eliminare. Se ritenete che la cache sia abbastanza rappresentativa del vostro carico di lavoro, tenetene semplicemente conto. Se nei successivi controlli, la vista sarà sempre presente, potrete valutare di effettuare altre indagini.

SELECT
  SCHEMA_NAME(vw.schema_id) AS schemaname
  ,vw.name AS name
  ,vw.object_id AS viewid
FROM
  sys.views AS vw
WHERE
  (vw.is_ms_shipped = 0)
EXCEPT
SELECT
  SCHEMA_NAME(o.schema_id) AS schemaname
  ,o.name AS name
  ,st.objectid AS viewid
FROM
  sys.dm_exec_cached_plans cp
CROSS APPLY
  sys.dm_exec_sql_text(cp.plan_handle) st
INNER JOIN
  sys.objects o ON st.[objectid] = o.[object_id]
WHERE
  st.dbid = DB_ID();

Risorse correlate

  1. DMVs for Performance Tuning (Video – SQL Saturday Pordenone)
  2. DMVs for Performance Tuning (Slide e Demo – SQL Saturday Pordenone)
  3. SQL Server Tuning in pillole (Video – SQL Saturday Parma)
  4. SQL Server Tuning in pillole (Slide e Demo – SQL Saturday Parma)
  5. Performance Tuning With SQL Server Dynamic Management Views
  6. The Most Prominent Wait Types of your SQL Server 2016

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.