Solved

SQL Defrag

Posted on 2015-02-19
8
143 Views
Last Modified: 2015-02-25
I am working with a very large reporting Database (3-4TB) that has nightly ETLs running. We also have a high percentage of fragmentation. I want to defrag the database, but due to the size of the database and the time it will probably take I will need to do this online and may not necessarily be able to run outside of business hours. Which method would least affect end users, backups, and ETLs? Should I just Reorganize, or can I Rebuild with Online parameter?
0
Comment
Question by:DKHenery
8 Comments
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 40619237
I once took 48 hours to rebuild a 2TB table. Took all weekend.
You need to defrag by object. Chose only the tables with more than 30% fragmentation and rebuild those.
For fragmentation level between 10%-30% it's better to reorganize.
Don't do nothing with tables with less than 10% of fragmentation.
If you do this by groups then you can split all the heavy work during the week or month depending on the available maintenance window.
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 215 total points
ID: 40619372
Reorganize will have much less affect than an online rebuild, although even reorganize can slightly slow down other requests.

The percentages above are very general guidelines.  The index's usage should also factor into it, and can definitely change what should be done.  For example, an extremely heavily used table might merit rebuilding at 20%.  A smaller table can be rebuilt rather than reorg'ed.  A very large, very active table might need defragmented rather than rebuilt at 31%.  You get the idea.  

You can get index usage from:
sys.dm_db_index_usage_stats

If you have time, you should also look at missing index stats from SQL to see if a new index(es) need built.  You can get that info from joining views with the name pattern:
sys.dm_db_missing_index%
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 72 total points
ID: 40620609
Planning to do this type of maintenance on a regular basis?
there are available scripts you may want to check out at some point

SQL Server Backup, Integrity Check, and Index and Statistics Maintenance

The SQL Server Maintenance Solution comprises scripts for running backups, integrity checks, and index and statistics maintenance on all editions of Microsoft SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, and SQL Server 2014. The solution is based on stored procedures, the sqlcmd utility, and SQL Server Agent jobs. I designed the solution for the most mission-critical environments, and it is used in many organizations around the world. Numerous SQL Server community experts recommend the SQL Server Maintenance Solution, which has been a Gold winner in the 2013, 2012, 2011, and 2010 SQL Server Magazine Awards. The SQL Server Maintenance Solution is free.
Ola Hallengren
http://ola.hallengren.com/

This (old but good, if a bit slow) conference video is a useful introduction as Ola explains his approach and answers questions: recommended video

also refer to
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_28163719.html#a39265474


Alternatively:  blitzindex from "Brent Ozar Unlimited"
http://www.brentozar.com/blitzindex/
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 71 total points
ID: 40623891
I am working with a very large reporting Database (3-4TB) that has nightly ETLs running.
If you have historical information you need to implement table partitioning.   That way you may only need to index the active partition.
0
 
LVL 24

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 142 total points
ID: 40629493
I would tell you that Reorganizing the database will help get rid of fragmentation at the leaf level. Reorganize will not defragment the intermediate levels or reset fill factor, but rebuild will require 1.5 times the size of the table/index to be free in the database and if not then it will grow your data files. If you don't have Enterprise Edition you won't be able to do an ONLINE rebuild, so you will want to be careful how much you rebuild at once.

Reorganize also only has an 8 KB overhead as it works by page and is restartable per se.  If you have not maintained it well I would start with a reorganize, but that is just me.  I have reorganized a database with 90,000 tables while maintaining no growth in the data files or the log files.

If you want to go that route, let me know, but I would not shy away from reorg as a method to get control.
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 215 total points
ID: 40629526
You can redirect the work storage required to be in tempdb instead of your main db by specifying:
SORT_IN_TEMPDB = ON

Of course you'll need to verify that tempdb has enough space to handle that.  But, in some cases, that option can speed up the rebuild and increase the compactness of the rebuilt table.
0
 
LVL 24

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 142 total points
ID: 40629539
That is part of it, but it will still create a copy of the table/index and build it while the other index/table is in place. So while the sort will be taken care of, you will still have the 1.5 times the storage for the re-build.
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 215 total points
ID: 40629562
True enough.  The tempdb option is more about getting the best final result whenever possible.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now