Optimize SQL2000 code for SQl2005?

We are migrating a 200GB SQL2000 database (running on W2K Ent x32, 32G RAM) to SQL2005 (W2K3 Ent x64 80G RAM) on new hardware. The new server is a HP DL585 with four quad-core Opteron 8360 SE's. The backup of this database to a local SAN on the old server takes 1-2 hours, and on the new server it took about 20 minutes to local SAS drives. We chose not do go direct to SQL 2008 because the application vendor doesn't support it at this time.
The job below typically takes 3-5 hours on the old server. When running it on the new server (it is currently running for the first time and hasn't completed yet), I notice that there are long periods of time where it is pegging just one cpu core and the others are basically idle, and sometimes it pegs all the cores at 100% for a period of time. The code below came over from the SQL2000 server. Not having a dedicated DBA in the house, I thought perhaps the script might benefit from re-writing for SQL2005... what suggestions would you make to optimize this process?
There seems to be some confusion on whether we need to install R2 on the new server to maximize it's ability for symmetric processing? Right now it has not yet been installed.
Thanks!
---------- USE [database]
GO
/****** Object: StoredProcedure [dbo].[check_all_tables] Script Date: 01/14/2009 10:23:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[check_all_tables]
AS
DECLARE @tablename varchar(45)
DECLARE @id int
DECLARE @status tinyint
DECLARE @c1 char(1)
DECLARE @tablename_header varchar(75)
DECLARE tnames_cursor CURSOR FOR
SELECT name, id
FROM sysobjects
WHERE type = 'U'
DECLARE ident_cursor CURSOR FOR
SELECT 'X'
FROM syscolumns
WHERE id = @id
AND status = 128
OPEN tnames_cursor
FETCH NEXT FROM tnames_cursor INTO @tablename, @id

WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
PRINT " "
SELECT @tablename_header = "************** " +
RTRIM(UPPER(@tablename)) + " *************"
PRINT @tablename_header
PRINT " "
EXEC ("DBCC CHECKTABLE(" + @tablename + ")")

EXEC ("DBCC DBREINDEX(" + @tablename + ")")
EXEC ("UPDATE STATISTICS " + @tablename)
OPEN ident_cursor
FETCH NEXT FROM ident_cursor INTO @c1
CLOSE ident_cursor
IF (@@FETCH_STATUS = 0)
BEGIN
EXEC ("DBCC CHECKIDENT(" + @tablename + ")")
END
END
FETCH NEXT FROM tnames_cursor INTO @tablename, @id
END
PRINT " "
PRINT " "
SELECT @tablename_header = "************* NO MORE TABLES" + " *************"
PRINT @tablename_header
PRINT " "
PRINT "DBCC has been run against all user-defined tables."
PRINT "Statistics have been updated for all user-defined tables."
DEALLOCATE tnames_cursor
DEALLOCATE ident_cursor
-----

Sorry for the delay, I'm not getting e-mail notifications. Regarding the SQL version, The output of the select @@version is:
Microsoft SQL Server 2005 - 9.00.4035.00 (X64) Nov 24 2008 16:17:31 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
We're already using bulk logging recovery mode. Not being that good at t-sql, I just want to confirm that when you say to take out the part which updates statistics, you're saying I can remove this single line?
EXEC ("UPDATE STATISTICS " + @tablename)
Where can I get more information on online index rebuild and whether it's right for us? Could I see an example of how I could implement it?
As far as the code goes, although some commands are deprecated, there wouldn't be any performance benefit from using the updated commands, correct?

Something else I found odd, was that while this script was running, there were times where all 16 cores were topped out near 100%, and other long periods of time where only one processor was pegged and the others were basically idle. What causes that?

Any idea why th e above script is now failing with the following error message on the new server?
-----
Executed as user: NT AUTHORITYSYSTEM. ...0] (Message 0) DBCC results for 'XXXXX'.
[SQLSTATE 01000] (Message 2536) There are 0 rows in 0 pages for object "XXXXX".
[SQLSTATE 01000] (Message 2593) DBCC execution completed. If DBCC printed error messages, contact your system administrator.
[SQLSTATE 01000] (Message 2528) DBCC execution completed. If DBCC printed error messages, contact your system administrator.
[SQLSTATE 01000] (Message 2528) ************** YYYYY *************
[SQLSTATE 01000] (Message 0) DBCC results for 'YYYYY'.
[SQLSTATE 01000] (Message 2536) There are 2588 rows in 5 pages for object "YYYYY".
[SQLSTATE 01000] (Message 2593) DBCC execution completed. If DBCC printed error messages, contact your system administrator.
[SQLSTATE 01000] (Message 2528) DBCC execution completed. If DBCC printed error messages, contact your system administrator.
[SQLSTATE 01000] (Message 2528) ************** TMP_CHECK *************
[SQLSTATE 01000] (Me... The step failed.
-----

did you deattch and attache your 2000 datafiles , or create new database 2005 and importing the structure and table.?
to gain best performance from sql 2005 , it is advised to apply best practice procedures for building storage infra structure.
you can find many posts on the site related to these topics.

We backed up the 2000 database, and restored it to a new 2005 database. Our first real performance test will come this Thursday, and then a few specific dates between now and the 5th of next month. So far we've had almost 280 sessions open and Quest Performance Analyzer says the SQL load hasn't been more than 1%. Backups of the database went from 90 minutes (FC-attached HP MSA1000 on old server) to 12 minutes (internal SAS array). Right now the storage design isn't ideal but not causing a problem We'll be getting a Netapp FAS2050 in less than a month and I found these links to review for storage design:
http://www.microsoft.com/technet/prodtechnol/sql/2005/physdbstor.mspx
http://www.sqlservercentral.com/Forums/Topic607318-146-1.aspx - Page 2 seems usefulhttp://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1262122_mem1,00.html
http://blogs.technet.com/vipulshah/...abase-storage-design-and-disk-allocation.aspx
How to move system tables: http://msdn.microsoft.com/en-us/library/ms345408(SQL.90).aspx

start to apply post upgrade review for database
here a list of reviews:
1) run script

SELECT name, suser_sname(sid) owner,
convert(nvarchar(11) , crdate) ,dbid, cmptlevel
FROM master.dbo.sysdatabases

review comtlevel to be 90 (test your application for that change)
owner of database sa
--------------------------------------------------
2) As there is a separation between user and schema owner in sql 2005, be sure that the default schema is dbo for all login users
modify default schema either by ssms or by script
----------------------------------------------------------------------------------
3) Change the default isolation level from READ COMMITTED to READ_COMMITTED_SNAPSHOT using alter database
it is valuable enhancement and minimize locking
revie BOL for that Isolation level
-------------------------------------------
4) review orphand users , and create corresponding logins
------------
5) rebuild index
6) rebuild statistics
7) recompile all stored procedures to get optimized cached plan
8) ... list is not complete []

1) All db's are 90 except for our production database which requires to remain at 80 until further notice from the vendor
2) The only default schema settings I can find is inside the db>security>users container. Some of these users have a 'blank' schema, others have it set to their username. Your suggestion is to set the default schema on all database logins to 'dbo'? Even guest, etc?
3) We'll review the BOL.
4) None orphaned.
7) Not sure how to recompile all SP's, would need to get approval from the vendor though.
5-6) Microsoft suggested creating a new maintenance plan to replace the query in the original post because it always fails when it is automated since we've went into production.
The new plan also continues to fail and we don't yet understand why. The errors are:
-----
Date 1/25/2009 10:25:00 PM
Log Job History (Update Stats and Rebuild Indexes.Subplan_1)
Step ID 0
Server FL2000-YARDI
Job Name Update Stats and Rebuild Indexes.Subplan_1
Step Name (Job outcome)
Duration 01:38:15
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
The job failed. The Job was invoked by Schedule 11 (Nightly). The last step to run was step 1 (Subplan_1).
-----
Date 1/25/2009 10:25:01 PM
Log Job History (Update Stats and Rebuild Indexes.Subplan_1)
Step ID 1
Server FL2000-YARDI
Job Name Update Stats and Rebuild Indexes.Subplan_1
Step Name Subplan_1
Duration 01:38:14
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: ORLANDOyardisqlcluster. ...4035.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:25:01 PM Progress: 2009-01-25 22:25:04.35
Source: {C358C323-B8B7-480E-B21F-B6C6B5819F4A} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Progress: 2009-01-25 22:30:18.47
Source: Rebuild Index Executing query "USE [Voyager] ".: 0% complete End Progress Progress: 2009-01-25 22:30:18.51
Source: Rebuild Index Executing query "ALTER INDEX [I_ABSORB_1] ON [dbo].[ABSORB] REBUILD".: 0% complete End Progress Progress: 2009-01-25 22:30:18.51 Source: Rebuild Index Executing query "USE [Voyager] ".: 0% complete End Progress Progress: 2009-01-25 22:30:18.51
Source: Rebuild Index Executing query "ALTER INDEX [PK_ABSORB] ON [dbo].[ABSORB] REBUILD ".: 0% complete End Progress Progress: 2009-01-25 22:30:18.51 Source: Rebui... The package execution fa... The step failed.
-----
...and the generated script is...
USE [msdb]
GO
/****** Object: Job [Update Stats and Rebuild Indexes.Subplan_1] Script Date: 01/27/2009 12:48:52 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [Database Maintenance] Script Date: 01/27/2009 12:48:52 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Update Stats and Rebuild Indexes.Subplan_1',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'Database Maintenance',
@owner_login_name=N'DOMAINuser', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Subplan_1] Script Date: 01/27/2009 12:48:53 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Subplan_1',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'SSIS',
@command=N'/Server "$(ESCAPE_NONE(SRVR))" /SQL "Maintenance PlansUpdate Stats and Rebuild Indexes" /set "PackageSubplan_1.Disable;false"',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Nightly',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20090122,
@active_end_date=99991231,
@active_start_time=222500,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

The index on a specific table is defined incorrectly. We will be removing that index from the table on Friday because it is for a sub-product we do not use.