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

tables, global temp tables, local temp tables, and table-valued functions. Capture this object-level temp
db usage by using this procedure:
CREATE PROC usp_StoreObjectTempDBHistory
AS
/*STORE TEMPDB USAGE HISTORY AT OBJECT LEVEL
=======================================================*/
INSERT INTO Benchmark_Object_TempDBHistory
SELECT
obj.objectid as [Object_Id],
getdate() as SampleDateTime,
sum(tmp.user_objects_alloc_page_count) AS user_objects_alloc_page_count,
sum(tmp.user_objects_dealloc_page_count) AS user_objects_dealloc_page_count,
sum(tmp.internal_objects_alloc_page_count)
AS internal_objects_alloc_page_count,
sum(tmp.internal_objects_dealloc_page_count)
AS internal_objects_dealloc_page_count
FROM sys.dm_db_task_space_usage AS tmp
LEFT OUTER JOIN sys.dm_exec_requests AS req
ON tmp.session_id = req.session_id
OUTER APPLY sys.dm_exec_sql_text(req.sql_handle) AS obj
WHERE tmp.session_id > 50
AND obj.objectid is not null
Group BY obj.ObjectId
CapturingBlocking Information
Applications that have long-running or slow-running queries may be affected by blocking. Blocking can
occur when two processes are competing for the same resources or during simple system locking. For
a guide to eliminating these bottlenecks, go back to Chapter 4. If you can identify issues with blocking,
you may have problems with the data model or the application design. It may indicate that transactions
are hierarchically at too high a level or that the level of transaction isolation is too broad. There are
many places to look for blocking information in SQL Server 2005. For our purposes of recording this
information periodically, store blocking information from the DMV sys.dm_db_index_operational_stats
in terms of wait metrics by object identifier.
What you need to be able to do prior to rolling out a database is go back through these metrics and
examine what objects are incurring any level of blocking. Blocking can occur at the table or the index
level and since this is how the information is provided in the DMV, you'll just keep that same level of
granularity. You can roll up to object-level summaries when you need to and be able to drill down into
the index level for tuning:
CREATE PROC usp_StoreObjectBlockingHistory
AS
/*STORE OBJECT BLOCKING HISTORY
=======================================================*/
BEGIN
INSERT INTO Benchmark_Table_BlockingHistory
SELECT s.object_id
, SampleDateTime = getdate()
, indexname=i.name, i.index_id
, [block_pct]=cast (100.0 * row_lock_wait_count / (1 + row_lock_count) as
numeric(15,2))