Performance difference between SQL Server 2005 and 2008

[Igor Marchenko]

Hello!

I realize this give questions might be too generic, but hopefully
somebody will point me into right direction.
We in the process if migrating our SQL Server 2005 database Active/Active
cluster to 2008. We have setup and configured SQL Server 2008 cluster and I
started testing/comparing performance between old and new clusters.
Database in question has been copied over to 2008, upgraded, statistic
updated and compatibility level changed to 100. I noticed that very same
stored procedure generated different plans in 2005 and 2008. It ran slower on
2008. Did anyone has similar experience when stored procedure ran slower
after upgrade to 2008?
I have also noticed that very same query that generates identical plan ran
slower on 2008:

select * from <tablename> --tested tables with 300K and 500K rows

SQL Server 2008 cluster has LUN shared by Data, Log, TempDB drives (not
exactly the best practice; has been done to cut costs). Also, SQL Server 2008
-64 bit (SP1 CU2) has 32GB of RAM and 8 CPU ,whereas SQL Server 2005 -64-bit
(SP2) has 16 CPU with 64GB. Parallel execution is disabled on both servers.
2008 server has 'lock pages in memory' configured

I compared performance of repetitive calls when data pages are in cache
and confirmed that number of physical reads and read ahead reads were 0
indicating that data pages were read from memory.
Number of logical reads was pretty much identical but overall duration on
2008 was somewhere between 25%-40% slower.
I assume that since data pages are in memory the only difference is RAM
characteristics. The fact that LUN(s) wasn't configured based on best
practices on 2008 server shouldn't be affecting performance when data pages
are in cache, correct?
Are there any hardware cache, HBA (other?) settings that worth looking at?

Any advice on this matter is appreciated.

Igor

[Andrew J. Kelly]

These are obviously two totally different machines so you can't really
compare durations directly like that. If the plans are identical and the #
of logical reads are the same then chances are the machine itself is not as
fast for these types of operations. I can see some plans in 2008 needing a
small tuning due to the differences in the optimizer but usually once you do
they run faster.

--

Andrew J. Kelly SQL MVP
Solid Quality Mentors

"Igor Marchenko" <IgorMa...@discussions.microsoft.com> wrote in message
news:96BBBFE5-D07A-499B...@microsoft.com...

[Igor Marchenko]

Thanks Andrew! I understand that configurations and versions are
different. I will try to tune stored procedures in question.

I am more concerned by the fact that the same queries using identical
execution plans are running slower (from 25 to40 % slower) on new server
(while all data pages already loaded in memory). Queries are simply scanning
entire table to return 300K and 500K rows respectively. I am thinking that
may be server hasn't been properly configured.
My understanding is that when data pages are loaded in memory, there is no
need to go to harddrive (confirmed by 0 physical and read-ahead numbers) and
the difference in memory size available and numbers of CPU shouldn't cause
significant difference in duration of query execution (parallel execution
disabled).

Thanks,
Igor

"Andrew J. Kelly" wrote:

> .
>

[Erland Sommarskog]

Igor Marchenko (IgorMa...@discussions.microsoft.com) writes:
> I am more concerned by the fact that the same queries using
> identical
> execution plans are running slower (from 25 to40 % slower) on new server
> (while all data pages already loaded in memory). Queries are simply
> scanning entire table to return 300K and 500K rows respectively. I am
> thinking that may be server hasn't been properly configured.

Looking back on your original post, it seems that your test query is

SELECT * FROM tbl

and it returns 300000 rows. You don't say whether you run this locally
or remotely, but in any case a big cost here is for the client to
receive and process the data. And if the client is remote, the network
will matter. Of course, if your application regularly return result sets
that big, this is an issue worth pursuing, but else I don't find this
a very interesting data point. A query like:

SELECT COUNT(DISTINCT nonindexedcol) FROM tbl

may be more relevant.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Igor Marchenko]

Hello Erland!

