MS-SQL database performance stinks

[miyamota]

Our team is currently experiencing poor performance executing stored procedures in SQL Server 2000. Currently we have two databases on the same server (cluster server). The one is serving as the QA db, the other as the beta db and both are full restores of the production db. The problem we are experiencing is that the stored procs that ran quickly in the QA db are much, much, much slower on the beta db. Similar amounts of data are being processed, the table structures and indexes are exactly the same and spot-checking shows that the query plans are very similar. We’ve reindexed, dropped and recreated the indexes, created and updated statistics; run just about every dbcc commands there is and have seen no noticeable increase in performance. We’ve also run into problems with the data load taking an obscene amount of time to insert 80,000 records into an aggregate table. In the QA db, and in our development environment, it runs consistently in 3-4 minutes but in beta it takes anywhere from 8-11 hours. We’ve run traces, checked for blocking, missing statistics and anything out of the ordinary but came up nil. We’ve run perfmon to check memory usage, CPU load, disk queuing and I/O but again saw nothing out of the ordinary. ANSI nulls and Quoted Identifier settings are the same for the stored procs we’re troubleshooting.

Any advise or suggestions will be extremely welcome!

 

[Bobalooey]

You say both databases are running on the same server, but are they both running under the same SQL instance? If not, you may have network protocol variances. One that has plagued me would be that the fast instance was running under TCP/IP and the slow performer was running under Named Pipes. There is up to a 10 to 1 performance variance between the two. Your statement however about SQL Server performance "stinking" is, in my honest opinion, confined to your local installation of it. I would match MSSQL up against Oracle any time. Like anything else though, they both need fine tuning from time to time. Another good test though would be to make a backup of either the Northwind or Pubs sample databases, and restore them into a second "renamed" database. Run stored procedures in both instances and check the timings. I'd also check to make sure the two databases are within the same disk array. Please keep us posted of your progress as your scenario is far from common.