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

ExtractingObject_Id-Level Information fromDMVs
After working with the new DMVs in SQL Server 2005, we were blown away by the amount of informa-
tion that was available. This awe stopped cold for a minute when we realized that the Object_Id wasn't
embedded into many of the DMV outputs. Where's the Object_Id? The DMV information is wonderful,
but you have to have the database object without trying to guess by looking at the plan or examining the
SQL text. After scrambling around a bit, we realized that the Object_Id is available in any of the func-
tions that take either a sql_handler or a plan_handle. The system-level table-value functions all return the
database identifier and the object identifier when you pass in either handle.

Table 14-6: Functions to Return Plans or SQL Statements from DMVs

System TVF Input Required to Retrieve Object_Id

Sys.dm_exec_sql_text SQL_Handle

sys.dm_exec_text_query_plan Plan_Handle

sys.dm_exec_query_plan Plan_Handle

We'll use these system TVFs to data-mine DMV information by object_Id for our benchmarking metrics
evaluation.

Capturing Table Throughput
Often when a new member is added to our development team, they want to know how the data flows
through the model as time and processes progress. If you are responsible for designing with performance
in mind from the start, you are going to want to be able to pull out this type of information from your
development and testing environments. A start is to record row counts by table on an hourly, daily, or
weekly basis during the testing phases of your project.

There are several ways to get the row count information from the database. One way is to iterate through
a cursor and count the rows in each table, but the more elegant way is to use the stored procedure
sp_msforeachtable . This undocumented stored procedure will perform an action on each table based
on the input command and is simple to use. The TSQL looks like this:

CREATE PROC usp_StoreBenchMarkTableDataVolumeHistory
AS
/*STORE TABLE ROW COUNTS FOR BENCHMARK TABLES
=======================================================*/
DECLARE @MYSQL NVARCHAR(2000)
SET @MYSQL = N'INSERT INTO Benchmark_Table_DataVolumeHistory select Object_Id(''?''),
getdate(), count(*) from ?';
EXEC sp_msforeachtable @MYSQL

The date uses the current date so that you can put this TSQL snippet into any scheduled process under
any schedule. The result is that the process can control how frequently it needs to run.