Database will not be returning that amount of data frequently. I agree
with you that it will be a problem otherwise. I was just trying to do some
performance comparison between old and new servers. I am concerned that
stored procedures run slower on new server. This could be due to query plan
difference, etc. In order to make comparison more accurate, I picked query
that produce identical plans on both SQL 2005 and SQL 2008.
I actually tested both local and remote client (SELECT * FROM
<TableName>) and observer consistent pattern: execution duration on new
server was slower (repetetive calls with 0 physical, read-aheads and very
close logical reads).
Since there was a hardware difference between 2005 and 2008 (2005 had
more memory and CPUs, although all database were located in memory), I tested
another database on second node (Active/Active). Second nodes on both 2005
and 2008 had the same amount of RAM (32GB) and number of CPUs (8). The result
pattern was the same: 2005 was returning data faster than 2008.
I am concerned because results are indicating that our new database
environment might have problems. I would like to feel more comfortable before
we migrate to new server. As a next step, we are planning to load users into
new environment and try to simulate realistic workload. But at this moment, I
am trying to investigate the cause of the slowness.
As I mentioned, our SAN setup (single LUN per server) is far from ideal,
but I thought that since data pages already in memory, it shouldn't be a
factor.
I will try to run query you suggested you report results lates.

Thank you,
Igor

"Erland Sommarskog" wrote:

> .
>

[Andrew J. Kelly]

Igor,

Yes if the data pages are all in memory then there will be no physical I/O
in this case. But that won't be true of a full scale production environment
and the physical I/O will have an effect none the less. It is hard to say
why this is slower but one factor can be that in 2008 it calculates
checksums on each page (once enabled and written once) where as 2000 only
had torn page detection. The engine was optimized for many things over 2000
but I am not sure scanning entire large tables was one of them since this is
not a normal OLTP operation. I would look for operations that will be more
frequent and typical of your operation than a full scan and returning so
many rows.

--

Andrew J. Kelly SQL MVP
Solid Quality Mentors

"Igor Marchenko" <IgorMa...@discussions.microsoft.com> wrote in message

news:0F1C2799-F83F-4C25...@microsoft.com...

[Igor Marchenko]

Hello Andrew,

Databases I looked at have 'Page verify' set to CHECKSUM on both SQL
2005 and SQL 2008. But I got your point regarding checking more typical
operations.
We will be performing load/stress testing simulating typical users'
activity.

Thanks again,
Igor

"Andrew J. Kelly" wrote:

> .
>

[Igor Marchenko]

Erland,

I tested following query on SQL 2005 and SQL 2008 (both with 8 CPUs, 32GB
RAM) on table with and repetitive calls came back close:

SELECT COUNT(DISTINCT city) FROM OFX_VenueErrorLog WITH (NOLOCK)
--non-indexed column; 17 mln row table
SQL 2005:

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.

(1 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead
reads 0.
Table 'OFX_VenueErrorLog'. Scan count 1, logical reads 226979, physical
reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.

SQL Server Execution Times:
CPU time = 9437 ms, elapsed time = 9448 ms.

SQL 2008:

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.

(1 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead
reads 0.
Table 'OFX_VenueErrorLog'. Scan count 1, logical reads 219016, physical
reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.

SQL Server Execution Times:
CPU time = 11185 ms, elapsed time = 11206 ms.

I am puzzled as to why despite the fact that both server have 8 CPUs (app.
the same speed, parallel execution disabled), 'CPU time' numbers are
consistently higher on SQL 2008.
Also, the difference in execution times is significantly higher during
very first execution when data pages are loaded in memory. . As I mentioned,
shared LUN is used on SQL 2008, but I thought that this shouldn't be a factor
since I am the only user on the server. Query is using the same execution
plan on both servers.
Is there anything I can do to further torubleshoot this issue. I would
like to get down the root of this problem.

Thanks,
Igor

"Erland Sommarskog" wrote:

> .
>

[Andrew J. Kelly]

You still have the checksum factor which I think can easily account for more
CPU usage and slightly more time to process. You are reading ~1/4 million
pages and the checksum will need to be applied to each. That can add up with
so many pages. But again I think you are wasting your time with this
process. Your real work load will be comprised of so many more types of
queries and hopefully many with much less pages to read. Some things in 2008
are slower and some are faster. Where you will gain and where you will loose
has many factors and only your real workload will tell where those are.
Besides speed should not be the main motivation for upgrading in the first
place. With added functionality and integrity usually comes with a slight
performance penalty in some areas.

--

Andrew J. Kelly SQL MVP
Solid Quality Mentors

"Igor Marchenko" <IgorMa...@discussions.microsoft.com> wrote in message

news:CF01864E-51D9-4078...@microsoft.com...