Friday, March 4, 2011

SQL Server Denali tidbits

So yesterday I was using some DMVs and checking space allocated in tempdb for user object, internal objects and version store, the sys.dm_db_file_space_usage view in particular. With it you can see unallocated page extents and how many pages are used by user and internal objects and the version store. On a whim I ran the same query query on my Denali instance and got two extra columns! These are Total Page Count and Allocated Extent Page Count. Ahhh, this is even more useful!Even more useful, the SQL Server 2008 version ONLY returns page usage for tempdb regardless of the database it is executed from. This is cool because I want to see what user objects (temp tables, etc), internal objects (spools, hash joins) are consuming tempdb.
However, with SQL Server Denali, the sys.dm_db_file_space_usage works on ANY database.
Use AdventureWorksDenali
GO

select * from sys.dm_db_file_space_usage

Use tempdb
GO

select * from sys.dm_db_file_space_usage

So make the most of this view and keep an eye on what's being allocated in your databases, particularly tempdb.

1 comment:

  1. great: BTW the SQLSaturday download will not decompress on Windows 7; says it's corrupt.

    ReplyDelete