With the expansion of the row versioning framework in SQL Server 2005 tempdb becomes an even more important part of the platform than it used to be. Snapshot transaction isolation as well as database snapshots make big use of this "temporary" storage facility and keeping track of its usage is the key to the continuous efficiency of data management solutions based on the SQL Server platform.
As mentioned in my post on SI in SQL 2005 there are several dynamic management views that can be used to monitor tempdb activity and usage, but for a more comprehensive overview of the subject I recommend this fine article by Wei Xiao, Matt Hink, Mirek Sztajno and Sunil Agarwal.
IMHO a must-read when considering the implementation of Snapshot Isolation.
And speaking of tempdb – have you read this article by Tony Rogerson?
Update (November 14th 2006)
On the SQL Server Best Practices site we can find another comprehensive study of the use of tempdb, specifically for the purposes of index creation and rebuilding:
- TEMPDB Capacity Planning and Concurrency Considerations for Index Create and Rebuild by Sanjay Mishra (Technical reviewers: Tom Davidson, Mark Souza, Prem Mehra, Sunil Agarwal, Mike Ruthruff; Project Editor: Suzanne Bonney).
The SQL Server Best Practices Toolbox includes two methods useful in monitoring tempdb use:
- List Real Time Tempdb Task Usage – a sample script that lists the real-time task usage of
tempdbin SQL Server 2005; and - List Real-Time Tempdb Statements – a smple script that lists real-time statements that use
tempdbin SQL Server 2005.
ML
1 comment:
Thank you for such valuable information.
For more information about Monitoring and Optmizing tempdb, I can suggest you to click the below link.
http://www.sqllion.com/2009/05/monitoring-tempdb-in-sql-server-2005/
Post a Comment