{"id":529,"date":"2015-12-30T10:09:28","date_gmt":"2015-12-30T16:09:28","guid":{"rendered":"https:\/\/jackdonnell.com\/?p=529"},"modified":"2015-12-30T10:15:53","modified_gmt":"2015-12-30T16:15:53","slug":"alwayson-availability-groups-query-to-find-latency-part-2","status":"publish","type":"post","link":"https:\/\/jackdonnell.com\/?p=529","title":{"rendered":"AlwaysOn Availability Groups &#8211; Query to Find Latency Part 2"},"content":{"rendered":"<p>Sometimes there is a need to maintain some historical content on how current your Availability Group is performing. The following script builds off the original script in <a href=\"https:\/\/jackdonnell.com\/?p=469\" target=\"_blank\">Part 1<\/a>. It populates a global temp table with information every minute. I really got the idea from <a href=\"http:\/\/www.brentozar.com\/blitz\/\" target=\"_blank\">Brent Ozar&#8217;s sp_SQL Blitz<\/a>.  I took the road of least intrusive by creating the ##LatencyCheck_tbl global temp table.   <a href=\"https:\/\/www.mssqltips.com\/sqlservertip\/4031\/sql-server-global-temporary-table-visibility\/\" target=\"_blank\">Sergey Gigoyan<\/a> has a good post on global temp tables and good links at the end of the post, too. <\/p>\n<p>The ##LatencyCheck_tbl temp table is populated after 1 minute and every minute until cancelled. Need to make sue the table gets dropped after you have done you analysis. It is a good idea to keep an eye on the size of the table, too (execute sp_spaceused &#8216;##LatencyCheck_tbl&#8217;). Like any temp table, service restart will drop the table the process cancelled. If this is something you would like always run. you can look into creating a SQL Agent job or even a procedure that auto starts. I don&#8217;t suggest doing that. If you do choose to do so, you should make sure you monitor of have a process in place to reduce the size of the temp table. Also, look into grabbing Perfmon counters and Extended Events. Everything has overhead and we do not want to succumb to the &#8220;Watcher Effect&#8221;..DAH! DAH! [Organ music]. <\/p>\n<p><!--more--><\/p>\n<pre lang=\"tsql\">\/*\r\nCreate tempdb table to capture AG metrics. \r\nIt will run until cancelled.\r\nAfter ##LatencyCheck_tbl data is exported, drop table.\r\nExample Query below.\r\n\r\njackdonnell.com\r\n*\/\r\nSET NOCOUNT ON \r\nUSE tempdb\r\nGO\r\n\r\n-- Remove exist table if found\r\nIF OBJECT_ID ('tempdb..##LatencyCheck_tbl','u' ) IS NOT NULL\r\nDROP TABLE ##LatencyCheck_tbl\r\nGO\r\n\r\n-- create temp table\r\nCREATE TABLE ##LatencyCheck_tbl\r\n(\r\n[Last Commit Time] varchar(20),\r\n[Time Behind Primary] varchar(30),\r\n[Redo Queue Size] varchar(25),\r\n[Estimated Completion Time] varchar(20),\r\n[Estimated Recovery Time Minutes] varchar(20),\r\n[Estimated Recovery Time Seconds] varchar(12),\r\n[SampleTime] DATETIME\r\n)\r\nGO\r\n\r\nUse tempdb\r\nGO\r\n-- Create GOTO loop\r\nRepeatLoop:\r\n\r\n\tBEGIN\r\n\r\n\t\tWAITFOR DELAY '00:01:00'; -- Execute every 1 minute\r\n\t\tINSERT ##LatencyCheck_tbl\r\n\t\tSELECT\r\n\t\tConvert(VARCHAR(20),last_commit_time,22) AS [Last Commit Time],\r\n\t\tCAST(((DATEDIFF(s,last_commit_time,GetDate()))\/3600) as varchar) + 'hour(s),' + CAST((DATEDIFF(s,last_commit_time,GetDate())%3600)\/60 as varchar) + 'min, '+ CAST((DATEDIFF(s,last_commit_time,GetDate())%60) as varchar) + ' sec' AS [Time Behind Primary],\r\n\t\tCAST(redo_queue_size as VARCHAR(25)) AS [Redo Queue Size],\r\n\t\tCONVERT(VARCHAR(20),DATEADD(mi,(redo_queue_size\/redo_rate\/60.0),GETDATE()),22) AS [Estimated Completion Time],\r\n\t\tCAST(CAST((redo_queue_size\/redo_rate\/60.0) as decimal(10,2)) as VARCHAR(20)) AS [Estimated Recovery Time Minutes],\r\n\t\tCAST((redo_queue_size\/redo_rate) as VARCHAR(12)) AS [Estimated Recovery Time Seconds],\r\n\t\tGETDATE() [SampleTime]\r\n\t\tfrom sys.dm_hadr_database_replica_states\r\n\t\tWHERE last_redone_time is not null\r\n\r\n\tEND;\r\nGOTO RepeatLoop -- Start process over\r\n<\/pre>\n<p>Now we have the proces running, we can use the following to query the output. From another SSMS window, execute the following query. I left some of the WHERE clause commented to show some examples.<\/p>\n<pre lang=\"tsql\">\r\nSET NOCOUNT ON \r\nGO\r\nUSE tempdb\r\nGO\r\n\r\nSELECT \r\n\tCONVERT(VARCHAR(25),CAST([SampleTime] as DATETIME),22) [SampleTime],\r\n\t[Last Commit Time],\r\n\t[Time Behind Primary],\r\n\t[Redo Queue Size],\r\n\t[Estimated Completion Time],\r\n\t[Estimated Recovery Time Minutes],\r\n\t[Estimated Recovery Time Seconds],\r\n\t--CAST([Redo Queue Size]as BIGINT)\/CAST([Estimated Recovery Time Seconds] as BIGINT) [Redo_Rate (REDO_QueueSize\/EstRecTimeSec)],\r\n\tDATEDIFF(second,[Last Commit Time],[SampleTime] )[Diff_Last_Commit_CurrentTime_sec]\r\nFROM ##LatencyCheck_tbl\r\nWHERE 1 = 1\r\nAND [SampleTime] >= DATEADD(MINUTE, -480, GETDATE())\r\n--AND [SampleTime] >= CONVERT(VARCHAR(10),GETDATE()-1,121)\r\n--and( DATEPART(hour,CAST([SampleTime] as datetime)) < =3 -- less than 4 AM\r\n--OR DATEPART(hour,CAST([SampleTime] as datetime)) >=19\r\n--) -- Greater than 7 PM\r\n\r\nORDER BY CAST([SampleTime] as DATETIME) DESC;\r\nGO\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Sometimes there is a need to maintain some historical content on how current your Availability Group is performing. The following script builds off the original script in Part 1. It populates a global temp table with information every minute. I &hellip;<\/p>\n<p class=\"read-more\"><a href=\"https:\/\/jackdonnell.com\/?p=529\">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":[363,362,303,364,315,36,295,52,360,291],"class_list":["post-529","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-alwayson","tag-availability-groups","tag-dba","tag-latency","tag-query","tag-sql","tag-sql-server","tag-ssms","tag-sys-dm_hadr_database_replica_states","tag-t-sql"],"_links":{"self":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/529","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=529"}],"version-history":[{"count":4,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/529\/revisions"}],"predecessor-version":[{"id":549,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/529\/revisions\/549"}],"wp:attachment":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=529"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=529"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=529"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}