Best Practices for Designing for Performance from the Start - Professional SQL Server 2005 Performance Tuning - page 516

Monitoring IndexUsage
After recording table throughput, the next thing that you'll want to monitor and benchmark is the index
usage history. This will give you the data you need to evaluate your index choices. If you see more
user scans than user seeks, then you need to examine the database object to see if it can be tuned for
performance in any way. If you have already created the benchmark table for this data, you can use this
procedure to store index usage periodically.
CREATE PROC usp_StoreBenchMarkObjectIndexUsage
AS
/*STORE INDEX USAGE HISTORY COUNTS FOR BENCHMARK TABLES
=======================================================*/
INSERT into Benchmark_Object_IndexUsageHistory
Select dmv.object_id
,sampledatetime=Getdate()
,index_id
, user_seeks
, user_scans
, user_lookups
from sys.dm_db_index_usage_stats dmv
inner join Benchmark_Table bt
on dmv.object_id = bt.object_id
TempDBUsage
Some applications really look good in their native databases but are not hospitable to the Temp database.
You don't want to be monitoring a database through the development stage only to be blindsided when
you go to production and find that the temp database requirement that you had in testing can't be met
in the production environment. You'll also find if the temp database is being overly used that something
may not be right with the modeling and you may need to denormalize a few select things. However, you
can't make those judgments without something to look at. Run this query on a regular basis to capture
the TempDB usage history for the database:
CREATE PROC usp_StoreTempDBHistory
AS
/*STORE TEMPDB USAGE HISTORY AS SERVER LEVEL
=======================================================*/
INSERT INTO Benchmark_TempDBHistory
SELECT getdate(),
SUM(user_object_reserved_page_count) * 8 as user_objects_kb,
SUM(internal_object_reserved_page_count) * 8 as internal_objects_kb,
SUM(version_store_reserved_page_count) * 8 as version_store_kb,
SUM(unallocated_extent_page_count) * 8 as freespace_kb
FROM sys.dm_db_file_Space_Usage
where database_id = 2
Notice that this information is stored at the server level. This won't be much help to you if you have
more than one database on a server. To dig into tempDB and see what database objects are using these
resources, you need to look at the DMV sys.dm_db_task_space_usage . This new DMV shows the alloca-
tion and de-allocation of pages per task in tempDB for your database. Page counts are stored in snapshots
at user and internal object levels. Internal objects are structures like work tables, spools, work files for
hash joins, or sort operations. User objects are structures like user-defined tables and indexes, system