SQL Server Profiler
Of course, deadlocks are not typically going to happen while you watch, so how can you know when and why they occur? You can use either SQL Server Profiler or Extended Events to capture a deadlock graph, an XML description of a deadlock.
A deadlock graph provides you with insight into the objects involved in a deadlock and identifies the terminated process. You can capture a deadlock graph by using either SQL Server Profiler to later review deadlock events that have yet to occur or by using Extended Events to review deadlock events that have already occurred.
Although SQL Trace is designated as a deprecated feature and will be removed from a future release of SQL Server, it remains an available option in SQL Server 2016.
You can define server-side traces by using system stored procedures and then run these traces on demand or on a scheduled basis. As an alternative, you can use SQL Server Profiler as a client-side option. The overhead of running server-side traces is much less than the overhead of using SQL Server Profiler, but the overhead is still significant. Therefore, take care when using SQL Trace in a production environment regardless of the approach you take and disable tracing as soon as possible.
Use the query_pre_execution_showplan or query_post_execution_showplan Extended Events as a lightweight method to capture the estimated or actual query plans, respectively. As an alternative, you can use SQL Trace system stored procedures for server-side tracing or use SQL Server Profiler to capture the Showplan XML or Showplan XML For Query Compile events for an estimated query plan or the Showplan XML Statistics Profile event for an actual query plan.
https://docs.microsoft.com/en-us/sql/tools/sql-server-profiler/sql-server-profiler?view=sql-server-ver15
If you use SQL Server Profiler to capture a deadlock graph, you must configure the trace before deadlocks occur.
Start by creating a new trace, and connect to your SQL Server instance. In the Trace Properties dialog box, select the Events Selection tab, select the Show All Events check box, expand Locks, and then select the following events:
Deadlock graph
Lock:Deadlock
Lock:Deadlock Chain
On the Events Extraction Settings tab, select the Save Deadlock XML Events Separately option, navigate to a directory into which SQL Server Profiler saves deadlock graphs, and supply a name for the graph. You can choose whether to save all deadlock graphs in a single .xdl file or save multiple deadlock graphs as a separate .xdl file. Now set up the deadlock scenario again to generate the deadlock graph.
When a deadlock occurs, you can see the deadlock graph as an event in SQL Server Profiler. In the deadlock graph, you see the tables and queries involved in the deadlock, which process was terminated, and which locks led to the deadlock.
The ovals at each end of the deadlock graph contain information about the processes running the deadlocked queries. The terminated process displays in the graph with an x superimposed on it. Hover your mouse over the process to view the statement associated with it.
The rectangles labeled Key Lock identify the database object and index associated with the locking. Lines in the deadlock graph show the relationship between processes and database objects. A request relationship displays when a process waits for a resource while an owner relationship displays when a resource waits for a process.
It is important to ensure that you are capturing only events of interest by limiting the events with filters. Effective use of filters helps minimize the size of the trace file and therefore assists with the analysis by reducing the number of captured events.
https://www.techveze.com/capturing-activity-sql-server-profiler
https://www.sqlservercentral.com/articles/using-sql-profiler-to-resolve-deadlocks-in-sql-server
You can find in-depth information about using SQL Server Profiler, the graphical interface, in Brad M. McGehee’s free ebook “Mastering SQL Server Profiler” at http://www.red-gate.com/library/mastering-sql-serverprofiler.
Example 1:
Answer 1:
Example 2:
Answer 2:
Example 3:
Which tool monitoring tools can you use to get information about SQL Server memory usage without writing any code?
A. DMVs or SQL Profiler
B. Server-side SQL Trace or Extended Events
C. Performance Monitor or Management Data Warehouse
D. Client-side SQL Trace or Resource Governor
The answer is C. Both Performance Monitor and Management Data Warehouse provide graphical interfaces that you can use to monitor SQL Server’s memory usage. In Performance Monitor, you can set up the data collection of performance counters for the SQL Server Memory Manager, such as Memory Grants Outstanding or Memory Grants Pending, among others. In Management Data Warehouse, you can use the Server Activity History report to drill into Memory Usage details for the server. To access information from DMVs, you must write a T-SQL query; therefore, A is incorrect. Similarly, B is incorrect because a server-side SQL Trace requires you to write code. Answer D is incorrect because Resource Governor is not a performance monitoring tool.
Example 4:
You are a database developer for a company. The company has a server that has multiple physical disks. The disks are not part of a RAID array. The server hosts three Microsoft SQL Server instances. There are many SQL jobs that run during off-peak hours.
You observe that many deadlocks appear to be happening during specific times of the day. You need to monitor the SQL environment and capture the information about the processes that are causing the deadlocks. Captured information must be viewable as the queries are running. What should you do?
A. Create a sys.dm_os_waiting_tasks query.
B. Create a sys.dm_exec_sessions query.
C. Create a Performance Monitor Data Collector Set.
D. Create a sys.dm_os_memory_objects query.
E. Create a sp_configure "˜max server memory' query.
F. Create a SQL Profiler trace.
G. Create a sys.dm_os_wait_stats query.
H. Create an Extended Event.
Correct Answer: F
To view deadlock information, the Database Engine provides monitoring tools in the form of two trace flags, and the deadlock graph event in SQL Server Profiler. Trace Flag 1204 and Trace Flag 1222 When deadlocks occur, trace flag 1204 and trace flag 1222 return information that is captured in the SQL Server error log. Trace flag 1204 reports deadlock information formatted by each node involved in the deadlock. Trace flag 1222 formats deadlock information, first by processes and then by resources. It is possible to enable both trace flags to obtain two representations of the same deadlock event.
References: https://technet.microsoft.com/en-us/library/ms178104(v=sql.105).aspx
Example 5:
You are a database developer for a company. The company has a server that has multiple physical disks. The disks are not part of a RAID array. The server hosts three Microsoft SQL Server instances. There are many SQL jobs that run during off-peak hours.
You must monitor the SQL Server instances in real time and optimize the server to maximize throughput, response time, and overall SQL performance. You need to ensure that the performance of each instance is consistent for the same queried and query plans. What should you do?
A. A. Create a sys.dm_os_waiting_tasks query.
B. Create a sys.dm_exec_sessions query.
C. Create a Performance Monitor Data Collector Set.
D. Create a sys.dm_os_memory_objects query.
E. Create a sp_configure "˜max server memory' query.
F. Create a SQL Profiler trace.
G. Create asys.dm_os_wait_stats query.
H. Create an Extended Event.
Correct Answer: H
Advanced Viewing of Target Data from Extended Events in SQL Server When your event session is currently active, you might want to watch the event data in real time, as it is received by the target. Management > Extended Events > Sessions > [your-session] > Watch Live Data. The query_post_execution_showplan extended event enables you to see the actual query plan in the SQL Server Management Studio (SSMS) UI. When the Details pane is visible, you can see a graph of the query plan on the Query Plan tab. By hovering over a node on the query plan, you can see a list of property names and their values for the node.
References: https://msdn.microsoft.com/en-us/library/mt752502.aspx