Leo Torres
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_operationa l_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
here is the data returned and difference in counts.
I am using the sys.dm_db_index_operationa
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
here is the data returned and difference in counts.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.