sys.dm_db_index_usage_stats --- User_Seeks has 0 sometimes NULL?

Question

sys.dm_db_index_usage_stats --- User_Seeks has 0 sometimes NULL?

curious_sqldba

SSC-Dedicated

Points: 36494
More actions
October 25, 2011 at 2:54 pm

#250717

The value of user_seeks column from this DMV sys.dm_db_index_usage_stats has sometimes 0 and sometimes NULL? What's the difference?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What query are you using? Because according to BoL, the counters are set to 0 when a row is added. Last user seek can be null, but the user seeks shouldn't be able to.

I checked a couple DBs, I see 0's but no nulls.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 2

GilaMonster (10/25/2011)
What query are you using? Because according to BoL, the counters are set to 0 when a row is added. Last user seek can be null, but the user seeks shouldn't be able to.
I checked a couple DBs, I see 0's but no nulls.

Using this one

"SELECT o.name AS object_name,

i.name AS index_name,

i.type_desc,

u.user_seeks,

u.user_scans,

u.user_lookups,

u.user_updates,

u.last_user_seek,

u.last_user_update,

u.last_user_scan

FROM sys.indexes i

JOIN sys.objects o

ON i.object_id = o.object_id

LEFT JOIN sys.dm_db_index_usage_stats u

ON i.object_id = u.object_id

AND i.index_id = u.index_id

AND u.database_id = Db_id()

WHERE o.type IN( 'U', 'V' )

AND i.type_desc NOT IN( 'Clustered', 'HEAP' )

AND COALESCE (u.user_seeks, u.user_scans, u.user_lookups, u.user_updates) IS NULL

ORDER BY ( Isnull(u.user_seeks, 0) + Isnull(u.user_scans, 0) + Isnull(u.user_lookups, 0) + Isnull(u.user_updates, 0) ),

o.name,

i.name

I did select * from sys.dm_db_index_usage_stats and do see NULLS under User_seeks and other columns too

"

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

You're getting nulls because of the left join. If there's an index in sys.indexes and there's no row in sys.index_usage_stats (which will happen if the index has never been used in any way since the last time the DB was opened), the left join will make all the columns from index_usage_stats null.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 4

GilaMonster (10/25/2011)
You're getting nulls because of the left join. If there's an index in sys.indexes and there's no row in sys.index_usage_stats (which will happen if the index has never been used in any way since the last time the DB was opened), the left join will make all the columns from index_usage_stats null.

Hmm..may be but as i said even if i did 'select * from sys.dm_db_index_usage_stats' on a database i still see NULLS for many columns like last_user_seek etc. Attached snapshot has the result set. Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

GilaMonster (10/25/2011)
Last user seek can be null, but the user seeks shouldn't be able to.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 6

GilaMonster (10/26/2011)
GilaMonster (10/25/2011)
Last user seek can be null, but the user seeks shouldn't be able to.

Totally my fault...got confused with those column name :-). Thanks again for all your help