Solved

SQL Defrag

Posted on 2015-02-19
8
140 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 46

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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
email the result out from a T-SQL queries 29 62
Alter table 4 23
Help with SQL Server Stoplist 2 16
Updating a column using a lookup on another table. 6 17
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

912 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

19 Experts available now in Live!

Get 1:1 Help Now