SQL Server Performance

differences between 2000 and 2005?

Discussion in 'SQL Server 2005 General DBA Questions' started by tkisling, Jul 30, 2007.

  1. tkisling New Member

    we are experiencing poor performance on a 2005 database. to date much of what we have worked with has been 2000. i am suspicious that some of the parameters we set when we run the DDL to create the database may be a problem. can anyone comment on any of the paraemeter below and whether they might cause an issue in 2005? Many thanks.
    EXEC sp_dboption N'ABC', N'autoclose', N'false'
    GO
    EXEC sp_dboption N'ABC', N'bulkcopy', N'false'
    GO
    EXEC sp_dboption N'ABC', N'trunc. log', N'false'
    GO
    EXEC sp_dboption N'ABC', N'torn page detection', N'true'
    GO
    EXEC sp_dboption N'ABC', N'read only', N'false'
    GO
    EXEC sp_dboption N'ABC', N'dbo use', N'false'
    GO
    EXEC sp_dboption N'ABC', N'single', N'false'
    GO
    EXEC sp_dboption N'ABC', N'autoshrink', N'false'
    GO
    EXEC sp_dboption N'ABC', N'ANSI null default', N'false'
    GO
    EXEC sp_dboption N'ABC', N'recursive triggers', N'false'
    GO
    EXEC sp_dboption N'ABC', N'ANSI nulls', N'false'
    GO
    EXEC sp_dboption N'ABC', N'concat null yields null', N'false'
    GO
    EXEC sp_dboption N'ABC', N'cursor close on commit', N'false'
    GO
    EXEC sp_dboption N'ABC', N'default to local cursor', N'false'
    GO
    EXEC sp_dboption N'ABC', N'quoted identifier', N'false'
    GO
    EXEC sp_dboption N'ABC', N'ANSI warnings', N'false'
    GO
    EXEC sp_dboption N'ABC', N'auto create statistics', N'true'
    GO
    EXEC sp_dboption N'ABC', N'auto update statistics', N'true'
    GO

    if( ( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) ) or ( (@@microsoftversion / power(2, 24) = 7) and (@@microsoftversion & 0xffff >= 1082) ) )
    EXEC sp_dboption N'ABC', N'db chaining', N'false'
    GO
    USE ABC
    GO
    EXEC sp_configure 'show advanced options', 1
    GO
    reconfigure with override
    EXEC sp_configure 'max degree of parallelism', 1
    GO
  2. satya Moderator

    Seems pretty much default configuration for the database options, but can you explain a bit more on whereyou are lacking the performance in this case.
    Without further information such as service pack level and where exactly it is failing to keepup will have issues to identify.
    Have you upgraded this database from SQL 2000 recently?
  3. tkisling New Member

    Thanks for your response. This is a 2005 database on SP2. We actually migrated to 2005 from Oracle recently. We are seeing some timeouts on connections. Still trying to determine what exactly is going on.
  4. satya Moderator

    As explained have you have performed the index rebuild and update statistics of the tables that are migrated from Oracle and also look under event viewer & SQL error log for more information on any warnings.
  5. dineshasanka Moderator

    Do an index rebuild

Share This Page