Solved

SQL Defrag

Posted on 2015-02-19
8
146 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 48

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL 10 35
Re-appearing SQL Server Agent jobs 7 30
CDC and AOG on MS SQL 2012 13 25
Remove the Middle name initials and the suffixes from a string in SQL Server 4 22
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

830 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