Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Rebuilding Indexes very often is good? Expand / Collapse
Author
Message
Posted Friday, December 20, 2013 6:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 11, 2014 2:32 AM
Points: 128, Visits: 182
Hi Friends,
I was about to work on the production server all day last week. I was doing ETL process. Every day i dumped around 2 to 3 millions rows into 4 to 5 tables. At the end of day 1 , there is an application in the front end felt performance issue when searching for the data. One of my colleague advised me to rebuild the indexes on the respective tables. I did and saw some improvement in the performance. The very next day, DBA team automated the prcoess of rebulding the indexes once my ETL is done. So they were keep rebuilding the indexes every day in tables consisting of 15 million rows.

My question is, Just Curious, whether it is good or bad rebuilding the indexes everyday?

Any suggestions would be really appreciated. I am just trying to learn from you guys.
Post #1524960
Posted Friday, December 20, 2013 8:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:51 AM
Points: 14,788, Visits: 27,264
Rebuilding indexes is a costly operation. But, if it's not interfering with any of your code, running it once a day is not exactly a big deal. I've had systems with serious down time at night (despite being a 24 hour shop, there just wasn't as much business at some points of the day) that allowed us to do it daily on some systems. It didn't hurt anything and it probably helped some. But, that's only if it's not causing resource conflicts. Also, you need to take into account the degree to which your indexes are fragmented and the size of the indexes. Below a certain threshold on both measures, it's not worth the effort.

So, there's nothing inherently wrong with it, but monitor to be sure.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1525005
Posted Friday, December 20, 2013 9:14 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 9:02 AM
Points: 956, Visits: 1,273
All of my production databases are not that large. Because of that fact I rebuild all of the indexes and update all statistics in all of my databases as a nightly process so that it doesn't interfere with day time processing.

Depending on how large your database is will determine how often and how frequent you would want to manage your indexes & statistics.

I have a specialized index manager where by I only rebuild indexes if they exceed a threshold degree of fragmentation. My case is 30% of fragmentation. This process is a lot more efficient to run on a larger database only because not all of the indexes become fragmented.

It is important to manage indexes and statistics on a routine schedule to maximize efficiency on your database.

Kurt


Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman
Post #1525047
Posted Monday, December 23, 2013 3:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 11, 2014 2:32 AM
Points: 128, Visits: 182
Thank you Grant and Kurt...

I will check on the fragementation. I had no idea when i was asking this question. Now i get an idea on what basis the indexes should be rebuilt...

Thanks a lot guys...
Post #1525457
Posted Monday, December 30, 2013 4:23 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:21 AM
Points: 2,797, Visits: 3,079
The main thing that slows down index-related performance is fragmentation. You should do some research around this issue to understand more about fragmentation and what an index rebuild does.

The index rebuild should leave you with indexes that have a standard amount of fragmentation, as determined by the Fill Factor. As you start to do inserts, index performance will actually improve for a time as the free-space pages are used, and then start to deteriorate as index fragmentation begins. Eventually the fragmentation in your index will be worse than it was after you completed your index rebuild, and performance can only get worse.

You should therefore perform regular index rebuilds to keep your indexes in the optimal state. Not every index in your database will be updated at the same rate, so because index rebuilds are an expensive operation you should work out a way to only rebuild those indexes that need this maintenance.

The Maintenance Plan process supplied with SQL Server can automate the process of selecting which indexes need maintenance, but there are better (and free) tools available for this. Ola Hallengren's DB maintenance toolkit is one of the best available free tools and gets regular updates to cope with new versions of SQL Server, etc. The commercial products often give more features than the free tools, and all of Red-Gate, Idera and Dell all have excellent products at very reasonable prices.

When you have an automated method of doing index maintenance, you need to decide how often you want to run it. Many organisations do this at the weekend, but some find they need to do this work daily. You will need to work out what is best for you.


Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 25 March 2014: now over 28,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #1526451
Posted Tuesday, December 31, 2013 2:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 11, 2014 2:32 AM
Points: 128, Visits: 182
Thank You Ed, I am rebuilding and reorgranizing all my indexes to reduce the fragmentation and to increase the page space.

thanks a lot for guiding me in this....
Post #1526699
Posted Tuesday, December 31, 2013 10:41 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 9:54 AM
Points: 1,201, Visits: 2,660
I was recently at a SQL Server user group and a former Microsoft employee that worked on developing SQL Server did a 2 hour talk about indexes. He basically said that if the table design and index design is good, meaning a good fill factor, that you should rarely if almost never have to rebuild indexes. He said if you keep stats updated enough that should be sufficient unless you do a TON of inserts and a TON of deletes. He talked about some large companies that have massive tables that have to be avail 24X7X365 and he said they can't rebuild indexes.... but the key is a good design from the get go.

I have monitored the fragmentation of some key tables here and have scaled way back rebuild indexes and have Update Stats running weekly and have not seen any performance hit at all with query duration.



Post #1526809
Posted Tuesday, December 31, 2013 10:53 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, April 13, 2014 12:44 PM
Points: 509, Visits: 300
Good or bad to rebuild all indexes daily...

I think that it is overkill to unconditionally rebuild.

I posted this elsewhere, but my recommendation is to get a hold of Michelle Ufford's Index defrag script at http://sqlfool.com/2011/06/index-defrag-script-v4-1/ and implement a job that runs this as a defrag for indexes over 25% fragmented and as a rebuild for indexes over 35% (or other thresholds that make sense for your situation). Michelle's scripts generate a sp that has been bulletproof and highly reliable for us.

If you have Enterprise edition, you can run this ONLINE ON overnight, and you can also organize how these defrags are run, and throttle the MAXDOP to limit processors involved in the work. I'd also rebuild stats weekly. Other posters correctly gave similar advice.

Thanks
John.
Post #1526814
Posted Tuesday, December 31, 2013 11:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, April 12, 2014 12:51 AM
Points: 5,986, Visits: 6,930
I'd like to add a special case to the general philosophy here. I worked on a BLOB system that was the majority of storage in the DB, and we had a 'noise' area of maybe 0.05% at the top of a particular table where most of our searching and work was done.

Because of the combination of these factors, that table was rebuilt every night. This was on SQL 2k, so partitioning was flakey at best, so we had to rebuild the entire billion row table for just a few trailing records (in ratio anyway) to keep the system up to speed. We had a pretty consistent hit rate from our applications in the background so we could tell when we had a query slowdown due to I/O and fragmentation concerns.

So, while I agree that page sizing and standardization of fragmentation are excellent baselines to work from, I would also recommend that you keep in mind the age old SQL Maxim: "It Depends." Sometimes you just have to beat the thing into submission.

Caveat: I only thought I was an expert at the time (small pond, big fish...), there may have been much better alternatives but that was a long time ago and I forget the details, and there's much better alternatives available now (partitioning comes to mind).



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1526817
Posted Tuesday, December 31, 2013 1:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 6:57 AM
Points: 16, Visits: 375
Some food for thought...There is a great video discussing why you generally should NOT worry about fragmentation. Instead, you should set fill factor to 100 and try to cache as much data as possible, in which case fragmentation levels do not matter as much.

http://www.brentozar.com/archive/2013/09/why-index-fragmentation-doesnt-matter-video/

In a nutshell, if you're constantly defragmenting indexes, you are writing more data to transaction logs, making backups take far longer, and overworking your SAN needlessly. Also, mirroring, and log shipping are going to take longer and use more resources. I have lessened the frequency that I do defragmentations and have seen backup times drop quite a bit.
Post #1526846
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse