November 29, 2010 at 2:30 am
Hi All,
I queried "dm_db_index_physical_stats" for a particular index and got the following values,
avg_fragmentation_in_percentavg_fragment_size_in_pages avg_page_space_used_in_percent
==================================================================================
95.33411489 1.038218111 43.02531505
100 1.243902439 57.46886583
0 1 17.40795651
After rebuilding the index again queried the same and got the following.
avg_fragmentation_in_percentavg_fragment_size_in_pages avg_page_space_used_in_percent
==================================================================================
27.92297111 3.043963712 84.16702496
100 1.666666667 58.82085495
0 1 8.685446009
Can you pls let me know how to analyse this.? Values in first row is OK, I understand it.
But 3rd row is quite confusing. Why the value has been reduced to '8.685446009' .? I am not clear.
Please help.
Thanks.
November 29, 2010 at 3:02 am
What query did you use to query the DMV? What command (exactly) did you use to rebuild the index?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 29, 2010 at 4:17 am
Hi Gila,
Below query is used for getting fragmentation details,
SELECTOBJECT_NAME(i.OBJECT_ID) AS TableName, i.name AS IndexName
, IDS.avg_fragmentation_in_percent
, IDS.avg_fragment_size_in_pages
, IDS.avg_page_space_used_in_percent
, IDS.record_count
, IDS.ghost_record_count
, IDS.fragment_count
FROMsys.dm_db_index_physical_stats(DB_ID('dbname'), object_id(@tb_name), NULL, NULL, 'DETAILED') IDS
INNER JOIN sys.indexes i ON i.OBJECT_ID = IDS.OBJECT_ID
ANDi.index_id = IDS.index_id
----------------------
For rebuilding index I executed the following,
exec sp_msforeachtable ' alter index all on ? rebuild'
Thanks.
November 29, 2010 at 5:30 am
What's the fill factor for that index?
What's the default fill factor?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 29, 2010 at 8:35 am
Hi Gila,
Default fill factor is '0'.
November 29, 2010 at 10:46 am
Can you rerun the query and include the page count in there? record_count, ghost_record_count and fragment_count not needed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 29, 2010 at 10:05 pm
Yes sure, as given below.
record_countghost_record_countfragment_count
===================================================
838864 2077 47237
512070 196
296 0 2
2 0 1
838864 4559 16559
16625 0 186
268 0 5
5 0 1
record_countghost_record_countfragment_count
===================================================
838837 0 1364
25604 0 14
104 0 1
838837 0 362
8224 0 5
91 0 2
2 0 1
Thanks.
November 29, 2010 at 10:17 pm
Err, please read what I asked for.
I said I want to see the results of that query with the page count included and that record_count, ghost_record_count and fragment_count are not needed
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 30, 2010 at 12:36 am
Ok. I dint capture it last week, since it's a production server, manager will allow to get this data only on saturday.
Wel, What information we get from the details I already posted.?
How to analyse it ?. can u pls help with it.?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply