Tutti gli articoli di Sergio Govoni

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

24 Hours of PASS (Aprile 2018): Sessioni registrate

Facebooktwittergoogle_plusredditlinkedin

Le sessioni erogate durante l’evento 24 Hours of PASS edizione “Cross-Platform SQL Server Management” tenuto lo scorso 25 e 26 Aprile 2018, sono state registrate e sono già disponibili per essere riguardate o viste per la prima volta nel caso non abbiate potuto seguire la diretta.

Ogni video è disponibile accedendo alla pagina della relativa sessione, dopo aver effettuato l’accesso.. non siete ancora membri del PASS, registratevi subito! È gratuito!

Un grazie agli Sponsor (Microsoft, Quest, IDERA, SentryOne e Redgate) che hanno reso possibile l’organizzazione di questo evento, gratuito per i partecipanti!

Buona visione!

SQL Server 2016 Service Pack 2 (SP2)

Facebooktwittergoogle_plusredditlinkedin

E’ stato rilasciato il Service Pack 2 (SP2) per SQL Server 2016!

Questi i link per il download e per consultare Release Note e KB:

Le caratteristiche più importanti del Service Pack 2 di SQL Server 2016 sono descritte in questo articolo.

Non c’è nessun buon motivo per non programmare l’aggiornamento!

SQL Saturday Parma 2018: Save the date!

Facebooktwittergoogle_plusredditlinkedin

Anche nel 2018, il SQL Saturday farà tappa in Emilia! L’edizione #777 sarà a Parma!

L’evento è gratuito (con registrazione obbligatoria) e si terrà sabato 24 novembre 2018 presso le aule del Dipartimento di Ingegneria dell’Informazione dell’Università degli Studi di Parma. Segnatevi la data!

La Call for Speakers rimarrà aperta fino al 25 settembre 2018, avete quindi tutto il tempo per inviare le vostre proposte.

L’agenda verrà finalizzata dopo la chiusura della Call for Speakers, nel frattempo potete consultare le proposte inviate accedendo alla pagina Submitted Sessions.

Per effettuare la registrazione, puntate il vostro browser qui.

Usate gli hashtag twitter #sqlsatparma e #sqlsat777 per farci sapere che parteciperete all’evento!

Ci vediamo a Parma, non mancate!!

SQL Saturday Pordenone 2018 – Sessioni registrate

Facebooktwittergoogle_plusredditlinkedin

Le sessioni tenute al SQL Saturday Pordenone 2018 sono state registrate!

I video sono disponibili sul canale UGISS di Vimeo nella raccolta SQL Saturday Pordenone 2018 (#sqlsat707), di seguito i link ai singoli video:

Il materiale (slide e script demo) utilizzato durante le sessioni è disponibile per il download accedendo allo schedule dell’evento.

Buona visione!

SQL Saturday Catania 2018 – Agenda online!

Facebooktwittergoogle_plusredditlinkedin

L’agenda del SQL Saturday Catania 2018 è online!

L’evento si terrà sabato 19 maggio 2018 presso Free Mind Foundry, Via Sclafani 40, Acireale. Sarà il primo SQL Saturday a Catania ed avrà 2 track che erogheranno sessioni in parallelo per un totale di 12 ore di formazione gratuita SQL Server, ma non solo! In questa edizione, le sessioni sono state raggruppate per livello, nel percorso begineer troverete sessioni di livello 100-200, mentre il percorso advanced ospiterà sessioni più approfondite, di livello 300-400.

Qui trovate l’agenda, è molto interessante!

Per la registrazione, puntate il vostro browser qui, usate l’hashtag twitter #sqlsat728 per farci sapere che parteciperete all’evento!

Fin da ora, ringraziamo gli Sponsor, il loro contributo rende possibile l’organizzazione di eventi come questo: Grazie!

Non mancate!

PASSGIVC – TSQL Advanced for Grouping and Windowing

Facebooktwittergoogle_plusredditlinkedin

Il prossimo webinar del PASS Italian Virtual Chapter è programmato per mercoledì 21 marzo 2018 alle ore 18:00.

Andrea Martorana Tusa (@bruco441) ci spiegherà come utilizzare il linguaggio T-SQL in modo avanzato per effettuare operazioni di grouping e utilizzare le funzioni di windowing, nel webinar dal titolo “T-SQL advanced: Grouping and Windowing”.

Questo è l’abstract:

Lo scopo di questa sessione è esplorare le possibilità offerte del linguaggio T-SQL in termini di aggregazione e calcolo dei dati. C’è molto di più della sola clausola GROUP BY! Con le funzioni di aggregazione e di ranking, si possono calcolare direttamente nella stessa query, totali parziali, totali complessivi, si possono trovare intervalli in serie, selezionare l’ultimo valore non-null, ecc. Le funzioni di windowing si applicano invece ad un set di righe delimitato dalla clausola OVER (una “window” appunto). Le funzioni di windowing non effettuano l’aggregazione del gruppo a livello di riga e non nascondono i dettagli. Così è possibile mostrare dei dati di dettaglio e dei totali nella stessa query. La sessione è basata principalmente su un copioso numero di demo, che consentiranno di esplicitare al meglio i concetti introdotti. Per ogni soluzione, verrà anche valutato l’impatto in termini di performance.

Per effettuare la registrazione, puntate il vostro browser qui.

PASS Marathon Edizione GDPR: Sessioni registrate

Facebooktwittergoogle_plusredditlinkedin

Le sessioni presentate durante l’evento PASS Marathon “Edizione GDPR”, tenuto lo scorso 13 marzo 2018, sono state registrate e sono già disponibili per essere riguardate o viste per la prima volta nel caso non abbiate potuto seguire la diretta.

Ogni video è disponibile accedendo alla pagina della relativa sessione, qui trovate lo schedule completo:

http://www.pass.org/marathon/2018/march/Schedule.aspx

Ricordo che GDPR è l’acronimo di General Data Protection Regulation, parte del Regolamento Europeo 679/2016 sulla protezione dei dati personali. Aziende e professionisti sono tenuti, entro il 25 maggio 2018, ad uniformarsi alle nuove regole in materia di protezione dei dati, pena il rischio di incorrere in pesanti sanzioni.

Buona visione!