3rd party vendor performance issue

Question

3rd party vendor performance issue

tcroninlifepoint

SSC Veteran

Points: 235
More actions
October 10, 2019 at 8:29 pm

#3687857

Everyone has seen this, new DBA at new company has 3rd party EMR product that we have AG setup for read only node. Week one on board users screaming. performance for RO node is awful, 2017 Std Edition 12 procs 82 gig of memory. Ran blitzcache and wow 3 sql statements that require over 250 million logical reads (yes that number is correct). CPU gets buried but PLE is not bad but with these 3 statements which have all in common. And yes with the 3rd party not easy to change, other than jumping out window anyone have any great ideas?
Compilation Timeout, Forced Parameterization, Parallel, Parameter Sniffing, Long Running Query, Plan created last 4hrs, Row estimate mismatch, >500mb spills
October 14, 2019 at 11:14 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

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

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 1

tcroninlifepoint wrote:

Everyone has seen this, new DBA at new company has 3rd party EMR product that we have AG setup for read only node. Week one on board users screaming. performance for RO node is awful, 2017 Std Edition 12 procs 82 gig of memory. Ran blitzcache and wow 3 sql statements that require over 250 million logical reads (yes that number is correct). CPU gets buried but PLE is not bad but with these 3 statements which have all in common. And yes with the 3rd party not easy to change, other than jumping out window anyone have any great ideas?
Compilation Timeout, Forced Parameterization, Parallel, Parameter Sniffing, Long Running Query, Plan created last 4hrs, Row estimate mismatch, >500mb spills

Capture the code and check the execution plan or post here.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

tcroninlifepoint SSC Veteran Points: 235 More actions · Answer 2

Looks multiple problems, query is bad but ran on test system I/o issues in report server. ran blitzio and got some scary results with stall time over 40ms

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 3

Sounds like a stupid question but what condition are the index and column statistics in?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

tcroninlifepoint SSC Veteran Points: 235 More actions · Answer 4

stats and indexes good, looks like this may be a storage issue

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 5

We went through that same issue because they went cheap on the RO node storage. They also forgot to do things like change the settings for parallelism, etc.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)