Warning: is_writable() [function.is-writable]: open_basedir restriction in effect. File(/f5/sqllion/public/wp-content/uploads/shareaholic/) is not within the allowed path(s): (/fs3d/sqllion/:/nfsn/apps/php53/lib/php/:/nfsn/apps/php5/lib/php/:/nfsn/apps/php/lib/php/) in /fs3d/sqllion/public/wp-content/plugins/sexybookmarks/sexy-bookmarks.php on line 335
Monitoring Tempdb in SQL Server 2005 | SQL Lion
Home > T - SQL > Monitoring Tempdb in SQL Server 2005

Monitoring Tempdb in SQL Server 2005

As the tempdb database is the common global resource for all the operations going on in SQL Server, so the DBA has to be bit cautious about the use of it. Because any unexpected operations by the applications running under the SQL Server instance or any uneven query by the user can eat all the space available to thetempdb resulting decrease in the performance of all other applications running under the same instance.

So it is necessary to keep track the usage of tempdb database by various applications and processes and to take necessary actions when the size falls down below the threshold limit. Monitoring tempdb over time will help in determining the optimal size of the tempdb.

Check my earlier post for more details on performance regarding tempdb:

http://www.sqllion.com/2009/05/optimizing-tempdb-in-sql-server-2005/
Use the below query to check the current tempdb size:

SELECT
      [name]                  AS [Logical File Name],
      CASE type_desc
            WHEN 'ROWS' THEN 'Data'
            WHEN 'LOG'  THEN 'Log'
      END                     AS [File Type],
      physical_name           AS [File Path],
      [size]                  AS [File Size],
      CASE growth
            WHEN 0 THEN 'Enabled'
            ELSE 'Disabled'
      END                     AS [Auto Growth]
FROM tempdb.sys.database_files

Output:

Logical File Name File Type File Path File Size (in KB) Auto Growth
tempdev Data D:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\tempdb.mdf

8192

Enabled
templog Log D:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\templog.ldf

512

Enabled

To get a brief info on the space used by tempdb database, use the below query:

USE tempdb
GO
EXEC sp_spaceused

Output:

database_name database_size unallocated space
tempdb 8.50 MB 6.82 MB

 

reserved data index_size unused
1208 KB 528 KB 608 KB 72 KB

Luckily SQL Server provides a rich set of DMVs (dynamic management views) to keep track of some performance counters that will help in managing disk usage by tempdb database.




So in order to properly manage tempdb, the below performance factors can be tracked:

  • Total Data File Size (in KB)
  • Total Log File Size (in KB)
  • Used data file size (in KB)
  • Used Log File size (in KB)
  • Free space left in tempdb (in KB)
  • Space utilized by user objects (in KB)
  • Space utilized by Internal objects (in KB)
  • Space utilized by Version Store (in KB)

There is one more major factor influencing the performance of tempdb i.e. I/O. If you have a slow I/O subsystem, then your I/O requests are queued up resulting in I/O bottleneck. When a user connects to a database, a session is created. And the DMV tracks all the events like allocation or deallocation of pages in tempdb for each active session. The session will remain active till the user disconnects.
[Note: Details about I/O performance will be posted soon.]
To achieve the above performance factors, the below DMVs can be used:

  • Sys.dm_db_file_space_usage
  • Sys.dm_db_session_file_usage
  • Sys.dm_db_task_space_usage
  • sys.dm_exec_requests
  • sys.dm_tran_active_snapshot_database_transactions
  • sys.dm_exec_query_stats
  • sys.dm_tran_version_store
  • sys.dm_io_virtual_file_stats

By using the above DMVs, the tempdb usage can be easily tracked.

  • Total Data File Size (in KB)

Space used by different components in tempdb database.
sys.sysfiles: Returns information for each file in the database.
sys.dm_io_virtual_file_stats: Returns I/O statistics for data and log files. It takes two parameters out of which one is Database ID (show all databases if NULL is provided) and File ID (show all files if NULL is provided).

 

