Monitoring Tempdb in SQL Server 2005

from:

[@more@]

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:

Use the below query to check the current tempdb size:

Output:

Logical File Name	File Type	File Path	File Size (in KB)	Auto Growth
tempdev	Data	D:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLDATAtempdb.mdf	8192	Enabled
templog	Log	D:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLDATAtemplog.ldf	512	Enabled

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

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).

Output:

Database Name

Logical Name

File Name 來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/66009/viewspace-1059126/，如需轉載，請註明出處，否則將追究法律責任。 0 0 分享到： 上一篇： sql server系統表與系統檢視詳細說明 下一篇： 使用CLR函式壓縮(Gzip)ntext型別欄位 請登入後發表評論 登入 全部評論 kitesky 註冊時間：2009-04-22 博文量 273 訪問量 2193778 最新文章 pandas用法大全 Python關鍵詞(keywords)筆記 使用waitfor time和waitfor delay在一定時間間隔下定時執行批處理 Troubleshooting: Connecting to SQL Server When System Administrators Are Locked Out Database Unit Testing 偶發的儲存過程呼叫效能問題的一次調優過程 獲取SQL Server中連線的客戶端IP地址 SQL Server中常用全域性變數和系統資訊 SQL Server中查詢使用者的物件許可權和角色的方法 SQL Server 2005/2008 對With Encryption選項建立的儲存過程解密 支援我們 作者招募 使用者協議 FAQ Contact Us 北京盛拓優訊資訊科技有限公司. 版權所有  京ICP備16024965號-8 北京市公安局海淀分局網監中心備案編號：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人舉報專區 廣播電視節目製作經營許可證(京) 字第1234號 中國網際網路協會會員