Πόσες και πόσες φορές δεν έχετε έρθει αντιμέτωποι με εργασίες που διαρκούν περισσότερο χρόνο από όσο λογικά θα έπρεπε να έχει και αναρωτιέστε γιατί. Σίγουρα το πρώτο πράγμα που θα σκεφτείτε είναι να βελτιώσετε το query ή τη stored procedure ή το batch ή ότι εκτελείται. Είναι όμως πάντα αυτή η σωστή προσέγγιση. Μήπως κάπου αλλού είναι το πρόβλημα; Μήπως έχεις το SQL Server instance με τα installation default;

Στα 26 χρόνια στο χώρο και στα 17 χρόνια που ασχολούμαι με databases και ειδικά με τον SQL Server έχω μάθει να αναλύω όσο περισσότερο γίνεται το πρόβλημα και ιδιαίτερα όταν αυτό αφορά θέμα απόδοσης.
Σε αυτά τα χρόνια πολλά από τα προβλήματα με τα οποία έχω έρθει αντιμέτωπος λύθηκαν κάνοντας tuning κάτι άλλο από αυτό που αρχικά φαίνονταν σαν πρόβλημα.

Σε πολλές από αυτές τις περιπτώσεις η αίτια του προβλήματος ήταν η tempdb και το γεγονός ότι είτε αυτή δεν ήταν σωστά configured είτε ότι δεν ήταν καθόλου configured.

Έχω πει και έχω γράψει αρκετά για το πόσο σημαντική είναι η tempdb και πόσο πολύ χρησιμοποιείται από τον SQL Server. Αυτό σημαίνει ότι υπάρχει αρκετά μεγάλη πιθανότητα σε ένα παραγωγικό σύστημα με πολλούς χρήστες και με εκτεταμένη χρήση αυτής να δημιουργείται καθυστέρηση σε αυτό εξαιτίας αυτής.

Η tempdb είναι και αυτή μια database στον SQL Server και ακολουθεί τα βασικά χαρακτηριστικά που έχουν όλες οι databases. Χαρακτηριστικά που αφορούν το size και το πώς βρίσκει ο SQL Server την επόμενη διαθέσιμη , φυσικά σαν system database έχει ιδιαιτερότητες κυρίως λόγο χρήσεως.

Τι είναι το tempdb contention;

