Δεν είναι λίγες οι φορές που σαν DBA θα έρθεις αντιμέτωπος με το φαινόμενο η tempdb να μεγαλώνει υπέρμετρα και ξαφνικά, ενώ δεν έχεις κάνει κάποιες αλλαγές ή δεν έχει βάλει κάτι να εκτελεστεί που είναι γνωστό ότι κάνει εκτεταμένη χρήση αυτής. Για να δούμε μπορούμε να βρούμε τι έχει γίνει;

Η ερώτηση

Αφορμή για αυτό το post είναι και πάλι μια ερώτηση που μου ήρθε από ένα συνάδελφο.

"Έχω ένα ERP από την εταιρεία Χ που έχει τα δεδομένα του σε SQL Server και η βάση είναι στα 50GB. H tempdb έχει φτάσει τα 55GB και ρωτάω την εταιρεία Χ και μου λένε κάτι κουφά όπως την tempdb την μεγαλώνει κάποιο query αλλά δεν ξέρουν ποιο είναι. Αυτή είναι η μόνη database στον SQL Server. Είναι φυσιολογικό να έχει φτάσει τόσο η tempdb;  Γιατί σε άλλο SQL Server instance έχω μεγαλύτερη βάση αλλά η tempdb είναι 5GB. Έχεις κάποιον μπούσουλα για να βρω τι είναι αυτό που χρησιμοποιεί τόσο πολύ την tempdb γιατί όπως σου είπα από την Χ εταιρεία δεν … Και πως μπορώ να μειώσω το μέγεθος της tempdb;"

Η απάντηση

