How to configure your own DMV 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

Follow up us on
Facebook Page , LinkedIn Group,Twitter,Networked Blogs,Facebook Group,Youtube Channel,

Spread the word

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>