sys.dm_db_index_physical_stats return wrong data

Question

Post reply

sys.dm_db_index_physical_stats return wrong data

b.ozga

SSCommitted

Points: 1891
More actions
February 5, 2015 at 7:26 am

#297464

Hi guys,
Last night I had to rebuild two indexes on very large db (>1TB) Luckily I could do it in online mode and it didn’t take too long ~40min.
Obviously after this task I wanted to check how index fragmentation was improved. And famous dmf sys.dm_db_index_physical_stats instead returning 3 indexes which are on that table, returned 1 with completely useless information, most of columns with zeros.
What it is even more surprising I could get valid and expected data about all 3 indexes from the snapshot of that db which we are able to perform and mirror to another SAN and SQL Server.
Have you experienced this behaviour of sys.dm_db_index_physical_stats before?
Just to add, my SQL Server 2008 has latest patches (the lastest one before SP4)

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

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

Gazareth One Orange Chip Points: 27737 More actions · Answer 1

Could you give us the full query you're using to return data from the DMV?

Thanks

b.ozga SSCommitted Points: 1891 More actions · Answer 2

Sure:

SELECT i.name, f.*

FROM sys.dm_db_index_physical_stats(10, 165575628, null, DEFAULT, 'sampled') as f

join sys.indexes i

on f.index_id = i.index_id

where i.object_id = 165575628

rameshm.sqldba Grasshopper Points: 17 More actions · Answer 3

USE DETAILED instead of 'sample'.. You will get all the column values..

Gazareth One Orange Chip Points: 27737 More actions · Answer 4

Make sure you're running it in the right database context if you're using a hard coded object_id in the where clause (just defining it in the function isn't enough).

There's a missing condtion from the join too:

join sys.indexes i

on f.index_id = i.index_id AND i.object_id = f.object_id

I'd run it with LIMITED mode as well unless you need the extra details.

Cheers

b.ozga SSCommitted Points: 1891 More actions · Answer 5

Thanks guys for your input.

I was trying all function parameters however with sampled I should have got also proper result set, so 3 indexes.

Of course my query could have been written with all join conditions which may be found in both views, however by providing object_id we should get the the same values, which was just enough.

And the last thing which I would like to underline, I have got the proper resultset from my query but on standby system. We take a snapshot of the volume where the db is located and attach this volume to another SQL Server on daily basis.

The problem is I cannot get information about fragmentation of those 3 indexes on productions system :doze:

Resultset from standby system:

name database_id object_id index_id partition_number index_type_desc alloc_unit_type_desc index_depth index_level avg_fragmentation_in_percent

------------- ----------- ----------- ----------- ---------------- -------------------- ----------------------- ----------- ----------- ----------------------------

index1 10 165575628 1 1 CLUSTERED INDEX IN_ROW_DATA 4 0 17.9237071009251

index2 10 165575628 2 1 NONCLUSTERED INDEX IN_ROW_DATA 5 0 16.5687676634959

index3 10 165575628 5 1 NONCLUSTERED INDEX IN_ROW_DATA 4 0 1.69726435496554

Resultset from production system:

name database_id object_id index_id partition_number index_type_desc alloc_unit_type_desc index_depth index_level avg_fragmentation_in_percent

----------- ----------- ----------- ----------- ---------------- ---------------------- ---------------------- ----------- ----------- ----------------------------

index2 10 165575628 2 1 NONCLUSTERED INDEX IN_ROW_DATA 0 0 0

I removed rest of coulmns from sys.dm_db_index_physical_stats which are not so vital in my here.