Performance difference between SQL 2005 and 2008

Question

Performance difference between SQL 2005 and 2008

Igor Marchenko

Hall of Fame

Points: 3281
More actions
December 18, 2009 at 4:48 pm

#214909

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 questions 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

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

Hmmm... A number of factors here aren't equal, so its hard to say that it should perform the same. Which server has the faster CPU clock speed, front side bus? What is the difference in networking configuration. Turn on Client Side Statistics in SSMS (Shft+Alt+S or Query Menu->Include Client Statistics) and see the differences between the two.

For the plan change, you can make use of plan freezing to see if the same execution plan makes it perform correctly:

http://blogs.msdn.com/sqlblog/archive/2009/02/19/plan-guides-plan-freezing-in-sql-server-2005-2008.aspx

http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032370895&EventCategory=3&culture=en-US&CountryCode=US

Igor Marchenko Hall of Fame Points: 3281 More actions · Answer 2

Thank you, Jonathan! I will report additional information and results on Monday!

Have a great weekend,

Igor

Igor Marchenko Hall of Fame Points: 3281 More actions · Answer 3

Jonathan!

I collected Client Side statistics from 2005 and 2008 servers while executing query returning 500K rows from local and remote (on the same network) clients :

SQL 2008 (local client) - total execution time reported by SSMS was 19 sec:

Trial1 Average

Client Execution Time 15:45:25

Query Profile Statistics

Number of INSERT, DELETE and UPDATE statements 0 0.0000

Rows affected by INSERT, DELETE, or UPDATE statements 0 0.0000

Number of SELECT statements 1 1.0000

Rows returned by SELECT statements 584231 584231.0000

Number of transactions 0 0.0000

Network Statistics

Number of server roundtrips 1 1.0000

TDS packets sent from client 1 1.0000

TDS packets received from server 20766 20766.0000

Bytes sent from client 110 110.0000

Bytes received from server 8.505641E+0785056410.0000

Time Statistics

Client processing time 7322 7322.0000

Total execution time 7322 7322.0000

Wait time on server replies 0 0.0000

I am not sure why 'Client Execution Time' reported 15 sec while SSMS said 19 sec.

SQL 2008 (remote client - on the same network) - total execution time reported by SSMS 19 sec:

Trial1 Average

Client Execution Time 15:49:43

Query Profile Statistics

Number of INSERT, DELETE and UPDATE statements 0 0.0000

Rows affected by INSERT, DELETE, or UPDATE statements 0 0.0000

Number of SELECT statements 2 2.0000

Rows returned by SELECT statements 584231 584231.0000

Number of transactions 0 0.0000

Network Statistics

Number of server roundtrips 1 1.0000

TDS packets sent from client 1 1.0000

TDS packets received from server 20766 20766.0000

Bytes sent from client 110 110.0000

Bytes received from server 8.505678E+0785056780.0000

Time Statistics

Client processing time 4464 4464.0000

Total execution time 4465 4465.0000

Wait time on server replies 1 1.0000

The same question regarding difference between execution time reported by Client Statistics and SSMS.

SQL 2005 (local client) - SSMS reported 15 sec:

Trial1 Average

Client Execution Time 15:54:14

Query Profile Statistics

Number of INSERT, DELETE and UPDATE statements 0 0.0000

Rows affected by INSERT, DELETE, or UPDATE statements 0 0.0000

Number of SELECT statements 1 1.0000

Rows returned by SELECT statements 584370584370.0000

Number of transactions 0 0.0000

Network Statistics

Number of server roundtrips 1 1.0000

TDS packets sent from client 1 1.0000

TDS packets received from server 21180 21180.0000

Bytes sent from client 106 106.0000

Bytes received from server 8.674985E+0786749850.0000

Time Statistics

Client processing time 56565656.0000

Total execution time 56565656.0000

Wait time on server replies 0 0.0000

SQL 2005 (remote client - on the same network) - SSMS reported 18 sec:

Trial1 Average

