Exam Ref 70-764 Administering a SQL Database Infrastructure by Isakov Victor

Author:Isakov, Victor [Isakov, Victor]
Language: eng
Format: azw3
Publisher: Pearson Education
Published: 2017-09-29T04:00:00+00:00

FIGURE 3-2 Activity Monitor

Identify sessions that cause blocking activity

Let’s now move on to how to identify sessions that are causing blocking activity. Blocking is a common cause of poor performance in a database engine where multiple transactions are isolated from each other through locking. By identifying sessions that are causing blocking you will be able to kill the offending sessions, engage your developers to rewrite their code and reindex tables appropriately.

You saw in Figure 3-2 an example of a query whose threads are blocking itself through parallelism, so Activity Monitor can be used to filter sessions that are being blocked through the Blocked By column filter. The database engine offers a number of different techniques that you can use to identify sessions that cause blocking. The techniques include, but are not limited to:

Using Activity Monitor

Querying any of the following DMVs:

[sys].[dm_exec_requests]

[sys].[dm_os_waiting_tasks]

Executing the [sp_who], or the undocumented [sp_who2] system stored procedures. These will most likely not be on the exam.

Using the blocked processed report.

Configuring and consuming the blocked process threshold reports in the database engine.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.