SELECT
	DDB_NAME(database_id) as [Database Name],
	[Name] as [Logical Name],
	[filename] as [File Name],
	[size_on_disk_bytes] / 1024 as [Size (in KB)]
FROM
	sys.dm_io_virtual_file_stats(2, 1)
	-- 2(tempdb database id), 1(tempdb data file id)
		inner join
	sys.sysfiles
		on sys.dm_io_virtual_file_stats.file_id = sys.sysfiles.fileid

Output:

Database Name Logical Name File Name Size (in KB)
tempdb tempdev D:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\tempdb.mdf

8192

  • Total Log File Size (in KB)

Space used by log in tempdb database.
sys.sysfiles: Returns information for each file in the database.
sys.dm_io_virtual_file_stats: Returns I/O statistics for data and log files.

SELECT
	DB_NAME(database_id)    as	[Database Name],
	[Name]                  as	[Logical Name],
	[filename]              as	[File Name],
	[size_on_disk_bytes] / 1024   as	[Size (in KB)]
FROM sys.dm_io_virtual_file_stats(2, 2)
	-- 2(tempdb database id), 2(tempdb log file id)
	inner join
	sys.sysfiles
		on sys.dm_io_virtual_file_stats.file_id = sys.sysfiles.fileid

Output:

Database Name Logical Name File Name Size (in KB)
tempdb templog D:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\templog.ldf

512

  • Used data file size (in KB) / Used Log File size (in KB)

Space currently used in tempdb data file and log file.

SELECT
	DB_NAME(database_id)    as	[Database Name],
	[Name]                  as	[Logical Name],
	[filename]              as	[File Name],
	[num_of_bytes_written] / 1024  as    [Space Used (in KB)]
FROM sys.dm_io_virtual_file_stats(2, NULL)
	-- 2(tempdb database id), NULL(all the files related to tempdb)
		inner join
	sys.sysfiles
		on sys.dm_io_virtual_file_stats.file_id = sys.sysfiles.fileid

 Output:

Database Name Logical Name File Name Space Used (in KB)
tempdb tempdev D:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\tempdb.mdf

2240

tempdb templog D:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\templog.ldf

417

  • Free space left in tempdb (in KB)

The amount of space left in tempdb.

SELECT
	DB_NAME(database_id)    as	[Database Name],
	[Name]                  as	[Logical Name],
	[filename]              as	[File Name],
	[size_on_disk_bytes] / 1024   as    [Total Size (in KB)],
	[num_of_bytes_written] / 1024 as    [Space Used (in KB)],
	([size_on_disk_bytes] - [num_of_bytes_written]) / 1024 as [Free space left (in KB)]
FROM sys.dm_io_virtual_file_stats(2, NULL)
	-- 2(tempdb database id), NULL(all the files related to tempdb)
      	inner join
      sys.sysfiles
      	on sys.dm_io_virtual_file_stats.file_id = sys.sysfiles.fileid

 Output:

Database Name Logical Name File Name Total Size (in KB) Space Used (in KB) Free space left (in KB)
tempdb tempdev D:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\tempdb.mdf

8192

2656

5536

tempdb templog D:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\templog.ldf

512

493

19

  • Space utilized by user objects (in KB) / Internal objects (in KB) / Version Store (in KB)

For more information regarding User Objects / Internal Objects / Version Store, please refer to the below link:

http://www.sqllion.com/2009/05/optimizing-tempdb-in-sql-server-2005/

SELECT
	DB_NAME(database_id)				as [Database Name],
	SUM (user_object_reserved_page_count) * 8       as [User Objects (in KB)],
	SUM (internal_object_reserved_page_count) * 8   as [Internal Objects (in KB)],
	SUM (version_store_reserved_page_count) * 8     as [Version Store (in KB)],
	SUM (mixed_extent_page_count)*8                 as [Mixed Extent (in KB)]
FROM sys.dm_db_file_space_usage group by database_id

Output:

Database Name User Objects (in KB) Internal Objects (in KB) Version Store (in KB) Mixed Extent (in KB)
tempdb

