How to monitor how much data is transfered in a linked server?

Question

I have a linked server to a remote computer and doing some insert, update and delete operations between local and remote databases. I want to know how much data is transfered when operation is doing. How can I do it? My local database is Sql server 2008 and remote database is SQlserver2ksp3

Answer 1

This sounds like a job for PERFMON, which is included with the latest windows server offerings.

http://technet.microsoft.com/en-us/library/cc749249.aspx

Basically you're going to setup a counter to watch the MSSQL data transfer between the servers.

You may be able to use a counter inside of MSSQL to track this as well, but I'm not familiar with any functionality that provides that.

Answer 2

One easy way to do it is get the data in a dataset, and export it to xml (or excel 2010 for ease of use) and have a look at the file size. The other way is to have a look at the firewall log ( if you are using a separate port or url for connection).

Answer 3

You're pretty much going to have to use networking tools to do the monitoring. Another option is to query the size of the tables involved directly on the linked server and that will give you a rough estimate. If you're joining across linked servers, all the data must be brought over, then filtered down, so you're probably transferring a lot more data than you think.