Tempdb 공간 사용 확인

SQL 2005 이상

 

http://www.madeira.co.il/troubleshooting-tempdb-space-usage/ 발췌

 

Tempdb is a critical resource in SQL Server. It is used internally by the database engine for many operations, and it might consume a lot of disk space. In the past two weeks I encountered 3 different scenarios in which tempdb has grown very large, so I decided to write about troubleshooting such scenarios.
Before I describe the methods for troubleshooting tempdb space usage, let’s begin with an overview of the types of objects that consume space in tempdb. There are 3 types of objects stored in tempdb:

• User Objects
• Internal Objects
• Version Stores 

A user object can be a temporary table, a table variable or a table returned by a table-valued function. It can also be a regular table created in the tempdb database. A common misconception is that table variables (@) do not consume space in tempdb, as opposed to temporary tables (#), because they are only stored in memory. This is not true. But there are two important differences between temporary tables and table variables, when it comes to space usage:

1.     Indexes and statistics on temporary tables also consume space in tempdb, while indexes and statistics on table variables don’t. This is simply because you cannot create indexes or statistics on table variables.

2.     The scope of a temporary table is the session in which it has been created, while the scope of a table variable is the batch in which it has been created. This means that a temporary table consumes space in tempdb as long as the session is still open (or until the table is explicitly dropped), while a table variable’s space in tempdb is deallocated as soon as the batch is ended.

Internal objects are created and managed by SQL Server internally. Their data or metadata cannot be accessed. Here are some examples of internal objects in tempdb:

• Query Intermediate Results for Hash Operations
• Sort Intermediate Results
• Contents of LOB Data Types
• Query Result of a Static Cursor

Unlike user objects, operations on internal objects in tempdb are not logged, since they do not need to be rolled back. But internal objects do consume space in tempdb. Each internal object occupies at least 9
pages (one IAM page and 8 data pages). tempdb can grow substantially due to internal objects when queries that process large amounts of data are executed on the instance, depending on the nature of the queries.

Version stores are used for storing row versions generated by transactions in any database on the instance. The row versions are required by features such as snapshot isolation, after triggers and online index build. Only when row versioning is required, the row versions will be stored in tempdb.
As long as there are row versions to be stored, a new version store is created in tempdb approximately every minute. These version stores are similar to internal objects in many ways. Their data and metadata cannot be accessed, and operations on them are not logged. The difference is, of-course, the data that is stored in them.

When a transaction that needs to store row versions begins, it stores its row versions in the current version store (the one that has been created in the last minute). This transaction will continue to store row versions in the same version store as long as it’s running, even if it will run for 10 minutes. So the size of each version store is determined by the number and duration of transactions that began in the relevant minute, and also by the amount of data modified by those transactions.

Version stores that are not needed anymore are deallocated periodically by a background process. This process deallocates complete version stores, not individual row versions. So, in some cases, it might take a while till some version store is deallocated.
There are two types of version stores. One type is used to store row versions for tables that undergo online index build operations. The second type is used for all other scenarios.

 

 

 

 

-- 실행되는 구문의 temp page allocate
select task.session_id, ses.host_name, ses.login_name,ses.login_time
       ,(task.internal_alloc + task.oject_alloc) -(task.internal_dealloc +task.oject_delloc) as used
       ,task.internal_alloc,task.internal_dealloc,task.oject_alloc,task.oject_delloc
    ,object_name(qt.objectid, qt.dbid) as 'spname'
    ,req.cpu_time,req.logical_reads,req.reads, req.writes  
    ,substring(qt.text,req.statement_start_offset/2,
        (case when req.statement_end_offset = -1
        then len(convert(nvarchar(max), qt.text)) * 2
        else req.statement_end_offset end - req.statement_start_offset)/2) as query
    ,req.statement_start_offset, req.statement_end_offset, req.plan_handle, req.sql_handle
from 
       (
             select session_id, request_id
                    ,sum (internal_objects_alloc_page_count) /128 as internal_alloc
                    ,sum (internal_objects_dealloc_page_count)/128 as internal_dealloc 
                                 ,sum (user_objects_alloc_page_count ) /128 as oject_alloc
                                 ,sum (user_objects_dealloc_page_count ) /128 as oject_delloc
              from sys.dm_db_task_space_usage  with (nolock)
             group by session_id, request_id
    ) as task
inner join  sys.dm_exec_requests as req  with (nolock) 
       on task.session_id = req.session_id and task.request_id = req.request_id
inner join sys.dm_exec_sessions as ses with(nolock) on req.session_id = ses.session_id
cross apply sys.dm_exec_sql_text(sql_handle) as qt  
order by (task.internal_alloc + task.oject_alloc) -(task.internal_dealloc +task.oject_delloc) desc, cpu_time desc

테이블 변수도 tempdb의 공간을 사용한다고 되어 있습니다. 알아 두세요

--tempdb 사용 Size
select sum( unallocated_extent_page_count
          + user_object_reserved_page_count
          + internal_object_reserved_page_count
          + mixed_extent_page_count
          + version_store_reserved_page_count ) /128 as [TotalTempDBSizeInMB]
   , sum(unallocated_extent_page_count) /128 as [FreeTempDBSpaceInMB]
from sys.dm_db_file_space_usage 

select
   db_name(su.database_id) dbname
   ,mf.physical_name
   ,(su.unallocated_extent_page_count
   + su.user_object_reserved_page_count
   + su.internal_object_reserved_page_count
   + su.mixed_extent_page_count
   + su.version_store_reserved_page_count ) /128 as total_size_mb
   ,su.user_object_reserved_page_count / 128 user_object_size_mb
   ,su.internal_object_reserved_page_count /128 as internal_object_size_mb
   ,su.unallocated_extent_page_count/ 128 unallocated_extent_size_mb
from sys.dm_db_file_space_usage as su
   join sys.master_files as mf on mf.database_id = su.database_id and mf.file_id = su.file_id