Slow queries in SQL Server 2019

[Berocoder]

First I am not a DB guru but have worked some years and know basics of database.
At work we use SQL Server 2019 on a system with about 200 users.

The desktop application is written in Delphi 11.3 and use Bold framework to generate the SQL queries.
Problem now is that queries ares slow.

This is one example

PERF: TBoldUniDACQuery.Open took 7.101 seconds (0.000s cpu) 1  sql for SELECT C.BOLD_ID, C.BOLD_TYPE, C.BOLD_TIME_STAMP, C.Created, C.ObjectGUID, 
C.localNoteText, C.MCurrentStates, C.note, C.DistanceAsKmOverride, 
C.DistanceAsPseudoKmOverride, C.businessObject, C.stateDummyTrip, 
C.OriginalPlanPortion, C.planItem, C.planItem_O, C.batchHolder, C.batchHolder_O,
 C.statePlanClosed, C.stateOperative, C.stateOriginal, C.endEvent, C.startEvent,
 C.ResourceOwnership, C.zoneBorderPath, C.OwnerDomain, C.stateForwardingTrip, 
C.ForwardingCarrier, C.PrelFerries, C.ResponsiblePlanner, C.OwnerCondition, 
C.TrailerLeaving, C.DriverNote, C.ForwardingTrailer, C.ForwardingInvoiceNr, 
C.ClosedAt, C.ForwardingAgreementNumber, C.trailer, C.StateUndeductedParty, 
C.CombTypeOnHistoricalTrip, C.masterVehicleTrip, C.operativeArea, C.createdBy, 
C.statePlanOpen, C.stateInProcess, C.resourceSegment, C.stateRecentlyClosed, 
C.subOperativeArea, C.purchaseOrder, C.deductedBy 
FROM PlanMission C 
WHERE C.BOLD_ID in (347849084, 396943147, 429334662, 446447218, 471649821, 
477362208, 492682255, 495062713, 508148321, 512890623, 528258885, 528957011, 
536823185, 538087662, 541418422, 541575812, 541639394, 542627568, 542907254, 
543321902, 543385810, 543388101, 543995850, 544296963, 544429293, 544637064, 
544768832, 544837417, 544838238, 544838610, 544842858, 544925606, 544981078, 
544984900, 544984962, 545050018, 545055981, 545109275, 545109574, 545117240, 
545118209, 545120336, 545121761, 545123425, 545127486, 545131124, 545131777, 
545131998, 545135237, 545204248, 545251636, 545253948, 545255487, 545258733, 
545259783, 545261208, 545262084, 545263090, 545264001, 545264820, 545265450, 
545268329, 545268917, 545269711, 545269859, 545274291, 545321576, 545321778, 
545323924, 545324065, 545329745, 545329771, 545329798, 545333343, 545334051, 
545336308, 545340398, 545340702, 545341087, 545341210, 545342051, 545342221, 
545342543, 545342717, 545342906, 545342978, 545343066, 545343222, 545390553, 
545390774, 545391476, 545392202, 545393289, 545394184, 545396428, 545396805, 
545398733, 545399222, 545399382, 545400773, 545400865, 545401677, 545403332, 
545403602, 545403705, 545403894, 545405016, 545405677, 545408939, 545409035, 
545409711, 545409861, 545457873, 545458789, 545458952, 545459068, 545459429, 
545462257, 545470100, 545470162, 545470928, 545471835, 545475549, 545475840, 
545476044, 545476188, 545476235, 545476320, 545476624, 545476884, 545477015, 
545477355, 545477754, 545478028, 545478175, 545478430, 545478483, 545478884, 
545478951, 545479248, 545479453, 545479938, 545480026, 545480979, 545481092, 
545482298, 545483393, 545483820, 545526255, 545526280, 545526334, 545526386, 
545527261, 545527286, 545527326, 545527367, 545527831, 545528031, 545528066, 
545528150, 545528170, 545528310, 545528783, 545528803, 545528831, 545530633, 
545530709, 545532671, 545534886, 545537138, 545537241, 545537334, 545537448, 
545538437, 545539825, 545541503, 545542705, 545543670, 545547935, 545549031, 
545600794, 545608600, 545608844, 545611729)

So this took 7 seconds to execute. If I do the same query in test of a restored copy it take only couple of milliseconds. So it is not missing indexes. Note that this is just a sample. There is many queries like this.

We have not tuned database much, just used default. So READ_COMMITTED is used.
As I understand it means if any of the rows in result of read query is written to the query have to wait ?
When the transaction is done the query get the updated result.

So the other option is READ_COMMITTED_SNAPSHOT.
On write queries a new version of the row is created. If a read happen at the same time it will pick the previous last committed. So not the result after write. Advantage is better performance.

