{"id":8688,"date":"2016-08-02T14:42:44","date_gmt":"2016-08-02T12:42:44","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/sql-server-alwayson-troubleshooting-improvements-with-new-service-packs\/"},"modified":"2016-08-02T14:42:44","modified_gmt":"2016-08-02T12:42:44","slug":"sql-server-alwayson-troubleshooting-improvements-with-new-service-packs","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/sql-server-alwayson-troubleshooting-improvements-with-new-service-packs\/","title":{"rendered":"SQL Server AlwaysOn: troubleshooting improvements with new service packs"},"content":{"rendered":"

As promised in my <\/span>latest blog<\/span><\/a>, I will talk about improvements in the detection of the availability group replication latency. As a reminder, replication latency between high available replicas may be monitored in different manners so far.<\/span><\/p>\n

Firstly, in my experience I had the opportunity to use different performance counters as \u201cDatabase Replica:Transaction Delay\u201d and \u201cDatabas Replica:Mirrored Write Transactions\/sec\u201d in order to get a good picture of the latency over the time after implementing an availability group. I have also experienced performance issues at customer places where referring to the HADR_SYNC_COMMIT wait type was also very valuable. However, in both cases experiencing high transaction delay means only that the primary is waiting on response from remote replica that the log block has been hardened. This is a relevant information for sure but this is only the symptom. Indeed, at this point we are not aware of where the issue is occurring exactly and we have to figure out what the root cause of this latency is. Is the network involved or maybe log flushing activity on the secondary or both? Well, it is sure that further investigations will be necessary to locate and get rid of the root cause.<\/p>\n

When I discovered the new diagnostic features from recent service packs of SQL Server, I enjoyed to see that new extended events were added and will allow to respond easier to the previous request. Before going further to the new extended events study, let\u2019s say that I learned a lot by attending to the very interesting web meeting about AlwaysOn troubleshooting improvements given by Amit Banerjee<\/a> (Senior PM Microsoft for Tiger Team). During this web meeting, he provided a lot of useful information about new ways to diagnostic AlwaysOn architectures including the new extended events.<\/p>\n

You may find out an exhaustive list of new \/ enhanced extended events and their corresponding performance counters by referring to the Microsoft KB3173156<\/a> but let me summarize the main benefit we may get. Well, we have now a complete picture of the replication stack and processing time value at each level of the architecture as you may see below:<\/p>\n

\"blog<\/a><\/span><\/p>\n

For an easier understanding, I tried to draw a picture of the AlwaysOn replication process by placing the extended events at their correct location in the replication stack. Please feel free to comment if you find out any mistakes. <\/span><\/p>\n

 <\/p>\n

\"blog<\/a><\/span><\/p>\n

As said earlier, these new extended events will probably make some troubleshooting stuffs easier and in this blog post I would like to focus on two latency issues I faced at customer places where I noticed high HADR_SYNC_COMMIT wait type values but the root cause was not the same.<\/p>\n

I decided to simulate these two issues on my lab environment (one simple architecture that includes one availability group, two replicas involved in synchronous replication and run with SQL Server 2014 SP2). My main goal was to check if the new extended events were able to locate quickly where the root cause is located in each case.<\/p>\n

So the first thing to do is to implement and to enable a customized AlwaysOn extended event session that includes all the events displayed in the above picture on each side (both primary and secondary). I used directly the definition of the extended event shared by Amit Banerjee on GitHub<\/a>.<\/p>\n

CREATE EVENT SESSION [AlwaysOn_Data_Movement_Tracing] \nON SERVER \nADD EVENT sqlserver.hadr_apply_log_block,\nADD EVENT sqlserver.hadr_capture_filestream_wait,\nADD EVENT sqlserver.hadr_capture_log_block,\nADD EVENT sqlserver.hadr_capture_vlfheader,\nADD EVENT sqlserver.hadr_database_flow_control_action,\nADD EVENT sqlserver.hadr_db_commit_mgr_harden,\nADD EVENT sqlserver.hadr_log_block_compression,\nADD EVENT sqlserver.hadr_log_block_decompression,\nADD EVENT sqlserver.hadr_log_block_group_commit,\nADD EVENT sqlserver.hadr_log_block_send_complete,\nADD EVENT sqlserver.hadr_lsn_send_complete,\nADD EVENT sqlserver.hadr_receive_harden_lsn_message,\nADD EVENT sqlserver.hadr_send_harden_lsn_message,\nADD EVENT sqlserver.hadr_transport_flow_control_action,\nADD EVENT sqlserver.hadr_transport_receive_log_block_message,\nADD EVENT sqlserver.log_block_pushed_to_logpool,\nADD EVENT sqlserver.log_flush_complete,\nADD EVENT sqlserver.log_flush_start,\nADD EVENT sqlserver.recovery_unit_harden_log_timestamps,\nADD EVENT ucs.ucs_connection_send_msg\n(\n    WHERE ([correlation_id]<>(0))\n) \nADD TARGET package0.event_file\n(\n\tSET filename=N'AlwaysOn_Data_Movement_Tracing.xel',max_rollover_files=(100)\n)\nWITH \n(\n\tMAX_MEMORY=4096 KB,\n\tEVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,\n\tMAX_DISPATCH_LATENCY=30 SECONDS,\n\tMAX_EVENT_SIZE=0 KB,\n\tMEMORY_PARTITION_MODE=NONE,\n\tTRACK_CAUSALITY=OFF,\n\tSTARTUP_STATE=ON\n)\nGO<\/pre>\n
Firstly, I have to warn about the use of such trace because it may generate a large amount of data depending mainly on the workload activity. In my test scenario, the extended event session has generated up to 4GB of events in a time interval of 20 minutes. So we will have probably to think about some customizations that might be needed according the context in order to minimize the overall performance impact and the disk usage as well. <\/span><\/p>\n
Then, after collecting extended events data, I experienced a very long execution time and a high tempdb usage as well by using the initial extraction script provided by Amit Banerjee on GitHub. In my case, the script took approximatively 9 hours to finish on my lab environment with 4 VCPUs and 4GB of RAM. This is definitely not a good way to perform quickly further analysis on collected data. My first feeling was the script was designed for the demonstration purpose with few data. So in order to handle larger collected data, I decided to modify the initial version of the script to reduce the extract duration time. It took less than 2h for the same amount of data in my case). You will also notice that I reduced the number of extracted data to the minimum in order to meet my own requirements (I only have two replicas in my case). But let\u2019s say that I\u2019m still in a process of improving the performance of the extraction script in order to meet more realistic production scenarios. So here my\u00a0own version of the script\u00a0used in this blog post:<\/span><\/p>\n
SET NOCOUNT ON\n\nIF OBJECT_ID('tempdb..#xe_AlwaysOn_Data_Movement_Tracing', 'U') IS NOT NULL\n\tDROP TABLE #xe_AlwaysOn_Data_Movement_Tracing;\n\n\nSELECT\n\tobject_name as event_name,\n\tCONVERT(XML,Event_data) AS event_data\nINTO #xe_AlwaysOn_Data_Movement_Tracing\nFROM sys.fn_xe_file_target_read_file(\n\t\t\t'C:\\Program Files\\Microsoft SQL Server\\MSSQL12.SQL14\\MSSQL\\Log\\AlwaysOn_Data_Movement_Tracing_0_*.xel', \n\t\t\tnull, null, null) as xe\nwhere object_name in ('hadr_log_block_group_commit',\n\t\t\t\t\t\t'log_block_pushed_to_logpool',\n\t\t\t\t\t\t'log_flush_start',\n\t\t\t\t\t\t'log_flush_complete',\n\t\t\t\t\t\t'hadr_log_block_compression',\n\t\t\t\t\t\t'hadr_capture_log_block',\n\t\t\t\t\t\t'hadr_capture_filestream_wait',\n\t\t\t\t\t\t'hadr_log_block_send_complete',\n\t\t\t\t\t\t'hadr_receive_harden_lsn_message',\n\t\t\t\t\t\t'hadr_db_commit_mgr_harden',\n\t\t\t\t\t\t'recovery_unit_harden_log_timestamps',\n\t\t\t\t\t\t'hadr_capture_vlfheader',\n\t\t\t\t\t\t'hadr_log_block_decompression',\n\t\t\t\t\t\t'hadr_apply_log_block',\n\t\t\t\t\t\t'hadr_send_harden_lsn_message',\n\t\t\t\t\t\t'hadr_log_block_decompression',\n\t\t\t\t\t\t'hadr_lsn_send_complete',\n\t\t\t\t\t\t'hadr_transport_receive_log_block_message');\n\n\nIF OBJECT_ID('dbo.DMReplicaEvents', 'U') IS NOT NULL\n\tDROP TABLE dbo.DMReplicaEvents_primary;\n\nCREATE TABLE [dbo].[DMReplicaEvents_primary](\n\t[server_name] [nvarchar](128) NULL,\n\t[event_name] [nvarchar](60) NOT NULL,\n\t[log_block_id] [bigint] NULL,\n\t[database_id] [int] NULL,\n\t[processing_time] [bigint] NULL,\n\t[start_timestamp] [bigint] NULL,\n\t[publish_timestamp] [datetimeoffset](7) NULL,\n\t[log_block_size] [int] NULL,\n\t[target_availability_replica_id] [uniqueidentifier] NULL,\n\t[mode] [bigint] NULL\n) ON [PRIMARY]\n\n\nINSERT INTO DMReplicaEvents_Primary\nSELECT \n\t@@SERVERNAME as server_name,\n\tevent_name,\n\txe.event_data.value('(\/event\/data[@name=\"log_block_id\"]\/value)[1]','bigint') AS log_block_id,\n\txe.event_data.value('(\/event\/data[@name=\"database_id\"]\/value)[1]','int') AS database_id,\n\tCASE event_name \n\t\tWHEN 'hadr_db_commit_mgr_harden' THEN xe.event_data.value('(\/event\/data[@name=\"time_to_commit\"]\/value)[1]','bigint')\n\t\tWHEN 'hadr_apply_log_block' THEN xe.event_data.value('(\/event\/data[@name=\"total_processing_time\"]\/value)[1]','bigint')\n\t\tWHEN 'hadr_log_block_send_complete' THEN xe.event_data.value('(\/event\/data[@name=\"total_processing_time\"]\/value)[1]','bigint')\n\t\tWHEN 'hadr_lsn_send_complete' THEN xe.event_data.value('(\/event\/data[@name=\"total_processing_time\"]\/value)[1]','bigint')\n\t\tWHEN 'log_flush_complete' THEN xe.event_data.value('(\/event\/data[@name=\"duration\"]\/value)[1]','bigint') * 1000\n\t\tELSE xe.event_data.value('(\/event\/data[@name=\"processing_time\"]\/value)[1]','bigint') \n\tEND AS processing_time,\n\txe.event_data.value('(\/event\/data[@name=\"start_timestamp\"]\/value)[1]','bigint') AS start_timestamp,\n\txe.event_data.value('(\/event\/@timestamp)[1]','DATETIMEOFFSET') AS publish_timestamp,\n\tCASE event_name\n\t\tWHEN 'hadr_log_block_compression' THEN xe.event_data.value('(\/event\/data[@name=\"uncompressed_size\"]\/value)[1]','int')\n\t\tWHEN 'hadr_log_block_decompression' THEN xe.event_data.value('(\/event\/data[@name=\"uncompressed_size\"]\/value)[1]','int')\n\t\tWHEN 'hadr_capture_log_block' THEN xe.event_data.value('(\/event\/data[@name=\"log_block_size\"]\/value)[1]','int')\n\t\tELSE NULL \n\tEND AS log_block_size,\n\tCASE event_name\n\t\tWHEN 'hadr_db_commit_mgr_harden' THEN xe.event_data.value('(\/event\/data[@name=\"replica_id\"]\/value)[1]','uniqueidentifier')\n\t\tWHEN 'hadr_log_block_compression' THEN xe.event_data.value('(\/event\/data[@name=\"availability_replica_id\"]\/value)[1]','uniqueidentifier')\n\t\tWHEN 'hadr_log_block_decompression' THEN xe.event_data.value('(\/event\/data[@name=\"availability_replica_id\"]\/value)[1]','uniqueidentifier')\n\t\tWHEN 'hadr_capture_log_block' THEN xe.event_data.value('(\/event\/data[@name=\"availability_replica_id\"]\/value)[1]','uniqueidentifier')\n\t\tWHEN 'hadr_capture_filestream_wait' THEN xe.event_data.value('(\/event\/data[@name=\"availability_replica_id\"]\/value)[1]','uniqueidentifier')\n\t\tWHEN 'hadr_receive_harden_lsn_message' THEN xe.event_data.value('(\/event\/data[@name=\"target_availability_replica_id\"]\/value)[1]','uniqueidentifier')\n\t\tWHEN 'hadr_transport_receive_log_block_message' THEN xe.event_data.value('(\/event\/data[@name=\"target_availability_replica_id\"]\/value)[1]','uniqueidentifier')\n\t\tWHEN 'hadr_capture_vlfheader' THEN xe.event_data.value('(\/event\/data[@name=\"availability_replica_id\"]\/value)[1]','uniqueidentifier')\n\t\tWHEN 'hadr_send_harden_lsn_message' THEN xe.event_data.value('(\/event\/data[@name=\"availability_replica_id\"]\/value)[1]','uniqueidentifier')\n\t\tELSE NULL \n\tEND AS target_availability_replica_id,\n\txe.event_data.value('(\/event\/data[@name=\"mode\"]\/value)[1]','bigint') AS mode\n--INTO DMReplicaEvents\nFROM #xe_AlwaysOn_Data_Movement_Tracing xe<\/pre>\n
Having talked about the data collector aspect, let\u2019s continue by using it against my two latency issue scenarios:<\/span><\/p>\n
 <\/p>\n
