none
SQL Deadlock Troubleshooting and Statistics RRS feed

  • Question

  • One of my company's SQL database generates a lot of deadlocks per day recently (someday >100)

    I'm trying to troubleshoot the causes of these deadlocks, i noticed that many deadlocks occur at the same tables, I'm a newbie in Database Administrator so i don't know what can be infer from these reports and what to do next.

    Also what is the best way to do some statistics on the deadlock reports, like i want to calculate and visualize things like what tables involed, how manytimes, what time the deadlocks usually happen.... so i can report to my boss, is there an easy way to import the all the deadlock xml columns to something like microsoft excel?

    I attached here the deadlock reports (.xel file)

    https://drive.google.com/file/d/1LSUEqq-GYRaQhDydo7lhjPRRhJRDc_sM/view?usp=sharing

    Sorry for english is not my first language.

    Thank you for your time,

    Monday, August 12, 2019 9:09 AM

All replies

  • Hi Ultramation,

    Here is a generic way how to query XML stored in the table column. It shows how to handle XML elements and attributes, different data types, etc.

    -- DDL and data population, start
    DECLARE @tbl TABLE (ID INT IDENTITY(1,1) PRIMARY KEY,[xmlData] XML NOT NULL);
    
    INSERT INTO @tbl([xmlData])
    VALUES
    (N'<root>
    	<row id="1">
    		<city>Miami</city>
    		<populaton>470914</populaton>
    		<submitted>2019-08-12</submitted>
    	</row>
    	<row id="2">
    		<city>Orlando</city>
    		<populaton>285713</populaton>
    		<submitted>2019-08-10</submitted>
    	</row>
    </root>')
    , (N'<root>
    	<row id="10">
    		<city>Tampa</city>
    		<populaton>392890</populaton>
    		<submitted>2019-07-25</submitted>
    	</row>
    </root>')
    -- DDL and data population, end
    
    SELECT [ID]
    	, [xmlData]
    	, col.value('@id', 'INT') AS [attributeID]
    	, col.value('(city)[1]', 'VARCHAR(30)') AS [City]
    	, col.value('(populaton)[1]', 'INT') AS [population]
    	, col.value('(submitted)[1]', 'DATE') AS [submittedDate]
    FROM @tbl AS tbl
          CROSS APPLY tbl.[xmlData].nodes('/root/row') AS tab(col);

    Output:
    Monday, August 12, 2019 2:35 PM