Dotaz: MSSQL profilování

[Jan Kratochvíl]

Zdravím všechny,

potřeboval bych poradit jak najít na MSSQL (2016 SP2) úzké místo, respektive asi nějaké blokování/zámky. Mám dost složitou proceduru, která provádí fakturaci v ERP (z objednávky vytvoří fakturu, má hromadu validací, atd.) Normálně běží od 0-0.5s což mi vyhovuje, ale občas trvá její běh násobně více (i 20s). Protože v systému pracuje mnoho uživatelů, podezřívám nějaké blokování/zamykání tabulek.

Otázka je, zdali někdo neporadí, jak to pomocí XE Profileru odhalit? Co jsem koukal do různých tutoriálů, tak se to zaměřuje na long running queries, ale to v zásadě už vím. Potřebuji zjistit stav celého toho serveru v momentě dlouhého běhu. Neví někdo jakým směrem se vydat?

Díky Honza

Odpovědi

[Dominika_myerp_cz]

Prověřit Deadlocky

Use Master
SELECT
xed.value('@timestamp', 'datetime') as Creation_Date,
xed.query('.') AS Extend_Event
FROM (
SELECT CAST([target_data] AS XML) AS Target_Data
FROM sys.dm_xe_session_targets AS xt
INNER JOIN sys.dm_xe_sessions AS xs
ON xs.address = xt.event_session_address
WHERE xs.name = N'system_health'
AND xt.target_name = N'ring_buffer'
) AS XML_Data
CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xed)
ORDER BY Creation_Date DESC

Zvednout Max degree of parallelism
https://docs.microsoft.com/fr-FR/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sql-server-ver15

Vypnout Boost SQL Server Priority
Nechat na serveru zapnutý režim výkonu procesoru a nepodtaktovávat

[j]

Chovani je zavisly (dost) na tom, jak mas nastavenou databazi, trebas read committed snapshot. Jen pozor na to, ze nez neco zacnes menit, je treba si vzdy uvedomit dusledky a ne s kazdou aplikaci to lze. Tohle samo o sobe pomerne efektivne umi zabranit deadlockum.

Velmi zjednodusene to funguje tak, ze pro transakci vytvori snapshot stavajicich dat, a tudiz dalsi query do stejnych dat neceka. Na druhou stranu to pak pochopitelne vraci starou verzi dat i v okamziku, kdyz je prave menis.

Taky je dobry pouzivat hinty (rowlock/nolock...) opet treba vedet, co to znamena a obnasi.

Potom taky spravny strukturovani transakci.

To ze zjistis, ze se ti useri navzajem blokujou ti nijak zvlast nepomuze. Bydefault SQLko lock dava na page, takze ses schopen znepristupnit hromadu zaznamu i jedinym query.

20s je jeste naprosta pohoda. A klidne se ti muzes stat, ze vysledkem tvyho zkoumani bude zjisteni, ze bez zasadni zmeny struktury databaze s tim nic neudelas.

Jinak samotna akce je IMO spis vec na tema "kdyz je cas" ne? Jestli fakturu udelas ted nebo za hodinu je asi uplne jedno. Takze mozna je resenim i to, proste jen doklad omarkovat "k fakturaci" a vyfakturovat to nekde pres noc.

---

Dete s tim guuglem dopice!

[UL]

Jako prvni krok doporucuju First Aid Kit on Brenta Ozara. https://www.brentozar.com/first-aid/

CLovek dostane prvni nahled na to, kde muze byt problem. Umi to doporucit indexy, najde to nejake nesrovnalosti v konfiguraci, umi to identifikovat dlouho bezici dotazy atd. Moc dobry nastroj.

Ohledne identifikace problemovych dotazu, ze jednou bezi 0.5s a jednou 20s. Setkal jsem se s tim taky. Jednou 3s jdenou 3 minuty. Je dost mozne, ze ta procedura ma pro urcite parametry neoptimalni exekucni plan (parametr sniffing). Tam muzes skusit pridat do procedury "WITH RECOMPILE" ci nejak potunit indexy. S identifikaci dotazu muze pomoci i Query store. Zkus ho zapnout, par dni sbirat data a pak se podivat na to co to hodi. Hledej dotazy s nejvetsi variabilitou.

Pokud je ta procedura od dodavatele a ne in-house, reklamoval bych u dodavatele. Je velka pravdepodobnost, ze se s tim uz setkal a mozna vi jak to vyresit.