![]() For example, when I executed this query on my test SQL Server where I'm deliberately running some code to cause blocking, it brings the following results ( Note: To fit the resultset, I've split the resultset into seven images): To examine the results of this query, run it on SQL Server where you are experiencing blocks. This query is also a good way to analyze detailed information about locks, and help you to identify the cause of a large number of blocks. This query returns the comprehensive information about the blocking and waiting processes, which is useful for troubleshooting SQL Server locking and blocking issues. The following is the query, which I have written using these dynamic management views (DMVs) that will help you to quickly identify the SPIDs and other information about the processes that are causing the blocking on SQL Server instance. ![]() sys.dm_exec_sql_text - Returns the text of T-SQL batch.sys.dm_exec_query_plan - Returns the showplan for the query in XML format.sys.dm_tran_locks - Returns the information about the current locks and the processes blocking them.sys.dm_exec_requests - Returns the detailed information about the requests currently executing on SQL Server.sys.dm_exec_sessions - Returns information about authenticated sessions on SQL Server. ![]() sys.dm_os_waiting_tasks - Returns information about blocked and blocking processes.I used the following dynamic management views (DMVs) for my query. That is why, writing such a query is quite simple in SQL Server. As discussed in my previous article, SQL Server has a rich set of dynamic management views (DMVs) that helps you to quickly identify locking and blocking in SQL Server.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |