SQL Server Help - New Server

[Goksly]

Ok very strange one this that is outside of my skill level unfortunately. We've got a fairly large database (35GB) with medium usage. This was on oldish hardware and SQL Server 2008. We got a new server, lots more ram / faster processors - great! HDD setup is the same (i.e. raid / configuration / file location). Backed up the database and restored on the new server (running in 2012 mode). Everything seemed fine - but all was not well. I'm getting very strange performance issues. Most queries are running slightly faster, which is great, but some queries on the first time are running a lot slower.

Example - we have a query that on initial run takes 7 seconds to complete. If I run it again it takes 250ms. If I change a parameter value it takes 7 seconds to run again. If I clear the query plan cache it takes 7 seconds to run again. If I run the same query on the old database, it takes 500ms on first run, 400ms on second run.

So something is defo up with how long it takes to compile the query. When I return the actual execution plan, its the same but the estimate rows / subtree costs are a lot higher on the new server. When I do properties and get the compile time its 7000 vs 350 on the old server (assuming thats ms).

If I amend the query and have options(recompile) it takes 3 seconds to run pretty much each and every time. So faster initial, but still too slow on recalls.

As part of the migration, rebuilt all indexes and updated statistics.

So long story short, new server is quicker but only after the query plan has been created. Ideas?

 

[Goksly]

Nope - the new server is hosted internally right next to the old server. I've been checking trace flags, server settings etc and they're all the same. Change the compat level and still the same. Switched on trace flags in our test environment and not seeing any improvement. When I look at the profiler on the server, the statement completes extremely quickly, but the batch is taking all of the time - but it's the only statement being run :/

 

[Goksly]

Yeah we have gone from 2008 to 2012. Here is some snippets from the profiler:
https://imgur.com/hq5t73t

 

[Goksly]

Production server is in use so plenty going on, where as the old server is read only / locked down now.

Can't switch to 2016 as we have purchased the 2012 licence outright and don't have the budget unfortunately.

Everything is patched / up to date. I've tried removing the auto created stats and turning on the optimise fixes trace flag (4199) but no difference.

 

[Goksly]

Could try the database tuning advisor, often suggests indexes that will help, however not always wise to blanket apply everything it suggests.

But my only issue with that is why does the profile show the query is running pretty fast but the "batch" has such a massive delay? So its not like the query is running slowly...

Random one but in server properties under processors have you got "boot SQL Server priority" checked? I noticed that not having this checked in 2012 really did make the server under perform.

It's off but having had a quick read everyone seems to say its an extremely bad idea to turn it on, and documentation states it will be removed in a future version.

Parallelism? https://www.mssqltips.com/sqlservertip/5404/parallelism-in-sql-server-execution-plan/

Same on both servers.

 

[Goksly]

No its all pretty standard. It's hitting some big tables, but the query variables ultimately bring back one row - so I'm happy with the query (less than half a second on the old server). So annoying!

 

[Goksly]

Old server had 24GB (currently using 9GB) with a X5550 CPU @ 2.67ghz (8 cores)

New server has 64GB (currently using 60GB) with a E5-2630 v4 @ 2.20ghz (2 processors, 20 cores, 40 logical)

 

[Goksly]

Yeah I changed the compatibility mode and no change unfortunately.
The hardware guys are running IO tests this evening... But as the statement completes quickly in the profiler and the batch takes long makes me doubt that. I'd rather the query just runs slow... But with stats time on etc the figures are good.

 

[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!

 

[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.

 

[Goksly]

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

Thanks for all the replys.

 

[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!

 

[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...

 

[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.

 

[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?

 

[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?