List Dynamic Management Views (DMV) by Category in SQL Server 2008
Monitor Running Processes in SQL Server 2005/2008 (written by Madhivanan)
SQL Server: Identify Memory and Performance Issues in T-SQL Queries and Fix them (written by Madhivanan)
Find the Most Used Stored Procedures in SQL Server
Dynamic Management Views (DMV) for SQL Server Performance and Tuning
Important DMVs to monitor CPU – SQL Server
In this article, we will see how to calculate total and free space in tempdb using the sys.dm_db_file_space_usage DMV, which returns space usage information for each file in the database. tempdb is an important object in SQL Server as all databases utilize its resources. Thus it becomes important to monitor tempdb’s space usage.
Here’s the query to find the Total tempdb Size and Free space
+ version_store_reserved_page_count) * (8.0/1024.0)
, SUM(unallocated_extent_page_count * (8.0/1024.0))
In the query above, we are doing a sum of columns across all tempdb files. The result is multiplied by 8 as page count values will always be a multiple of eight. The division by 1024 is to calculate the size in MB’s.