How to configure your own DMV monitors dashboards…? (2/4)

By last article we explained how to build your DMV monitors for expensive queries and today we are going explain how we can build DMV monitors for Connectivity and outage cases between DB and Application servers

Statistics of connections coming from APP server

As done previously within the 1st blog , we are going to create new repository table to buffer all DMV results inside it for further usage

1- Thereby let us first create this repository as below:


--Create buffer table
USE [msdb]

CREATE TABLE [dbo].[Connections_Statistics](
	[client_net_address] [nvarchar](100) NULL,
	[NoOfConnections] [int] NULL,
	[Time] [datetime] NULL
) ON [PRIMARY]

2- Then cache all Connections statistics coming from APP servers to DB Server through a scheduled job run by any appropriate frequency basis like 1 minute..


--caching all connection statistics 

use msdb
insert into Connections_Statistics
SELECT

    A.client_net_address,
    NoOfConnections = COUNT(*),
 (SELECT  MAX(A.connect_time)    from sys.dm_exec_connections a )as [current_time]

FROM
    sys.dm_exec_connections A
		INNER JOIN sys.dm_exec_sessions B ON
			A.session_id = B.session_id
GROUP BY

    client_net_address order by client_net_address 

–Report Connection statistics through pivort query

3- Then get connections statistics coming from APP server to DB server through pivot queries below:


--Report Connection statistics through pivort query
use msdb
declare @source nvarchar (1000) ='[IP Address 1],[IP Address 2],[IP Address 3]'

declare @sql nvarchar (max) 

set @sql =N'
select * FROM  Connections_Statistics PIVOT (AVG(Noofconnections)  for  client_net_address IN( '+@source+')  )as pivot_table order by [current_time] desc '

EXEC SP_EXECUTESQL @SQL

You can find a mix of pivot query with parameterized SQL statement is there to identify IP Address sources easily within one variable

4- Hence , you can initiate now a new alert to check out APP server outages with DB server as below:


--figure out outage cases 

use msdb

declare @mesage nvarchar (200)

If not exists ( select 1 FROM  Connections_Statistics with (nolock)where
(datediff (N,[time], GETDATE()) <1 AND client_net_address='192.168.3.158')
or(datediff (N,[time], GETDATE()) <1 AND client_net_address='192.168.4.77')
or(datediff (N,[time], GETDATE()) <1  AND client_net_address='192.168.4.106') 

) 

begin 

set @mesage='It was traced a total outage between all APP Servers and DB Server ...Kindly please check out network connectivity'

EXECUTE msdb.dbo.sp_notify_operator @profile_name ='Profile_Name',@name=N'Mail group',@subject=N'App Servers Outage',@body=@mesage

end

Where it should be scheduled as job to run the same frequency basis (Recommended 1 minute to be proactive enough) to validate if no connections are exists there between any of APP servers and DB server for the last 1 minute which does mean necessarily an outage case between App and DB server and thus system standstill case which it should be notified as shown above

As we have used usually by the previous blog , we have to realize well the functionality of each DMV and here we have mainly one sys.dm_exec_connections = DMV which is specialized to get all details of connections coming from different sources describing :

• IP address source
• Session ID
• Connect time
• Used network protocol
• Encryption case
• Network packet size
• …etc

By the next blog , we will have new DMVs monitors regarding waits statistics which is indeed much useful for lots of DBA and DB Analysts

5 comments to How to configure your own DMV monitors dashboards…? (2/4)

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>