select session_id, db_name(database_id) as "db_name", status, wait_type, wait_time, text from sys.dm_exec_requests cross apply sys.dm_exec_sql_text(sql_handle) where session_id>50
select session_id, most_recent_session_id, net_transport, auth_scheme, client_net_address, client_tcp_port, local_net_address, local_tcp_port from sys.dm_exec_connections
從查詢結果可以大致推斷出本地SSMS作為一個客戶端如果使用TCP/IP協議也是要走網卡的，而且執行結果顯示了登錄使用的協議以及登錄驗證方式還有使用的端口號。使用shared memory協議的連接不通過socket通信的方式獲取數據，而是直接通過系統總線從共享內存讀取。
關于等待事件：
This wait type is where SQL Server has sent some data to a client through TDS and is waiting for the client to acknowledge that is has consumed the data, and can also show up with transaction replication if the Log Reader Agent job is running slowly for some reason.
這個等待類型表示SQL Server正在通過TDS向客戶端傳送請求的數據，也可能表示事務復制的日志讀取代理由于某些原因運作緩慢。
(Books Online description: “Occurs on network writes when the task is blocked behind the network. Verify that the client is processing data from the server.”)
（聯機叢書的解釋：當任務由于被阻塞于網絡時出現，證明客戶端正在接收服務端的數據）
Other information:
This wait type is never indicative of a problem with SQL Server, and the vast majority of the time it is nothing to do with the network either (it’s very common to see advice stating that this is a network issue). A simple test for network issues is to test the ping time between the SQL Server and the client/application/web server, and if the ping time is close to the average wait time, then the wait is because of the network (which may just be the normal network latency, not necessarily a problem).
這個等待類型表示并非SQL Server的問題，絕大多數情況下也與網絡問題無關（很多時候大家都認為是網絡問題），一個簡單的測試方式是從客戶端ping一下服務端，如果延遲接近sys.dm_exec_requests中wait_time的平均值則證明確實與網絡相關（很多時候都只是正常的網絡延遲，并不是網絡故障）。
There is usually nothing that you can do with your SQL Server code that will affect this wait type. There are a few causes of this on the client side, including:
- The client code is doing what is known as RBAR (Row-By-Agonizing-Row), where only one row at a time is pulled from the results and processed, instead of caching all the results and then immediately replying to SQL Server and proceeding to process the cached rows.
- The client code is running on a server that has performance issues, and so the client code is running slowly.
- The client code is running on a VM on a host that is configured incorrectly or overloaded such that the VM doesn’t get to run properly (i.e. slowly or coscheduling issues).
針對此等待事件一般無需對SQL代碼做什么改動，引發此問題的原因基本都是由于來源于客戶端，例如：
。客戶端代碼使用RBAR方式處理數據集，每次只從結果集拉取一條數據，而不是全部獲取完畢后再處理。
。客戶端所在的服務器有某些性能問題，導致客戶端運作緩慢。
。客戶端運行在配置錯誤或者過載的虛擬機上，總之也是服務器本身的問題。
On the SQL Server side, the only possibility I know of for causing this is using MARS (Multiple Active Result Sets) with large result sets.
You can demonstrate this wait type easily by running a query with a large result set through SSMS on the SQL Server itself, with no network involved.
在數據庫服務端，就我所知唯一可能的原因就是使用了MARS的大結果集引起的。（其實就是因為結果集太大）
你可以很輕易的通過在數據庫服務器上使用本機名登錄的方式，運行一個獲取大結果集的查詢，來驗證這個等待事件是否會出現。
Some other things you can try:
- Look for incorrect NIC settings (e.g. TCP Chimney Offload enabled) with the help of your network/system administrator. Whether some settings should be enabled or not depends on the underlying OS version. See this post for some more details.
- Consider increasing the TDS packet size (carefully) – see this post for more details.
Description:
This wait type is when a thread is calling the Windows WaitForSingleObject function for synchronization with an external client process that is communicating using that object.
(Books Online description: N/A --表示聯機叢書沒有說明)
這個等待事件表示一個線程正在向外部客戶端進程同步某個對象的數據，因此出現此種等待。一般此種等待出現在SQL Server 2012及以上的版本，以前用ASYNC_NETWORK_IO代替。
Other information:
This wait type is commonly seen in conjunction（同時出現） with ASYNC_NETWORK_IO, depending on the network transport used to communicate with the client, so to troubleshoot, follow the same steps as for ASYNC_NETWORK_IO.
Note that when a thread calls out to Windows, the thread changes from non-preemptive (SQL Server controls the thread) to preemptive (Windows controls the thread) mode. The thread’s state will be listed as RUNNING, as SQL Server doesn’t know what Windows is doing with the thread.
這種等待事件一般與ASYNC_NETWORK_IO等待事件一起出現，取決于連接所使用的網絡傳輸類型，因此解決步驟參考ASYNC_NETWORK_IO的解決方式。
注意，當一個連接線程被從SQL Server控制（非搶占式）到被Windows控制（搶占式）的后，線程的狀態就會變為running,此時SQL Server并不知道windows在對此線程做什么。
關于搶占式與非搶占式的區別，參考官網博客中關SQL OS與Windows OS對線程的不同處理方式的介紹。
文章列表
留言列表
