SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Fragmentation
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gangadhara.ms
Aged Yak Warrior

India
547 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,
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

USA
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.

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
Flowing Fount of Yak Knowledge

United Kingdom
2067 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
--------------------
http://www.sqlserver-dba.com
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.05 seconds. Powered By: Snitz Forums 2000