Αρχικά θα πρέπει να υπενθυμίσω ότι η χρήση της συγκεκριμένη βάσης στον SQL Server με την πάροδο των εκδόσεων γίνεται όλο και μεγαλύτερη όπως έχω γράψει και σε παλαιότερο μου post (http://www.sqlschool.gr/blog/λίγα-λόγια-για-την-tempdb-στο-sql-server-2005-89.aspx) και το οποίο ακόμα και σήμερα ισχύει

Πως μειώνω το μέγεθος της tempdb;

Η απάντηση στο ερώτημα το πώς μειώνεται το μέγεθος της είναι απλή. Κάθε φορά που ξεκινάει το SQL Service η tempdb δημιουργείται με ότι έχει ορισθεί σαν αρχικό μέγεθος δημιουργίας και αυτό μπορεί κανείς εύκολα να το δει από τα properties > Files στην συγκεκριμένη βάση. Βέβαια αυτό σημαίνει ότι θα πρέπει να κάνεις ένα restart σε κάποια στιγμή που δεν γίνεται χρήση του SQL Server, αλλά θεωρώ ότι σε περιπτώσεις σαν και αυτή του συναδέλφου είναι αρκετά εύκολο να βρεθεί η στιγμή αυτή που μπορεί το σύστημα να πέσει για μερικά λεπτά.

Τι είναι αυτό που χρησιμοποιεί τόσο πολύ την tempdb;

Για να αρχίσει κανείς να απαντά στο ερώτημα του τι είναι αυτό που κάνει μεγάλη χρήση στην tempdb, θα πρέπει να δει ποια είναι η κατηγορία εργασιών που κάνει μεγάλη χρήση αυτής. Υπάρχουν τρεις κατηγορίες  βασικές κατηγορίες δραστηριοτήτων που μας ενδιαφέρουν και αφορούν τα internal objects, τα user objects και όσοι έχουν snapshot isolation level ενεργοποιημένο στην βάση τους (δείτε το SQL Night με θέμα Concurrency in SQL Server) το version store.

Αυτό μπορεί κανείς να το δει εύκολα κάνοντας ένα query στο dmv sys.dm_db_file_space_used. Αυτό επιστρέφει τόσες γραμμές όσα είναι τα data file της tempdb. Επειδή μπορεί να έχω περισσότερα από ένα συνήθως κάνω sum στα δεδομένα του για να πάρω την συνολική εικόνα. Να επισημανθεί ότι αυτό τo dmv απεικονίζει την χρονική στιγμή που εκτελείται.
Για αυτούς που πιθανώς αντιμετωπίζουν θέμα για να γράψουν το συγκεκριμένο query είναι το παρακάτω με τις επιστρεφόμενες τιμές να είναι σε ΚΒ. Αν θέλει κάποιος αυτές να είναι σε ΜΒ ας χρησιμοποιήσει την δεύτερη εκδοχή.

USE tempdb;
-- results in kb
SELECT
        SUM (user_object_reserved_page_count)*8 as [User Objects Total (KB)]
      , SUM (internal_object_reserved_page_count)*8 as [Internal Objects Total (KB)]
      , SUM (version_store_reserved_page_count)*8  as [Version Store Total (KB)]
      , SUM (unallocated_extent_page_count)*8 as [Free Space Total (KB)]
      , SUM (mixed_extent_page_count)*8 as [Mixed Extents Total (KB)]
FROM sys.dm_db_file_space_usage;
GO
-- results in mb
SELECT
        (SUM (user_object_reserved_page_count)*8)/1024.0 as [User Objects Total (MB)]
      , (SUM (internal_object_reserved_page_count)*8)/1024.0 as [Internal Objects Total (MB)]
      , (SUM (version_store_reserved_page_count)*8)/1024.0  as [Version Store Total (MB)]
      , (SUM (unallocated_extent_page_count)*8)/1024.0 as [Free Space Total (MB)]
      , (SUM (mixed_extent_page_count)*8)/1024.0 as [Mixed Extents Total (MB)]
FROM sys.dm_db_file_space_usage;
GO

Αυτό που μας ενδιαφέρει είναι να δούμε ποιο από τα User Objects Total, Internal Object Total και Version Store Total είναι μεγαλύτερο.

Εάν το μεγαλύτερο είναι το Version Store Total σημαίνει ότι μεγαλώνει το version store γρηγορότερα από ότι καθαρίζει και για αυτό θα πρέπει να δεις τι γίνεται στην βάση που έχεις ενεργοποιημένο το snapshot isolation level.

Για το internal δεν κάνουμε κάτι, και συνήθως δεν θα είναι αυτό το μεγαλύτερο. Αν είναι θα είναι στιγμιαίο και σε επόμενη εκτέλεση θα έχει μικρύνει.

Αν όμως το User Object Total είναι το μεγαλύτερο τότε κάπου υπάρχει κάτι (query) που χρησιμοποιεί temp table / variable ή αν θα δεις το execution plan θα δεις ότι περιέχει για παράδειγμα sort ή hash match ή spool operator κ.α. που  παραδοσιακά κάνουν χρήση της tempdb.

Για να δεις λοιπόν τι γίνεται εκτελείται την χρονική στιγμή αυτή και πόσο χώρο καταλαμβάνει αρκεί να εκτελεστει το παρακάτω query. Αυτό έχει σαν βάση ένα άλλο dmv το sys.dm_db_task_space_usage το οποίο επιστρέφει τις σελίδες που χρησιμοποιούνται ή ελευθερώνονται σε κάθε ενέργεια (request_id) από κάθε session(session_id) στην tempdb database (database_id=2) μαζί με το execution context (exec_context_id). Αυτά μου είναι χρήσιμα καθώς μπορώ να διαβάσω περισσότερες και χρήσιμες πληροφορίες από τα dmv που αναφέρονται στο query και μπορείτε να δείτε περισσότερες πληροφορίες για αυτά στα BOL. Φυσικά μπορείς να εμφανίσεις περισσότερη πληροφορία καθώς ο συνδυασμός αυτών των DMVs μπορεί να δώσει πολλά περισσότερα.

USE tempdb;
SELECT  
(        T.user_objects_alloc_page_count - T.user_objects_dealloc_page_count) +
        (T.internal_objects_alloc_page_count - T.internal_objects_dealloc_page_count) as [Total OutStanding Pages]   
      , (T.user_objects_alloc_page_count - T.user_objects_dealloc_page_count) as [User Objects OutStanding Pages]
      , (T.internal_objects_alloc_page_count - T.internal_objects_dealloc_page_count) as [Internal Objects OutStanding Pages]
      , T.user_objects_alloc_page_count as [User Object Allocated Pages]
      , T.internal_objects_alloc_page_count as [Internal Objects Allocated Pages]
      , T.user_objects_dealloc_page_count as [User Objects Deallocated Pages]
      , T.internal_objects_dealloc_page_count as [Internal Objects Deallocated Pages]
      , T.session_id as [session ID]
      , S.login_name as [User login]
      , S.program_name as [Application name]
      , R.start_time as [Start Time]
      , R.command as [Command Type]
      , DB_NAME(R.database_id) as [Database Name]
      , SUBSTRING(Q.text, R.statement_start_offset/2 + 1, 
                    (CASE 
                        WHEN R.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max),Q.text)) * 2 
                        ELSE R.statement_end_offset 
                        END - R.statement_start_offset)/2) as [Query Text]
      , R.status as [Status]
      , Q.dbid as QueryExecContextDBID
      , DB_NAME(Q.dbid) as QueryExecContextDBNAME
      , Q.objectid as [Module Object Id]
      , R.cpu_time as [CPU Time (ms)]
      , R.total_elapsed_time as [Elapsed Time (ms)]
      , R.reads
      , R.writes
      , R.logical_reads
FROM sys.dm_db_task_space_usage as T 
      inner join sys.dm_exec_requests as R ON ( T.session_id = R.session_id and T.request_id = R.request_id) 
      inner join sys.dm_exec_sessions as S ON ( T.session_id = S.session_id ) 
      CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) as Q
WHERE (T.internal_objects_alloc_page_count+T.user_objects_alloc_page_count) > 0
ORDER BY 1 DESC;
GO

Το συγκεριμένο query επιστρέφει αυτά τα task που έχουν το άθροισμα των allocated internal και user objects μεγαλύτερο από μηδέν, ταξινομημένα σε φθίνουσα σειρά με βάση το άθροισμα των Total OutStanding Pages.

Enjoy it
/*antonch*/