December 28, 2016 at 2:33 pm
We're in the midst of an application upgrade, and part of the vendor instructions after the database pieces are upgraded is to rebuild all indexes, for improved performance. I've been reading over index rebuilds, and what to consider (sounds like >30% avg fragmentation along with > 1000 pages is a good indicator of the need to rebuild an index).
Is this a difference scenario though, since there are an untold number of table changes? Is there an argument for just rebuilding all indexes here? And maybe I'm just overthinking things, and the 30% argument is just meant for maintenance, while an overall index rebuild is fine in this scenario. We have the downtime to rebuild all of the indexes.
--=Chuck
December 28, 2016 at 4:04 pm
chuck.forbes (12/28/2016)
We're in the midst of an application upgrade, and part of the vendor instructions after the database pieces are upgraded is to rebuild all indexes, for improved performance. I've been reading over index rebuilds, and what to consider (sounds like >30% avg fragmentation along with > 1000 pages is a good indicator of the need to rebuild an index).Is this a difference scenario though, since there are an untold number of table changes? Is there an argument for just rebuilding all indexes here? And maybe I'm just overthinking things, and the 30% argument is just meant for maintenance, while an overall index rebuild is fine in this scenario. We have the downtime to rebuild all of the indexes.
--=Chuck
To be honest, index rebuilds are a bit of an old wive's tale when it comes to performance. [font="Arial Black"]The important part is to rebuild statistics[/font]. I haven' t rebuilt or reorganized any indexes on my big production box since 17 Jan 2016 and performance actually got better due to what I call "nature fill factors". And remember that the optimizer never looks at fragmentation.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 28, 2016 at 4:41 pm
I came across some other suggestions about rebuilding the stats if you are moving to a new version of SQL Server. In this case, we are, from 2008 to 2012. I should have mentioned that as well in the original post. So I've already got that process running..
Before I read your response, I'd already just gone ahead and rebuilt all of the indexes (we're still in our dev environment), but that was also something I'd read in several other web articles when upgrading to a newer SQL instance. Do you have any experience with rebuilding indexes if moving between SQL versions, or do you still think that just rebuilding stats has far more powerful of an effect?
Thanks,
--=Chuck
December 28, 2016 at 4:51 pm
chuck.forbes (12/28/2016)
I came across some other suggestions about rebuilding the stats if you are moving to a new version of SQL Server. In this case, we are, from 2008 to 2012. I should have mentioned that as well in the original post. So I've already got that process running..Before I read your response, I'd already just gone ahead and rebuilt all of the indexes (we're still in our dev environment), but that was also something I'd read in several other web articles when upgrading to a newer SQL instance. Do you have any experience with rebuilding indexes if moving between SQL versions, or do you still think that just rebuilding stats has far more powerful of an effect?
Thanks,
--=Chuck
Doesn't make any difference either. Doing a migration by restore or in place doesn't do things like magically invert indexes like a shrink can. The only thing I was going to consider doing was to rebuild any indexes that got below 70% average page density but that hasn't happened.
Also, when we did restores this previous June from old 2005 boxes to brand new hardware with 2012 on them, we didn't do any index rebuilds/reorgs because of the move. In fact, we initially didn't do any stats rebuilds because the stats also come along for the ride. To the best of my knowledge, there was no difference in the way stats are built. We saw no performance degradation never mind any degradation because of our inaction in those areas.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 28, 2016 at 5:17 pm
They used to be absolutely good things to do a really long time ago (2005 and older) when upgrades were really interesting beasts and hardware was "weak" and really hard to get right. A lot of those are either no longer relevant at all or have no noticeable impact for most systems today because their size, txn rates, etc... won't make commodity servers break a sweat. Unfortunately, documentation, including guides, are often just revved to add new features and remove/tag old features. Things that don't fall into either are usually not touched so old recommendations silently live forever.
If your upgraded SQL Server will run on slow storage, clustered index fragmentation on large tables may have significant impact on some queries (e.g. reporting types that scan for lots of consecutive rows). Selective lookups are unlikely to see any performance impact due to fragmentation as it'll likely be using both index types to seek rather than scan.
Stats are a different story and, as Jeff highlighted, are really important to keep up-to-date. This is independent of whether you are upgrading or not. If they're already current, upgrades won't invalidate the stats.
December 28, 2016 at 6:31 pm
I agree with what everyone has said but I would probably just follow exactly what the vendor wants even if it is kind of a waste but doesn't hurt anything. I'd rather follow their instructions and have any future app issues be their issues and not something anyone could attribute to the upgrade process.
Sue
December 28, 2016 at 8:55 pm
chuck.forbes (12/28/2016)
We're in the midst of an application upgrade, and part of the vendor instructions after the database pieces are upgraded is to rebuild all indexes, for improved performance.
Feel sorry for you.
This kind of suggestions indicates the level of knowledge your vendor has about databases, indexes and performance.
With that kind of knowledge - it's not so hard to imagine the quality of software produced by that house.
There is a very good chance no index rebuild can fix its performance.
So, do what Sue said - follow their instructions.
This way you at least will avoid taking any blame for a sluggish performance of the application.
_____________
Code for TallyGenerator
December 29, 2016 at 7:17 am
chuck.forbes (12/28/2016)
We're in the midst of an application upgrade, and part of the vendor instructions after the database pieces are upgraded is to rebuild all indexes, for improved performance. I've been reading over index rebuilds, and what to consider (sounds like >30% avg fragmentation along with > 1000 pages is a good indicator of the need to rebuild an index).Is this a difference scenario though, since there are an untold number of table changes? Is there an argument for just rebuilding all indexes here? And maybe I'm just overthinking things, and the 30% argument is just meant for maintenance, while an overall index rebuild is fine in this scenario. We have the downtime to rebuild all of the indexes.
--=Chuck
As others have stated, when your index keys follow best practices such as being narrow and ever increasing, fragmentation isn't really too much of a problem. Without knowing your application's design, it's difficult to give specific recommendations, but I've found even in the case where the indexes don't follow best practices that the 30% rule tends to be a bit too aggressive and would cause it to be rebuilt more frequently than is really beneficial unless there's a tremendous amount of scans happening on the table instead of seeks and new data is getting added all over the place (key is not ever increasing).
To build a little on what Jeff said, especially if you have large tables with large quantity of new records getting added, statistics can get stale quickly because of Microsoft's 20% rule. When the query optimizer doesn't have good statistics on a table, it can start to do strange things:
http://www.sqlskills.com/blogs/erin/understanding-when-statistics-will-automatically-update/
January 3, 2017 at 9:58 pm
This was removed by the editor as SPAM
January 4, 2017 at 8:22 am
First off, I appreciate all of the education everyone's been providing to me over the last year and a half.
After reading over everyone's suggestions, I think there's a couple of things here that have definitely been affirmed for me. One, that I can safely just update the statistics on all indexes, and then SQL Server perform this step from now on. And two, I also concur that this vendor's suggestion on rebuilding indexes is likely a carried-over instruction from early SQL Server versions. They're definitely more focused on building the front-end (and to their credit, very helpful when we've located bugs, or anomalies).
Thanks again, and I hope to be able to start answering other peoples questions, here in the near future,
--=Chuck
January 4, 2017 at 8:55 am
chuck.forbes (1/4/2017)
First off, I appreciate all of the education everyone's been providing to me over the last year and a half.After reading over everyone's suggestions, I think there's a couple of things here that have definitely been affirmed for me. One, that I can safely just update the statistics on all indexes, and then SQL Server perform this step from now on. And two, I also concur that this vendor's suggestion on rebuilding indexes is likely a carried-over instruction from early SQL Server versions. They're definitely more focused on building the front-end (and to their credit, very helpful when we've located bugs, or anomalies).
Thanks again, and I hope to be able to start answering other peoples questions, here in the near future,
--=Chuck
Just to b sure, rebuilding stats has been more important than rebuilding indexes since I can remember.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 4, 2017 at 9:20 am
Thanks for that. In my searches on index rebuilding, they've been in either of three categories:
1) Don't rebuild them.
2) Rebuild them when fragmentation exceeds 30%,
3) Moving to a newer version of SQL? Rebuild indexes, but also rebuild stats, and recompile sp's
So, from that search perspective, the emphasis was never towards a statistics rebuild.
--=cf
January 4, 2017 at 11:40 am
For any SQL Server upgrade I rebuild indexes when I migrate up to a new version. It seems like that it isn't as important as it used to be.. However, update your stats. The newer optimizer needs to gather its new algorthim of stats collection to have good data access paths.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply