April 18, 2016 at 11:32 am
had old 3rd party oltp system on older HP server with 64 gig of memory. This weekend went to brand new super HP server (512 gig of memory) and SQL 2012. My CPU was 30-40% on old server memory maxed. On new box today with same oltp app and same users box getting pegged at 80% cpu. I even have local SSD for tempdb, anyone run into this before with upgrade to 2012 or newer server (numa)
April 18, 2016 at 12:27 pm
tcronin 95651 (4/18/2016)
had old 3rd party oltp system on older HP server with 64 gig of memory. This weekend went to brand new super HP server (512 gig of memory) and SQL 2012. My CPU was 30-40% on old server memory maxed. On new box today with same oltp app and same users box getting pegged at 80% cpu. I even have local SSD for tempdb, anyone run into this before with upgrade to 2012 or newer server (numa)
1) If you are coming from a very old SQL Server edition you should update all statistics with a FULL SCAN before turning the system loose.
2) I have indeed seen systems with SLOW IO start CRUSHING the CPUs once spiffy SSD storage (or lots more RAM either one - sounds like you did both) was dropped in place. The reason is that before the system simply wasn't getting data into the CPUs fast enough to make them work hard.
3) Be happy you weren't hit with a blizzard of deadlocks. I have seen that too in such scenarios, once so bad they had no recourse but to revert to the older hardware. And of course they had no plan to do so because better hardware can't POSSIBLY be bad, RIGHT?!? 😀 That was a VERY bad morning for them, and WOW did I chew the client out (after I got them back online) for not letting me know they were planning this evolution!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 18, 2016 at 12:33 pm
updated stats and indexes, I am consultant on this one, never seen this happen before. May consider setting back to 2008 compatible for database. They were actually getting killed on memory before the new servers with PLE at 100
April 18, 2016 at 12:44 pm
First, confirm that SQL Server is recognizing all the memory and CPU cores you'd expect. Also, from what you see, is it a systemic high CPU utilization across the board, or can you narrow it down to a specific misbehaving stored procedure?
http://seniordba.wordpress.com/2015/07/26/top-10-cpu-consuming-queries-in-sql-server/
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 18, 2016 at 12:58 pm
Quick question, is the SQL server a VM or bare metal?
😎
April 18, 2016 at 1:28 pm
brand new HP physical
April 18, 2016 at 1:29 pm
3rd party same bad code offenders as before none of that has changed other than new box and sql 2012. I have heard CPU issues with numa
April 18, 2016 at 1:44 pm
Let's take a step back for sanity's sake 🙂
Is there actually a performance issue (e.g., queries taking longer on the new server than on the old server), or is the CPU utilization just higher?
As Kevin pointed out, with such a big increase in RAM, it could just be that queries that were previously spending time reading from disk are now reading that same data in memory, leading to increased CPU utilization.
80% CPU utilization is not necessarily a problem. It does mean that you may not have much overhead for increased workload, but if processes are not having to wait for time on a core to free up then that level of CPU utilization may not be causing any problems.
That would be goal #1, then. Figure out if the workload's performance is actually suffering compared to the old server.
Having said all that, there is one relevant-seeming detail I haven't seen provided yet.
How many cores on the old server, and how many on the new?
If wait stats were being collected at some interval on the old server, I'd also definitely want to compare those to stats from an equivalent interval on the new server.
Cheers!
April 18, 2016 at 1:44 pm
Is it possibly procedure recompilation that causing high CPU ultilization?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 18, 2016 at 1:51 pm
Will start checking for recompiles, this is a fairly old code set from vendor way to hard to describe. ALso seeing a lot of deadlocks which did not happen before. CPU actually hit 100% for about 30 seconds.
April 18, 2016 at 3:13 pm
is maxdop set to 0? this may cause some quite misbehaving queries to show up now. this should be set neither too high nor too low if your machine has a fair enough number of cores.
And what was the setting and number of cores on the old server?
And did you check if it it only particular queries causing the issue or is it spread all over the queries?
On another note make sure that the new server does not have power saving turned on. that can also cause havoc to performance.
April 18, 2016 at 7:26 pm
tcronin 95651 (4/18/2016)
Will start checking for recompiles, this is a fairly old code set from vendor way to hard to describe. ALso seeing a lot of deadlocks which did not happen before. CPU actually hit 100% for about 30 seconds.
I called that!! 😀
If you need a performance tuning guru to help out I just so happen to know a really good one. 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 19, 2016 at 6:23 am
maxdop at 8, have on particular query that is deadlocking all the time (it is some ugly code, 3rd party can't change). It would cause blocking on old server and 2008r2 now just deadlocking and killing victims. Set compatibility back to 2008 for that database. Will also try 834 flag.
April 19, 2016 at 6:31 am
Other thing to bear in mind is that sometimes the third party software just is the problem in itself. You have commented more than once that there is some nasty code there that they cannot change. If they have not updated what they do, older hardware and versions of SQL could actually hide some of that (i.e. you would have not seen the deadlocks because it was slower). How much access to the vendor for help do you have? If the application itself is causing deadlocks, you won't be able to fix that without help from the vendor more than likely.
April 19, 2016 at 6:31 am
Like I said, you may not be able to do anything about this if the root cause is that your old box had poor IO and the new one is SSD. On the old box your queries were waiting for IO so much they rarely ran into each other. Now on SSD they don't wait for IO nearly as much and that greatly increases the chance of bumping into each other on the lock requests.
Having said that, suboptimal indexing is often a root cause of blocking/deadlocking. That would be the first place I look. Too many indexes (especially rampant use of Database Tuning Advisor) can also be a cause.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply