SQL Server congestion Analysis

SQL Server congestion analysis (2010-08-27)
Today, the staff suddenly reported that an interface could not be opened normally. The first thought was that sqlserver was blocked again.
In sqlserver, a scheduled task that runs once every five minutes is performed to regularly scan for congestion. However, it has been a little too long for five minutes.
Run the query blocking script to check the current system congestion.
Select
Blocked_query.session_id as blocked_session_id,
Blocking_query.session_id as blocking_session_id,
Blocking_ SQL _text.text as blocking_ SQL _text,
Blocked_ SQL _text.text as blocked_ SQL _text,
Waits. wait_type as blocking_resource,
Blocked_query.command as blocked_command,
Blocking_query.command as blocking_command,
Blocked_query.wait_type as blocked_wait_type,
Blocked_query.wait_time as blocked_wait_time,
Blocking_query.total_elapsed_time as blocking_elapsed_time,
Getdate ()
From SYS. dm_exec_requests blocked_query
Join SYS. dm_exec_requests blocking_query on
Blocked_query.blocking_session_id = blocking_query.session_id
Cross apply
(
Select *
From SYS. dm_exec_ SQL _text (blocking_query. SQL _handle)
) Blocking_ SQL _text
Cross apply
(
Select *
From SYS. dm_exec_ SQL _text (blocked_query. SQL _handle)
) Blocked_ SQL _text
Join SYS. dm_ OS _waiting_tasks waits on
Waits. session_id = blocking_query.session_id

The query result is very simple,
A select statement is blocked and a trigger is blocked. The two operate on the same table, and blocking_resource is lck_m_s, which is obviously a block between reading and writing.
The Analysis Steps should first start from blocking the process, and then analyze the SELECT statement.
The trigger's business logic is complex, with more than 600 rows, of which 10 or 20 select and update statements
Only select and update statements can be analyzed one by one in order to see which SQL statement may have a problem.
It mainly depends on whether the SQL where condition meets the index and high selectivity requirements, and soon locates an SQL statement
Select top 1 @ var1 = field1 from tablename where field2 = @ var2 and field1 is not null and primarykey <> @ primarykey
The table contains nearly 0.1 million records, but the query is not displayed after 1 minute. In theory, it should not. Mark it first and continue tracking.
Soon I found another query with a database link.
Select top 1 primarykey from dblink. dbname. username. tablename where cond1
Run the SQL statement for a while, but it does not respond for half a day.
The problem should occur in these two places. You need to understand the corresponding business logic and then optimize the SQL statement. It is imperative that the session be killed first.
Run kill
Sessionid, but still cannot open the program interface, continue to run the query blocking script, it is found that blocking_command is changed to killed/rollback
That is to say, it has been in the rollback status and has not been killed successfully. It is very strange. In addition, the entire database seems to be completely paralyzed, and all applications cannot be executed.
As a result, the system engineer restarted the database and re-opened the program interface for data processing. The results quickly showed up the previous symptoms.
Later, I wondered if dblink had a problem. I continued to run the query based on this dblink and found that basically all the tasks could not be executed. Check the network first.
The system engineer logs on to the server to view the Windows logs. As a result, many network faults have been found and the network is urgently handled.
Run the query blocking script again to find that the blocking has been automatically eliminated, and the seemingly slow SQL statement runs quickly.

The congestion problem has been solved.
Given that the SQL Server lock isolation mechanism is set to read_committed_snapshot, the read and write operations may cause conflicts, and the root cause of the problem is not hard to understand, but the root cause may be many reasons.