在SQL2005~SQL2008R2中可以透過sys.dm_exec_connections動態檢視表查詢資料庫中連線的相關訊息,

請注意<圖一>、<圖二>執行

SELECT session_id,auth_scheme,client_net_address,client_tcp_port,
local_net_address,local_tcp_port
FROM sys.dm_exec_connections
GO

所得到的訊息的差異,<圖一>欄位client_net_address(連接到這部伺服器之用戶端的主機位址)只得到<local machine>的訊息、其後三個欄位皆為空值,而<圖二>欄位client_net_address取得目前連線到資料庫主機的使用者主機IP且三個後面欄位client_tcp_port(連接相關聯的用戶端電腦上的通訊埠編號)、local_net_address(連接的目標伺服器的 IP 位址。只適用於使用 TCP 傳輸提供者的連接)、local_tcp_port(連接是使用 TCP 傳輸的連接時,代表這項連接的目標伺服器 TCP 埠)卻能取得當前資料庫主機的連線IP相關資料 。 

<圖一>

  5.sys.dm_exec_connections_query_in_Shared Memory2 

<圖二>

6.sys.dm_exec_connections_query_in_tcpip2      

若需取得如<圖二>的資料,必須先執行下列步驟:

1.修改Sqlserver Configuration Manager的SQL Server Network Configuration-Protocols for MSSQLSERVER的TCP/IP與

SQL Native Client 10.0 configuration-Client Protocols的TCP/IP改為Enable,請參考<圖三>、<圖四>。

<圖三>

開啟TCPIP設定 

<圖四>

開啟TCPIP設定2  

2.重新啟動SQLServer 服務

3.開啟Microsoft SQL Server Managerment Studio

4.連線到資料庫時點選Option按鈕,請參考<圖五>

<圖五> 

1.Connection to Server

5.點選Option後會顯示多個頁簽,在Connection Properties中的Network protocol選單,有<default>、Shared Memory、TCP/IP、Named Pipes四個選項,請參考<圖六>、<圖七>、<圖八>,選擇TCP/IP選項後再按Connect連線,通常連線到資料庫Network protocol使用的是<default>選項,而<default>的設定是依據<圖四>中Order的順序來執行,預設是使用Shared Memory連線。

 <圖六>  

2.Connection Properties-default

<圖七>

3.Connection Properties-Shared Memory  

<圖八>

4.Connection Properties-TCPIP

 

 進階應用

sys.dm_exec_connections、sys.dm_exec_sessions、sys.dm_exec_sql_text 三者搭配使用可取得更多有用的訊息,例如:

 

查詢目前資料庫正在執行的TSQL

SELECT A.session_id,A.login_name,A.original_login_name,B.auth_scheme,
A.host_name,A.program_name,A.client_interface_name,A.login_name,
A.nt_domain,A.nt_user_name,A.status,A.cpu_time,
A.total_elapsed_time,A.total_scheduled_time,B.client_net_address,B.client_tcp_port,
B.local_net_address,B.local_tcp_port,C.dbid,c.objectid,
CASE
WHEN c.encrypted=0 THEN '未加密'
WHEN c.encrypted=1 THEN '資料加密'
ELSE '未知加密狀態'
END,
c.text AS 'TSQL語法'
FROM sys.dm_exec_sessions A INNER JOIN sys.dm_exec_connections B ON A.session_id=B.session_id
CROSS APPLY sys.dm_exec_sql_text(B.most_recent_sql_handle) C
go

統計帳號的連線數量

SELECT B.login_name, A.client_net_address,
'連線數' = COUNT(*)
FROM sys.dm_exec_connections A
INNER JOIN sys.dm_exec_sessions B ON A.session_id = B.session_id
GROUP BY login_name, client_net_address
ORDER BY '連線數'

 

參考資料

http://msdn.microsoft.com/zh-tw/library/ms181509(v=sql.105).aspx

http://msdn.microsoft.com/zh-tw/library/ms176013(v=sql.105).aspx

http://msdn.microsoft.com/zh-tw/library/ms181929.aspx

,

K 發表在 痞客邦 PIXNET 留言(0) 人氣()