Solved

SQL Defrag

Posted on 2015-02-19
8
138 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 45

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:ScottPletcher
ScottPletcher 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
 
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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:ScottPletcher
ScottPletcher 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:ScottPletcher
ScottPletcher earned 215 total points
ID: 40629562
True enough.  The tempdb option is more about getting the best final result whenever possible.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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 combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

760 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

21 Experts available now in Live!

Get 1:1 Help Now