query plans drive off the cliff

Question

query plans drive off the cliff

tcronin 95651

SSCertifiable

Points: 6735
More actions
March 7, 2019 at 1:00 pm

#377220

query plans drive off the road. Have 3rd party app db users complained of slowness. This db and app running for over a year. I look at memory PLE over 20000, cpu was at 45-55 which is high for this box. Go to query store and I see some queries have plans with over 40 million logical reads, same query also has 496 logical reads. No changes to app or database, index and stats rebuilt twice weekly only about 20 users on this system. I have never seen query store show this kind of variance, kind of like taking a plane to cross the street. Any ideas? I did do a reboot which seems to have cured it but never seen a bunch of plans run off the road like this. Blitzcache and Pinal Dave sql showed nothing?

Viewing 3 posts - 1 through 3 (of 3 total)

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

andreas.kreuzberg SSCertifiable Points: 6206 More actions · Answer 1

Hi,
I think, if anybody should help, we need more informations
CPU?
DISK? SSD, SAN?
Memory
Size of the database
Rowcount of the biggest table
etc.

Kind regards,
Andreas

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 2

tcronin 95651 - Thursday, March 7, 2019 1:00 PM
query plans drive off the road. Have 3rd party app db users complained of slowness. This db and app running for over a year. I look at memory PLE over 20000, cpu was at 45-55 which is high for this box. Go to query store and I see some queries have plans with over 40 million logical reads, same query also has 496 logical reads. No changes to app or database, index and stats rebuilt twice weekly only about 20 users on this system. I have never seen query store show this kind of variance, kind of like taking a plane to cross the street. Any ideas? I did do a reboot which seems to have cured it but never seen a bunch of plans run off the road like this. Blitzcache and Pinal Dave sql showed nothing?

This sounds like a classic case of "bad parameter sniffing" (Google for that term on possible fixes for it). Also, have you ever done any statistics maintenance on this system?

--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)