x

Newer Server (better hardware) is slower than older

Hi everyone, I know it's hard to identify this type of problem without a lot of specifics, but I'm going to ask this "general" question anyway, in hopes that someone can steer me in the right direction to start tracking this down.

We have an older development server, running SQL Server 2012 Express. The disks are all typical magnetic hard drives. We also have a newer server, running SQL Server 2008 R2 Standard, with sold state drives, significantly more powerful CPUs, and plenty of memory (100GB, of which only 28GB approx is being used by SQL Server thus far).

The newer server was noticeably slower than the older one, despite the better hardware, so we chose a query (which happened to have several joins) and started to compare them. (the data on both servers is identical - the older server data was just refreshed by restoring a backup from the newer server).

The newer server typically takes twice as long (based on the output of "set statistics time on") as the older server. When I compare the output from "set statistics io on" they are dead on identical. One just takes twice as long as the other.

There is no significant load on either server. There isn't a lot of disk i/o going on or anything else I can see that would cause the newer server to be slower.

The queries we're testing with do not appear to trigger any parallelism, and the server settings for memory, cpus, parallelism, etc seem to be the same on both servers.

At this point I have no explanation why the "better" machine would take twice as long to execute a query...

If anyone has any pointers at what I should do next to track this down, I'd appreciate hearing from you.

Thanks!

more ▼

asked Nov 10, 2016 at 04:06 PM in Default

avatar image

Bill
206 7 11 12

@Bill - how's it going? Did you manage to get this sorted?

Nov 14, 2016 at 10:33 AM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Check one more thing: power management. Both in Windows and in the BIOS.

more ▼

answered Nov 11, 2016 at 09:07 AM

avatar image

ThomasRushton ♦♦
41.9k 20 50 53

Great suggestion. I've asked them to look at this, and they did indeed find some less than optimal power options. They will check the BIOS tonight when they're able to reboot the server. Thanks!

Nov 11, 2016 at 03:17 PM Bill

Thanks again for the suggestions everyone. It turned out the power management settings were indeed misconfigured. They've been running that way for a while, and never realized the server could be significantly quicker... I'm still waiting on the final results (they were going to reboot things this past weekend) but the settings were definitely incorrect.

Nov 14, 2016 at 01:58 PM Bill

Did you manage to get benchmarks before & after? That could have been an interesting &useful blog post for someone else suffering the same problems...

Nov 14, 2016 at 02:06 PM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left

Check 2 things:

1st make sure that the drives are laid out the same as on the old server, including RAID levels. Make sure that the databases are placed on the drives the same way. Yes, even with SSDD I have had it make a difference.

2nd check the controller to make sure that it is configured the same. Some set theirs to 50/50 where 50% is read and 50% is write. For an OLTP database I prefer 25/Read and 75 write while OLAP is reversed.

more ▼

answered Nov 10, 2016 at 07:43 PM

avatar image

sjimmo
1.6k 1 3 5

Thanks. We did check this, and there may be a bit of an issue with the drives. The SSDs are mirrored, but via Windows, not a RAID controller. Not ideal... The dev server, however, is only a single disk, no array, so that likely causing much of a difference. I've asked them to run some benchmarks on the servers to compare overall disk access speeds, however.

Nov 11, 2016 at 03:16 PM Bill
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x776
x74
x9

asked: Nov 10, 2016 at 04:06 PM

Seen: 71 times

Last Updated: Nov 14, 2016 at 02:06 PM

Copyright 2017 Redgate Software. Privacy Policy