Σε κάθε βάση έτσι και σε στη tempdb το κάθε data file έχει ένα μεγεθος Χ το οποίο είναι εσωτερικά δομημένο σε σελίδες των 8ΚΒ. Οι σελίδες αυτές δεν ανήκουν αρχικά σε κάποιο αντικείμενο είναι με άλλα λόγια ελεύθερες προς χρήση. Για να βρει ο SQL Server ποιες είναι διαθέσιμες για χρήση δεν θα τις ψάξει μία μία γιατί κάτι τέτοιο όπως εύκολα είναι κατανοητό θα ήταν χάσιμο χρόνου. Αυτό ο SQL Server το πετυχαίνει με την χρήση κάποιων special pages (έχω αναφερθεί σε αυτές αναλυτικά στο http://www.sqlschool.gr/blog/database-data-file-special-pages-830.aspx).

Αυτές που χρησιμοποιούνται για τον εντοπισμό των ελεύθερων σελίδων είναι οι Page Free Space (PFS) , Global Allocation Map (GAM) και Shared Global Allocation Map (SGAM).

Κάθε data file έχει την πρώτη του PFS στην θέση 1 και επαναλαμβάνεται κάθε 8088 σελίδες, την πρώτη του GAM στην θέση 2 και επαναλαμβάνεται κάθε 511.232 σελίδες και την πρώτη του SGAM στην θέση 3 και η οποία επαναλαμβάνεται κάθε 511.232+1 σελίδες.

Όταν υπάρχει μεγάλη χρήση της tempdb οι σελίδες αυτές αναγκαστικά θα χρησιμοποιηθούν πάρα πολύ. Kάθε φορά ένας μπορεί να τις μεταβάλει με αυτό να σημαίνει ότι υπάρχει κάποιο είδος locking (PAGELATCH) σε αυτές. Επειδή στην tempdb συνήθως μπαίνουν πολλά προσωρινά και μεγάλα objects κάτι τέτοιο απαιτεί και περισσότερο χώρο και εδώ εστιάζεται το πρόβλημα καθώς αυτό θα δημιουργήσει καθυστέρηση λόγο "κυκλοφοριακού" πράγμα μη επιθυμητό.

Πώς λύνεται το tempdb contention;

Αν και έχουν γραφτεί αρκετές λύσεις για την αντιμετώπιση του συγκεκριμένου θέματος, μια είναι αυτή που έχει προκριθεί και θεωρείτε σαν best practice προερχόμενη από την εμπειρία των μηχανικών της Microsoft και η οποία λέει:
Για servers με  8 cores και κάτω δημιουργούμε για αρχή 4 data files στην tempdb του ίδιου μεγέθους όλα και με ίδια χαρακτηριστικά όσον αφορά πως μεγαλώνουν σε διαφορετικό χώρο από εκεί που είναι οι user databases και παρακολουθούμε την κατάσταση αν μειώνονται τα PAGELATCHes στις συγκεκριμένες σελίδες. Αν συνεχίζει τότε προσθέτουμε ακόμα 4 data files πάλι με ίδια λογική, ισομεγέθη και με τα ίδια χαρακτηριστικά με τα πρώτα. Αλλά δεν βάζουμε περισσότερα από τον αριθμό των cores.
Για servers με πάνω από 8 cores ξεκινάμε με 8 data files παρακολουθούμε και ανεβαίνουμε ανά 4 data files εφόσον βλέπουμε να συνεχίζει το tempdb contention και πάλι δεν ξεπερνάμε τον αριθμό των cores.
Επίσης υπάρχει και το trace flag 1118 (ΚΒ 328551) το οποίο επικουρικά και σε πραγματικά πολύ μεγάλο όγκο βοηθάει περισσότερο την κατάσταση.

Πως βρίσκω το αν έχω tempdb contention;

Για να φτάσουμε στο σημείο να υλοποιήσουμε την λύση θα πρέπει να βρούμε ότι υποφέρουμε από αυτό το φαινόμενο και αυτό μπορεί να γίνει με τους δύο παρακάτω τρόπους

Ο πρώτος είναι να εκτελούμε το παρακάτω query, το οποίο κοιτάζει για contention μόνο στην tempdb και μόνο για τις PFS, GAM, SGAM για αυτό και βλέπετε ότι υπάρχει το τελευταίο field, Resource Type.

WITH T AS
(
      SELECT  
        session_id
            , wait_type
            , wait_duration_ms
            , blocking_session_id
            , resource_description
            , CAST(RIGHT(resource_description, LEN(resource_description)
              - CHARINDEX(':', resource_description, 3)) AS INT) AS PageID
    FROM sys.dm_os_waiting_tasks
    WHERE 
            wait_type LIKE 'PAGE%LATCH_%'
            AND 
            resource_description LIKE '2:%'
)     
SELECT 
            * , 
            CASE
            WHEN PageID = 1 OR PageID % 8088 = 0 THEN 'Is PFS Page'
            WHEN PageID = 2 OR PageID % 511232 = 0 THEN 'Is GAM Page'
            WHEN PageID = 3 OR (PageID - 1) % 511232 = 0 THEN 'Is SGAM Page'
            ELSE 'Is Not PFS, GAM, or SGAM page'
            END AS ResourceType
FROM T;
GO

To μειονέκτημα είναι ότι αν πχ το εκτελέσω τώρα μπορεί να μην μου δείξει κάτι και κάθε φορά που το εκτελώ να μην έχω θέμα αλλά στο ενδιάμεσο χρόνο να δείχνει πρόβλημα. Αυτό σημαίνει ότι θα πρέπει να έχω ένα μικρό interval μεταξύ των κλήσεων.

Ο άλλος τρόπος αν είμαι σε SQL Server 2012 να φτιάξω ένα Extended Event το οποίο να κάνει χρήση του sqlserver.latch_suspend_end event και να εξετάσω τα δεδομένα που αυτό έχει συγκεντρώσει

CREATE EVENT SESSION [MonitorTempdbContentionXE] ON SERVER 
ADD EVENT sqlserver.latch_suspend_end(
    WHERE (((([database_id]=(2)) AND ([duration]>(0))) AND ([mode]=(2))) OR ([mode]=(3)))) 
ADD TARGET package0.event_file(SET filename=N'D:\TEMP\MonitorTempdbContentionXE.xel')
GO

/*antonch*/