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