Link to home
Start Free TrialLog in
Avatar of Leo Torres
Leo TorresFlag for United States of America

asked on

SQL sys.dm_db_index_operational_stats

I am trying to understand whats going on here.

I am using the sys.dm_db_index_operational_stats on a sql server 2012 instance and I am trying to do analysis on the Singleton_lookup_count column provided by this sql function.

I understand that when all the parameters are NULL SQL looks at all the tables in the given database or I can pass DB ID and object_id for a specific table to get data for that table.

In theory that sounds great but the singleton_Lookup_counts i am getting are off by 150 Billion. That makes no sense something has to be wrong there.  

I would like to understand questions above so i can ultimately  compare Singleton_Lookup_counts between Index and Heap.

Thank you

Here is sample code
SELECT singleton_lookup_count,* FROM sys.dm_db_index_operational_stats(DB_ID(), 1542973269, NULL, NULL) 

select 
    ops.object_id         as [Object Name]
    , sum(ops.range_scan_count)         as [Range Scans]
    , sum(ops.singleton_lookup_count)   as [Singleton Lookups]
    , sum(ops.row_lock_count)           as [Row Locks]
    , sum(ops.row_lock_wait_in_ms)      as [Row Lock Waits (ms)]
    , sum(ops.page_lock_count)          as [Page Locks]
    , sum(ops.page_lock_wait_in_ms)     as [Page Lock Waits (ms)]
    , sum(ops.page_io_latch_wait_in_ms) as [Page IO Latch Wait (ms)]
from sys.dm_db_index_operational_stats(null,null,NULL,NULL) as ops
  inner join sys.indexes as idx on idx.object_id = ops.object_id and idx.index_id = ops.index_id
  inner join sys.sysindexes as sysidx on idx.object_id = sysidx.id
where ops.object_id = 1542973269 
group by ops.object_id

Open in new window




here is the data returned and difference in counts.
User generated image
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial