| Author |
Topic |
|
mattbeckman
Starting Member
8 Posts |
 Posted - 2008-06-17 : 15:42:33
|
| Hi all,Recently, I upgraded one of our database servers from SQL 2000 to SQL 2005. At the moment, all the databases are in 80 compatibility mode. I upgraded them based on the false pretenses that I could perform online reindexing right off the bat. Apparently, that isn't the case, and it requires switching to 90 compatibility. My question is if I switch to 90, will all indexes be invalidated/unusable? Or will I be able to switch to 90 and perform the online reindexing right away with no downtime? We have a few 24/7 production VLDB's in the mix (one of which is on this newly upgraded server), and only reasonable downtime is possible. The largest window of downtime I have is about 10 PM - 5 AM with prior notice.The upgrade was performed using SAN-based LUN's with a detach/reattach.Thanks,Matt |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-17 : 16:04:45
|
| Remember if you are doing Online Reindexing ,it uses lots of Tempdb space because of sorting , row versioning and etc.Make sure you have enough room in TempDB and you should be able to do it after changing to 90 compatibility. |
 |
|
|
mattbeckman
Starting Member
8 Posts |
Posted - 2008-06-17 : 17:28:38
|
quote:
Originally posted by tkizer
You can reindex databases on SQL Server 2005 that are using 80 for the compatibility level.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
You can reindex, but can you do it online?Thanks,Matt |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-17 : 17:37:07
|
| I have a database set to 80 compatiblity level on a SQL Server 2005 instance. I don't have any issues running my isp_ALTER_INDEX code (http://weblogs.sqlteam.com/tarad/archive/2007/11/27/60415.aspx) against it. We use SQL Server 2005 Enterprise Edition, so my code does the online option where possible. I don't see any checks in my code for the compatibility level. I don't see anything in the ALTER INDEX topic in SQL Server Books Online that suggests the online option isn't available for 80 compatibility level.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
mattbeckman
Starting Member
8 Posts |
Posted - 2008-06-17 : 17:45:46
|
| More information...The database is about 90GB (and is in 80 compatibility mode).The table data space is about 1GB.The table index space is about 8GB.The SQL server machine is an Intel Xeon quad-core 2.83GHz and 16GB of RAM on Windows Server 2k3 x64.Last night I attempted to run the following:ALTER INDEX [IX_FolderMem_FolderID_AccountID] ON [dbo].[FolderMem] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = ON, ONLINE = ON )GOIt ran for about 5 minutes...then it returned the message "completed successfully" without any additional information. I felt that 5 minutes was too short a time for it to actually have done anything useful. I was expecting maybe an hour or two for it to finish.I did some more searching online and found an article that stated online reindexing was not supported in modes below 9.0. |
|
|
|
mattbeckman
Starting Member
8 Posts |
Posted - 2008-06-17 : 17:46:54
|
quote:
Originally posted by tkizer
I have a database set to 80 compatiblity level on a SQL Server 2005 instance. I don't have any issues running my isp_ALTER_INDEX code (http://weblogs.sqlteam.com/tarad/archive/2007/11/27/60415.aspx) against it. We use SQL Server 2005 Enterprise Edition, so my code does the online option where possible. I don't see any checks in my code for the compatibility level. I don't see anything in the ALTER INDEX topic in SQL Server Books Online that suggests the online option isn't available for 80 compatibility level.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
Really? If that's the case, then that would be awesome. I'll check out the link you sent.Edit: Are you talking about reorganizing an index (aka INDEXDEFRAG in SQL2K) or rebuilding an index (aka drop/create in SQL2K)? - Matt |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-17 : 17:53:25
|
| How did you find out that online reindexing wasn't possible for 80 compatibility level? Did you try it and get an error or did you read it somewhere? If you got an error, I'd be curious as to your circumstances so that I can modify my code to account for it.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-17 : 17:55:21
|
| On the 80 compatibility level database, I have my isp_ALTER_INDEX code set to run with the REBUILD option. It runs on a weekly basis. It has never errored on that system.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
mattbeckman
Starting Member
8 Posts |
Posted - 2008-06-17 : 18:04:09
|
quote:
Originally posted by tkizer
How did you find out that online reindexing wasn't possible for 80 compatibility level? Did you try it and get an error or did you read it somewhere? If you got an error, I'd be curious as to your circumstances so that I can modify my code to account for it.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
I used a very unscientific method: duration. Performing the online index rebuild only took a short time on almost every index I tried... but it ended with "completed successfully". It made me think it didn't actually do anything.I can only compare it to when we'd rebuild large indexes in SQL 2000 and it would take hours... extending beyond the normal maintenance period... causing all sorts of havoc. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-17 : 18:05:48
|
| But was the fragmentation fixed after it completed? To check this, query sys.dm_db_index_physical_stats before and after the ALTER INDEX. Let us know what you find.Could you provide the article that you found that stated online reindexing wasn't available for compatiblity levels lower than 90? Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
mattbeckman
Starting Member
8 Posts |
Posted - 2008-06-17 : 18:29:50
|
quote:
Originally posted by tkizer
But was the fragmentation fixed after it completed? To check this, query sys.dm_db_index_physical_stats before and after the ALTER INDEX. Let us know what you find.Could you provide the article that you found that stated online reindexing wasn't available for compatiblity levels lower than 90? Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
I queried the index that I ran the rebuild on last night, and the fragmentation was down to 0.48%! Obviously, it looks like it must have worked, and you are awesome for responding so quickly! I'll try to find that article... but it was at about 2 AM last night so I may have misunderstood what it said about compatibility mode 80. Thanks!!- Matt |
|
|
|
|
Indexes & SQL 2000 -> SQL 2005 Upgrade