SQL Server Performance

Optimize SQL2000 code for SQl2005?

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by jimm, Jan 14, 2009.

  1. jimm New Member

    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
    -----
  2. FrankKalis Moderator

    Since you are on SQL Server 2005 EE, you would definitely want to have a look at online index rebuild, if you have the disk space. Apart from that I would say, that you don't need to update the statistics when you rebuild your indices. The rebuild should internally trigger the stats update anyway.
  3. FrankKalis Moderator

    This thread might also be helpful: http://sql-server-performance.com/Community/forums/p/29150/154490.aspx
  4. satya Moderator

    Online INDEX is only available in Enterprise edition of SQL, so you need to check whether your current edition is EE or not.
    As the code you referred still works perfectly in SQL 2005 without any issues, as few of the statements referred are deprecated but still works.
  5. FrankKalis Moderator

    Good catch, Satya. I was somehow thinking that jimm was on SQL Server Enterprise Edition, but after rereading the thread he doesn't really mention that.
  6. jimm New Member

    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?
  7. FrankKalis Moderator

    Basically yes, but if you are unsure I would rather call the vendor's application support to see if they can help you or if there is a recommended way for this application.
    To be honest, if you don't know exactly what you are doing or feel uncertain in any way, I wouldn't just do it because someone in an online community told you so. [:)]
    A good start to read about indices is here: http://msdn.microsoft.com/en-us/library/ms189858.aspx
  8. jimm New Member

    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?
  9. satya Moderator

  10. FrankKalis Moderator

    [quote user="jimm"]
    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?
    [/quote]
    Not really odd. We had timeouts during the online rebuild as well although we had only one user at that time. We had to set the MAXDOP = 1 option to resolve this problem.
  11. jimm New Member

    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.
    -----
  12. moh_hassan20 New Member

    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.
  13. jimm New Member

    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
  14. satya Moderator

    Over a period of time that will cause the issue even for simple transaction log backup, as you say not a great deal of database at the moment it is better to plan and optimize the WRITE/READ performance from the hardware now.
  15. moh_hassan20 New Member

    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 [;)]
  16. jimm New Member

    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:
  17. jimm New Member

    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.

Share This Page