Client Execution Time 15:58:09

Query Profile Statistics

Number of INSERT, DELETE and UPDATE statements 0 0.0000

Rows affected by INSERT, DELETE, or UPDATE statements 0 0.0000

Number of SELECT statements 2 2.0000

Rows returned by SELECT statements 584368 584368.0000

Number of transactions 0 0.0000

Network Statistics

Number of server roundtrips 1 1.0000

TDS packets sent from client 1 1.0000

TDS packets received from server 21180 21180.0000

Bytes sent from client 110 110.0000

Bytes received from server 8.674988E+0786749880.0000

Time Statistics

Client processing time 4219 4219.0000

Total execution time 4234 4234.0000

Wait time on server replies 15 15.0000

If I look at just at 'Client Execution Time' (if that's what I should be looking at), results are pretty much identical, but SSMS execution time is higher.

SQL 2008 has 2 Quad-Core AMD Opteron 2210 MHz, whereas SQL 2005 has 4 Quiad-Core Intel EM64T Family 6 Model 2394 MHz.

I will try to gather details about front bus speed.

What specific network configuration setting would be of our interest?

Thanks,

Igor

Igor Marchenko Hall of Fame Points: 3281 More actions · Answer 4

I actually tested database on another node (this is Active/Active). Second node has the same number of CPUs (slighly better characteristics on 2005 server) and RAM (32GB) by running SELECT * FROM <TableName> with 300K rows. Number of logical reads is almost identical (no physical, read-ahead reads). The same pattern - duration of the query on SQL 2008 is slower.

Thanks,

Igor

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 5

I don't know, looking at client statistics, which removes the SSMS overhead populating/display gridview data in .NET, the SQL 2008 appears to me to be faster. I think you are looking at very arguable statistics here and making an invalid conclusion. Perhaps someone else can chime in and validate that. I personally think you are chasing a non-problem. Have you tested the executions from another tool other than SSMS?

Igor Marchenko Hall of Fame Points: 3281 More actions · Answer 6

Hello!

SQL 2005 Cluster doens't have a shared LUN. That's why I was checking repetitive executions when data pages are loaded in memory and no disk IO involved (I assumed). Both SQL 2005 and 2008 systems had almost no other users at the time of the test.

Parallel execution is disabled because this is typical OLTP environment and using parallel plan can be counter productive (CPU saturation, etc.).

I realize that scanning table with 300K/500K rows is not something that you would typically do in OLTP environment, but I was simply comparing performance side by side.

Thanks,

Igor

Igor Marchenko Hall of Fame Points: 3281 More actions · Answer 7

Hello Jonathan,

Thanks for you reply. Yes, I tested typical sp calls and in some cases results were almost identical in others SQL 2008 was slower (but never faster) than 2005. I noticed that execution plan wasn't identical (although I only random checks). We will be doing stress/load test soon trying to simulate 'typical' users' activity.

I realize that having shared LUN will adversely affect IO performance, but unfortunately this was a budget saving decision.

Thanks again,

Igor

l.hesketh Newbie Points: 7 More actions · Answer 8

Igor

The "Client Execution Time 15:45:25" is the time you started the query (followed by 15:49:43, 15:54:14, 15:58:09).

The SMSS reported xx sec: is the actual execution time.

It therefore appears that using 2008 your execution times are slower than 2005, but local or remote makes no difference.

Appears to run quicker under 2005, the shortest being the local connection as you would expect.

Have you had a look at other traffic on the network at the times of execution?

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 9

1) different CPUs/motherboards in the two servers could have a significant affect on query performance

2) if your new box has win2008+, is power mode in the default balanced configuration? BAD if so. Change to high performance mode

3) the optimizer can make different plan choices based on available memory, which is clearly different between the two

4) have you certified that nothing else was going on each box at query run time?

5) did you check for paging? locked pages in memory can do bad things if max memory setting is too high

6) do a wait stats analysis while the queries are running. search web for track_waitstats_2005 and use the goodness there.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service