Sql – How to you get database specific performance metrics for things like CPU/Memory/etc. in SQL Server 2005

I think i read somewhere, that the SQL Server 2005 database engine should be about 30% faster than the SQL Server 2000 engine. It might be, that you have to run your database in compatibility mode 90 to get these benefits.

But i stumbled on two scenarios, where performance can drop dramatically when using mssql 2005 compared to mssql 2000:

1. Parameter Sniffing: When using a stored procedure, sql server will calculate exactly one execution plan at the time, you first call the procedure. The execution plan depends on the parameter values given for that call. In our case, procedures which normally took about 10 seconds are running for hours under mssql 2005. Take a look here and here.

2. When using distributed queries, mssql 2005 behaves different concerning assumptions about the sort order on the remote server. Default behavior is, that the server copies the whole remote tables involved in a query to the local tempdb and then execute the joins locally. Workaround is to use OPENQUERY, where you can control exactly which resultset is transferred from the remote server.

The reason that the approach that Adam suggested won't work is that during the time that you are looping over the active connections new one can be established, and you'll miss those. You could instead use the following approach which does not have this drawback:

-- set your current connection to use master otherwise you might get an error

use master
ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE 

--do you stuff here 

ALTER DATABASE YourDatabase SET MULTI_USER