sqlschool.gr logo


Articles of SQLschool.gr Team

Manage and Monitor SQL Server instances - Identify sessions that consume tempdb resources

Antonios Chatzipavlis
Monday 02 April 2018

Η 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 τα οποία χρησιμοποιώντας τα μπορούμε να κατανοήσουμε αλλά και να βρούμε τα τυχόν θέματα με την βάση αυτή.


Παρέχει πληροφορίες για τη χρήση του κάθε αρχείου (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.


Παρέχει πληροφορίες για τον αριθμό των 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


Παρέχει πληροφορίες για τον αριθμό των 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.


Παρέχει πληροφορίες για όλα τα ενεργά 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.


Παρέχει πληροφορίες για όλα τα 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 */

Antonios Chatzipavlis

Antonios Chatzipavlis

Antonios is a Data Solutions Consultant and Trainer. He has been working in IT since 1988. In his career, he has worked as senior developer, IT Manager, Solutions Architect and IT Consultant. Since 1995 he has been devoted on new technologies and software development tools, mainly by Microsoft, either by training company staff and colleagues or assisting them in design, development and implementation as a consultant or chief developer. He has focused in Databases and Data Science since 1995. He specialized in Microsoft SQL Server since version 6.0 in areas like SQL Server Internals, Database Design and Development, Business Intelligence and in 2010 he has started working with Azure Data Platform, NoSQL databases, Big Data Technologies and Machine Learning. He is an active member of many IT communities in Greece, answering colleagues' questions and writing articles in his web site. He is the owner of SQLschool.gr which is a community portal with a lot of information about Microsoft SQL Server. He has been a Microsoft Certified Trainer (MCT) since 2000. Microsoft honored him as MVP on Data Platform due to his activities in SQL Server since 2010. He holds a large number of Microsoft Certifications and Microsoft SQL Server Certifications since version 6.5.


What's New in SQL Server 2022 - Episodes

More Tips...

Become a member

If you want to receive updates from us become a member to our community.




sqlschool.gr © 2010-2024 All rights reserved

This site uses cookies for operational and analytics purposes only. By continuing to browse this site, you agree to their use.