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.