Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic
Aged Yak Warrior

549 Posts

Posted - 03/13/2014 :  15:05:51  Show Profile  Reply with Quote
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,
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.

Gangadhara MS
SQL Developer and DBA

Starting Member

6 Posts

Posted - 03/20/2014 :  14:59:45  Show Profile  Reply with Quote
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.

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

Flowing Fount of Yak Knowledge

United Kingdom
2179 Posts

Posted - 03/22/2014 :  06:07:01  Show Profile  Visit jackv's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000