{"id":1217,"date":"2015-04-14T23:02:22","date_gmt":"2015-04-14T13:02:22","guid":{"rendered":"http:\/\/ntsblog.homedev.com.au\/?p=1217"},"modified":"2015-04-14T23:48:30","modified_gmt":"2015-04-14T13:48:30","slug":"terrible-performance-sql-server-linked-server-query-wan","status":"publish","type":"post","link":"https:\/\/ntsblog.homedev.com.au\/index.php\/2015\/04\/14\/terrible-performance-sql-server-linked-server-query-wan\/","title":{"rendered":"Terrible Performance of SQL Server Linked Server query over WAN"},"content":{"rendered":"<div id=\"ntsbl-794942255\" class=\"ntsbl-before-content ntsbl-entity-placement\"><script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js?client=ca-pub-6288941070289539\" crossorigin=\"anonymous\"><\/script><ins class=\"adsbygoogle\" style=\"display:inline-block;width:728px;height:90px;\" \ndata-ad-client=\"ca-pub-6288941070289539\" \ndata-ad-slot=\"9356781486\"><\/ins> \n<script> \n(adsbygoogle = window.adsbygoogle || []).push({}); \n<\/script>\n<\/div><h2>The problem<\/h2>\n<p>I have a linked server connection between 2 SQL servers approx half the world away. One server resides in Sydney, Australia and the other in London, UK.<\/p>\n<p>I was getting terrible performance.<\/p>\n<p>To simulate this for testing purposes, on my home internet connection, I created an Azure database in Northern Europe and my local SQL server is in Sydney.<\/p>\n<p>I used the DimDate table as created in this code project example. <a href=\"http:\/\/www.codeproject.com\/Articles\/647950\/Create-and-Populate-Date-Dimension-for-Data-Wareho\" title=\"http:\/\/www.codeproject.com\/Articles\/647950\/Create-and-Populate-Date-Dimension-for-Data-Wareho\" target=\"_blank\">http:\/\/www.codeproject.com\/Articles\/647950\/Create-and-Populate-Date-Dimension-for-Data-Wareho<\/a><br \/>\n(This is a 36 column table)<\/p>\n<p>I ran this against my azure database and populated it with 45,656 records, (days between 1-Jan-1974 to 31-Dec-2099, just adjust the populate script)<\/p>\n<p>On my local sql server, I have a Linked Server connection called LSEUROPE set to use SQLNCLI &#8211; SQL Native Client, configured to connecting to the Azure database in Europe.<\/p>\n<p>On the local sql server:<\/p>\n<p>1. Download and Install Wireshark on the local server, <a href=\"https:\/\/www.wireshark.org\/download.html\" title=\"https:\/\/www.wireshark.org\/download.html\" target=\"_blank\">https:\/\/www.wireshark.org\/download.html<\/a><br \/>\n2. Download and Install Compass wildpackets free from, <a href=\"http:\/\/download.cnet.com\/Compass-Free\/3000-2085_4-75447541.html\" title=\"http:\/\/download.cnet.com\/Compass-Free\/3000-2085_4-75447541.html\" target=\"_blank\">http:\/\/download.cnet.com\/Compass-Free\/3000-2085_4-75447541.html<\/a>. This is used to visualise the throughput.<\/p>\n<p>Wireshark was enabled and set to monitor only the remote server, (capture filter &#8211; &#8220;host 10.10.0.1&#8221; where 10.10.0.1 is the IP address of the remote server).<\/p>\n<p>2 tests were executed:<\/p>\n<p><strong>Test 1 &#8211; SSMS Direct<\/strong><\/p>\n<p>1. Open SSMS and connect directly to the REMOTE server.<br \/>\n2. Start Wireshark Capture<br \/>\n3. execute &#8220;select * from dbo.DimDate&#8221;<br \/>\n4. once finished stop wireshark<br \/>\n5. Save the wireshark session as a .pcap file, (compass only opens .pcap files)<\/p>\n<p><strong>Test 2 &#8211; Linked Server<\/strong><\/p>\n<p>1. Open SSMS and connect to the LOCAL server.<br \/>\n2. Start Wireshark Capture<br \/>\n3. execute &#8220;select * from LSEUROPE.TestDB.dbo.DimDate&#8221;, (Note: OpenQuery in this instance makes no difference as all we are testing is the throughput of transferring data from the other side of the world.)<br \/>\n4. once finished stop wireshark<br \/>\n5. Save the wireshark session as a .pcap file.<\/p>\n<p><em><strong>Fig 1. SSMS Direct Test &#8211; 35 sec<\/strong><\/em><br \/>\n<a href=\"https:\/\/ntsblog.homedev.com.au\/wp-content\/uploads\/2015\/04\/SSMS_Remote.png\"><img loading=\"lazy\" decoding=\"async\" data-attachment-id=\"1219\" data-permalink=\"https:\/\/ntsblog.homedev.com.au\/ssms_remote\/\" data-orig-file=\"https:\/\/ntsblog.homedev.com.au\/wp-content\/uploads\/2015\/04\/SSMS_Remote.png\" data-orig-size=\"1427,467\" data-comments-opened=\"1\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}\" data-image-title=\"SSMS_Remote\" data-image-description=\"\" data-image-caption=\"\" data-medium-file=\"https:\/\/ntsblog.homedev.com.au\/wp-content\/uploads\/2015\/04\/SSMS_Remote-300x98.png\" data-large-file=\"https:\/\/ntsblog.homedev.com.au\/wp-content\/uploads\/2015\/04\/SSMS_Remote-1024x335.png\" src=\"https:\/\/ntsblog.homedev.com.au\/wp-content\/uploads\/2015\/04\/SSMS_Remote-1024x335.png\" alt=\"SSMS_Remote\" width=\"1024\" height=\"260\" class=\"aligncenter size-large wp-image-1219\" \/><\/a><\/p>\n<p><em><strong>Fig 2. Linked Server Test &#8211; 6m 29 sec<\/strong><\/em><br \/>\n<a href=\"https:\/\/ntsblog.homedev.com.au\/wp-content\/uploads\/2015\/04\/LSRemote.png\"><img loading=\"lazy\" decoding=\"async\" data-attachment-id=\"1221\" data-permalink=\"https:\/\/ntsblog.homedev.com.au\/lsremote\/\" data-orig-file=\"https:\/\/ntsblog.homedev.com.au\/wp-content\/uploads\/2015\/04\/LSRemote.png\" data-orig-size=\"1428,470\" data-comments-opened=\"1\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}\" data-image-title=\"LSRemote\" data-image-description=\"\" data-image-caption=\"\" data-medium-file=\"https:\/\/ntsblog.homedev.com.au\/wp-content\/uploads\/2015\/04\/LSRemote-300x99.png\" data-large-file=\"https:\/\/ntsblog.homedev.com.au\/wp-content\/uploads\/2015\/04\/LSRemote-1024x337.png\" src=\"https:\/\/ntsblog.homedev.com.au\/wp-content\/uploads\/2015\/04\/LSRemote-1024x337.png\" alt=\"LSRemote\" width=\"1024\" height=\"260\" class=\"aligncenter size-large wp-image-1221\" \/><\/a><\/p>\n<p>As you can see, throughput on the Linked server query is limited to 0.22 Mbits\/s, whilst the remote SSMS query happily averages around 3Mbits\/s (again, this is just on my standard ADSL2+ home internet connection, but I have replicated this exact same pattern at work on a 20MBps dedicated WAN link).<\/p>\n<p>There is some bottle neck that is slowing performance of the data being sent via a linked server connection!!<\/p>\n<h2>A solution of sorts<\/h2>\n<p>After much playing and testing of the various Linked server connections I have managed to get the following results over a linked server connection.<\/p>\n<p><em><strong>Fig 3<\/strong><\/em><br \/>\n<a href=\"https:\/\/ntsblog.homedev.com.au\/wp-content\/uploads\/2015\/04\/LSRemote_SNCLIDSN.png\"><img loading=\"lazy\" decoding=\"async\" data-attachment-id=\"1223\" data-permalink=\"https:\/\/ntsblog.homedev.com.au\/lsremote_snclidsn\/\" data-orig-file=\"https:\/\/ntsblog.homedev.com.au\/wp-content\/uploads\/2015\/04\/LSRemote_SNCLIDSN.png\" data-orig-size=\"1430,477\" data-comments-opened=\"1\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}\" data-image-title=\"LSRemote_SNCLIDSN\" data-image-description=\"\" data-image-caption=\"\" data-medium-file=\"https:\/\/ntsblog.homedev.com.au\/wp-content\/uploads\/2015\/04\/LSRemote_SNCLIDSN-300x100.png\" data-large-file=\"https:\/\/ntsblog.homedev.com.au\/wp-content\/uploads\/2015\/04\/LSRemote_SNCLIDSN-1024x342.png\" src=\"https:\/\/ntsblog.homedev.com.au\/wp-content\/uploads\/2015\/04\/LSRemote_SNCLIDSN-1024x342.png\" alt=\"LSRemote_SNCLIDSN\" width=\"1024\" height=\"260\" class=\"aligncenter size-large wp-image-1223\" \/><\/a><\/p>\n<p><em><strong>Fig 4<\/strong><\/em><br \/>\n<a href=\"https:\/\/ntsblog.homedev.com.au\/wp-content\/uploads\/2015\/04\/LSRemote_ODBCDSN.png\"><img loading=\"lazy\" decoding=\"async\" data-attachment-id=\"1222\" data-permalink=\"https:\/\/ntsblog.homedev.com.au\/lsremote_odbcdsn\/\" data-orig-file=\"https:\/\/ntsblog.homedev.com.au\/wp-content\/uploads\/2015\/04\/LSRemote_ODBCDSN.png\" data-orig-size=\"1429,473\" data-comments-opened=\"1\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}\" data-image-title=\"LSRemote_ODBCDSN\" data-image-description=\"\" data-image-caption=\"\" data-medium-file=\"https:\/\/ntsblog.homedev.com.au\/wp-content\/uploads\/2015\/04\/LSRemote_ODBCDSN-300x99.png\" data-large-file=\"https:\/\/ntsblog.homedev.com.au\/wp-content\/uploads\/2015\/04\/LSRemote_ODBCDSN-1024x339.png\" src=\"https:\/\/ntsblog.homedev.com.au\/wp-content\/uploads\/2015\/04\/LSRemote_ODBCDSN-1024x339.png\" alt=\"LSRemote_ODBCDSN\" width=\"1024\" height=\"260\" class=\"aligncenter size-large wp-image-1222\" \/><\/a><\/p>\n<p>Figure 4 was obtained by creating a ODBC System DSN of type ODBC Driver 11 for SQL Server. This provided the fastest performance, bringing down the same 45,656 rows in 27 seconds.<\/p>\n<p>Figure 3 was obtained by creating the ODBC System DSN using the SQL Native Client. It is now approx 46 seconds which is much better than 6 minutes, but still not the 27 seconds of the ODBC driver.<\/p>\n<p>After multiple runs I was getting similar performance regardless of the Client used in the DSN<\/p>\n<p><a href=\"https:\/\/ntsblog.homedev.com.au\/wp-content\/uploads\/2015\/04\/ODBCSystemDSN.png\"><img loading=\"lazy\" decoding=\"async\" data-attachment-id=\"1218\" data-permalink=\"https:\/\/ntsblog.homedev.com.au\/odbcsystemdsn\/\" data-orig-file=\"https:\/\/ntsblog.homedev.com.au\/wp-content\/uploads\/2015\/04\/ODBCSystemDSN.png\" data-orig-size=\"760,535\" data-comments-opened=\"1\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}\" data-image-title=\"ODBCSystemDSN\" data-image-description=\"\" data-image-caption=\"\" data-medium-file=\"https:\/\/ntsblog.homedev.com.au\/wp-content\/uploads\/2015\/04\/ODBCSystemDSN-300x211.png\" data-large-file=\"https:\/\/ntsblog.homedev.com.au\/wp-content\/uploads\/2015\/04\/ODBCSystemDSN.png\" src=\"https:\/\/ntsblog.homedev.com.au\/wp-content\/uploads\/2015\/04\/ODBCSystemDSN.png\" alt=\"ODBCSystemDSN\" width=\"760\" height=\"535\" class=\"aligncenter size-full wp-image-1218\" srcset=\"https:\/\/ntsblog.homedev.com.au\/wp-content\/uploads\/2015\/04\/ODBCSystemDSN.png 760w, https:\/\/ntsblog.homedev.com.au\/wp-content\/uploads\/2015\/04\/ODBCSystemDSN-300x211.png 300w\" sizes=\"auto, (max-width: 760px) 100vw, 760px\" \/><\/a><\/p>\n<p>In both cases Fig 3 and Fig 4, the linked server was created using the &#8220;Microsoft OLE DB Provider for ODBC Drivers&#8221; and configured the Linked server to use the System DSN.<\/p>\n<p><a href=\"https:\/\/ntsblog.homedev.com.au\/wp-content\/uploads\/2015\/04\/LSConfig.png\"><img loading=\"lazy\" decoding=\"async\" data-attachment-id=\"1220\" data-permalink=\"https:\/\/ntsblog.homedev.com.au\/lsconfig\/\" data-orig-file=\"https:\/\/ntsblog.homedev.com.au\/wp-content\/uploads\/2015\/04\/LSConfig.png\" data-orig-size=\"704,632\" data-comments-opened=\"1\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}\" data-image-title=\"LSConfig\" data-image-description=\"\" data-image-caption=\"\" data-medium-file=\"https:\/\/ntsblog.homedev.com.au\/wp-content\/uploads\/2015\/04\/LSConfig-300x269.png\" data-large-file=\"https:\/\/ntsblog.homedev.com.au\/wp-content\/uploads\/2015\/04\/LSConfig.png\" src=\"https:\/\/ntsblog.homedev.com.au\/wp-content\/uploads\/2015\/04\/LSConfig.png\" alt=\"LSConfig\" width=\"704\" height=\"632\" class=\"aligncenter size-full wp-image-1220\" srcset=\"https:\/\/ntsblog.homedev.com.au\/wp-content\/uploads\/2015\/04\/LSConfig.png 704w, https:\/\/ntsblog.homedev.com.au\/wp-content\/uploads\/2015\/04\/LSConfig-300x269.png 300w\" sizes=\"auto, (max-width: 704px) 100vw, 704px\" \/><\/a><\/p>\n<p>Now the real question, why does this seem to work?<\/p>\n<p>I am going to test using this as I am sure there will be synax type issues of changing the linked server connection, (I think ODBC doesn&#8217;t handle TEXT\/NTEXT fields)<\/p>\n<p>Microsoft, what is going on here? <\/p>\n<p>I am going to be trying to find out what the reason is for the poor performance of the &#8220;Microsoft OLEDB provider of SQL Server&#8221;, and if there is something that can be done to improve the performance.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>The problem I have a linked server connection between 2 SQL servers approx half the world away. One server resides in Sydney, Australia and the [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[13],"tags":[],"class_list":["post-1217","post","type-post","status-publish","format-standard","hentry","category-sql-server"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/posts\/1217","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/comments?post=1217"}],"version-history":[{"count":0,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/posts\/1217\/revisions"}],"wp:attachment":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/media?parent=1217"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/categories?post=1217"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/tags?post=1217"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}