Home
%3CLINGO-SUB%20id%3D%22lingo-sub-758475%22%20slang%3D%22en-US%22%3ELesson%20Learned%20%23103%3A%20Performance%20issue%20retrieving%20the%20deadlock%20list%20in%20Azure%20SQL%20Database.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-758475%22%20slang%3D%22en-US%22%3E%3CP%3E%3CFONT%20size%3D%223%22%3EToday%2C%20I%20worked%20on%20a%20service%20request%20that%20our%20customer%20is%20using%20one%20of%20our%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FAzure-Database-Support-Blog%2FLesson-Learned-19-How-to-obtain-the-deadlocks-of-your-Azure-SQL%2Fba-p%2F368847%22%20target%3D%22_self%22%3Earticles%3C%2FA%3Eto%20obtain%20the%20deadlocks%20but%20the%20number%20the%20deadlocks%20generated%20is%20causing%20a%20delay%20of%20hours%20of%20the%20query%20execution%20that%20takes%20hours%20to%20complete.%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CFONT%20size%3D%223%22%3EEven%20if%20our%20customer%20tries%20to%20use%20filter%20is%20taking%20time%20because%20the%20deadlocks%20are%20saving%20internally%20in%20an%20extended%20event%20file%2C%20for%20this%20reason%2C%20before%20applying%20the%20filter%20SQL%20Enginee%20needs%20to%20read%20the%20entire%20content%20of%20the%20extended%20event%20file.%20For%20this%20reason%2C%20depending%20on%20the%20size%20of%20the%20extended%20event%20file%20it%20could%20take%20time.%26nbsp%3B%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CFONT%20size%3D%223%22%3EWe%20suggested%20two%20solutions%20to%20avoid%20this%20problem%3A%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%3CFONT%20size%3D%223%22%3ECreate%20your%20own%20Extended%20Event%20to%20capture%20our%20deadlock%20explained%20in%20this%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FAzure-Database-Support-Blog%2FLesson-Learned-98-Is-possible-to-create-an-extended-event-on-the%2Fba-p%2F745344%2520%22%20target%3D%22_self%22%3Earticle%3C%2FA%3E.%3C%2FFONT%3E%3C%2FLI%3E%0A%3CLI%3E%3CFONT%20size%3D%223%22%3EUse%20Diagnostic%20Setting%20of%20the%20database%20that%20you%20could%20capture%20from%20the%20internal%20telemetry%20data%2C%20data%2C%20like%20Error%2C%20timeouts%2C%20Blocks%2C%20Deadlocks%2C%20etc..%20and%20saving%20it%20in%20a%20Log%20Analytics%20workspace%20or%20storage%20account.%3C%2FFONT%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20407px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F123498iD2B6059BF8096587%2Fimage-dimensions%2F407x372%3Fv%3D1.0%22%20width%3D%22407%22%20height%3D%22372%22%20alt%3D%22diagnosti.png%22%20title%3D%22diagnosti.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEnjoy!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-758475%22%20slang%3D%22en-US%22%3E%3CP%3EToday%2C%20I%20worked%20on%20a%20service%20request%20that%20our%20customer%20is%20using%20one%20of%20our%20articles%20to%20obtain%20the%20deadlocks%20but%20the%20number%20the%20deadlocks%20generated%20is%20causing%20a%20delay%20of%20hours%20of%20the%20query%20execution%20that%20takes%20hours%20to%20complete.%3C%2FP%3E%3C%2FLINGO-TEASER%3E

Today, I worked on a service request that our customer is using one of our articles to obtain the deadlocks but the number the deadlocks generated is causing a delay of hours of the query execution that takes hours to complete.

 

Even if our customer tries to use filter is taking time because the deadlocks are saving internally in an extended event file, for this reason, before applying the filter SQL Enginee needs to read the entire content of the extended event file. For this reason, depending on the size of the extended event file it could take time. 

 

We suggested two solutions to avoid this problem:

 

  • Create your own Extended Event to capture our deadlock explained in this article.
  • Use Diagnostic Setting of the database that you could capture from the internal telemetry data, data, like Error, timeouts, Blocks, Deadlocks, etc.. and saving it in a Log Analytics workspace or storage account.

 

diagnosti.png

 

Enjoy!