from: http://www.sqllion.com/2009/05/monitoring-tempdb-in-sql-server-2005/
[@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:
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 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:
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 来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/66009/viewspace-1059126/,如需转载,请注明出处,否则将追究法律责任。 ![]()
请登录后发表评论
登录
全部评论
<%=items[i].createtime%>
<%=items[i].content%> <%if(items[i].items.items.length) { %>
<%for(var j=0;j
<%}%> <%}%>
<%=items[i].items.items[j].createtime%>
<%=items[i].items.items[j].username%> 回复 <%=items[i].items.items[j].tousername%>: <%=items[i].items.items[j].content%>
还有<%=items[i].items.total-5%>条评论
) data-count=1 data-flag=true>点击查看
<%}%>
最新文章
|
转载于:http://blog.itpub.net/66009/viewspace-1059126/