Several typical waiting _mssql in SQL Server

In order to prepare for this year's dual 11 long time no update blog, in the recent several SQL Server troubleshooting, summed up several typical SQL Server waiting type, similar to the waiting event in Oracle, if you see such a waiting type can quickly locate the root cause of the problem, Here's a case to sort out these typical wait-processing methods:

The first kind of waiting. Memory waiting

Received a user feedback in the morning its RDS instance is very slow, and you can see more than 10 W waiting tasks by observing the waiting tasks (similar to the thread running of MySQL) of the SQL Server active session monitor (Active monitor). You can be clear that the database is now a large bottleneck, followed by resource waits see a large number of memory memory waiting in the database:

See is memory resources waiting, in order to immediately restore user applications, think of immediately to adjust large memory, found that the example is already 24G, it seems that the OS's spare memory, there are more memory surplus, so the memory into 36G, found resource waits or memory on the waiting At the same time the CPU usage soared, to around 90% (before about 10% of the wait). This does not solve the underlying problem, so by recent expensive queries, the following SQL is found to be very high in logic reading and very frequently performed:

SELECT * from Refundorder_message messages0_ WHERE messages0_. Order_id= @p0;

You can also obtain the SQL that causes the memory wait in the following ways:
SELECT St.text from sys.dm_exec_query_memory_grants req CROSS APPLY sys.dm_exec_sql_text (req.sql_handle) as St where req. Grant_time is null or REQ.GRANTED_MEMORY_KB is NULL

The columns Grant_time and granted_memory_kb is NULL for those queries which are waiting to get their requested Y

Sp_helpindex Refundorder_message
Found that the table has only one primary key index:

To create an index:
Create INDEX ind_refundorder_message_order_id on refundorder_message (order_id);

The second kind of wait: latch waiting

After the index is added, the memory wait disappears immediately, but resource waits's wait turns to lock:

The following internal view allows you to find that the following call appears to wait:
SELECT Ss.host_name, Req.blocking_session_id,req.wait_type, Req.wait_time, Req.wait_resource, req.transaction_id, St.text from sys.dm_exec_requests req CROSS APPLY sys.dm_exec_sql_text (req.sql_handle) as ST
Cross apply sys.dm_exec_sessions SS where Req.status =n ' suspended ' and ss.session_id=req.session_id;

Get SQL that blocks other sessions:
(@p0 int, @p1 nvarchar (4000), @p2 bit)
SELECT Top (@p0) this.* from Viewsalesorder this_ WHERE this_. Membercode = @p1 and This_. Isobsolete = @p2 ORDER by This_. Odcode desc;

View Viewsalesorder is a very core view, which is associated with orders, order messages, order delivery and other business logic; the query conditions in the Membercode for the store name, may operate a shop order;
With the definition in the Viewsalesorder view, Membercode,isobsolete, Odcode is the three fields of the SalesOrder table, see that there is no corresponding index on the SalesOrder, and then add the following index:
Create INDEX Ind_salesorder_member on SalesOrder (Membercode,isobsolete,code);
After the index has been added, the waiting tasks of the database are dropped, batch requests promoted:

The third kind of wait: lock

The third kind of waiting is a common wait, a common situation at the time of deletion, update because there is no proper index in the condition, the locked record range is too large, causing the other session request to be blocked:

The user finds an UPDATE statement executing very slowly at the time of the pressure test, causing the entire system to be jammed:

Update Dd_shenhe Set ZF = 0 where ZF is null;

View the index above the Dd_shenhe table:

You can see that the table does not have the index of the ZF field, which has a total of 400w of data, and the ZF is 8,000 null, so it is appropriate to add the index to the ZF field:

Create index IND_DD_SHENHE_ZF on Dd_shenhe (ZF);

The system returned to normal after the index was added.