July 29, 2011 at 4:54 pm
What does sql server offline reindex mean, how it compare with online reindex?
Which one is the better option to use?
It seems standard edition only has offline reindex.
Should reindex always be run at the time not many users connection like at night?
Thanks
July 29, 2011 at 6:15 pm
Should reindex always be run at the time not many users connection like at night?
- it's depends on your environment & your maintenance window. Re indexing is part of maintenance so it should be run when their is no load on server. Day time you should not use due to heavy users activity.
What does sql server offline reindex mean, how it compare with online reindex?
Which one is the better option to use?
It seems standard edition only has offline reindex.
- Check out below article and their is bunch of other article also, check them for better idea about online indexing..
http://www.sqlskills.com/BLOGS/PAUL/category/Indexes-From-Every-Angle.aspx
July 29, 2011 at 7:47 pm
Depending on your definition and scope of "reindex", it could mean one of two things. A REBUILD or a REORGANIZE. They are very different results with very different overhead. A REORGANIZE doesn't have to have an index taken offline, but it only defrags the leaf nodes of the b-tree. The REBUILD actually drops and rebuilds the whole index from the root all the way down to the leaf pages.
If you have a window, then you should be rebuilding indexes during schedule maintenance. If not, you will have to work in a smart mixture between reorganizes and rebuilds.
July 30, 2011 at 9:12 pm
sqlfriends (7/29/2011)
What does sql server offline reindex mean, how it compare with online reindex?Which one is the better option to use?
It seems standard edition only has offline reindex.
Should reindex always be run at the time not many users connection like at night?
Thanks
Offline indexing is faster and database outage is required where as online indexing is slow but no need to take the database offline.
So you decide which is ok for you to do. if your database in not operational in any time just do offline indexing at that time.
Should reindex always be run at the time not many users connection like at night?
Yes
July 30, 2011 at 9:18 pm
A REBUILD or a REORGANIZE. They are very different results with very different overhead.
In general,
When your indexes are 30 % fragmented or less then we do reorganizing the indexes
When your indexes are more than 30% fragmented then go for rebuilding indexes.
If you google it you can get the defrag sp that do rebuild and re-org in a single job based on benchmark.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply