{"id":8505,"date":"2016-07-08T18:05:53","date_gmt":"2016-07-08T16:05:53","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-alwayson-direct-seeding-and-performance-considerations\/"},"modified":"2023-07-18T07:28:04","modified_gmt":"2023-07-18T05:28:04","slug":"sql-server-2016-alwayson-direct-seeding-and-performance-considerations","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-alwayson-direct-seeding-and-performance-considerations\/","title":{"rendered":"SQL Server 2016 AlwaysOn: Direct seeding and performance considerations"},"content":{"rendered":"<p>This blog post follows the <a href=\"http:\/\/dbi-services.com\/blog\/sql-server-2016-alwayson-new-way-to-add-dbs-in-ag-with-direct-seeding\/\" target=\"_blank\" rel=\"noopener noreferrer\">previous one<\/a> about new direct seeding feature shipped with SQL Server 2016. As a reminder, I had some doubts about using direct seeding with large databases because log stream is not compressed by default but I forgot the performance improvements described into the Microsoft <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/bb510411.aspx\" target=\"_blank\" rel=\"noopener noreferrer\">BOL<\/a>. I also remembered&nbsp;to talk about it a couple of months ago in this <a href=\"http:\/\/dbi-services.com\/blog\/sql-server-2016-availability-groups-and-performance-improvements\/\" target=\"_blank\" rel=\"noopener noreferrer\">blog post<\/a>. So let&#8217;s try to combine all the things with the new direct seeding feature.<\/p>\n<p>Microsoft did a good work of improving the AlwaysOn log transport layer and it could be very interesting to compare two methods: Adding a 100 GB database by using usual way that includes backup and restore operations from the primary to the secondary or using direct seeding feature. Which one is the quickest method?<\/p>\n<p>Let\u2019s just have a quick look at my test environment. Two virtual machines with the following configuration:<\/p>\n<ul>\n<li>4x Intel Core i7-3630QM 2.3 GHz<\/li>\n<li>4GB of RAM<\/li>\n<li>2 10Gbps network cards<\/li>\n<li>One disk that will host both the database data and log files on my primary replica (Samsung Portable SSD T3&nbsp;500GB with S.M.A.R.T, NCQ and TRIM)<\/li>\n<li>One disk that will host both the database data and log files on my secondary replica (Samsung Portable SSD T1&nbsp;500GB with S.M.A.R.T, NCQ and TRIM)<\/li>\n<li>One disk that will host backups (Samsung SSD 840 EVC, 250GB with S.M.A.R.T, NCQ and TRIM) used by both virtual machines<\/li>\n<\/ul>\n<p>As an aside, each SSD disk is able to deliver at least 450MB\/s and 35000 IOPS.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-9635 size-full\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-99-AG-direct-seeding-0-lab-environment-e1467998674857.jpg\" alt=\"blog 99 - AG direct seeding - 0 - lab environment\" width=\"600\" height=\"475\"><\/p>\n<p><span style=\"color: #000000; font-family: Calibri;\">I also used a custom database named <em>GestionCom<\/em> that contains roughly 100GB of data for my test. 100GB would be sufficient to get relevant results.<\/span><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-9636\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-99-AG-direct-seeding-01-db-GestionCom.jpg\" alt=\"blog 99 - AG direct seeding - 01 - db GestionCom\" width=\"337\" height=\"93\"><\/p>\n<p>Go ahead and let&#8217;s compare both synchronization methods<\/p>\n<p><strong>First test by using an usual way to add&nbsp; a database to an availability group<\/strong><\/p>\n<p><span style=\"font-family: Calibri;\"><span style=\"color: #000000;\">As said earlier, my first test will consist in using the usual way so far to add a database to an availability group. Let\u2019s say that we may use 3 ways for data synchronization: FULL, join only and skip initial synchronization. We will use the first method for this test that <\/span>includes <span style=\"color: #000000;\">all the steps: backup and restore the concerned database and then join it to the availability group. At this point we may easily image that the most part of the time will be consumed in the backup and restore step. I also want to precise that I did not use voluntary fine tuning options like BUFFERCOUNT, MAXTRANSFERSIZE or splitting backups to several media files in order to stay compliant with the availability group wizard. <\/span><\/span><\/p>\n<table>\n<tbody>\n<tr>\n<td width=\"567\"><strong>Step<\/strong><\/td>\n<td width=\"216\"><strong>Duration<\/strong><\/td>\n<\/tr>\n<tr>\n<td width=\"567\">Backup database to backup local disk (primary)WITH CHECKSUM, COMPRESSION<\/td>\n<td width=\"216\">06\u201955\u2019\u2019<\/td>\n<\/tr>\n<tr>\n<td width=\"567\">Restore database from network share (secondary)WITH CHECKSUM, NORECOVERY<\/td>\n<td width=\"216\">17\u201910\u2019\u2019<\/td>\n<\/tr>\n<tr>\n<td width=\"567\">Join database to availability group + start synchronization<\/td>\n<td width=\"216\">00\u201901\u2019\u2019<\/td>\n<\/tr>\n<tr>\n<td width=\"567\"><strong>Total<\/strong><\/td>\n<td width=\"216\">24\u201906\u2019\u2019<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><span style=\"color: #000000; font-family: Calibri;\">What about resource consumption? <\/span><\/p>\n<p>On the primary &#8230;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-9637 size-full\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-99-AG-direct-seeding-10-CPU-and-disk-activity-during-backup-e1467999039777.jpg\" alt=\"blog 99 - AG direct seeding - 10 - CPU and disk activity during backup\" width=\"800\" height=\"127\"><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-9638 size-full\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-99-AG-direct-seeding-1-network-bandwith-during-restore-from-primary-e1467999577951.jpg\" alt=\"blog 99 - AG direct seeding - 1 - network bandwith during restore from primary\" width=\"300\" height=\"297\"><\/p>\n<p>On the secondary &#8230;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-9639\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-99-AG-direct-seeding-11-CPU-and-disk-activity-during-restore-e1467999111391.jpg\" alt=\"blog 99 - AG direct seeding - 11 - CPU and disk activity during restore\" width=\"800\" height=\"126\"><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-9640 size-full\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-99-AG-direct-seeding-2-network-bandwith-during-restore-from-secondary-e1467999548687.jpg\" alt=\"blog 99 - AG direct seeding - 2 - network bandwith during restore from secondary\" width=\"300\" height=\"295\"><\/p>\n<p>CPU utilization is equal to 35% on average during the test. Moreover, disk write throughput seems to stagnate to 130 MB\/s on average and includes both backup and restore activities. The network throughput utilization seems also to stagnate between 135 Mbps and 174 Mbps according to my test.<\/p>\n<p>So it is clear that my environment is under-used regarding resource consumption in this first test.<\/p>\n<p><strong>Second test by using new direct seeding method<\/strong><\/p>\n<p>This time I will use the new database deployment method: direct seeding. As said in my previous blog, using this feature will simplify a lot the adding database process but what about the synchronization speed and resource consumption in this case?<\/p>\n<p>Well, to get a good picture of what happens during the seeding process, we will use different tools as the new <em>sys.dm_hadr_automatic_seeding<\/em> DMV and extended events as well. Extended events will help us to understand what happens under the cover in this case but to measure only the time duration of the operation we don\u2019t need them. If you look at the event list as well as categories, you will probably notice a new <em>dbseed <\/em>category available that corresponds to the direct seeding. Events in this category are only available from the debug channel. That\u2019s fine because we want to track when the seeding process starts, when it finishes and what\u2019s happen between these two events (like failure, timeout, progress). By the way, the <em>hadr_physical_progress<\/em> may be very useful to get a picture of network activity for the concerned seeding session if your network card is shared between other sessions or availability group replication activities. In my case, I\u2019m the only one and I will get this information directly from the task manager panel.<\/p>\n<p>So let\u2019s create the extended event session:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">CREATE EVENT SESSION [hadr_direct_seeding] \nON SERVER \nADD EVENT sqlserver.hadr_automatic_seeding_start\n(\n    ACTION(sqlserver.database_name,sqlserver.sql_text)\n\t)\n,\nADD EVENT sqlserver.hadr_automatic_seeding_state_transition\n(\n    ACTION(sqlserver.database_name,sqlserver.sql_text)\n),\nADD EVENT sqlserver.hadr_automatic_seeding_success\n(\n    ACTION(sqlserver.database_name,sqlserver.sql_text)\n),\nADD EVENT sqlserver.hadr_automatic_seeding_timeout\n(\n    ACTION(sqlserver.database_name,sqlserver.sql_text)\n),\nADD EVENT sqlserver.hadr_physical_seeding_progress\n(\n    ACTION(sqlserver.database_name,sqlserver.sql_text)\n)\nADD TARGET package0.event_file\n(\n\tSET filename = N'hadr_direct_seeding',\n\tmax_file_size = (2048), \n\tmax_rollover_files = (10))\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 = OFF\n)\nGO<\/pre>\n<p>And after adding the <em>GestionCom<\/em> database to the <em>TestGrp<\/em> availability group, the direct seeding feature comes into play. Honestly, it was a very big surprise! Let\u2019s take a look at the network utilization:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-9642 size-full\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-99-AG-direct-seeding-3-network-bandwith-during-seeding-e1467999527150.jpg\" alt=\"blog 99 - AG direct seeding - 3 - network bandwith during seeding\" width=\"600\" height=\"300\"><\/p>\n<p><span style=\"color: #000000; font-family: Calibri;\">A network usage of 2.2 Gbps on average this time! The direct seeding feature provides a better use of the network bandwidth and we may understand clearly why efforts have been made by Microsoft to improve the synchronization process. <\/span><\/p>\n<p><span style=\"color: #000000; font-family: Calibri;\">Let\u2019s now move on the CPU and disk utilization respectively from the primary and then the secondary <\/span><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-9643\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-99-AG-direct-seeding-5-disk-throughput-during-seeding-from-primary-e1467999679529.jpg\" alt=\"blog 99 - AG direct seeding - 5 - disk throughput during seeding from primary\" width=\"800\" height=\"88\"><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-9644\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-99-AG-direct-seeding-6-disk-throughput-during-seeding-from-secondary-e1467999731314.jpg\" alt=\"blog 99 - AG direct seeding - 6 - disk throughput during seeding from secondary\" width=\"800\" height=\"126\"><\/p>\n<p>~ 350 up to 450 MB\/s on each side (gain x2) but an increase of the CPU utilization up to 70% during my test (increase x2). So a better resource usage but at the cost of a higher CPU utilization \u2026<\/p>\n<p>Let\u2019s finish by looking at the <em>sys.dm_hadr_automatic_seeding <\/em>DMV that provides the answer to the question: are we faster in this case?<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">select\n    ag.name as aag_name,\n    ar.replica_server_name,\n    d.name as database_name,\n    has.current_state,\n    has.failure_state_desc as failure_state,\n    has.error_code,\n    has.performed_seeding,\n\tDATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), has.start_time) as start_time,\n\tDATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), has.completion_time) as completion_time,\n    has.number_of_attempts\nfrom sys.dm_hadr_automatic_seeding as has\njoin sys.availability_groups as ag\n    on ag.group_id = has.ag_id\njoin sys.availability_replicas as ar\n    on ar.replica_id = has.ag_remote_replica_id\njoin sys.databases as d\n    on d.group_database_id = has.ag_db_id<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-9645\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-99-AG-direct-seeding-7-DMV-stats-e1467999801289.jpg\" alt=\"blog 99 - AG direct seeding - 7 - DMV stats\" width=\"800\" height=\"78\"><\/p>\n<p>And the answer is yes as we may expect! Only 8 minutes (gain x3) to replicate and to synchronize the <em>GestionCom<\/em> database between the two high available replicas compared to the first method. But that\u2019s not all \u2026 let\u2019s focus on the redo thread activity from the secondary and you may notice a very interesting rate value (~ 12 MB\/s). I don\u2019t remember to have seen this value with current availability groups at customer places. This is the second improvement made by Microsoft concerned that has introduce parallel redo capability. As a reminder, before SQL Server 2016, there is only one redo thread per database. In this context, a single redo thread simply could not keep up with applying the changes as persisted in the log.<\/p>\n<p>From the secondary, we may see some changes by looking at the <em>sys.dm_exec_requests<\/em> DMV:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">select \n\tr.command, \n\tr.status,\n\tr.wait_type,\n\tr.wait_time,\n\tr.last_wait_type,\n\tr.scheduler_id\nfrom sys.dm_exec_requests as r\nwhere r.command like '%REDO%'\norder by r.scheduler_id<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-9646\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-99-AG-direct-seeding-70-parallel-redo-thread-e1467999888923.jpg\" alt=\"blog 99 - AG direct seeding - 70 - parallel redo thread\" width=\"800\" height=\"105\"><\/p>\n<p>Using direct seeding is definitely a solution to take into account to our future database deployment but I think we have to keep in mind two things according to this test: CPU and network consumption from seeding activity may impact the performance of other applications and vis-versa. In real world, there are good chances to be in this situation.<\/p>\n<p>Finally let\u2019s have a look at the extend event output. In respect of what we want to highlight in this blog post, we don\u2019t get any other valuable information but one thing got my attention: LIMIT_CONCURRENT_BACKUPS value from the current value column (underlined in red). What does it mean exactly?&nbsp;Let&#8217;s talk about it&nbsp;&nbsp;in a next blog post because this is a little bit out of scope of the main subject.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-9648 size-full\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-99-AG-direct-seeding-12-Xe-with-direct-seeding-e1468000015171.jpg\" alt=\"blog 99 - AG direct seeding - 12 - Xe with direct seeding\" width=\"800\" height=\"290\"><\/p>\n<p><strong>Third test by using direct seeding and compression<\/strong><\/p>\n<p>Let\u2019s talk about the last test I performed. I used direct seeding without compression in the previous test so SQL Server didn\u2019t compress the data stream by default in this case. However we may force SQL Server to use compression by setting a special trace flag 9567. After all, we want to avoid direct seeding flooding the wire and impacting the existing workload from other applications.<\/p>\n<p>I have to admit that enabling compression with direct seeding is not so obvious. For instance I didn\u2019t see any difference from the DMVs that indicates we\u2019re using compression. (<em>is_compression_enabled<\/em> column from the <em>sys.dm_hadr_physical_seeding_stats<\/em> DMV is always equal to 0 regardless we use or not compression). The only obvious difference comes from the network throughput usage that is lower with compression (gain x 2.5). However I noticed an important increase of CPU utilization near from 100% on the primary in my case.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-9649\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-99-AG-direct-seeding-8-resource-with-seeding-and-compression-from-primary-e1468000114422.jpg\" alt=\"blog 99 - AG direct seeding - 8 - resource with seeding and compression from primary\" width=\"800\" height=\"261\"><\/p>\n<p><span style=\"font-family: Calibri;\"><span style=\"color: #000000;\">What about seeding time? Well, I didn\u2019t notice any gain on this field. <\/span>Does <span style=\"color: #000000;\">compression allow to save network bandwidth? Maybe \u2026 hard to say with only&nbsp;this test&nbsp;and one specific&nbsp;environment. <\/span><\/span><\/p>\n<p><span style=\"color: #000000; font-family: Calibri;\">I tried to add 3 VCPUs to each replica and leave one VCPU to the system so a total number of 7 VCPUS dedicated for SQL Server use. <\/span><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-9650\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-99-AG-direct-seeding-12-cpu-usage-during-seeding-with-compression-8-vcpus-e1468000206634.jpg\" alt=\"blog 99 - AG direct seeding - 12 - cpu usage during seeding with compression 8 vcpus\" width=\"800\" height=\"372\"><\/p>\n<p><span style=\"font-family: Calibri;\"><span style=\"color: #000000;\">At this point, I&nbsp; admit to be a little bit surprising and I wonder if compression uses correctly all the available processors regarding the uneven distribution of CPU resource usage. The above picture is a good representation of what I saw during other tests I&nbsp;performed with compression. In addition, I didn\u2019t see <\/span>any <span style=\"color: #000000;\">obvious performance gain in terms of duration except that wire is <\/span>less <span style=\"color: #000000;\">used.&nbsp;I&#8217;m a little bit disappointed by compressiion but once again it is still much too early to draw a conclision&nbsp;and I\u2019m looking forward direct seeding in action at my customers with real production infrastructure.<\/span><\/span><\/p>\n<p>The bottom line is that direct seeding is a very promising feature and I love it because it is the direct visible part of the AlwaysOn performance improvements shipped with SQL Server 2016. However, and this is my personal opinion and not a&nbsp; recommandation, I think we&nbsp;don&#8217;t let it fool&nbsp;us and consider to use direct seeding carefully according to your workload and available resources. Fortunately, in most cases it will be suitable.<\/p>\n<p><span style=\"color: #000000; font-family: Calibri;\">Stay tuned! <\/span><\/p>\n<p>By David Barbarin<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This blog post follows the previous one about new direct seeding feature shipped with SQL Server 2016. As a reminder, I had some doubts about using direct seeding with large databases because log stream is not compressed by default but I forgot the performance improvements described into the Microsoft BOL. I also remembered&nbsp;to talk about [&hellip;]<\/p>\n","protected":false},"author":26,"featured_media":8520,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,368],"tags":[466,297,860,67,874,566],"type_dbi":[],"class_list":["post-8505","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database-administration-monitoring","category-development-performance","tag-alwayson","tag-availability-groups","tag-direct-seeding","tag-performance","tag-redo","tag-sql-server-2016"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.5) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>SQL Server 2016 AlwaysOn: Direct seeding and performance<\/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-2016-alwayson-direct-seeding-and-performance-considerations\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server 2016 AlwaysOn: Direct seeding and performance considerations\" \/>\n<meta property=\"og:description\" content=\"This blog post follows the previous one about new direct seeding feature shipped with SQL Server 2016. As a reminder, I had some doubts about using direct seeding with large databases because log stream is not compressed by default but I forgot the performance improvements described into the Microsoft BOL. I also remembered&nbsp;to talk about [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-alwayson-direct-seeding-and-performance-considerations\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-07-08T16:05:53+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-07-18T05:28:04+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-99-AG-direct-seeding-0-lab-environment-e1467998674857-1.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"600\" \/>\n\t<meta property=\"og:image:height\" content=\"475\" \/>\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=\"10 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-2016-alwayson-direct-seeding-and-performance-considerations\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-alwayson-direct-seeding-and-performance-considerations\\\/\"},\"author\":{\"name\":\"Microsoft Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/bfab48333280d616e1170e7369df90a4\"},\"headline\":\"SQL Server 2016 AlwaysOn: Direct seeding and performance considerations\",\"datePublished\":\"2016-07-08T16:05:53+00:00\",\"dateModified\":\"2023-07-18T05:28:04+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-alwayson-direct-seeding-and-performance-considerations\\\/\"},\"wordCount\":1642,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-alwayson-direct-seeding-and-performance-considerations\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/blog-99-AG-direct-seeding-0-lab-environment-e1467998674857-1.jpg\",\"keywords\":[\"AlwaysOn\",\"Availability groups\",\"direct seeding\",\"Performance\",\"redo\",\"SQL Server 2016\"],\"articleSection\":[\"Database Administration &amp; Monitoring\",\"Development &amp; Performance\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-alwayson-direct-seeding-and-performance-considerations\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-alwayson-direct-seeding-and-performance-considerations\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-alwayson-direct-seeding-and-performance-considerations\\\/\",\"name\":\"SQL Server 2016 AlwaysOn: Direct seeding and performance\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-alwayson-direct-seeding-and-performance-considerations\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-alwayson-direct-seeding-and-performance-considerations\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/blog-99-AG-direct-seeding-0-lab-environment-e1467998674857-1.jpg\",\"datePublished\":\"2016-07-08T16:05:53+00:00\",\"dateModified\":\"2023-07-18T05:28:04+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/bfab48333280d616e1170e7369df90a4\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-alwayson-direct-seeding-and-performance-considerations\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-alwayson-direct-seeding-and-performance-considerations\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-alwayson-direct-seeding-and-performance-considerations\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/blog-99-AG-direct-seeding-0-lab-environment-e1467998674857-1.jpg\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/blog-99-AG-direct-seeding-0-lab-environment-e1467998674857-1.jpg\",\"width\":600,\"height\":475},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-alwayson-direct-seeding-and-performance-considerations\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server 2016 AlwaysOn: Direct seeding and performance considerations\"}]},{\"@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 2016 AlwaysOn: Direct seeding and performance","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-2016-alwayson-direct-seeding-and-performance-considerations\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server 2016 AlwaysOn: Direct seeding and performance considerations","og_description":"This blog post follows the previous one about new direct seeding feature shipped with SQL Server 2016. As a reminder, I had some doubts about using direct seeding with large databases because log stream is not compressed by default but I forgot the performance improvements described into the Microsoft BOL. I also remembered&nbsp;to talk about [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-alwayson-direct-seeding-and-performance-considerations\/","og_site_name":"dbi Blog","article_published_time":"2016-07-08T16:05:53+00:00","article_modified_time":"2023-07-18T05:28:04+00:00","og_image":[{"width":600,"height":475,"url":"http:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-99-AG-direct-seeding-0-lab-environment-e1467998674857-1.jpg","type":"image\/jpeg"}],"author":"Microsoft Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Microsoft Team","Est. reading time":"10 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-alwayson-direct-seeding-and-performance-considerations\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-alwayson-direct-seeding-and-performance-considerations\/"},"author":{"name":"Microsoft Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4"},"headline":"SQL Server 2016 AlwaysOn: Direct seeding and performance considerations","datePublished":"2016-07-08T16:05:53+00:00","dateModified":"2023-07-18T05:28:04+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-alwayson-direct-seeding-and-performance-considerations\/"},"wordCount":1642,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-alwayson-direct-seeding-and-performance-considerations\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-99-AG-direct-seeding-0-lab-environment-e1467998674857-1.jpg","keywords":["AlwaysOn","Availability groups","direct seeding","Performance","redo","SQL Server 2016"],"articleSection":["Database Administration &amp; Monitoring","Development &amp; Performance"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-2016-alwayson-direct-seeding-and-performance-considerations\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-alwayson-direct-seeding-and-performance-considerations\/","url":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-alwayson-direct-seeding-and-performance-considerations\/","name":"SQL Server 2016 AlwaysOn: Direct seeding and performance","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-alwayson-direct-seeding-and-performance-considerations\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-alwayson-direct-seeding-and-performance-considerations\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-99-AG-direct-seeding-0-lab-environment-e1467998674857-1.jpg","datePublished":"2016-07-08T16:05:53+00:00","dateModified":"2023-07-18T05:28:04+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-alwayson-direct-seeding-and-performance-considerations\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-2016-alwayson-direct-seeding-and-performance-considerations\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-alwayson-direct-seeding-and-performance-considerations\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-99-AG-direct-seeding-0-lab-environment-e1467998674857-1.jpg","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-99-AG-direct-seeding-0-lab-environment-e1467998674857-1.jpg","width":600,"height":475},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-alwayson-direct-seeding-and-performance-considerations\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"SQL Server 2016 AlwaysOn: Direct seeding and performance considerations"}]},{"@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\/8505","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=8505"}],"version-history":[{"count":2,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/8505\/revisions"}],"predecessor-version":[{"id":26795,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/8505\/revisions\/26795"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/8520"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=8505"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=8505"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=8505"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=8505"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}