Monitoring Linked Server Usage

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Monitoring Linked Server Usage

Author

Topic

drsloat
Starting Member

45 Posts

Posted - 2010-07-30 : 15:43:39

I have some stored procedures that do something like:

if (have local data)
query local table
else
query linked server

Is there any performance monitor counter I can use to monitor the linked server usage? I want to find out how often the procedure is querying the local data vs the linked server.

Maybe the best way is to run sql server profiler on the linked server?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-30 : 15:46:59

No such counter exists. You will need tomodify that bit of code so that it does the logging for you.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2010-07-31 : 07:16:44

You could try something like the following flow:

if (have local data)
query local table
else
begin
query linked server
insert into mylogtable(cnt)VALUES(1)
end

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources