Evaluate performance of SQL Server installation

Question

We ported a database server from SQLServer 2005 to SQLServer 2008 (SP1). The new server has more Processors (4 Quadcore versus 1 Quadcore ) and more memory (4GB versus 64GB).

Processors are 2.1Ghz(new) versus 2.0Ghz(old). The new OS is Windows Server 2008 and the old is Windows Server 2003.

The databases were transfered via backup/restore and run in native SQL Server 2008 mode (not in SQLServer 2005 compatability mode ).

Some queries on the new server run slower than before. These queries use indexed views. The queryplan looks the same on both systems. Most of the queries perform equal.

My task is now to decide if we have a problem with our SQLServer installation, if the we have a problem with the database or if this is an expexted result.

I first want to compare performance of both

1. Sytems
2. SQLServer installations.

Is there an easy way to do this?

Has anybody had comparabele results on new SQLServer installations?

Answer 1

Before you check your hardware/OS, make sure you:

• update statistics
• rebuild all indexes

and then run your tests again. Also, are the editions of SQL Server identical? There are differences in how you have to write queries against indexed views based on the edition (Standard vs. Enterprise) of SQL Server.

Also, confirm that your indexed views are still indexed properly by selecting 1 row from them and observing the query plan. You should see only one table in the resulting plan.

Answer 2

Hello...

The easiest way to collect performance of both systems is to run a PAL, and collect the approriate data.

PAL has extra counter sets for SQL Server. It will collect and analyse the data, and let you know where you have an issue.

PAL can be found here http://www.codeplex.com/PAL

Also an important issu is to location of the filegroups. How is the underlying storrage system defined? It usually has a huge impact on the SQL Server. (You should talk about spindels here, and not raw size...) Make sure your database files are not sharing the resources with anyone else

Answer 3

Goos points by OrbMan there, to add to that I would suggestion you check the Compatability Mode to make sure you not running the database in 2005 mode.