sqlschool.gr logo

articles

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

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 */


Antonios Chatzipavlis

Antonios Chatzipavlis

Antonios Chatzipavlis is a highly experienced Data Solutions Consultant and Trainer. He has been working in the IT industry since 1988, holding various roles such as senior developer, IT Manager, Data & AI Solutions Architect and Consultant.

Since 1995, Antonios has focused on modern technologies and software development tools, primarily by Microsoft. He has specialized in Data & AI since 2000, with expertise in Microsoft Data Platform (SQL Server, Azure SQL Databases, Azure Synapse Analytics, Microsoft Fabric, Power BI, AI) and Databricks.

Antonios is also a Microsoft Certified Trainer (MCT) for over 25 years, has been recognized as a Microsoft Most Valuable Professional (MVP) in Data Platform since 2010 and he is in the Data Expert 40 Powerlist 2024 by Boussias. He is the co-founder and visionary behind XLYTiCA, a company dedicated to Data & AI solutions.

Episode

Task Flows in Microsoft Fabric

image

More Episodes...

Tip

Get Certified: Become a Fabric Data Engineer

More Tips...

Become a member

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

Connect

Explore

Learn

sqlschool.gr © 2010-2025 All rights reserved

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