slow performing query

Question

slow performing query

Viewing 5 posts - 31 through 34 (of 34 total)

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

vsamantha35 SSChampion Points: 11551 More actions · Answer 1

Jonathan AC Roberts wrote:

I don't understand why you would have indexes (apart from maybe a clustered one) on a table with only a few rows?

I wasn't sure about it. They were there from past 5-6 years even before I started supporting this app from past 2 years. So, no idea. However, it is a good point. May be we can disable those and see how things work out.

Again, thanks everyone for the support without judging. Thanks.

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

vsamantha35 wrote:

RCSI is enabled for this database. However, writers are blocking writers. INSERTs and DELETE's,

Sometimes, even a good healthy dose of magic pixie dust can't break through a mountain of poop. 😀 Why on Earth would the 3rd part try to manage locks on tables if RCSI is present? It makes no sense.

There's not much we can do to help because it's "turd party" stuff 😀 They are the ones that need to fix this and, unfortunately, it means that you have to prove to them that this problem is all them.

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

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

p.s. SQLProfiler isn't "dangerous". People that don't know what they're doing with either SQLProfile or Extended Events are what's dangerous. 😀

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

vsamantha35 wrote:

Jonathan AC Roberts wrote:

Again, thanks everyone for the support without judging. Thanks.

Heh... ah... but we ARE judging. 😀 The third party code and database design sucks. 😀 😀 😀

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

Ken McKelvey SSCoach Points: 18843 More actions · Answer 5

vsamantha35 wrote:

Can you give some idea(s) to prove that this is kind of a "Hot Spot" contention scenario.

A bit of a shot in the dark but do you have a lot of PAGELATCH_EX waits?

The following has some scripts and potential mitigations:

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/resolve-pagelatch-ex-contention

If PAGELATCH_EX is a problem, and given the ghost cleanup task also seems to cause blocking, it might be an idea to make dbo.Applied_lock an in memory table. If there is no need to retain the rows of this table between server restarts it could have a durability of SCHEMA_ONLY which would be even better.

Something like this might provide the developers with a quick short term fix to their poor design pattern giving more time for the code to be refactored.

This reply was modified 1 year, 9 months ago by Ken McKelvey.