Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Why index stats return me two rows in clustered indexes fragmentation valus

Avatar of motioneye
motioneyeFlag for Singapore asked on
Microsoft SQL Server
3 Comments1 Solution346 ViewsLast Modified:
I'm checking on how fragmented my clustered indexes and nonclustered indexes on Test2 table, I starting to get confuse why it return me two values in clustered indexes where I only have one clustered index with only one column be specify:

SELECT object_id=object_name(object_id),index_id,index_type_desc,alloc_unit_type_desc,avg_fragmentation_in_percent,avg_fragment_size_in_pages,page_count,          
avg_page_space_used_in_percent, record_count,min_record_size_in_bytes, max_record_size_in_bytes, avg_record_size_in_bytes,forwarded_record_count
  FROM sys.dm_db_index_physical_stats

results me below

object_id index_id    index_type_desc     alloc_unit_type_desc  avg_fragmentation_in_percent avg_fragment_size_in_pages page_count           avg_page_space_used_in_percent record_count         min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes forwarded_record_count
--------- ----------- ----------------------------------------- ---------------------------- -------------------------- -------------------- ------------------------------ -------------------- ------------------------ ------------------------ ------------------------ ----------------------
Test2     1           CLUSTERED INDEX     IN_ROW_DATA           0                            6.6                        33                   78.9713367926859               5000                 33                       42                       40.2                     NULL
Test2     1           CLUSTERED INDEX     IN_ROW_DATA           0                            1                          1                    6.49864096861873               33                   14                       14                       14                       NULL
Test2     3           NONCLUSTERED INDEX  IN_ROW_DATA           0                            12                         24                   95.7252285643687               5000                 28                       37                       35.2                     NULL
Test2     3           NONCLUSTERED INDEX  IN_ROW_DATA           0                            1                          1                    12.8119594761552               24                   34                       43                       41.291                   NULL

Avatar of puranik_p
puranik_pFlag of India image

Our community of experts have been thoroughly vetted for their expertise and industry experience.

This problem has been solved!
Unlock 1 Answer and 3 Comments.
See Answers