Monitoring Tempdb in SQL Server 2005

最新推荐文章于 2024-02-07 22:09:49 发布

envykok

最新推荐文章于 2024-02-07 22:09:49 发布

阅读量225

点赞数

分类专栏： SQL Monthly Maintanence 文章标签： sql server database transactions file session performance

SQL Monthly Maintanence 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

http://www.sqllion.com/2009/05/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

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_IN
FOMSGS ]
 ]


    }


]

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