First scenario (network bandwidth issue)<\/strong><\/p>\n
My first test consisted in simulating a network bandwidth issue by limiting the maximum bandwidth of the public network card as low as possible to trigger a noticeable increase of the availability group replication latency. This is what you may see in the following picture between 19:37 and 19:42. The average transaction delay (or latency) increases accordingly up to 17ms against 2ms during normal processing.<\/span><\/p>\n
 <\/p>\n
\"blog<\/a><\/p>\n
If we take a look at the wait statistics we may found out the HADR_SYNC_COMMIT wait stat with an average value of 20ms.<\/span><\/p>\n
\"blog<\/a><\/p>\n
But at this point we only know that we are experiencing latency issues but we didn\u2019t get any additional information to prove that the network is the culprit. This is where new extended events come into play. I used Excel to consume collected data and to produce charts for a better analysis. Here the picture of the first test scenario (total processing time per event) :<\/p>\n
\"blog<\/a><\/span><\/p>\n
\"blog<\/a><\/p>\n
From the above charts, we may easily notice between 19:37 and 19:43 an increase of the hadr_db_commit_mgr_harden processing time on the primary. Other events stay constant over the time. Referring to the first replication diagram, we know this event type represents the remote response from the secondary that increases during the concerned time interval. Furthermore, from the secondary, we may see constant processing time values for all events meaning that the root cause doesn\u2019t seem to concern the secondary itself but certainly an external item between the primary and the secondary, so in this case probably the network stack. By using new extended events, we were able to point out quickly the issue and perform further investigations on the network side. <\/span><\/p>\n
 <\/p>\n
