SQL Server Help - New Server

[Goksly]

So we've had a play with various settings:
* power management plan switched from balance to high - slight improvement but nothing drastic
* HT disabled - no difference
* Throttling disabled - no difference
* MAXDOP options - no difference
* CPU affinity - no difference
* Max memory usage (keep 8GB for "other" even though it's a dedicated server) - no difference

Spoken with the hardware guy and he has confirmed the HDDs are in exactly the same configuration / RAID setup (logs and data on different drives, RAID 5 for them etc) as the old server. Old and new server both HP Edge Servers as far as I'm aware. Not "premium" servers but not "value" either.

They will run some HDD checks later, but don't have access to 2016 or a spare representative server (would have to be another HV on our dev box), but even if it fixes it we wouldn't be in a position to fund it. But yeah can't help but think there is a setting... somewhere!

 

[glenimp617]

I'd put HT back on as SQL will make use of it. It's a very strange one indeed.

I know it's probably not something you want to do, but could you install SQL 2008 on that new server on another instance and test it? That would tell you for sure if it's an SQL issue.

Is this SQL server a VM? or straight baremetal

 

[skyripper]

Spoken with the hardware guy and he has confirmed the HDDs are in exactly the same configuration / RAID setup (logs and data on different drives, RAID 5 for them etc) as the old server. Old and new server both HP Edge Servers as far as I'm aware. Not "premium" servers but not "value" either.

It's probably too late now, but I've always been advised to use RAID1+0 and not RAID 5 for databases due to the vagaries of how databases use Temp space etc. when crunching big sorts, queries and aggregations.

 

[glenimp617]

Yes, not many use RAID5 these days. It's not best suited for databases especially.

 

[Goksly]

I'm probably not 100% on the raid make up, as there are a lot of drives and they're in various configurations (different for OS vs logs vs data vs backup). But whatever it is, it worked well on the previous server.

Server is stand alone server - only SQL on there and no VM / Hyper-V setup. Might be able to spin up an instance on the test server and run 2016. Whilst it won't be indicative of production vs production, I will be able to compare dev vs dev.

 

[glenimp617]

I'm probably not 100% on the raid make up, as there are a lot of drives and they're in various configurations (different for OS vs logs vs data vs backup). But whatever it is, it worked well on the previous server.

Server is stand alone server - only SQL on there and no VM / Hyper-V setup. Might be able to spin up an instance on the test server and run 2016. Whilst it won't be indicative of production vs production, I will be able to compare dev vs dev.

It's definately worth trying at least.

 

[Goksly]

Aye suppose it gives me next steps and can use the MSDN one for development purposes. Sigh!

Thanks for all the replys.

 

[OspreyO]

Have you got more aggressive power saving on the new server, on the disks.

 

[SoundsGood]

When comparing them are you running the query on the old server following a cold start? If not a similar query may have been made on it already causing some caches to be prewarmed

 

[Goksly]

Have you got more aggressive power saving on the new server, on the disks.

Tried this early on - it had a balanced mode, and when setting it to full power it did improve things by ~10%, so was good but old server still significantly quicker.

We had another query timings out - its created by an ORM and run as exec query... taking over 30 seconds to run. At the end of the query (inside the string) I put "--", and the query ran instantly, even when changing some of the parameters. Just bizarre!

Currently getting one of the support guys to mirror the test server and put 2016 on there so we'll see what that shows. If it costs £3k to do an inplace upgrade then so be it.

When comparing them are you running the query on the old server following a cold start? If not a similar query may have been made on it already causing some caches to be prewarmed

Yeah everything was done the same between testing new server and old server.

Thought I was fairly comfortable with SQL Server. This has rocked me!

 

[OspreyO]

Can you create another instance of the database on a completely different disk array using the new server.

 

[Goksly]

Not sure what you mean - do you mean add a load of new disks and build on that? If so no - the current setup has a number of enterprise SSD disks which cost a fair penny and the company I work for is not cash rich for a test like that. Also had the support guys run throughput tests etc and they've confirmed its better in every way compared to previous box. I just have to take their word for it...

 

[SoundsGood]

It could be NUMA related, I'd be interested to see the timings with 1 of the CPUs disabled.
Also what do your disk metrics look like, are you seeing access latencies above 1ms?

 

[Goksly]

I'll ask for more detail on Monday.

I locked down to 1 CPU / 10 cores and there wasn't any change. Tried the DOP to 4 and no difference.

 

[OspreyO]

Any progress?

 

[Goksly]

I think so yeah... So this last week we've readjusted the VMs on the development box and managed to create a new SQL server, which we duplicated the 2012 instance, and then did an inplace upgrade to 2017. First impressions were the same... and then I remembered compatibility version; doh! Changed that and it is now rapid every time. Less than a second, yet on the old instance (same CPU / memory config etc) it's 4 seconds.

So I need to do more testing... but its looking good. Only thing that is then "annoying" is that SQL Server 2019 is coming out in potentially 3 months (anywhere in H2 2019) which would suck. The company I work for doesn't have bags of money - what ever we buy we're "stuck" with for a while so ideally would be the latest and greatest. I don't suppose Microsoft do a "and if we bring out a new version within 6 months..." type deal?

 

[nightmare99]

I don't suppose Microsoft do a "and if we bring out a new version within 6 months..." type deal?

Check out software assurance.

 

[Goksly]

We used to have that but the guy who handles the licencing worked out it was cheaper to buy outright (we don't migrate very often)... Not sure I understand it 100%, but looks like its $1400 per year to "lease?" SQL server, with the view that we can always have access to the latest version, or $3400 to purchase but thats all we can have? So if you're happy to skip a version, buying outright doesn't seem too bad?

 

[OspreyO]

Do you have any support days from MS. It would be worth getting someone in to see whats happening.

Even set up a test server for beta releases and test for issue pre-migration. At some point the old servers go out of support. Then you might be snookered.

 

[Django x2]

Also, irrespective of what the "hardware guy" says, has he created the disk arrays with the same block size format as the old server? For DB boxes, block size is probably the most important part of the disk array.