Archivi categoria: Articoli

SQL Server e SQL Azure Virtual Labs

Facebooktwittergoogle_plusredditlinkedin

Quante volte avreste voluto provare le nuove funzionalità di SQL Server 2017, ma siete stati frenati dai setup di installazione? Quante volte, dopo un SQL Saturday, non siete riusciti a rifare le demo per mancanza dell’ambiente su cui rifare le esercitazioni?

La risposta a queste domande la trovate nei Virtual Labs, ambienti virtuali, predisposti da Microsoft, dove è possibile svolgere esercitazioni guidate e mirate che vi permetteranno di scoprire o consolidare la conoscenza su nuove funzionalità di un prodotto o su una nuova tecnologia, comodamente a casa vostra o dall’ufficio.

Con i Virtual Labs di Microsoft avrete accesso ad una macchina virtuale già configurata con tutto quello che serve per svolgere l’esercitazione, senza preoccuparvi dei setup e senza il rischio di compromettere qualcosa in un server di produzione 🙂 Al termine dell’esercitazione la VM verrà distrutta e potrete ripartire da zero nel caso vogliate ripeterla.

I Virtual Labs predisposti non riguardano solo il “mondo” Data Platform, ma anche altre tecnologe, per accedere alla pagina generale dei Virtual Labs di Microsoft, cliccate qui.

In particolare, per quanto riguarda SQL Server 2017 e SQL Azure sono disponibili numerosi Virtual Labs che trovetere già filtrati a questo link.

Ora non avete più scuse, le funzionalità di SQL Server 2017 e SQL Azure sono davvero alla portata di tutti, è sufficiente ritagliarsi un paio d’ore con una tazzina di caffè.

Buon divertimento!!

Usate ancora netstat? Retrogradi, lo strumento del futuro è ss!

Facebooktwittergoogle_plusredditlinkedin

Il servizio che ho avviato si è messo in ascolto sulla porta che ho configurato?

In genere la risposta a questa domanda di apertura è racchiusa per la maggioranza degli utilizzatori in questo comando:

[root@anomalia ~]# netstat -nutlp

che utilizza il comando netstat per fornire una lista di porte numeriche (n) di tipo udp (u) o tcp (t) in ascolto (l) per mezzo di un processo (p). Ma forse non tutti sanno che le moderne distribuzioni installando, di default, un nuovo tool che permette di recuperare le stesse informazioni ed anche qualcosa di più: ss (socket statistics).

Ma come funziona ss? Quali informazioni fornisce? ss permette di recuperare informazioni di tipo summary (-s) per avere un’idea di quante e quali porte siano aperte nel proprio sistema, oppure permette di investigare nel dettaglio le porte in ascolto (-l) associate ai processi (-p), ottenendo un risultato del tutto simile a quello illustrato ad inizio articolo con netstat anzi, osservando questo comando in maniera del tutto identica (almeno in termini di opzioni):

[root@anomalia ~]# ss -nutlp
Netid State Recv-Q Send-Q Local Address:Port Peer Address:Port
udp UNCONN 0 0 192.168.122.1:53 0.0.0.0:* users:(("dnsmasq",pid=1279,fd=5))
...

udp UNCONN 7680 0 [::]:5353 [::]:* users:(("avahi-daemon",pid=873,fd=16))

tcp LISTEN 0 5 127.0.0.1:631 0.0.0.0:* users:(("cupsd",pid=1001,fd=11))
...

tcp LISTEN 0 50 *:1716 *:* users:(("kdeconnectd",pid=1588,fd=12))

Sebbene quindi la conformazione dell’output sia leggermente differente, in particolare per quel che riguarda la composizione delle informazioni sul PID associato al socket aperto, tutto sommato i due comandi sono intercambiabili. Dove però ss può far la differenza è nella composizione di query per limitare l’output unicamente a quanto interessa.

Ad esempio, supponendo di trovarci su un server web, per capire quante connessioni sono aperte dallo stesso per la porta https basterà esplicitare il filtro come segue:

