January 31, 2012 at 6:48 pm
Hi,
We got a isuue that user is facing a performance problem.?
i have checked and don't find any issue in the errorlog and no blockings. i have checked the index Fragmentation and found that "avg_fragmentation_in_percent" is greater than 90%.?
Can any one help me for the below questions PLZ.?
a).how to defragment the index.?
b).Does defragmenting the indexes require down time. If so how much? If down time is not needed, does it just mean that performance will be very slow during the defrag?
January 31, 2012 at 8:34 pm
All your questions are covered in below link.
http://www.mssqltips.com/sql-server-tip-category/39/fragmentation-and-index-maintenance/
February 1, 2012 at 2:13 pm
Simple answer is defragmenting indexes does not require downtime, but unless you are on enterprise edition and use the ONLINE option rebuilding an index is blocking. In addition to blocking it does require resources so can be a cause of slow performance while it runs. You can do a reorganize that does not block as much, but won't clean up the index as much either. At 90% fragmentation you need a rebuild, but if you need to keep the table available I'd do a reorganize to reduce the fragmentation some. Typically you'd schedule regular index maintenance during your slowest time. Ola Hallengren and and Michelle Ufford both have excellent scripts out there to do index maintenance. If you google/bing those names and index maintenance you'll find the scripts.
Oh and today's featured article here on SSC discusses fragmentation as well, http://www.sqlservercentral.com/articles/Stairway+Series/72443/
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 1, 2012 at 11:18 pm
😀
ok check links
http://www.google.com.eg/url?sa=t&rct=j&q=ms+sql+defragmenting+the+indexes+solutions&source=web&cd=5&ved=0CEIQFjAE&url=http%3A%2F%2Fwww.vldb.org%2Fpvldb%2Fvol4%2Fp1407-narasayya.pdf&ei=0SgqT-LRJIiF-wa4rqGPDg&usg=AFQjCNExIhOX4tNEne-VPLPOJZ0gJ3Lhrg
When should I do one over the other?
As noted above, an important factor is the amount of time it will take to complete each operation (usually using the amount of fragmentation as a guide). However there are some other factors to consider. If you are using any edition of SQL Server 2000 or older, or any edition of SQL Server 2005 and higher that is not Enterprise Edition, then index rebuilds are an offline operation.
What this means is that no users will be able to access the data within the table while the index rebuild is happening. In SQL Server 2005, users of the Enterprise Edition were given the ability to do online index rebuilds. This ability allows you to rebuild indexes without taking the index offline and without locking the table while the index is being rebuilt.
Defragmenting indexes on the other hand is an online operation. This means that while the index defragmentation process is happening, your users will be able to access the data.
Because of this key difference, you may opt to perform index defragmenting even though your indexes are heavily fragmented especially if you cannot afford the downtime to rebuild your indexes.
If you are using SQL Server 2005/2008 Enterprise Edition and opt for the online index rebuilding, do keep in mind that rebuilding indexes online is a longer operation than rebuilding them offline. If you can afford the downtime, an offline index rebuild is recommended since it is faster.
However tables using the TEXT, NTEXT and IMAGE data types cannot have their indexes rebuilt online. If you specify the ONLINE=ON flag when rebuilding the indexes the index rebuild command will fail with an error message and the index will not be rebuild.
If you need to change one of the options that you specified when creating the index, you will need to rebuild the index. When you defragment the index you cannot change the fill factor, or any of the other SET options.
http://itmanagement.earthweb.com/netsys/article.php/3800071/SQL-Server-Re-indexing-Tips.htm
February 3, 2012 at 8:14 am
vijay82 (1/31/2012)
Hi,We got a isuue that user is facing a performance problem.?
i have checked and don't find any issue in the errorlog and no blockings. i have checked the index Fragmentation and found that "avg_fragmentation_in_percent" is greater than 90%.?
Can any one help me for the below questions PLZ.?
a).how to defragment the index.?
b).Does defragmenting the indexes require down time. If so how much? If down time is not needed, does it just mean that performance will be very slow during the defrag?
You are asking questions that a DBA should already know before he/she is put in charge of maintaining a system. PLEASE do yourself and your company a favor and have a mentor come in to review your systems, help get you set up with good maintenance activities and teach you how to keep on top of your system's maintenance issues!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply