{"id":469,"date":"2015-12-29T16:14:07","date_gmt":"2015-12-29T22:14:07","guid":{"rendered":"https:\/\/jackdonnell.com\/?p=469"},"modified":"2015-12-30T10:15:23","modified_gmt":"2015-12-30T16:15:23","slug":"alwayson-availability-groups-query-to-find-latency","status":"publish","type":"post","link":"https:\/\/practical-prints.com\/?p=469","title":{"rendered":"AlwaysOn Availability Groups &#8211; Query to Find Latency Part 1"},"content":{"rendered":"<p><a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/hh510230.aspx\" target=\"_blank\">AlwaysOn Availability Groups<\/a> latency can be a real concern. Microsoft provides a <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/hh213474.aspx\" target=\"_blank\">AlwaysON Dashboard<\/a> to monitor and some administration of Availability Groups in SQL Server Management Studio(SSMS). <a href=\"https:\/\/www.mssqltips.com\/sqlservertip\/2573\/monitor-sql-server-alwayson-availability-groups\/\" target=\"_blank\">Ben Snaidero<\/a> as an excellent posting about the dashboard and T-SQL monitoring of AG.<\/p>\n<p>The query below uses <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ff877972.aspx\" target=\"_blank\">sys.dm_hadr_database_replica_states<\/a> to query the current state. It can be run on the primary or secondary server. We use a readable secondary and it is nice to see how close to real-time updates. As a roll-your-own solution, you could create a SQL Agent job to monitor and send an email when certain criteria is met. <\/p>\n<p><!--more--><\/p>\n<pre lang=\"tsql\">\r\n\/* \r\nAlwaysON AG Monitor\r\njackdonnell.com\r\n12\/29\/2015\r\n*\/\r\nSET NOCOUNT ON\r\nGO\r\nUSE master\r\nGO\r\n\r\nSELECT CAST(DB_NAME(database_id)as VARCHAR(40)) database_name,\r\nConvert(VARCHAR(20),last_commit_time,22) last_commit_time\r\n,CAST(CAST(((DATEDIFF(s,last_commit_time,GetDate()))\/3600) as varchar) + ' hour(s), '\r\n+ CAST((DATEDIFF(s,last_commit_time,GetDate())%3600)\/60 as varchar) + ' min, '\r\n+ CAST((DATEDIFF(s,last_commit_time,GetDate())%60) as varchar) + ' sec' as VARCHAR(30)) time_behind_primary\r\n,redo_queue_size\r\n,redo_rate\r\n,CONVERT(VARCHAR(20),DATEADD(mi,(redo_queue_size\/redo_rate\/60.0),GETDATE()),22) estimated_completion_time\r\n,CAST((redo_queue_size\/redo_rate\/60.0) as decimal(10,2)) [estimated_recovery_time_minutes]\r\n,(redo_queue_size\/redo_rate) [estimated_recovery_time_seconds]\r\n,CONVERT(VARCHAR(20),GETDATE(),22) [current_time]\r\nFROM sys.dm_hadr_database_replica_states\r\nWHERE last_redone_time is not null;\r\n\r\nGO\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>AlwaysOn Availability Groups latency can be a real concern. Microsoft provides a AlwaysON Dashboard to monitor and some administration of Availability Groups in SQL Server Management Studio(SSMS). Ben Snaidero as an excellent posting about the dashboard and T-SQL monitoring of &hellip;<\/p>\n<p class=\"read-more\"><a href=\"https:\/\/practical-prints.com\/?p=469\">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,"advanced_seo_description":"","jetpack_seo_html_title":"","jetpack_seo_noindex":false,"jetpack_seo_schema_type":"","_wpcom_ai_launchpad_first_post":false,"footnotes":""},"categories":[273,365,266,22],"tags":[267,363,362,303,263,361,364,315,366,35,295,52,360,291],"class_list":["post-469","post","type-post","status-publish","format-standard","hentry","category-administration","category-alwayson-availability-groups","category-dba","category-sql-server","tag-admin","tag-alwayson","tag-availability-groups","tag-dba","tag-dmv","tag-ha","tag-latency","tag-query","tag-redo_rate","tag-select","tag-sql-server","tag-ssms","tag-sys-dm_hadr_database_replica_states","tag-t-sql"],"jetpack_shortlink":"https:\/\/wp.me\/phfTTO-7z","jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/practical-prints.com\/index.php?rest_route=\/wp\/v2\/posts\/469","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/practical-prints.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/practical-prints.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/practical-prints.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/practical-prints.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=469"}],"version-history":[{"count":15,"href":"https:\/\/practical-prints.com\/index.php?rest_route=\/wp\/v2\/posts\/469\/revisions"}],"predecessor-version":[{"id":544,"href":"https:\/\/practical-prints.com\/index.php?rest_route=\/wp\/v2\/posts\/469\/revisions\/544"}],"wp:attachment":[{"href":"https:\/\/practical-prints.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=469"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/practical-prints.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=469"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/practical-prints.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=469"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}