SQL Server Help - New Server

Goksly · 22 Jan 2019 at 23:43

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?

glenimp617 · 23 Jan 2019 at 09:10

daft question, but you have set the memory allocation within sql studio haven't you? although I think the default should be ok.
Obviously queries after the first run are cached in ram and compiled, hence the performance boost....but I doubt ram is the issue here. Very strange.

Actually, your performance issues are on first run aren't they...Mmmmmm

are the queries pulling data from remote systems or is all the data local? any external connectors?

Goksly · 23 Jan 2019 at 10:36

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 :/

glenimp617 · 23 Jan 2019 at 11:48

If the only difference you can see is a change of server, I'd check that all the firmware and bios are up to date. Run a disk io tool on both servers to check disk speeds.
I assume the disk layouts and filesystems are the same?

Also, have you gone from an old sql version to a newer one? (ie say from 2008 to 2017).

It does sound like a sql setting though, like the new server is doing an extra check, or piece of work whilst running the initial query.

Goksly · 23 Jan 2019 at 12:03

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

glenimp617 · 23 Jan 2019 at 12:35

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

That looks like everything on the old server is performing better?

is this in production? could you test it on sql 2016. The jump up from 2012 to 2016 is staggering.

also, is everything patched?

Goksly · 23 Jan 2019 at 14:10

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.

chroniclard · 23 Jan 2019 at 14:15

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

Randomface74 · 23 Jan 2019 at 14:23

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

Vince · 23 Jan 2019 at 14:27

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.

Goksly · 23 Jan 2019 at 15:06

chroniclard said:
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...

Vince said:
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.

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

Same on both servers.

Vince · 23 Jan 2019 at 15:24

Goksly said:
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...

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.

Same on both servers.

Ahh I wonder why that is. Perhaps I have to have a read and find out why it is such a bad idea, what I do know is that if we don't have it on certain jobs really suffer. Out of interest are you running any linked servers or statements that go across servers?

Goksly · 23 Jan 2019 at 15:30

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!

Randomface74 · 23 Jan 2019 at 15:39

how much ram is in the server?
what is the configured minimum and maximum memory allocation? https://docs.microsoft.com/en-us/sq...er-configuration-options?view=sql-server-2017
how much memory is sql currently using (task manager)?

Goksly · 23 Jan 2019 at 16:47

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)

Randomface74 · 23 Jan 2019 at 16:54

OK so I think the first time you run the query it's fetching data from disk and loading it into memory.
The second time you run, the data is already in memory so it's faster.
When you clear the cache you remove the data from memory so it's slow again.

So I think you need to look at disk performance, read specifically.
Probably some generic benchmarks around for that.

AHarvey · 23 Jan 2019 at 16:55

good thread to read through

https://dba.stackexchange.com/questions/35579/sql-server-2012-slower-than-2008

Look up Maxdop/Numa, multi cpu systems can have odd issues at times.

Aka-Matt · 23 Jan 2019 at 19:10

There is some sound advice above, but one thing I've not yet seen suggested is for you to set the database to 2008 compatibility mode. There are changes to various parts of the storage and relational engines between 2008 and later (more so 2016+) SQL Server releases which can affect the cardinality estimates and the resulting query plans for the worse.

Goksly · 23 Jan 2019 at 20:50

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.

glenimp617 · 24 Jan 2019 at 08:35

CPU Boost should definately be kept off. I originally thought disk performance, but I would be doubtful if that was the root cause.
Did you run any disk benchmarks as I suggested? Like I say, doubtful but who knows if it's an iffy raid driver causing slowdowns. We are talking seconds though, so unlikely.

Could you, as a side test build a second server running sql 2016 and take a copy of the database and run the same tests just to see what the performance is like? Do you have a spare server?

I'm not a fan of 2012. Had past woes with it. Running 2016 now and they are much better, but our queries run mega amounts of data so we can see small improvements make a big difference here.

also check BIOS settings, and even stuff like power management in windows. Everything needs setting to high/max performance.

Still thinking it's something within SQL itself rather than the server. What's the make/model?