root@web-1:~# ss -np state established '( sport = :https )'
Netid Recv-Q Send-Q Local Address:Port Peer Address:Port
tcp 0 0 ::ffff:172.16.0.11:443 ::ffff:172.16.0.254:52017 users:(("apache2",pid=22173,fd=31))
tcp 0 0 ::ffff:172.16.0.11:443 ::ffff:172.16.0.254:50605 users:(("apache2",pid=20254,fd=31))
tcp 0 0 ::ffff:172.16.0.11:443 ::ffff:172.16.0.254:54608 users:(("apache2",pid=25649,fd=31))
tcp 0 0 ::ffff:172.16.0.11:443 ::ffff:172.16.0.254:47168 users:(("apache2",pid=16788,fd=31))

O qualcosa di più complesso, ad esempio tutte le porte in ascolto relative ad un mail server:

root@mail-1:~# ss -np state listening '( sport = :pop3s or sport = :imaps or sport = :ssmtp )'
Netid Recv-Q Send-Q Local Address:Port Peer Address:Port
tcp 0 100 *:993 *:* users:(("dovecot",pid=1132,fd=43))
tcp 0 100 *:995 *:* users:(("dovecot",pid=1132,fd=29))
tcp 0 100 *:465 *:* users:(("smtpd",pid=2052,fd=6),("master",pid=1260,fd=17))
tcp 0 100 :::993 :::* users:(("dovecot",pid=1132,fd=44))
tcp 0 100 :::995 :::* users:(("dovecot",pid=1132,fd=30))
tcp 0 100 :::465 :::* users:(("smtpd",pid=2052,fd=7),("master",pid=1260,fd=18))

Quali possono essere le opzioni per le query? È tutto elencato nella man page del comando, ad esempio per le porte TCP standard gli stati disponibili sono: established, syn-sent, syn-recv, fin-wait-1, fin-wait-2, time-wait, closed, close-wait, last-ack, listen and closing.

Ma la capacità dei filtri va oltre, è possibile ad esempio effettuare la lista di porte che rispettando determinati range:

root@mail-1:~# ss -np state listening '( sport ge 500 and sport le 1000 )'
Netid Recv-Q Send-Q Local Address:Port Peer Address:Port
tcp 0 100 *:993 *:* users:(("dovecot",pid=1132,fd=43))
tcp 0 100 *:995 *:* users:(("dovecot",pid=1132,fd=29))
tcp 0 100 :::993 :::* users:(("dovecot",pid=1132,fd=44))
tcp 0 100 :::995 :::* users:(("dovecot",pid=1132,fd=30))

E quindi tutte le combinazioni possibili <= / le, >= / ge, == / eq, != / ne, < / lt, > / gt. Niente male per un tool che potrebbe essere la copia sbiadita di netstat, non credete?

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

Introduzione al licensing per-VM

Facebooktwittergoogle_plusredditlinkedin

Come molte aziende hanno fatto negli ultimi anni, anche Veeam ha introdotto un modello di licensing basato su singole VM. A dire il vero questa soluzione era già presente sul mercato da molto tempo, ma mirata solo al mondo Partner e soprattutto legata alla versione Cloud di Veeam Backup & Replication.

Il perchè si è arrivati a questa soluzione è abbastanza semplice. L’opzione basata su Socket, la più famosa e storica, funziona per le realtà classiche che hanno almeno più di 15 VM da proteggere con ambienti più o meno complessi; tuttavia per scenari molto piccoli, in Italia siamo pieni di queste realtà, o semplicemente di Branch Office l’acquisto di un pack standard può risultare oneroso o addirittura inutile. Anche la licenza Essential non risulta funzionale, perchè offre due socket ed i costi potrebbero lo stesso essere alti.

Come si può vedere dalla tabella, i costi risultano vantaggiosi fino a 15 VM anche se poi non esiste un limite imposto da Veeam stessa e quindi si potrebbero acquistare 100 VM. Un’altra cosa che si nota è che la subscription è su base annuale con un periodo di 3 anni che garantisce al cliente anche uno sconto del 15%.

La licenza per-VM è disponibile per Veeam Availability Suite, Veeam Backup & Replication e Veeam ONE, tutti Update 3 o superiori.

Missing Index… Cache

Facebooktwittergoogle_plusredditlinkedin

Qualche settimana fa un collega mi ha sottoposto un quesito la cui risposta coinvolge alcuni concetti molto interessanti. La domanda più o meno era:

