{"id":550,"date":"2015-12-30T10:56:20","date_gmt":"2015-12-30T16:56:20","guid":{"rendered":"https:\/\/jackdonnell.com\/?p=550"},"modified":"2015-12-30T12:11:23","modified_gmt":"2015-12-30T18:11:23","slug":"alwayson-availability-groups-query-to-find-latency-part-3","status":"publish","type":"post","link":"https:\/\/jackdonnell.com\/?p=550","title":{"rendered":"AlwaysOn Availability Groups &#8211; Query to Find Latency Part 3"},"content":{"rendered":"<p>Like <a href=\"https:\/\/jackdonnell.com\/?p=469\" target=\"_blank\">Part 1<\/a> and <a href=\"https:\/\/jackdonnell.com\/?p=529\" target=\"_blank\">Part 2<\/a>, I use a similar query to find latency in AlwaysOn Availability Groups. The queries will return one line for each DB in the group. Part 1 , we looked at a simple query to grab the current status of the replica. Part 2, we looked at populating a Global Temp table every minute to gain a historical perspective of our AG latency. This query will look into creating an <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/hh245116.aspx\" target=\"_blank\">email notification<\/a>\u00a0when certain criteria is met. It will send the current status\u00a0to a specific set of email recipients.<\/p>\n<p>The example will be around specific criteria set as a step used in a SQL Agent job. If you use something like this, you need to tweak it to you organization&#8217;s needs. I do suggest that you set the job to a frequency that will not overflow your inbox with notices.<\/p>\n<p>Send an <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ms186358.aspx\" target=\"_blank\">email<\/a> from server when latency for a specfic database datbase is over\u00a0x number of minutes.<\/p>\n<ul>\n<li>\u00a0Alter If statement &gt; value for specific needs. This example fires of fthe email when latency is over 60 minutes.<\/li>\n<li>\u00a0Alter &#8220;database_id = DB_ID(&#8216;MyAGDatabase&#8217;)&#8221; to specify datbase to monitor<\/li>\n<li>Alter &#8220;@recipients=N&#8217;DBA@mycompany.com;&#8221; to specify recipients. I suggest using a email distribution list over listing spefic emails.<\/li>\n<\/ul>\n<p>The example is pretty simple and intended for demonstration purposes.<\/p>\n<p><!--more--><\/p>\n<pre lang=\"tsql\">SET NOCOUNT ON \r\nGO\r\nIF (SELECT CAST((redo_queue_size\/redo_rate\/60.0) as decimal(10,2))FROM sys.dm_hadr_database_replica_states WHERE database_id = DB_ID('MyAGDatabase') and last_redone_time is not null)  &gt; 60\r\nBEGIN\r\n\r\nEXEC msdb.dbo.sp_send_dbmail\r\n@recipients=N'DBA@mycompany.com;',\r\n@subject = N'Availability Group Latency Alert',\r\n@body = N'Availability Group Latency alert has been raised.',\r\n@query=' SET NOCOUNT ON; SELECT  ''\r\nLast Commit_Time:   ''+Convert(VARCHAR(20),last_commit_time,22) +''\r\n\r\nTime Behind Primary:   ''+CAST(((DATEDIFF(s,last_commit_time,GetDate()))\/3600) as varchar) + '' hour(s), ''\r\n\r\n+ CAST((DATEDIFF(s,last_commit_time,GetDate())%3600)\/60 as varchar) + ''min, ''\r\n\r\n+ CAST((DATEDIFF(s,last_commit_time,GetDate())%60) as varchar) + '' sec \r\n\r\nRedo Queue Size (KB):   ''+CAST(redo_queue_size as VARCHAR(25))+''\r\n\r\nRedo Rate (KB\/sec):   ''+CAST(redo_rate as VARCHAR(25))+''\r\n\r\nEstimated Completion Time:   ''+CONVERT(VARCHAR(20),DATEADD(mi,(redo_queue_size\/redo_rate\/60.0),GETDATE()),22)+''\r\n\r\nEstimated Recovery Time Minutes:   ''+ CAST(CAST((redo_queue_size\/redo_rate\/60.0) as decimal(10,2)) as VARCHAR(20))+''\r\n\r\nEstimated Recovery Time Seconds:   ''+ CAST((redo_queue_size\/redo_rate) as VARCHAR(12))\r\nFROM sys.dm_hadr_database_replica_states\r\nWHERE database_id = DB_ID('MyAGDatabase') and last_redone_time is not null;',\r\n@query_result_header = 0,\r\n@importance = 'High'\r\n\r\nEND\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Like Part 1 and Part 2, I use a similar query to find latency in AlwaysOn Availability Groups. The queries will return one line for each DB in the group. Part 1 , we looked at a simple query to &hellip;<\/p>\n<p class=\"read-more\"><a href=\"https:\/\/jackdonnell.com\/?p=550\">Read more &raquo;<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"footnotes":""},"categories":[273,365,266,22,351,5],"tags":[267,371,362,367,369,303,263,364,368,315,370,249,35,360,291],"class_list":["post-550","post","type-post","status-publish","format-standard","hentry","category-administration","category-alwayson-availability-groups","category-dba","category-sql-server","category-ssms","category-t-sql","tag-admin","tag-alert","tag-availability-groups","tag-database-mail","tag-db_id","tag-dba","tag-dmv","tag-latency","tag-msdb-dbo-sp_send_dbmail","tag-query","tag-redo_queu_size","tag-script","tag-select","tag-sys-dm_hadr_database_replica_states","tag-t-sql"],"_links":{"self":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/550","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=550"}],"version-history":[{"count":8,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/550\/revisions"}],"predecessor-version":[{"id":555,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/550\/revisions\/555"}],"wp:attachment":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=550"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=550"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=550"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}