Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gangadhara.ms
Aged Yak Warrior
549 Posts |
 Posted - 2014-03-13 : 15:05:51
|
| Hi All,In one of the production server we have extracted the fragmentation details and seeing that morethan 60 tables having "PercentFragment" morethan 50 percent.I have used this query to identify the same.SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level,avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_countFROM sys.dm_db_index_physical_stats(DB_ID(N'AdventureWorksLT'), NULL, NULL, NULL , 'SAMPLED')ORDER BY avg_fragmentation_in_percent DESCCOuld you please suggest me when we need to have index rebuild option.??Currently we have index rebuild on week end SUnday ones.The scedule Job is running every day and taking more time to complete.Please suggest.Thanks,Gangadhara MSSQL Developer and DBA |
|
|
MasterP
Starting Member
6 Posts |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2014-03-22 : 06:07:01
|
| Index Fragmentation of an index can severely affect performance. When logical ordering of the key within a page does not match the physical ordering within the data file, fragmentation exists. The third link is an excellent resource .I usually look for 50% fragementation rather than the musch quoted 30%. Index rebuild can have a big impact on performance - one option is to consider a maintenace window - where there is little conflicting activityJack Vamvas--------------------http://www.sqlserver-dba.com |
 |
|
|
|
|
|
|
|
Fragmentation