Η tempdb είναι μια database την οποία μοιράζονται ΟΛΟΙ όσοι χρησιμοποιούν το instance στο οποίο αυτή υπάρχει. Στην tempdb αποθηκεύονται πολλά όπως user objects, internal objects, και version stores.

Τα user objects περιλαμβάνουν global temporary tables & indexes, local temporary tables & indexes, system tables & indexes, table-valued functions output tables, table variables, user-defined tables, and indexes.

Τα Internal objects περιέχουν sort runs, work files (είναι αυτά που χρησιμοποιούνται σε joins), και work tables (είναι αυτά που χρησιμοποιούνται από cursor operations, spool operations και temporary large object (LOB) storage).

Τα version stores χρησιμοποιούνται από λειτουργίες του SQL Server όπως όταν μια database κάνει χρήση των ALLOW_SNAPSHOT_ISOLATION ή READ_COMMITTED_SNAPSHOT isolation levels. Υπάρχουν DML triggers fired. Όταν έχω sessions στα οποία έχω Multiple Active Results Sets (MARS) ενεργοποιημένο. Τέλος όταν έχω online indexing operations.

Από τα παραπάνω και μόνο καταλαβαίνουμε ότι η καθολική και βαριά της χρήση την καθιστά σαν την σημαντικότερη system database στο instance και για αυτό πρέπει να δοθεί ιδιαίτερη προσοχή στο configuration της.

Για το λόγο αυτό έχουμε στα χέρια μας κάποια DMVs τα οποία χρησιμοποιώντας τα μπορούμε να κατανοήσουμε αλλά και να βρούμε τα τυχόν θέματα με την βάση αυτή.

sys.dm_db_file_space_usage

Παρέχει πληροφορίες για τη χρήση του κάθε αρχείου (data / log ) σε κάθε database. Στην περίπτωση μας μας ενδιαφέρει μόνο η tempdb οπότε το συγκεκριμένο θα πρέπει αν το εκτελέσουμε στο context αυτής. Αυτό γυρίζει αρκετές χρήσιμες πληροφορίες αλλά κυρίως μας ενδιαφέρουν τα πεδία (δείτε τα docs για αυτά) allocated_extent_page_count, internal_object_reserved_page_count , mixed_extent_page_count, unallocated_extent_page_count , user_object_reserved_page_count, version_store_reserved_page_count.

sys.dm_db_session_space_usage

Παρέχει πληροφορίες για τον αριθμό των pages που κάθε session χρησιμοποιεί ή όχι στο ενεργό από αυτό query σε όποια database. Έτσι και αλλιώς το συγκεκριμένο DMV δείχνει μόνο δεδομένα από την tempdb. Ενδιαφέρον έχουν τα πεδία internal_objects_alloc_page_count, internal_objects_dealloc_page_count, user_objects_alloc_page_count, user_objects_dealloc_page_count, user_objects_deferred_dealloc_page_count

sys.dm_db_task_space_usage

Παρέχει πληροφορίες για τον αριθμό των pages που χρησιμοποιούνται για κάθε task. Και αυτή φέρνει μόνο πληροφορίες από την tempdb. Επιστρέφει τα ίδια πεδία (internal_objects_alloc_page_count, internal_objects_dealloc_page_count, user_objects_alloc_page_count, user_objects_dealloc_page_count) που επιστρέφει και η sys.dm_db_session_space_usage DMV.

sys.dm_tran_active_snapshot_database_transactions

Παρέχει πληροφορίες για όλα τα ενεργά transactions τα οποία δημιουργούν ή διαβάζουν τα row versions. Για να δούμε αν κάποιο transactions κάνει χρήση των row versions τα πεδία που μας ενδιαφέρουν είναι τα transaction_id, transaction_sequence_num, average_version_chain_traversed, is_snapshot, elapsed_time_seconds, first_snapshot_sequence_num, max_version_chain_traversed.

sys.dm_tran_version_store

Παρέχει πληροφορίες για όλα τα version records στο version store. Η συγκεκριμένη θέλει μια μικρή προσοχή καθώς τα records που δεν χωράνε στο page (8192 bytes) καταναλώνουν περισσότερο και τα βλέπουμε με τα record_image_first_part και record_image_second_part πεδία και πιθανά θα δούμε περισσότερες γραμμές.

Common Error Messages

Συχνότερα error messages είναι τα 1101, 1105, 3958, 3966, 3959, 3967

How to find queries that consumes space in tempdb

with t as 
(
    select  session_id
        ,   request_id
        ,   sum(internal_objects_alloc_page_count) as allocated
        ,   sum(internal_objects_dealloc_page_count) as deallocated
    from sys.dm_db_task_space_usage
    group by session_id, request_id
)
select  r.session_id
    ,   r.request_id
    ,   cast ('<?query --'+CHAR(13)+CHAR(10)+q.text++CHAR(13)+CHAR(10)+'--?>' as xml) AS query
    ,   t.allocated AS task_allocatted_pages_internal_object
    ,   t.deallocated AS task_deallocated_pages_internal_object
from t
join sys.dm_exec_requests as r on t.session_id = r.session_id  AND t.request_id = r.request_id
cross apply sys.dm_exec_sql_text (r.sql_handle) as q
order by t.allocated desc;

/* antonch */