Today, I've worked on an interesting service request that I would like to share with you.
Our customer reported that connecting to Azure SQL Database using SQL Server Management Studio and running a query that retrieves 400.000 rows is much faster that using a Linked Server connecting to the same server and database.
After multiple troubleshooting steps using collations (server OnPremises has a different collation that Azure SQL Database), I noticed that the packet size that is using SQL Server Management Studio when it connects to Azure SQL Database is 8096, however, connecting using Linked Server the packet size is 4170. I found this executing the following query: select * from sys.dm_exec_connections A
INNER JOIN SYS.DM_EXEC_SESSIONS B ON A.session_id=B.session_id
where net_transport='TCP' and program_name = 'Microsoft SQL Server'
I added in the connection string of the linked server the packet size "'Server=tcp:yourservername.database.windows.net,1433;Persist Security Info=False;MultipleActiveResultSets=False;Connection Timeout=30;PACKETSIZE=8096" and the performance is very similar in both scenarios.
Enjoy!
