getting killed on new server

Question

getting killed on new server

Viewing 12 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply

tcronin 95651 SSCertifiable Points: 6735 More actions · Answer 1

one of the problems before was the PLE as almost zero, new server has 512 gig of RAM. Old server CPU was never really above 45%. This is a new HP monster. It looks like with NUMA there may be a need for the 834 flag to help on the CPU side. Little pull with the vendor. They have changed their mind on whether or not Maxdop should be 0 or 8. Setting to zero did not help. Will watch to see if setting back to 2008 helps

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

tcronin 95651 (4/19/2016)
one of the problems before was the PLE as almost zero, new server has 512 gig of RAM. Old server CPU was never really above 45%. This is a new HP monster.

More information that supports my statements about being IO bound on the old box and NOT being IO bound on the new box causing a) CPU usage to go up and b) queries to bang up against each other much more frequently

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 3

I seriously doubt compatibility mode will help -- from what I know, that controls not the way the queries are processed but what T-SQL statements are allowed.

If it is that severe and you can't make easy headway with it, see if you can set up a trial version of a third party tool (I am thinking Solarwinds DPA, formerly known as Confio Ignite) which can easily and quickly look at how the workload as a whole is being distributed. You want to know whether all queries are now taking longer or if there are one or two obvious offenders.

tcronin 95651 SSCertifiable Points: 6735 More actions · Answer 4

already have that use IDera DM and looking at Ozar Blitzcache. These are the same offensive queries that were killing us before. Only difference is we now have more horsepower and sql 2012. The deadlocks are just coming from one ugly sp which unfortunately gets run 1000 times a day

MadAdmin SSChampion Points: 11261 More actions · Answer 5

check missing indexes.

You could be caching whole tables and doing table scans in memory, which could be why cpu is high.

Before you maybe couldn't cache the whole table so a scan took longer and cpu had to wait and therefore was lower.

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 6

If it is just one SP which was a problem before, then I have to concur with Kevin that the increase in horsepower is making blocks turn to deadlocks. Sometimes hardware can't solve bad code. If the vendor won't fix the code or take responsibility for the problem, there may not be much you can do unless you want to rewrite their code and risk what little support they offer you.

tcronin 95651 SSCertifiable Points: 6735 More actions · Answer 7

going to run blitzindex and look at sql that deadlock keeps causing.

MadAdmin SSChampion Points: 11261 More actions · Answer 8

Are you checking deadlocks with extended events?

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

tcronin 95651 SSCertifiable Points: 6735 More actions · Answer 9

going to try that also, the blocking we got on old box and 2008r2 was once a day, now getting this deadlock almost every time. the statement it is deadlocking on is doing and index seek, no table scans

MadAdmin SSChampion Points: 11261 More actions · Answer 10

Try this

CREATE EVENT SESSION [Deadlock Graphs] ON SERVER

ADD EVENT sqlserver.xml_deadlock_report(

ACTION(sqlserver.database_id,sqlserver.database_name))

ADD TARGET package0.ring_buffer(SET max_events_limit=(50),max_memory=(51200))

WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)

GO

That is my deadlock session.

Then you can double click on the ring_buffer and view the deadlock information.

Just including it if you don't have one already set up.

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

tcronin 95651 SSCertifiable Points: 6735 More actions · Answer 11

tcronin 95651

SSCertifiable

Points: 6735

April 19, 2016 at 7:18 am

#1873058

Idera is safe enough to let me save as XDL

tcronin 95651 SSCertifiable Points: 6735 More actions · Answer 12

going through blitindex also, results from same db on 2008 and 2012 are pretty significant.