Avatar of motioneye
motioneyeFlag for Singapore asked on

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

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
    (DB_ID(N'FFDS'), OBJECT_ID(N'Test2'), NULL, NULL , 'DETAILED');

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


Microsoft SQL Server

Avatar of undefined
Last Comment
puranik_p

8/22/2022 - Mon
SOLUTION
puranik_p

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
motioneye

is this only differences between using detailed or just put as NULL ? so how do I look at the report when it produced multiple rows when using with "detailed", becoz the first row report me 5000 recod count but secnd one only report me 33 record count, my table indeed only have 5000 records
ASKER CERTIFIED SOLUTION
puranik_p

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Your help has saved me hundreds of hours of internet surfing.
fblack61