Second scenario (storage performance issue)<\/strong><\/p>\n
Let\u2019s continue with the second scenario. At first glance we may see the same kind of wait type after the second test meaning that we\u2019re facing the same latency issue as the previous scenario<\/p>\n
\"blog<\/a><\/p>\n
But this time the root cause is not the same as we may see very soon by looking at the following extended event charts<\/span><\/p>\n
 <\/p>\n
\"blog<\/a><\/p>\n
\"blog<\/a><\/p>\n
This time, we may notice a pretty obvious correlation between the two charts (primary and secondary replicas). Indeed, each time the remote response time (in other words the latency) increases from the primary, we may see the same increase of the log_flush_complete processing time from the secondary and with the same order of magnitude. In this case, we may quickly focus on database file (and storage) response time on the secondary replica. In my case I voluntary used a very slow storage to host the killerdb transaction log file as you may see below at the database file io statistics<\/p>\n
\"blog<\/a><\/p>\n
Bottom line<\/strong><\/p>\n
In this blog post, we have seen how useful may be the new extended events shipped with new service packs of SQL Server and we probably have just scratched the surface. But I think we have to keep in mind that using these new extended events has a cost and for the moment I must admit to be not confident to use blindly the initial version script of collecting on production for the reasons cited above. I have to investigate further on a better way to collect and extract data in a real situation.<\/p>\n
Happy troubleshooting! <\/span><\/p>\n
By David Barbarin<\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
As promised in my latest blog, I will talk about improvements in the detection of the availability group replication latency. As a reminder, replication latency between high available replicas may be monitored in different manners so far. Firstly, in my experience I had the opportunity to use different performance counters as \u201cDatabase Replica:Transaction Delay\u201d and […]<\/p>\n","protected":false},"author":26,"featured_media":8699,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[466,895,84,67,896,51,884,885],"type_dbi":[],"class_list":["post-8688","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database-administration-monitoring","tag-alwayson","tag-extended-events","tag-high-availability","tag-performance","tag-replication-latency","tag-sql-server","tag-sql-server-2012-sp3","tag-sql-server-2014-sp2"],"acf":[],"yoast_head":"\nSQL Server AlwaysOn: troubleshooting improvements with new service packs<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/sql-server-alwayson-troubleshooting-improvements-with-new-service-packs\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server AlwaysOn: troubleshooting improvements with new service packs\" \/>\n<meta property=\"og:description\" content=\"As promised in my latest blog, I will talk about improvements in the detection of the availability group replication latency. As a reminder, replication latency between high available replicas may be monitored in different manners so far. Firstly, in my experience I had the opportunity to use different performance counters as \u201cDatabase Replica:Transaction Delay\u201d and […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/sql-server-alwayson-troubleshooting-improvements-with-new-service-packs\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-08-02T12:42:44+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-102-AG-2014-SP2-extended-events-new-columns-1.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"464\" \/>\n\t<meta property=\"og:image:height\" content=\"227\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Microsoft Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Microsoft Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"12 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-alwayson-troubleshooting-improvements-with-new-service-packs\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-alwayson-troubleshooting-improvements-with-new-service-packs\/\"},\"author\":{\"name\":\"Microsoft Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4\"},\"headline\":\"SQL Server AlwaysOn: troubleshooting improvements with new service packs\",\"datePublished\":\"2016-08-02T12:42:44+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-alwayson-troubleshooting-improvements-with-new-service-packs\/\"},\"wordCount\":1410,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-alwayson-troubleshooting-improvements-with-new-service-packs\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-102-AG-2014-SP2-extended-events-new-columns-1.jpg\",\"keywords\":[\"AlwaysOn\",\"extended events\",\"High availability\",\"Performance\",\"replication latency\",\"SQL Server\",\"SQL Server 2012 SP3\",\"SQL Server 2014 SP2\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/sql-server-alwayson-troubleshooting-improvements-with-new-service-packs\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-alwayson-troubleshooting-improvements-with-new-service-packs\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-alwayson-troubleshooting-improvements-with-new-service-packs\/\",\"name\":\"SQL Server AlwaysOn: troubleshooting improvements with new service packs\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-alwayson-troubleshooting-improvements-with-new-service-packs\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-alwayson-troubleshooting-improvements-with-new-service-packs\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-102-AG-2014-SP2-extended-events-new-columns-1.jpg\",\"datePublished\":\"2016-08-02T12:42:44+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-alwayson-troubleshooting-improvements-with-new-service-packs\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/sql-server-alwayson-troubleshooting-improvements-with-new-service-packs\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-alwayson-troubleshooting-improvements-with-new-service-packs\/#primaryimage\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-102-AG-2014-SP2-extended-events-new-columns-1.jpg\",\"contentUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-102-AG-2014-SP2-extended-events-new-columns-1.jpg\",\"width\":464,\"height\":227},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-alwayson-troubleshooting-improvements-with-new-service-packs\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server AlwaysOn: troubleshooting improvements with new service packs\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4\",\"name\":\"Microsoft Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"caption\":\"Microsoft Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/microsoft-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"SQL Server AlwaysOn: troubleshooting improvements with new service packs","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/sql-server-alwayson-troubleshooting-improvements-with-new-service-packs\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server AlwaysOn: troubleshooting improvements with new service packs","og_description":"As promised in my latest blog, I will talk about improvements in the detection of the availability group replication latency. As a reminder, replication latency between high available replicas may be monitored in different manners so far. Firstly, in my experience I had the opportunity to use different performance counters as \u201cDatabase Replica:Transaction Delay\u201d and […]","og_url":"https:\/\/www.dbi-services.com\/blog\/sql-server-alwayson-troubleshooting-improvements-with-new-service-packs\/","og_site_name":"dbi Blog","article_published_time":"2016-08-02T12:42:44+00:00","og_image":[{"width":464,"height":227,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-102-AG-2014-SP2-extended-events-new-columns-1.jpg","type":"image\/jpeg"}],"author":"Microsoft Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Microsoft Team","Est. reading time":"12 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-alwayson-troubleshooting-improvements-with-new-service-packs\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-alwayson-troubleshooting-improvements-with-new-service-packs\/"},"author":{"name":"Microsoft Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4"},"headline":"SQL Server AlwaysOn: troubleshooting improvements with new service packs","datePublished":"2016-08-02T12:42:44+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-alwayson-troubleshooting-improvements-with-new-service-packs\/"},"wordCount":1410,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-alwayson-troubleshooting-improvements-with-new-service-packs\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-102-AG-2014-SP2-extended-events-new-columns-1.jpg","keywords":["AlwaysOn","extended events","High availability","Performance","replication latency","SQL Server","SQL Server 2012 SP3","SQL Server 2014 SP2"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-alwayson-troubleshooting-improvements-with-new-service-packs\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-alwayson-troubleshooting-improvements-with-new-service-packs\/","url":"https:\/\/www.dbi-services.com\/blog\/sql-server-alwayson-troubleshooting-improvements-with-new-service-packs\/","name":"SQL Server AlwaysOn: troubleshooting improvements with new service packs","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-alwayson-troubleshooting-improvements-with-new-service-packs\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-alwayson-troubleshooting-improvements-with-new-service-packs\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-102-AG-2014-SP2-extended-events-new-columns-1.jpg","datePublished":"2016-08-02T12:42:44+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-alwayson-troubleshooting-improvements-with-new-service-packs\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-alwayson-troubleshooting-improvements-with-new-service-packs\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-alwayson-troubleshooting-improvements-with-new-service-packs\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-102-AG-2014-SP2-extended-events-new-columns-1.jpg","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-102-AG-2014-SP2-extended-events-new-columns-1.jpg","width":464,"height":227},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-alwayson-troubleshooting-improvements-with-new-service-packs\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"SQL Server AlwaysOn: troubleshooting improvements with new service packs"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4","name":"Microsoft Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","caption":"Microsoft Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/microsoft-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/8688","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/26"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=8688"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/8688\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/8699"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=8688"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=8688"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=8688"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=8688"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}