256

384

0

1024

  • Tempdb session File usage

sys.dm_db_session_space_usage : Returns the number of pages allocated and deallocated by each session for the database.

sys.dm_exec_sessions: Gives details about the sessions.

SELECT
	sys.dm_exec_sessions.session_id as [Session ID],
	DB_NAME(database_id) as [Database Name],
	host_name as [System Name],
	program_name as [Program Name],
	login_name as [User Name],
	status,
	cpu_time as [CPU Time (in milisec)],
	total_scheduled_time as [Total Scheduled Time (in milisec)],
	total_elapsed_time as    [Elapsed Time (in milisec)],
	(memory_usage * 8)      as [Memory Usage (in KB)],
	(user_objects_alloc_page_count * 8) as [Space Allocated for User Objects (in KB)],
	(user_objects_dealloc_page_count * 8) as [Space Deallocated for User Objects (in KB)],
	(internal_objects_alloc_page_count * 8) as [Space Allocated for Internal Objects (in KB)],
	(internal_objects_dealloc_page_count * 8) as [Space Deallocated for Internal Objects (in KB)],
	case is_user_process
		when 1      then 'user session'
		when 0      then 'system session'
	end         as [Session Type], row_count as [Row Count]
from sys.dm_db_session_space_usage
		inner join
	sys.dm_exec_sessions
		on sys.dm_db_session_space_usage.session_id = sys.dm_exec_sessions.session_id

 Possible values for status of the session:

        Running – Currently running one or more requests
        Sleeping – Currently running no requests
        Dormant – Session is in prelogin state   

 monitoringtempdb11

 

 

  • Long-running transaction:

Sometimes transactions may run for long time preventing the shrinking of version store data.
The below queries will list the transactions that are running for long time.

SELECT top 10
	transaction_id as [Transacton ID],
	transaction_sequence_num as [Transation Sequence Number],
	elapsed_time_seconds as [Elapsed Time (in sec)]
FROM sys.dm_tran_active_snapshot_database_transactions
ORDER BY elapsed_time_seconds DESC

A long running transaction may prevent cleanup of transaction log thus eating up all log space available resulting space crisis for all other applications.

select
	transaction_id as [Transacton ID],
	[name]      as [Transaction Name],
	transaction_begin_time as [Transaction Begin Time],
	datediff(mi, transaction_begin_time, getdate()) as [Elapsed Time (in Min)],
	case transaction_type
		when 1 then 'Read/write'
      	when 2 then 'Read-only'
      	when 3 then 'System'
      	when 4 then 'Distributed'
	end as [Transaction Type],
	case transaction_state
		when 0 then 'The transaction has not been completely initialized yet.'
		when 1 then 'The transaction has been initialized but has not started.'
		when 2 then 'The transaction is active.'
		when 3 then 'The transaction has ended. This is used for read-only transactions.'
		when 4 then 'The commit process has been initiated on the distributed transaction. This is for distributed transactions only. The distributed transaction is still active but further processing cannot take place.'
		when 5 then 'The transaction is in a prepared state and waiting resolution.'
		when 6 then 'The transaction has been committed.'
		when 7 then 'The transaction is being rolled back.'
		when 8 then 'The transaction has been rolled back.'
	end as [Transaction Description]
from sys.dm_tran_active_transactions
  • Long running Queries:

sys.dm_exec_requests : Returns information regarding the requests made to the database server.

