| Author |
Topic |
|
portrman
Starting Member
24 Posts |
 Posted - 2010-03-15 : 15:55:11
|
| I found a script that's suppose to rebuild/reorganize a database or a whole sqlinstance in a way that's low risk, and picking smart options - http://sqlfool.com/2009/06/index-defrag-script-v30/Overall it's looking great, but I'm confused as to one aspect.It generates a list of all indexes and then as it cycles through each one. If the index is on a table with LOBs (text, ntext, image) it won't rebuild the index, only reorganize. Otherwise, it tries to do an online rebuild if enterprise edition, or offline if not.Why would it not instead look at the index itself for such column types? Why is it looking at the table?This is preventing a great many indexes from being rebuilt, they are only being reorganized. As the script is suppose to be playing things safe, is there a performance consideration I'm missing?I'm very much learning still, so feel free to respond with articles and I can read.Cheers,Chad |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
portrman
Starting Member
24 Posts |
Posted - 2010-03-15 : 16:17:05
|
| Then why does sql not complain when I do online rebuilds of several of the indexes?Example:CREATE TABLE [dbo].[Event]( [EventKey] [bigint] NOT NULL, [AccountKey] [int] NOT NULL, [EventID] [varchar](50) NOT NULL, [MessageID] [varchar](70) NULL, [EventStateCode] [int] NOT NULL, [EventTypeCode] [int] NOT NULL, [SentTime] [datetime] NOT NULL, [EventName] [varchar](50) NULL, [EventXml] [xml] NOT NULL, [CreateDate] [datetime] NOT NULL, CONSTRAINT [PK_Event] PRIMARY KEY CLUSTERED ( [EventKey] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]CREATE NONCLUSTERED INDEX [IX_Event_MessageID] ON [dbo].[Event] ( [MessageID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]GOwhen I run:ALTER INDEX IX_Event_MessageID ON Event WITH (ONLINE = ON)It's perfectly happy. I have an index on SentTime as well that it is happy to rebuild online. Note, this is a sql2008 enterprise and the db in question is running in 2005 compat mode.Thanks,Chad |
 |
|
|
portrman
Starting Member
24 Posts |
Posted - 2010-03-15 : 16:18:41
|
| Sorry, mistyped:ALTER INDEX IX_Event_MessageID ON Event REBUILD WITH (ONLINE = ON) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-03-15 : 17:01:06
|
Please see the ALTER INDEX BOL topic: http://technet.microsoft.com/en-us/library/ms188388.aspxIf the custom stored procedure isn't doing what BOL says is and isn't possible, then you should instead try my custom stored procedure.Here's an important part of that topic:quote:
Indexes, including indexes on global temp tables, can be rebuilt online with the following exceptions:Disabled indexesXML indexesIndexes on local temp tablesPartitioned indexes Clustered indexes if the underlying table contains LOB data types Nonclustered indexes that are defined with LOB data type columnsNonclustered indexes can be rebuilt online if the table contains LOB data types but none of these columns are used in the index definition as either key or nonkey columns.
Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
portrman
Starting Member
24 Posts |
Posted - 2010-03-15 : 17:34:31
|
| Agreed, The clustered index should fail to rebuild online.However, is there any reason the index on the MessageID column should NOT be run online?It's the MessageID index that the script I originally referenced won't rebuild online, but instead will only reorganize.Looking at yours, yours will rebuild the messageid index online.Cheers |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-03-16 : 00:06:04
|
| I think you need to review SQL Fool's procedure a little closer. The procedure will reorganize indexes based upon the level of fragmentation. If the fragmentation is more than 10% and less than 30% (default values, I believe) - the index will be reorganized. Any index with more than 30% fragmentation will be rebuilt - and rebuilt online if possible.I have not reviewed the code recently, but the logic may be forcing offline logic for all indexes where the table contains an LOB column. If that is the case, then that portion of the procedure needs to be updated to check the actual index instead. |
|
|
|
portrman
Starting Member
24 Posts |
Posted - 2010-03-17 : 15:02:40
|
| Jeff,You're mostly correct. Before it decides whether to reorg or rebuild it first checks to see if the *table* has any LOBs. If so, it will always only reorganize.What it should do instead is, check if the index is Clustered or not. If Clustered, check the *table* for any LOBs. If yes, then organize, otherwise rebuild(either online or offline). If not-clustered, check if *index* contains any LOB columns. If yes, then organize, if no, rebuild(either online or offline).I've updated my version to do this additional checking. I've also added a param that controls if offline rebuilds are allowed or not. If not allowed but that's the only rebuild option, then it will reorg instead. This is useful to include if you'll be running this against non-enterprise versions and you want to play completely safe. I will only reorg or online rebuild during the week. Offline rebuilds I only allow to happen on Saturdays and Sundays.It has another issue where it will rebuild stats every time. I changed the logic so it only rebuilds stats if executeSQL = 1 ... I also played around some with the various print statements and debug statements to give more detail. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-17 : 15:58:19
|
| I agree with you, that's my understanding - Online rebuild of non-clustered indexes is fine if they don't include any LOB columns (and I've never come across an instance where an index did include a LOB column!)I don't have good data to go on about SQL2008, yet, but in SQL2000 we found very little, if any, disadvantage in only ever using Defrag and never using Rebuild on an index.If you've got an improved version it would be appreciated if you posted it in the SCRIPT forum herehttp://www.sqlteam.com/forums/forum.asp?FORUM_ID=11 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
portrman
Starting Member
24 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-03-21 : 12:26:03
|
| ChadDid you give your feedback the Michelle (SQLFool)? I would bet that she would appreciate the information and code and would happily incorporate your additions.I agree with everything you outlined above, which is what I was trying to get at in the first place. Basically, any script you download should be reviewed and understood, and modified for your environment as needed.Thanks for the update and the link to your version,Jeff |
|
|
|
|
ReOrganize VS Online Rebuild on table with LOBs