This provided the fastest performance, bringing down the same 45,656 rows in 27 seconds.įigure 3 was obtained by creating the ODBC System DSN using the SQL Native Client. There is some bottle neck that is slowing performance of the data being sent via a linked server connection!! A solution of sortsĪfter much playing and testing of the various Linked server connections I have managed to get the following results over a linked server connection.įigure 4 was obtained by creating a ODBC System DSN of type ODBC Driver 11 for SQL Server. pcap file.Īs 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).
execute “select * from ”, (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.)ĥ. Open SSMS and connect to the LOCAL server.ģ. Open SSMS and connect directly to the REMOTE server.ĥ. Wireshark was enabled and set to monitor only the remote server, (capture filter – “host 10.10.0.1” where 10.10.0.1 is the IP address of the remote server).ġ. This is used to visualise the throughput. Download and Install Compass wildpackets free from. Download and Install Wireshark on the local server, Ģ. On my local sql server, I have a Linked Server connection called LSEUROPE set to use SQLNCLI – SQL Native Client, configured to connecting to the Azure database in Europe.ġ. I ran this against my azure database and populated it with 45,656 records, (days between to 3, just adjust the populate script) I used the DimDate table as created in this code project example. 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.
One server resides in Sydney, Australia and the other in London, UK. I have a linked server connection between 2 SQL servers approx half the world away.
0 kommentar(er)