select
	host_name 			as [System Name],
	program_name 			as [Application Name],
	DB_NAME(database_id)    	as [Database Name],
	User_Name(user_id)            	as [User Name],
	connection_id 			as [Connection ID],
	sys.dm_exec_requests.session_id as [Current Session ID],
	blocking_session_id 		as [Blocking Session ID],
	start_time 			as [Request Start Time],
	sys.dm_exec_requests.status 	as [Status],
	command                         as [Command Type],
	(select text from sys.dm_exec_sql_text(sql_handle)) AS [Query Text],
	wait_type 			as [Waiting Type],
	wait_time 			as [Waiting Duration],
	wait_resource 			as [Waiting for Resource],
	sys.dm_exec_requests.transaction_id as [Transaction ID],
	percent_complete 		as [Percent Completed],
	estimated_completion_time 	as [Estimated Completion Time (in mili sec)],
	sys.dm_exec_requests.cpu_time 	as [CPU time used (in mili sec)],
	(memory_usage * 8)            	as [Memory Usage (in KB)],
	sys.dm_exec_requests.total_elapsed_time as [Elapsed Time (in mili sec)]
from sys.dm_exec_requests
		inner join
	sys.dm_exec_sessions
		on sys.dm_exec_requests.session_id = sys.dm_exec_sessions.session_id
where DB_NAME(database_id) = 'tempdb'

Status: Status of the request.

This can be from one of the following options:
Background
Running 
Runnable 
Sleeping
Suspended

Command: Identifies the current type of command that is being processed.

Common command types include the following:
SELECT
INSERT
UPDATE
DELETE 
BACKUP 
LOG
BACKUP 
DB DBCC
WAITFOR




Temporary Recovering:
Although it’s very difficult to reset the tempdb size once it is filled, but the DBA can take the below steps so as to recover it temporarily without restarting SQL Server service.

Step 1: Check out for all open transactions running under tempdb database.

DBCC OPENTRAN
[ ( [ 'database_name' | database_id| 0 ] ) ]
    { [ WITH TABLERESULTS ]
      [ , [ NO_INFOMSGS ] ]
    }
]

Displays information about oldest active transactions.


Example
:

   Create table #temptable (EmpName varchar(30), empID int)
   GO
   Begin Tran
	  Insert into #temptable values ('arun', 101)
	  drop table #temptable DBCC OPENTRAN('tempdb')

Output:

	(1 row(s) affected)
Transaction information for database 'tempdb'.

Oldest active transaction:
    SPID (server process ID): 57
    UID (user ID) : -1
    Name          : user_transaction
    LSN           : (30:386:457)
    Start time    : May 15 2009 12:17:22:190PM
    SID           : 0xfb001f0fe0668f4cbbde733034447069
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.

Step 2: Check for the SPID (server process ID) and use the below command:

Kill [SPID]

The above operation will kill the transaction taking more space in tempdb. But this is not the permanent solution.
So in order to boost the performance of the SQL server, it is recommended that the tempdb database should be split to as many files as possible to maximize the disk bandwidth. But creating so many files can increase maintenance overhead, so it’s better to create one data file for each CPU.
Note: A dual core CPU can be treated as 2 CPUs.

The I/O bottleneck will be discussed in the upcoming posts.

References:
http://technet.microsoft.com/en-au/library/cc966545.aspx
MSDN Books Online

Categories: T - SQL
  1. sateesh
    January 14th, 2011 at 23:58 | #1

    very nice

  2. lalitha
    September 24th, 2011 at 23:12 | #2

    Very useful info. thanks for imp info in detailed and readable way.

  3. Ackers
    February 25th, 2013 at 16:36 | #3

    very good….

  4. August 9th, 2013 at 22:27 | #4

    I just like the helpful info you supply on your articles.
    I will bookmark your blog and check once more here frequently.
    I am reasonably certain I will learn lots of new stuff
    proper right here! Best of luck for the next!

  5. September 9th, 2013 at 14:03 | #5

    I was recommended this web site by my cousin. I’m not sure whether this post is written by him as nobody else know such
    detailed about my problem. You are incredible!
    Thanks!

  6. September 27th, 2013 at 23:40 | #6

    My brother suggested I may like this web site. He was once entirely right.
    This publish truly made my day. You cann’t believe simply how a lot time I had spent for this info!
    Thanks!

  1. August 29th, 2014 at 03:18 | #1
  2. August 31st, 2014 at 20:51 | #2
  3. September 9th, 2014 at 18:55 | #3
You must be logged in to post a comment.