[MSSQL] Stored procedure soms onverklaarbaar traag

[Crazy D]

Situatie:
ASPX pagina met 4 tabbladen. Elk tabblad wordt gevoed met een stored procedure. Zichtbare tab toont de resultaten, voor de overige tabbladen wordt de stored procedure ook aangeroepen maar dan met een Count-parameter zodat die enkel het aantal teruggeeft.

1 van de tabbladen kent een parameter, medewerker. Die wordt onthouden, zodat ook als je van tab wisselt, die medewerker onthouden is (en de Count ook rekening houdt met dat filter).

Wat er mis gaat in een vrij specifiek scenario (dat wil zeggen, hiermee is het te reproduceren, als het misgaat, dan is het enkel in deze combinatie): open de tab zonder filter, kies een medewerker (lijst ververst). Wissel van tab. Ga terug naar de probleem-tab. Maak medewerker leeg. Op dat moment doet ie er mega lang over (als het meezit krijg je na een minuut data, als het tegenzit een 502 error). Start je nu de pagina opnieuw, heeft ie onthouden dat je op de probleem-tab zat, en dan laadt de lijst (dus zonder filter....) weer op normale snelheid.

Als je dit probeert te reproduceren op database niveau lukt het niet: alle stored procedures geven consequent in ongeveer dezelfde tijd de data terug. En, omdat als je de pagina opnieuw opent, de lijst weer wel wordt getoond, lijkt het mij ook niet in de query te zitten.

Als ik wat logging toevoeg zie ik dat ook echt het data-ophalen traag is (in de specifieke situatie waarin het reproduceerbaar is dus...). Dat is de stap die er dan tot 1 minuut over doet. Rest van de pagina is normale snelheid.

Heeft iemand een suggestie in welke richting ik moet zoeken? "Google eens op dit woord" of zo. Ik kan met geen mogelijkheid bedenken wat het probleem is, maar het is een probleem

[Teknix1982]

Wij hebben een keer een vergelijkbare situatie gehad dat er op bepaalde momenten een bepaalde query onverklaarbaar traag werd. Als we de query vervolgens zelf uitvoerden dan was er niets aan de hand. Uiteindelijk was bij ons de oplossing dat de database zijn indexes moest rebuilden en update van de statistieken. Dit zou sowieso regelmatig moeten gebeuren wanneer je een hoge frequentie aan schrijfacties hebt. Misschien helpt dat?

[Crazy D]

Teknix1982 schreef op woensdag 21 juli 2021 @ 07:49:
Wij hebben een keer een vergelijkbare situatie gehad dat er op bepaalde momenten een bepaalde query onverklaarbaar traag werd. Als we de query vervolgens zelf uitvoerden dan was er niets aan de hand. Uiteindelijk was bij ons de oplossing dat de database zijn indexes moest rebuilden en update van de statistieken. Dit zou sowieso regelmatig moeten gebeuren wanneer je een hoge frequentie aan schrijfacties hebt. Misschien helpt dat?

Die gaan we proberen. De database is idd flink in gebruik om het zo maar te zeggen.

[Woy]

Om wat meer informatie te krijgen zul je toch iets meer data moeten vergaren aan de SQL Server kant. Laat de SQL Server profiler eens meelopen terwijl je het probleem probeert te reproduceren.

Ik vermoed ook iets van caching, locking of indexen waar in bepaalde situatie iets mis mee gaat.

[CurlyMo]

In PostgreSQL kan je letterlijk alle queries die uitgevoerd zijn met een bepaald user account en vanaf een bepaald ip adres loggen. Met daarbij de looptijd. Dat zal vast ook in MSSQL kunnen.

Dan kan je de boel ook op query niveau in stappen herhalen. Kijk dan eens of je het dus zonder front-end kan reproduceren direct op je database. Volgende stap is om de probleem query in met een analyser te draaien zodat je het execution plan kan inzien. Dat is meestal genoeg informatie om het op te lossen. Kom je er dan niet uit, plaats dan eens de output van die analyser hier.

[Mrlten]

Mijn gevoel zegt dat het een locking issue is of parameter sniffing.

Zijn er op het moment dat het issue op treedt meer queries aan het draaien (van bijv. andere users)?

Of het parameter sniffing is kan je makkelijk checken door 'OPTION (RECOMPILE)' aan de stored proc toe te voegen.

[Lethalis]

Mrlten schreef op woensdag 21 juli 2021 @ 08:12:
Mijn gevoel zegt dat het een locking issue is of parameter sniffing.

Het is zeer waarschijnlijk parameter sniffing.

Dat dezelfde stored procedure verschillende dingen kan doen op basis van een parameter is waarschijnlijk het probleem (medewerker of niet).

@Crazy D
Mogelijke oplossing: maak 2 verschillende stored procedures, zodat een enkel execution plan altijd optimaal is voor het uitvoeren van de procedure (ongeacht de parameters).

[ Voor 12% gewijzigd door Lethalis op 21-07-2021 08:49 ]

[Lethalis]

Crazy D schreef op woensdag 21 juli 2021 @ 07:41:
Als je dit probeert te reproduceren op database niveau lukt het niet: alle stored procedures geven consequent in ongeveer dezelfde tijd de data terug.

Omdat je dan waarschijnlijk met een andere connection string verbindt Andere user, wel / geen connection pooling, andere application name.

[Crazy D]

Qua query: profiler toont in dat geval dat het mis gaat, ook dat die SP er heel lang over doet. Het lijkt niet afhankelijk te zijn van gebruik van het systeem (vanochtend om 7:30 was ik de enige en toen had ik het probleem direct gereproduceerd, 4 x daarna nogmaals hetzelfde gedaan en toen weer niet....).

Ik ga een 2e SP maken voor 'met filter'. Maar wat ik wel typisch vind, is dat dit concept ook op diverse andere pagina's zo werkt, ook met parameters, ook met lange lijsten (1000+ records) en dat dit probleem nergens anders wordt gemeld dan in dit ene specifieke geval (en dus ook niet consequent). Dat is dus het gekke. Maar vind het de moeite waard. Tnx so far

[P_de_B]

Zit er verschil in de execution plans? Dat zou je in de profiler moeten bekijken. Dan heb je meer houvast waar het fout gaat.

[jan-marten]

Als 't om dead locks gaat zou je misschien met optie with (nolock) kunnen gebruiken.

Zoiets als dit dus;

SQL:

SELECT tbl.* FROM dbo.Tabel AS tbl WITH (NOLOCK)

[Crazy D]

Beetje lastig testen omdat we het niet heel bewust consequent kunnen reproduceren, maar ik heb nu 2 stored procs: 1 voor de hele lijst en 1 gefilterd op medewerker, en het lijkt erop dat dat de oplossing is. Vind het niet helemaal een verklaring waarom andere procs (met meer parameters) dit probleem niet hebben maar goed, misschien moet je van sommige dingen niet alles willen begrijpen waarom het zo is, na een paar uur nog geen errors meer doorgekregen. Hopelijk blijft dat zo!

[Crazy D]

weekje verder en tot nu toe nog geen meldingen meer gehad. Het lijkt er dus op dat 2 SP's in dit geval de oplossing was.