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.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Fragmentation

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_count
FROM sys.dm_db_index_physical_stats
(DB_ID(N'AdventureWorksLT'), NULL, NULL, NULL , 'SAMPLED')
ORDER BY avg_fragmentation_in_percent DESC


COuld 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 MS
SQL Developer and DBA

MasterP
Starting Member

6 Posts

Posted - 2014-03-20 : 14:59:45
I had the same question awhile back and instead of trying to rewrite all of it, I'll post a few links for your own edification that helped me greatly.

http://www.brentozar.com/archive/2009/02/index-fragmentation-findings-part-1-the-basics/

http://technet.microsoft.com/en-us/library/cc966523.aspx

http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

There is always a way...
Go to Top of Page

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 activity

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page
   

- Advertisement -