sys.dm_db_index_operational_stats: Kick Your Assumptions: Part 1

                  A two-part tale in which your humble correspondent interrogates 
                  databases & Very Smart Persons (VSPs), & is forced to kick his ass-
                  umptions regarding a popular DMF.

Executive Summary
Documentation for the DMF sys.dm_db_index_operational_stats in BOL here & here as well as elsewhere is incorrect. 

In fact, ops stats will not return information for all indexes, but lists only those indexes (& their partitions) in the metadata cache.  On a newly re-started server, no user indexes will appear in the result set.  This contradicts documentation.

An outer join to a system object such as sys.indexes on object_id & index_id is required to list all indexes & partitions.

Using op stats Returns Info ONLY on Indexes in the Metadata Cache
The following returns names & metadata only for those indexes (& their partitions) in the metadata cache.  On a newly re-started, this will consist only of system objects.

--ops stats returns info only for a db’s indexes & their part’ns in cache
    --this contradicts documentation
--note: db context here is not mandatory
USE AdventureWorks;
GO
DECLARE @dbid sysname = DB_ID();  --SQL 2008 syntax
--DECLARE @dbid sysname;  SET @dbid = DB_ID();
SELECT DB_NAME(@dbid) as db_name
    , OBJECT_NAME(object_id) as obj_name
    FROM sys.dm_db_index_operational_stats
        (@dbid, NULL, NULL, NULL)
    ORDER BY obj_name;

Use op stats for Info on All Indexes—System & User Objects
Obtaining information for all indexes (& their partitions) in a specific database—regardless of metadata cache status—including all system & user indexes, requires a query such as the following which uses an outer join to sys.indexes.  (Note that both of the following queries are sensitive to the database context.  Note also not validating the db & passing an invalid id to the DMF will produce undesired results.)

--to get ops stats to work as advertised
--return all rows for all indexes (& partitions) for a specific db
    --use an outer join between ops stats & sys.indexes
--note: db context is relevant
USE AdventureWorks;
GO
DECLARE @dbid sysname = DB_ID();  --SQL 2008 syntax
--DECLARE @dbid sysname;    SET @dbid = DB_ID();
SELECT DB_NAME(@dbid) as db_name
    , OBJECT_NAME(i.object_id) as obj_name
    , ops.*
    FROM sys.dm_db_index_operational_stats
            (@dbid, NULL, NULL, NULL) as ops
        RIGHT JOIN sys.indexes as i
           ON ops.object_id = i.object_id AND ops.index_id = i.index_id
    ORDER BY obj_name , i.index_id , i.partition_number;

Use ops stats for Info on All User Indexes
The following uses op stats returns information for all user indexes in a specific database—regardless of metadata cache status:

--use op stats to return all rows for all *user* indexes (& partitions) 
    --for a specific db
    --combine outer join between ops stats & sys.indexes
    --w/ OBJECTPROPERTY
--note: db context is relevant
USE AdventureWorks;
GO
DECLARE @dbid sysname = DB_ID();  --SQL 2008 syntax
--DECLARE @dbid sysname;    SET @dbid = DB_ID();
SELECT DB_NAME(@dbid) as db_name
    , OBJECT_NAME(i.object_id) as obj_name
    , ops.*
    FROM sys.dm_db_index_operational_stats
            (@dbid, NULL, NULL, NULL) as ops
        RIGHT JOIN sys.indexes as i
           ON ops.object_id = i.object_id AND ops.index_id = i.index_id
    WHERE OBJECTPROPERTY(i.object_id,'IsUserTable') = 1
    ORDER BY obj_name , ops.index_id , ops.partition_number;

To Be Continued...
Stay tuned for Part 2 for additional details & documentation, including a walk-through, widespread misconceptions even amongst VSPs, & internals.

Jimmy May, MCM, MCDBA, MCSE, MCITP: DBA + DB Dev
Senior Performance Consultant: SQL Server
A.C.E.: Assessment Consulting & Engineering Services
http://msinfosec.com
http://blogs.msdn.com/jimmymay
Politics leads you in the direction of a belief.   Data, if you follow them, lead you to truth.
    —Michael Crichton