April 26, 2011 at 6:02 am
Can you do online indexing when in compatibility level 80? Would this make any difference in the time it would take to do a massive table? Many thanks
April 26, 2011 at 6:07 am
I don't see why you couldn't. Online indexing is in the engine, not in the system tables.
Have you tried running the command?? Are you getting any errors?
Indexing a big table is just going to take more time than a small table. A lot of work is a lot of work. There's way around that except to have it happen in the less busy time possible and allocate enough ressources.
Ok now that the obvious is out of the way, again have you ran the command and had problems with it?
How much data are you talking about? I can reindex a 17 GB db in less than 30 minutes here... so unless you have a much much slower system or losts more data I wouldn't be worried too much if I were you.
April 26, 2011 at 6:11 am
I am worried becuase the index size is over 100GB, do I not need to alter the index first to do it online?
April 26, 2011 at 6:16 am
Ok that's outside my knowledge range. I'll shutup and listen in now :w00t:.
One thing I can say tho is that our SAN can write approximately 1 GB / minute. That's after the read / sort operation. So unless you have a much, much faster san that ours you're definitely looking at hours of processing.
April 26, 2011 at 6:24 am
Called in a few experts... they should be here soon.
April 26, 2011 at 6:28 am
ISTR that you cannot rebuild an index online for SQL Server 2000 (level 80). It is an offline process. But the DBCC IndexDefrag is an online process.
I think you have to drop the index and recreate it or use DBCC DBReIndex (offline) to get your index rebuilt.
EDIT: After re-reading the question and doing a bit of research, I have verified my impression that Online Indexing is new to SQL Server 2005 (Enterprise Edition) and above. Which means you'll have to alter your compatibility level to 90 if you have the proper edition of SQL Server.
April 26, 2011 at 6:59 am
Edward-445599 (4/26/2011)
Can you do online indexing when in compatibility level 80? Would this make any difference in the time it would take to do a massive table?
Yes you can, assuming you are running Enterprise Edition, and assuming the index meets the usual conditions for an online rebuild. An ONLINE index rebuild is slower than offline, because SQL Server has to do a good deal of extra work. The upside is that the index remains available to queries while it is being rebuilt.
Although an ONLINE index rebuild will always take longer, and use more server resources than an offline rebuild, it does depend on the level of concurrent index modifications while the rebuild is in progress.
Another important consideration is logging. If your database is set to FULL recovery, consider switching to BULK_LOGGED recovery to take advantage of minimal logging. Backup the log before and after the switch to BULK_LOGGED to minimize the amount of time you are without the ability to restore to an arbitrary point in time, if that is important to you. If your database uses SIMPLE recovery, this is not an issue - the index rebuild will always be minimally logged.
That said, your question worries me. A database should only be set to compatibility mode as a short-term workaround, while you complete the work necessary to upgrade to (presumably) SQL Server 2005. Also, 100GB is awfully large for an index - if you were to share some more details about your environment and the index, we might be able to offer additional advice.
References:
http://msdn.microsoft.com/en-us/library/ms191261.aspx
http://msdn.microsoft.com/en-us/library/ms190981.aspx
http://msdn.microsoft.com/en-us/library/ms177442.aspx
http://msdn.microsoft.com/en-us/library/ms188388.aspx
http://msdn.microsoft.com/en-us/library/ms191244.aspx
http://msdn.microsoft.com/en-us/library/ms190203.aspx
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 26, 2011 at 7:04 am
Sql Wiki in action 😀
I bet you could fin 20 more usefull refferences for this issue if you really tried hard enough :hehe:.
April 26, 2011 at 7:26 am
Its a poorly designed database years and years old which uses 2 tables to store most things. Short term thats not going to change. So in the short term we have to index these tables. As to 2005 in 80 again thats the way it is in short term. months from now and this will all cahnge but for now thats just the way it is
April 26, 2011 at 7:28 am
Ninja's_RGR'us (4/26/2011)
Sql Wiki in action 😀I bet you could fin 20 more usefull refferences for this issue if you really tried hard enough :hehe:.
At least!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 26, 2011 at 7:29 am
You CAN do online indexing when in compatibility level 80.
The following code ran fine for me under 2005 Enterprise Edition; never hurts to test. 😎
USE [pubs]
select compatibility_level, name from sys.databases where name=db_name()
GO
ALTER INDEX [UPKCL_auidind] ON [dbo].[authors]
REBUILD WITH
( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
ONLINE = ON, SORT_IN_TEMPDB = OFF )
GO
USE [pubs]
GO
ALTER INDEX [aunmind] ON [dbo].[authors]
REBUILD WITH
( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
ONLINE = ON, SORT_IN_TEMPDB = OFF )
Results:
compatibility_level name
------------------- ------
80 pubs
April 26, 2011 at 8:02 am
Michael,
You're right. Totally my bad. I was working of a SQL 2000 assumption and by the time I figured out I was wrong, I was rushing to correct my post before a meeting, and didn't write the correct information.
I should know better than to do that.
April 26, 2011 at 8:08 am
Brandie Tarvin (4/26/2011)
Michael,You're right. Totally my bad. I was working of a SQL 2000 assumption and by the time I figured out I was wrong, I was rushing to correct my post before a meeting, and didn't write the correct information.
I should know better than to do that.
I think we can let it go, THIS TIME :hehe:.
April 26, 2011 at 8:10 am
Brandie Tarvin (4/26/2011)
Michael,You're right. Totally my bad. I was working of a SQL 2000 assumption and by the time I figured out I was wrong, I was rushing to correct my post before a meeting, and didn't write the correct information.
I should know better than to do that.
I was wondering why the OP even posted this question.
Taking 5 minutes to actually test it would have verified that you can.
April 26, 2011 at 8:14 am
Michael Valentine Jones (4/26/2011)
Brandie Tarvin (4/26/2011)
Michael,You're right. Totally my bad. I was working of a SQL 2000 assumption and by the time I figured out I was wrong, I was rushing to correct my post before a meeting, and didn't write the correct information.
I should know better than to do that.
I was wondering why the OP even posted this question.
Taking 5 minutes to actually test it would have verified that you can.
I stopped torturing me with that question after only 3 months here... I'm actually a lot saner since then :w00t:.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply