SQL Server Monitoring Statistics blocking script information

The essential reason for a database to block (Blocking) is that the SQL statement holds the lock for too long, too many, and has a large amount of time. Blocking is a side effect of transaction isolation, it is unavoidable, and is a common phenomenon in a database system. However, the blocking time and frequency should be controlled within a certain range, the blocking duration is too long or too much congestion (too frequent), will have a serious impact on database performance.

Many times, DBAs need to know if there is a performance problem with the database, is there a blockage? Since when? What happened to that database? Blocking occurs between those SQL statements? How long is the blocking time? How often does the blocking occur? What are the blocking connections sent from the client application? .......

If we can know these specific information, we will be able to quickly locate the problem, analyze the cause of the blocking, to identify the root cause of performance problems, and for specific reasons for the corresponding solution (index tuning, optimizing SQL statements, and so on).

There are a lot of ways to view blocking, I am in this blog Ms SQL Daily Maintenance Management common script (ii) inside mentions some ways to view blocking:

Method 1: View the record that caused the blocking, view the blk is not 0, and if there is a blocking process, the session ID of the blocking process. Otherwise this column is zero.

EXEC sp_who Active

Method 2: View the blocking, view the field Blkby, which can get more information than sp_who.

EXEC Sp_who2 Active

Method 3:sp_lock The system stored procedure, reports the information about the lock, but it is inconvenient to locate the problem

Method 4:sp_who_lock Stored Procedures

Method 5: Right-click the server-select activities and monitors to view the process options. Note the Task Status field.

Method 6: Right-click service Name-Select report-Standard report-activity-all blocked transactions.

But the above methods, such as sp_who, Sp_who2,sp_who_lock, and so on, have more or less disadvantages: for example, you cannot view blocked and blocked SQL statements. Unable to view blocking occurrences for a period of time, and no time to show blocking .... We want to implement the following features:

1: View that session blocked the session

2: Blocking session and the SQL statement being executed by the blocked session

3: How long has it been blocked?

4: Information like client IP, proagram_name, etc.

5: The point of time of the blocking happening

6: Frequency of the blocking happening

7: If necessary, should inform the relevant developers, the DBA can not do anything to do is it, that does not have to be exhausted, must let the developer to participate in the optimization (some problems on their solution), more understanding of some system operation of the specific situation, conducive to their understanding of problems, solve problems.

8: When needed to turn on this feature, do not need to turn off this feature

So in order to meet the above features, there are the following SQL statements

SELECT wt.blocking_session_id as                  Blockingsessesionid

      , Sp.program_name as                         programname

      , COALESCE (sp. Loginame, Sp.nt_username) as   HostName    

      , ec1.client_net_address as                  clientipaddress

      , Db.name as                                 DatabaseName        

      , Wt.wait_type as                            waittype                    

      , Ec1.connect_time as                        blockingstarttime

      Wt. wait_duration_ms/1000 as                waitduration

      , ec1.session_id as                          Blockedsessionid

      , H1. TEXT as                                 Blockedsqltext

      , H2. TEXT as                                 Blockingsqltext