0

I'm working on a multithreaded application (VB.Net/C#), who is working on an SQL-server database.
Regularly, my customer is complaining that the application show "deadlock"-like exceptions about the so-called SQLClient, and by restarting the application, everything works well again (for a while).

From the description, I don't believe my customer is facing a real deadlock (one thread waiting on another thread), but I believe I'm dealing with two database requests, most probably on the same table, where one transaction takes that much time that it blocks the other transaction, but this is just a guess, and I would like to be sure of this. For that, I'm thinking of asking the database which threads are currently accessing which table(s), hopefully this will give me some information.

While searching on the site here, I discovered the dm_db_index_operational_stats table, mentioned in this other question, but being such a newbie, this looks quite complicated, therefore I would like to ask this as a simple question:

How can I know which threads of which application are currently accessing which tables in my SQL-server database? Obviously, the more information (process ID, thread ID, thread name, (upper part of the) call stack, ...) the better.

Does this exist?

As far as a possible solution is concerned:
My application is based on this DLL:

"C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.0\System.Data.dll" 

... does the following in order to access the database (e.g. for removing obsolete entries):

Public Shared Sub ExecuteSQL(ByVal sSQL As String, ByVal ParamArray dbParams() As SqlParameter)
    Dim oCommand As SqlCommand = Nothing
    Try
        oCommand = BuildCommand(sSQL, dbParams)
        oCommand.CommandTimeout = 60
        oCommand.ExecuteNonQuery()
    Catch ex As Exception
        Debug.WriteLine(ex.Message)
        Throw
    Finally
        If oCommand IsNot Nothing Then ReleaseConnection(oCommand.Connection)
    End Try
End Sub

I would like to add a locking-mechanism in order to avoid this type of problems, something like:

DatabaseConnection.Lock_Table("Table_which_is_handled");
ExecuteSQL(...); // working on "Table_which_is_handled"
DatabaseConnection.Release_Lock_Table("Table_which_is_handled");

Does this exist?

Does anybody have an idea?
Thanks in advance

Edit after discussion with Dan Huzman:
This answer, referred to by Dan Huzman, mentions following SQL query:

--Get xml_deadlock_report events from system_health session file target
WITH
  --get trace folder path and append session name with wildcard (assumes base file name is same as session name)
  all_trace_files AS (
    SELECT path + '\system_health*.xel' AS FileNamePattern
    FROM sys.dm_os_server_diagnostics_log_configurations
    )
  --get xml_deadlock_report events from all system_health trace files
, deadlock_reports AS (
    SELECT CAST(event_data AS xml) AS deadlock_report_xml
    FROM all_trace_files
    CROSS APPLY sys.fn_xe_file_target_read_file ( FileNamePattern, NULL, NULL, NULL) AS trace_records
    WHERE trace_records.object_name like 'xml_deadlock_report'
)
SELECT TOP 10
  deadlock_report_xml.value('(/event/@timestamp)[1]', 'datetime2') AS UtcTimestamp
, deadlock_report_xml AS DeadlockReportXml
FROM deadlock_reports;

This refers to some files, located on the computer of the customer, most probably in the directory "C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Log" (in order to be sure, check SELECT path FROM sys.dm_os_server_diagnostics_log_configurations).
I can retrieve the mentioned "system_health*.xel" files and put them on my local computer (let's say in the directory "C:\Temp_Folder"), and then launch the following new query:

--Get xml_deadlock_report events from system_health session file target
WITH
  --get trace folder path and append session name with wildcard (assumes base file name is same as session name)
  all_trace_files AS (
    SELECT 'C:\Temp_Folder\system_health*.xel' AS FileNamePattern
    FROM sys.dm_os_server_diagnostics_log_configurations
    )
  --get xml_deadlock_report events from all system_health trace files
, deadlock_reports AS (
    SELECT CAST(event_data AS xml) AS deadlock_report_xml
    FROM all_trace_files
    CROSS APPLY sys.fn_xe_file_target_read_file ( FileNamePattern, NULL, NULL, NULL) AS trace_records
    WHERE trace_records.object_name like 'xml_deadlock_report'
)
SELECT deadlock_report_xml.value('(/event/@timestamp)[1]', 'datetime2') AS UtcTimestamp
, deadlock_report_xml AS DeadlockReportXml
FROM deadlock_reports;

Would this give me the information I'm looking for or do I need to open a Microsoft SQL Server Management Studio session on my customer's computer?

6
  • 2
    A deadlock is not just one thread waiting on another (blocking) but 2 more threads waiting on each other (deadlocking). If the SqlException shows an actual deadlock error, examine the xml deadlock report (captured by the system_health trace) and add it to your question if you need help understanding it. Deadlocks are often resolved with query/index tuning. Serializing database access will avoid deadlocks but not address the root cause.
    – Dan Guzman
    Dec 5, 2022 at 11:05
  • @DanGuzman: thanks for your very quick reply. Can you tell me where to retrieve the system_health trace? (I'm currently working on the restore of a backup database, I hope this information will be available?)
    – Dominique
    Dec 5, 2022 at 11:10
  • The deadlock info is captured on the server where the deadlock occurred but unfortunately not available in database backups. See this answer for a query to retrieve recent deadlocks from the system_health trace.
    – Dan Guzman
    Dec 5, 2022 at 11:29
  • @DanGuzman: thanks again. I've updated my question. Do you think I might get the information I'm looking for by checking the *.xel files, as mentioned in my last edit?
    – Dominique
    Dec 5, 2022 at 12:33
  • 1
    Yes, you can copy the trace files to a local machine with SQL Server installed and query using SSMS. Run this query at your customer site to identify the files to copy: SELECT path + '\system_health*.xel' AS FileNamePattern FROM sys.dm_os_server_diagnostics_log_configurations;.
    – Dan Guzman
    Dec 5, 2022 at 14:22

1 Answer 1

2

A deadlock is not just one thread waiting on another (blocking) but 2 more threads waiting on each other (deadlocking). Deadlocks can often be resolved with query/index tuning rather than going so far as to serialize database access.

The xml_deadlock_report is captured by the system_health Extended Event trace and will help identify the processes and resources involved of recent deadlocks. The deadlock information can be obtained with the queries gleaned from the comments and added to your question.

2
  • I just saw that it's possible to open the "system_health*.xel" files in Microsoft SQL Server Management Studio. In there (just the ones on my PC) I find event names like "connectivity_ring_buffer_recorded", "error_reporting", ..., but nothing like "deadlock" (which seems to be "generated" by the sys.fn_xe_file_target_read_file(...) function. Does this mean that in the "system_health*.xel" files from the customer, there will be event names like "deadlock" or will I need to find those inside the "error_reporting" events? (Just in case I don't use the query)
    – Dominique
    Dec 5, 2022 at 15:59
  • 1
    @Dominique, the trace files will contain xml_deadlock_report if at least one deadlock occurred. Be aware that the trace files rollover so, depending on the amount of activity, older events may have rolled off.
    – Dan Guzman
    Dec 5, 2022 at 16:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.