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
SOLUTION
Avatar of puranik_p
puranik_p
Flag of India image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of motioneye
motioneye
Flag of Singapore image

ASKER

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
Avatar of puranik_p
puranik_p
Flag of India image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo