March 27, 2009 at 5:42 am
... does this also reorganize the non-clustered indexes on that table?
I believe when you REBUILD a clustered index it will also rebuild the NC indexes - is the same true of REORGANIZE?
The reason I'm asking is I'm setting up ascript to reorg all indexes in a DB on a nightly basis, and am trying to establish what order to reorg the indexes in.
The data from all the tables gets truncated nightly, and then populated again from somewhere else
I can't use REBUILD because we have Standard Edition and rebuild cannot be performed ONLINE on Standard unfortunately.
The other option would be to drop the indexes prior to truncating the data, then recreating the indexes after the data is loaded, I'm currently mulling over what would be the better option.
I will of course monitor what fragmentation occurs after one night's truncate/load operation in order to establish if the fragmentation is below the recommended percentage for reorg (as opposed to rebuild)
Any thoughts would be much appreciated
March 27, 2009 at 5:50 am
No, I don't think it rearranges the nonclustered indexes.
If you're doing large scale loads, you might want to drop the indexes & recreate them after the load because a large number of inserts can be very expensive around indexes. It depends on the volume of course.
----------------------------------------------------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
March 27, 2009 at 5:58 am
Thanks Grant
So the order in which I'd reorg indexes would not be important then, I expect?
The tables are reasonably small (generally under 20000 rows), so I'm going to monitor for a couple of days to see what happens when I don't do anything to the indexes.
They're hugely fragmented now, but that's due to the lack of clustered indexes on tables (which is one of the reasons for this exercise...)
March 27, 2009 at 6:05 am
So the first thing I wonder is if you have any control over what order the data comes into the DB when it gets reloaded. If you could dictate the order the data come in it would be easier to leave the existing indexes or rebuild them because the data was already ordered correctly.
Just a thought.
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
March 27, 2009 at 6:10 am
Hmm, that's a thought
I have full control over how the data comes in (I designed the SSIS package to do it)
Unfortunately there's no real order, as such. Our Id fields are varchars, but in GUID form (and ther's not much I can do about that in the current timescales)
March 27, 2009 at 6:18 am
If your clustered indexes are on identity columns then you have some control over the order of the data. If the indexes are on other columns then it would be difficult.
"Keep Trying"
March 27, 2009 at 6:18 am
mazzz (3/27/2009)
Thanks GrantSo the order in which I'd reorg indexes would not be important then, I expect?
The tables are reasonably small (generally under 20000 rows), so I'm going to monitor for a couple of days to see what happens when I don't do anything to the indexes.
They're hugely fragmented now, but that's due to the lack of clustered indexes on tables (which is one of the reasons for this exercise...)
It is one of those places where your mileage will vary, so testing is necessary.
Ordering the data helps.
If you're using GUID's, make sure you use sequential guids and not random ones.
----------------------------------------------------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
March 27, 2009 at 6:20 am
Chirag (3/27/2009)
If your clustered indexes are on identity columns then you have some control over the order of the data. If the indexes are on other columns then it would be difficult.
Hardly any of our tables have identity columns, unfortunately
March 27, 2009 at 6:22 am
Grant Fritchey (3/27/2009)
It is one of those places where your mileage will vary, so testing is necessary.Ordering the data helps.
If you're using GUID's, make sure you use sequential guids and not random ones.
They are random unfortunately - but they're not uniqueidentifier datatype, don't know if that makes a difference. I suppose it's just a varchar and one can order it like any other string?
Thanks for the responses everyone, much appreciated - lots of food for thought!
March 27, 2009 at 9:40 am
Since you are truncating and loading every time - here is what i would recommend:
1) Disable all NC indexes
a) Leave clustered indexes enabled - or you can't access the table
b) ALTER INDEX {indexname} ON {tablename} DISABLE;
2) Truncate Table
3) Load data
a) if possible, load in clustered key order, but is not absolutely necessary
4) Rebuild ALL indexes
a) ALTER INDEX ALL ON table REBUILD WITH ({available options})
You should be able to use the SORT_IN_TEMPDB option, even on standard.
There is no reason to worry about order of building the indexes, unless you are actually creating a clustered index.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
March 27, 2009 at 10:03 am
Thanks very much Jeffrey
Jeffrey Williams (3/27/2009)
Since you are truncating and loading every time - here is what i would recommend:1) Disable all NC indexes
a) Leave clustered indexes enabled - or you can't access the table
b) ALTER INDEX {indexname} ON {tablename} DISABLE;
I have to shoot off in a minute (to go to the SQLBits IV conference, of all things!), so no time to read about DISABLE just now - I must say I've never looked into it.
I'm guessing that, if there are inserts, deletes etc on a table with a disabled NC index, then the index isn't updated to reflect the insert/delete? Presumably minimising overhead during the dataload
2) Truncate Table
3) Load data
a) if possible, load in clustered key order, but is not absolutely necessary
I will look into this - it's something I had never thought of until Stamey mentioned it further up the thread, but it makes perfect sense
4) Rebuild ALL indexes
a) ALTER INDEX ALL ON table REBUILD WITH ({available options})
Rebuild might be a problem for us, due to it not being an online operation (on Sql Server Standard)
Would REORGANIZE be an acceptable substitute in this case?
You should be able to use the SORT_IN_TEMPDB option, even on standard.
There is no reason to worry about order of building the indexes, unless you are actually creating a clustered index.
Thanks for the clarification
I thought NC indexes were rebuilt whenever the clustered index was rebuilt, irrespective of whether one explicitly rebuilt the NC indexes?
Many thanks again
March 27, 2009 at 1:17 pm
Once an index is disabled - the only way to enable it is to rebuild. Rebuilding an index that was disabled in an offline operation (meaning, the index is not available until it has been rebuilt).
Since you are truncating the table - why does it matter whether or not the index rebuild is online or offline? The table (you know, all that data that was truncated and reloaded) won't be available anyways, well, because you are reloading all the data.
Offline in this case does not mean the system is offline - just that the index that is being rebuilt is offline (unavailable). Reorganize or rebuild online just means the index is still available while being rebuilt/reorganized.
The only time this would be an issue is if you had users on the system accessing the data while you are trying to rebuild the indexes. Again, since you are truncated the table - the table is not available until your process is done anyways.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
March 30, 2009 at 4:20 am
Jeffrey Williams (3/27/2009)
Once an index is disabled - the only way to enable it is to rebuild. Rebuilding an index that was disabled in an offline operation (meaning, the index is not available until it has been rebuilt).Since you are truncating the table - why does it matter whether or not the index rebuild is online or offline? The table (you know, all that data that was truncated and reloaded) won't be available anyways, well, because you are reloading all the data.
Offline in this case does not mean the system is offline - just that the index that is being rebuilt is offline (unavailable). Reorganize or rebuild online just means the index is still available while being rebuilt/reorganized.
... and the penny drops. I don't know what I thought exactly, but I suppose making an entire database unavailable while an index is being rebuilt would have been a bit over the top. Thanks for that clarification!
The only time this would be an issue is if you had users on the system accessing the data while you are trying to rebuild the indexes. Again, since you are truncated the table - the table is not available until your process is done anyways.
Our tables are fairly small, so that's not really an issue (data is unavailable for a matter of seconds really)
Many thanks for your help, Jeffrey
April 1, 2009 at 5:07 am
Reorganizing or rebuilding a cluster index does not have any impact on the non-cluster indexes in SQL Server 2000 SP4 or above, or any version of SQL 2005 or 2008. In older versions of SQL Server 2000, rebuilding a cluster index would also rebuild the NC indexes but this behaviour has since changed.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply