Home > Articoli > Uso “nascosto” del tempdb

Uso “nascosto” del tempdb

Qualche settimana fa, mentre ero al lavoro, mi sono ritrovato a dover risolvere un problema apparentemente non molto strano, ma che tuttavia nasconde qualche retroscena interessante. I fatti sono stati più o meno questi:

Circa a metà mattinata mi è stato segnalato un problema di prestazioni su uno dei sistemi che abbiamo in gestione nel nostro team di lavoro; dopo alcuni semplici controlli è stato subito chiaro che il rallentamento era sostanzialmente dovuto ad un problema di contency sul tempdb. “Beh.. abbastanza semplice!” – ho subito pensato! – “la colpa è mia perché (ahi ahi ahi) non ho ancora fatto lo split del file dati sul tempdb!”.

Così, alla prima occasione utile, ho provveduto a splittare i file (4 nel mio caso) pensando di aver già praticamente risolto il problema! Il primo giorno tutto ok, il secondo anche, ma il terzo… immaginerete la sorpresa nel sentirmi dire che si stava ripresentando esattamente lo stesso problema! Ovviamente, da tecnico, ho voluto (e dovuto) verificare, perché il primo pensiero è stato che la causa dei (nuovi) rallentamenti fosse un’altra. Ma la sorpresa è stata doppia: non solo c’era ancora un problema di contency sul tempdb, ma era apparentemente più grave di quello segnalatomi qualche giorno prima. Utilizzando la store procedure sp_whoisactive di Adam Machanic, sono facilmente risalito al comando che stressava maggiormente il tempdb: una banale stored procedure per la scrittura di log!

Eccola:

CREATE PROCEDURE dbo.WriteLog
    @level INT,
    @message NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;
    IF EXISTS (SELECT * FROM dbo.LogLevel WHERE [Level] >= @level)
        INSERT INTO [dbo].[Log] ([Level], [Message], [TimeStamp]) VALUES (@level, @message, GETDATE());
END;

… eh??? … ma dove sarebbe l’accesso al tempdb?!

La risposta alla domanda è abbastanza semplice (anche se non del tutto intuitiva) e riguarda l’uso (implicito) delle “Worktables” (qui la definizione ufficiale), ma per scoprirlo dobbiamo scendere più in dettaglio e dare un’occhiata al piano di esecuzione:

Uso nascosto del tempdb Old Plan

Quello che balza subito all’occhio è che per valutare l’esistenza (funzione EXISTS) del set di dati definito dalla query “SELECT * FROM dbo.LogLevel WHERE [Level] >= @level” viene utilizzato l’operatore “Nested Loops”. In questa situazione, Query Optimizer produce un piano di esecuzione finalizzato alla determinazione della prima riga nel minor tempo possibile e presuppone che i dati siano distribuiti uniformemente. L’operatore Nested Loops fornisce spesso il modo più veloce per restituire un piccolo numero di righe (altri operatori come Hash Join o Merge Join sono più performanti su grandi volumi di dati). Qui, la scelta di utilizzare l’operatore Nested Loops è dovuta al fatto che il Query Optimizer ha applicato il pattern Row Goal, tipico per l’implementazione della clausola EXISTS. L’output viene utilizzato da uno step successivo del piano di esecuzione (Compute Scalar) per poter effettuare il test di esistenza (COND WITH QUERY). La seconda query (INSERT) invece utilizza un piano di esecuzione del tutto lineare senza presentare nulla di anomalo.

Quando l’operazione di join, implementata da Nested Loops, non potrà essere eseguita in memoria, l’Engine utilizzerà una worktable (come abbiamo osservato in questo esempio) per memorizzare temporaneamente il risultato di tale algoritmo al fine di renderlo disponibile per lo step successivo.

Più in generale è possibile dire che l’engine di SQL Server può decidere di utilizzare una worktable per poter completare qualsiasi operazione logica di uno statement SQL.

  • UNION, EXCEPT, INTERSECT
  • CTE
  • Sub-query
  • Funzioni
  • Viste
  • Variabili tabella
  • Operazioni di ordinamento
  • Raggruppamenti
  • etc etc

sono tutti ottimi candidati all’utilizzo di working tables.

Ritornado al caso esemplificato, il problema era dunque legato all’utilizzo della funzione EXISTS, la quale probabilmente sia appoggiava al tempdb per valutare l’esistenza del set di dati definito dalla query “SELECT * FROM dbo.LogLevel WHERE [Level] >= @level“.

Un numero molto elevato di scritture di log (applicativi) nei momenti di picco di utilizzzo del sistema producevano un numero altrettanto elevato di accessi al tempdb e conseguenti latch che, di fatto, accodavano tutte le richieste (peraltro in un contesto dove il tempdb è già molto sollecitato da operazioni ben più rilevanti del semplice logging!).

La soluzione più efficace, in questo caso, è stata quella di elimiare l’utilizzo della funzione EXISTS e riscrivere la stored procedure nel seguente modo:

ALTER PROCEDURE dbo.WriteLog
    @level INT,
    @message NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO [dbo].[Log] ([Level], [Message], [TimeStamp])
    SELECT TOP 1 @level, @message, GETDATE()
    FROM dbo.LogLevel
    WHERE [Level] >= @level;
END;

Il piano di esecuzione diventa dunque:

Uso nascosto del tempdb New Plan

Come si può facilmente vedere, oltre ad aver unito le due query in una sola, scompare in questo modo anche l’utilizzo dell’operatore Nested Loop e, scorrendo più attentamente il piano di esecuzione, possiamo concludere che non dovrebbero esserci ulteriori step “a rischio di Worktable”!

Chi è Luca Bruni

Luca Bruni ottiene la Laurea Magistrale in Ingegneria Informatica presso il Politecnico di Milano (sede di Como) nel 2006. Ha lavorato in diverse aziende coprendo vari ruoli: Software Developer, Consultant, Project Manager. Dal 2010 lavora in qualità di Software & Data Architect presso una nota software house svizzera. Lavora con SQL Server dalla versione 2000 e da Dicembre 2016 è membro attivo di UGISS.

Leggi Anche

SQL Server 2016 Cumulative Updates (Agosto 2017)

Sono stati rilasciati i seguenti Cumulative Update per SQL Server 2016 (RTM e SP1). Cumulative …