Dynamic Management Views will save your life
One of my favorite features in SQL Server 2005 are the dynamic management views. I've been playing with them for quite a while now and there is a wealth of information available to you.
I've been doing work with a client looking over the performance of their database application. Two DMV queries in particular have been helpful and I thought I would share them. The first has to do with index fragmentation and the other with stored procedure execution.
We start with the management function (not a view I know, but bear with me) sys.dm_db_index_physical_stats. This function returns size and fragmentation information for the data and indexes of the specified object. The function takes inputs of database_id, object_id, index_id, partition_number, and mode. Here's an example that will return index fragmentation at an index level for your database:
SELECT
OBJECT_NAME(s.[object_id]) AS [tableName]
,i.[name] AS [indexName]
,s.index_type_desc AS [indexType]
,s.partition_number
,s.page_count AS [numberOfPages]
,s.fragment_count AS [fragmentCount]
,ROUND(avg_fragmentation_in_percent,2) AS [fragmentationPercent]
,ROUND(s.avg_fragment_size_in_pages, 2) AS [averageFragmentationInPages]
FROM
sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
WHERE
s.database_id = DB_ID()
AND i.name IS NOT NULL --Filter HEAPs
AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
ORDER BY
fragmentationPercent DESC;
Next we move to stored procedure workload. The query makes use of the sys.dm_exec_query_stats view which returns a row for each query statement within the cached plan. We also use the management function sys.dm_exec_sql_text to return the T-SQL text of the query. If you do not use stored procedures in your database application simply remove the join to sys.procedures as well as the [objectName] reference in the query output.
DECLARE @CurrentTime DATETIME ;
SET @CurrentTime = GETDATE() ;
-- Get executed SP's ordered by calls/sec
SELECT
SCHEMA_NAME([schema_id]) AS [schemaName]
,p.[name] AS [objectName]
,qs.execution_count AS [executionCount]
,qs.total_worker_time / qs.execution_count AS [avgWorkerTime]
,qs.total_worker_time AS [totalWorkerTime]
,qs.total_elapsed_time / qs.execution_count AS [avgElapsedTime]
,qs.max_logical_reads
,qs.max_logical_writes
,qs.total_physical_reads
,qs.total_physical_reads/(qs.execution_count * 1.0) AS [avgPhysicalReads]
,qs.creation_time
,DATEDIFF(MI,qs.creation_time,@CurrentTime) AS [ageInCache]
,qs.execution_count / DATEDIFF(SS,qs.creation_time,@CurrentTime) AS [callsPerSecond]
,qs.last_execution_time
,SUBSTRING(qt.text,qs.statement_start_offset / 2,(
CASE
WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX),qt.text)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset ) / 2
) AS [individualQueryText]
FROM
sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
INNER JOIN sys.procedures AS p ON qt.objectid = p.[object_id]
WHERE
qt.dbid = DB_ID()
ORDER BY
[callsPerSecond] DESC ;
Keep in mind that the DMVs are dumped at each server restart. If you need this data for historical purposes I would suggest saving your results to file or table.
-Enjoy!