SQL Server mi dice che manca un indice, l’ho creato. Perchè eseguendo una query su sys.dm_db_missing_index_details trovo ancora l’indice come mancante? Come dico a SQL di aggiornare la DMV?

La risposta alla domanda è abbastanza semplice: una DMV non può essere aggiornata dall’utente. Unica componente con diritti di “scrittura” di questa particolare DMV è il Query Optimizer, che però va ad “aggiungere” informazioni. Se invece si vuole “svuotare” il contenuto della DMV.. beh.. non ci sono santi: serve riavviare l’istanza del server (come peraltro chiaramente riportato nella documentazione online)

E quindi? Come possiamo ricavare un’informazione in qualche modo equivalente?

A venirci in aiuto è il preziosissimo Query Plan, che grazie al lavoro del Query Optimizer può contenere anche alcune segnalazioni di warning, ovvero delle piccole anomalie che, se risolte, consentirebbero al Query Processor di eseguire la nostra query in maniera più efficiente e performante.

Così come già indicato da Sergio Govoni nell’articolo Conversioni implicite: La plan cache ci svela quante sono e dove avvengono!”  i tipi di warning che possono essere segnalati dal query optimizer sono:

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

“No matched indexes”? Proprio quello che ci serviva!!

Possiamo dunque pensare di ricavare le informazioni inerenti ad eventuali indici mancanti tramite una semplice query che vada ad esplorare lo stato attuale della plan cache!

Come? Con questa semplice query:

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
   , cte AS (
        SELECT QP.query_plan
             , Q.N.value('(//MissingIndexGroup/@Impact)[1]', 'FLOAT') AS Impact
             , Q.N.value('(//MissingIndex/@Database)[1]', 'SYSNAME') AS DatabaseName
             , Q.N.value('(//MissingIndex/@Schema)[1]', 'SYSNAME') AS SchemaName
             , Q.N.value('(//MissingIndex/@Table)[1]', 'SYSNAME') AS TableName
             , (
                 SELECT DISTINCT R.C.value('@Name', 'SYSNAME') + ', '
                 FROM Q.N.nodes('//ColumnGroup') AS T(CG)
                 CROSS APPLY T.CG.nodes('Column') AS R(C)
                 WHERE T.CG.value('@Usage', 'VARCHAR(10)') = 'EQUALITY'
                 FOR XML PATH('')
             ) AS EqualityColumns
             , (
                 SELECT DISTINCT R.C.value('@Name', 'SYSNAME') + ', '
                 FROM Q.N.nodes('//ColumnGroup') AS T(CG)
                 CROSS APPLY T.CG.nodes('Column') AS R(C)
                 WHERE T.CG.value('@Usage', 'VARCHAR(10)') = 'INEQUALITY'
                 FOR XML PATH('')
             ) AS InequalityColumns
             , ( 
                 SELECT DISTINCT R.C.value('@Name', 'SYSNAME') + ', '
                 FROM Q.N.nodes('//ColumnGroup') AS T(CG)
                 CROSS APPLY T.CG.nodes('Column') AS R(C)
                 WHERE T.CG.value('@Usage', 'VARCHAR(10)') = 'INCLUDE'
                 FOR XML PATH('')
             ) AS IncludeColumns
        FROM sys.dm_exec_cached_plans CP
        CROSS APPLY sys.dm_exec_query_plan(CP.plan_handle) QP
        CROSS APPLY QP.query_plan.nodes('//StmtSimple') AS Q(N)
        WHERE Q.N.exist('QueryPlan/MissingIndexes') = 1
    ) 
SELECT cte.query_plan
     , cte.Impact
     , cte.DatabaseName
     , cte.SchemaName
     , cte.TableName
     , LEFT(cte.EqualityColumns  , LEN(cte.EqualityColumns)   - 1) AS EqualityColumns
     , LEFT(cte.InequalityColumns, LEN(cte.InequalityColumns) - 1) AS InequalityColumns
     , LEFT(cte.IncludeColumns   , LEN(cte.IncludeColumns)    - 1) AS IncludeColumns
FROM cte;

 

OK.. non proprio semplicissima, ve lo concedo!… ma il bello è che l’ho già scritta io per voi! 😉

Alla prossima!