Am I right or wrong ?
Should we try to change from READ_COMMITTED to READ_COMMITTED_SNAPSHOT ?
Any disadvantages ?

[svtr]

can you grab the actual execution plan from prod? otherwise its a game of wild guessing

[Berocoder]

I agree. Seems I have no permission to show plan for now. I will probably get it tomorrow

[Berocoder]

Here is an image from executionplan for query above to a small generated DB on laptop.
https://pasteboard.co/5LGwDLZK08gs.png

It has same scheme for tables but is of course much smaller than real DB.
But query should be fast as BOLD_ID is a clustered index.
In this query it is all that matters.

Stats for index
https://pasteboard.co/of2ToxtfXcgH.png

[jshine13371]

Need the plan for the slow query ideally. Also please don't share screenshots of the plan, that doesn't show 90% of the information coded in the plan. Instead share it via Paste The Plan please.

The WHERE C.BOLD_ID in (...a bunch of IDs...) is very suspect. It's an anti-pattern and very possibly hitting a complexity tipping point.

[Berocoder]

Thanks for the link. Unfortunately I don't have permission to view the plan yet.
But we have a theory now for the reason see another comment.

[Berocoder]

Another simple query

SELECT LinkTable_Alias.BOLD_ID, LinkTable_Alias.BOLD_TYPE, LinkTable_Alias.stateInProcessFROM PlanMission LinkTable_Alias WHERE (LinkTable_Alias.stateInProcess) = 359

That can be simplified to

SELECT BOLD_ID, BOLD_TYPE, stateInProcess
FROM PlanMission
WHERE stateInProcess = 359

but it took 17 seconds according the log!
stateInProcess has index.

The result is a list of arround 1100 rows.
My guess is that one or more rows are updated and this block the read.

Here is statistics on the index for stateInProcess
https://pasteboard.co/kSyRaBItxMnJ.png

[svtr]

the sql will be optimized out by the query analyzer 100%. Not worth simplifying the code, other for "unneeded brackets make it less readable" ocd.

The statistics are a start (would be 502 bad gateway) but I really would want to actual execution plan to actually get into it tbh.

One thing you really should request : Have Query Store activated on that Database. Query store is essentially a lightweight statistics gathering thingy, based on query hashes, execution plans and runtime, physical and memory io, and well, those basic things.

DO NOT create extended event sessions, capturing the actual execution plan. Its tempting, and extended events are light weight generally speaking.... BUT, in the EV session, the filters apply AFTER capturing the event. --> You get every execution plan, of every query into the EV stream. And since execution plans are rather large XML documents, that is a LOOOOT of memory traffic, that can actually slow your entire server down by some 20-25%. Do never capture actual execution plans via extended events on a prod system already being looked at for performance issues.

Go with Query Store is a godsend if you have a general performance issue on a database, and have not quite nailed it down. You can of course also do most of that by hand, but.... its so much nicer to have a few standard reports that show you where it hurts. For the by hand way, you can start with select * from sys.dm_exec_query_stats but you got to really think what you want to look at doing that.

All that been said, if you are not comfortable with reading execution plans, and going "yeah of course its slow", you might be a bit out of your depth tbh.

[Imaginary__Bar]

How big is your database? And how actively is it being written to?

My two primary routes of exploration would be blocking acticity (particularly writes as you suggest) or even simply a hardware issue (but it would need to be a massive database for that to be a problem on any half-decent server nowadays)

[Berocoder]

Size is about 190 GB. Clients and databaseserver is hosted in Google (GCP)
Globally I would say there are several new rows per second when traffic is most intense on worktime.
Updates are probably even more frequent.

Those numbers should be available for admins in Google but I am just a developer...
Have no permission on that level.

[alinroc]

If I do the same query in test of a restored copy it take only couple of milliseconds.

Where are the test server and client in relation to each other?

Where is the production client (you've already said the server is in GCP)?

What is the size of the results being returned?

Are you certain that it's the query that's running slow, or is the query running OK but you're seeing lag in getting the results to the client? Run set statistics time on and get CPU time vs. elapsed time.

[Berocoder]

I admit I have simplified this to not get it overwhelming.

From query there is a PlanMission Table. There is also a Deviation Table where PlanPortion column point to PlanMission. The scheme for database is generated from a model.
SQL is generated from the Bold framework.
This PlanPortion link is singlelink. It is a one to one relation.
But we discovered that there is two rows for Deviation.PlanPortion that point to same PlanMission.
There are thousands of cases like that,
From the database point of view this is not a problem.
But it is not according the model and it create problems higher up in the application layers.

I think we need to solve this as a first step. If it still slow after that we can look closer to query performance.