February 4, 2015 at 4:06 pm
Hey Guys,
I am working in sqlserver 2008 R2
I have an interesting question. My question is does index Reorganize and rebuild everyday increases the DB performance? Which is good weekly rebuild or daily rebuild? please advice me.
February 4, 2015 at 6:51 pm
KGJ-Dev (2/4/2015)
Hey Guys,I am working in sqlserver 2008 R2
I have an interesting question. My question is does index Reorganize and rebuild everyday increases the DB performance? Which is good weekly rebuild or daily rebuild? please advice me.
I would say that it depends on many factors. For example how much activity in the tables, how new INSERTs are placed into the overall index space (e.g., is the PRIMARY KEY a composite that will spread the inserts over many pages of the index?) and how specifically the INDEXes are used in the various retrieval queries.
Basically, if I understand it correctly, re-indexing is done to reduce the number of page splits in an index, which in turn should reduce the number of IOs for any query using that INDEX.
Of course, others (like Jeff Moden, Gail Shaw, Grant Fritchey, et. al.) that are more familiar with the internals of SQL server than me may come along and say I'm full of crap, or at least give a better technical explanation.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 4, 2015 at 7:17 pm
thank you dwain for your explanation. The reason why i am asking, is i have some exe's which have some program running on that time and it produces time out. Also please suggest me should we try re-indexing online/offine mode. Because as far as i understood was, if we do it online mode we can read/write into the table though the re-indexing happening at the time. As you said the guru's (Jeff/Gail/etc) could help me in understanding to move forward.
February 4, 2015 at 7:34 pm
KGJ-Dev (2/4/2015)
thank you dwain for your explanation. The reason why i am asking, is i have some exe's which have some program running on that time and it produces time out. Also please suggest me should we try re-indexing online/offine mode. Because as far as i understood was, if we do it online mode we can read/write into the table though the re-indexing happening at the time. As you said the guru's (Jeff/Gail/etc) could help me in understanding to move forward.
Personally I can't see doing re-indexing every day, unless you're inserting 100s of thousands or rows everyday and those are causing lots of index fragmentation. It is probably better to do it once a week during off-peak usage. And if the off-peak time is totally unused, then do it in the offline mode because presumably it should run quicker.
Again, pick and choose what you re-index. Doing it to every table is probably a waste of time.
Note that there is probably a way to determine the level at which your indexes are fragmented (I'd need to research it as I'm not a DBA), and use that to determine whether a table is ripe for re-indexing.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 4, 2015 at 7:49 pm
thank you dwain for your precious time to mentor me. Looking for others suggestion as well.
February 4, 2015 at 8:23 pm
KGJ-Dev (2/4/2015)
thank you dwain for your precious time to mentor me. Looking for others suggestion as well.
Yeah, me too. That's why I replied to this thread in the first place. 🙂
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 4, 2015 at 9:07 pm
Online index rebuilds are nice, but they're only on the enterprise version of the db (IIRC). If you have an environment with that, they're nice to use. The drawback is that they use twice as much space as they rebuild since they basically store two copies of the index. Also note if you have any column stored off-row (XML, varchar(max) at the like) you cannot use online rebuilds.
February 5, 2015 at 4:45 am
Everything Dwain has said so far is spot on.
So, to your first question, is it ok and will it help performance to defragment your indexes daily.
Sure. If you have the downtime to defragment them daily, it will help performance some. There are three different sets of scripts I recommend for this, Ola Hollengren's, Michelle Ufford's and the new one Minion Reindex. I wrote a review of Minion[/url] just the other day. It's probably worth checking out to see if you can put it to use.
Now, to what I think your real question is. You have queries that are running slow and you're looking to make them run faster and reindexing looks like an easy fix.
Bad news. You're probably only going to see marginal improvements on most queries because of defragging the indexes. For point look-ups, queries that go to a specific row using an index, it won't help at all. No, to tune queries you need to identify which queries are running slow, then understand why they're running slow. You do the first by capturing performance metrics using trace or extended events. You do the second by looking at the execution plans.
You should also look to your statistics. You might need to run a statistics update occasionally. For some statistics, depending on the size of the table, the automatic statistics update processes are not enough. In some cases, just using the sampled mechanisms from sp_udpatestats is not enough and you'll need to use the full command UPDATE STATISTICS WITH FULLSCAN. It's hard to advise you on this since we don't know anything about the system at all.
For scads more detail on all this, I'd suggest getting a copy of my book. The 2014 version linked below will still be very largely applicable to 2008 and is structured much better than the 2008 version of the book.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 5, 2015 at 5:28 am
Hi Grant,
I am using enterprise edition only.
thanks a lot for your knowledge sharing. it is really helpful to understand. Still i need to learn before taking any decision.
February 5, 2015 at 11:17 am
If you are having performance issues, you need to review the indexes themselves too, in particular verifying that you have the best clustered index on each table (hint: it's usually not the identity column). The clustered index is the single biggest performance factor on a table.
To do that, at a minimum you'll need to review the stats the SQL DM views provide for index usage and missing indexes. The former will give you a good idea of how the indexes are actually being used. The latter a rough idea of what index(es) might be missing; but, do not automatically create all the indexes SQL says are missing, you need someone knowledgeable to review each one first.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
February 5, 2015 at 1:09 pm
Thank you Scott for the knowledge sharing.
February 5, 2015 at 1:26 pm
KGJ-Dev (2/4/2015)
thank you dwain for your explanation. The reason why i am asking, is i have some exe's which have some program running on that time and it produces time out. Also please suggest me should we try re-indexing online/offine mode. Because as far as i understood was, if we do it online mode we can read/write into the table though the re-indexing happening at the time. As you said the guru's (Jeff/Gail/etc) could help me in understanding to move forward.
I'm a bit confused here.
Are you saying that you have some programs with queries that timeout, and you are hoping rebuilding indexes will make them run faster?
Or are you saying that right now you have a daily job rebuilding indexes, and your exe's time out while they are being rebuilt?
February 5, 2015 at 2:27 pm
Hi Nevyn,
your second line is the correct issue.
are you saying that right now you have a daily job rebuilding indexes, and your exe's time out while they are being rebuilt?
February 5, 2015 at 2:35 pm
In that case, I'd think doing it online is certainly worth a look. Even if you do it only once a week or less, that's an issue if it will break an ongoing process that you can't have a downtime window for.
Also, I the Olla Hallengren scripts Grant referenced (and probably the others, I'm not familiar with them) are good in that they can be set to only rebuild indexes that have higher fragmentation levels.
As to how often the rebuild should be running, we come back to Dwain's original answer .... it depends on how fast the indexes are becoming fragmented, and how much that fragmentation is affecting your queries.
February 5, 2015 at 4:31 pm
Hi Nevyn,
Thanks for the reply. Online indexing rebuild happening on my environment. Though, we had an time out issue.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply