Solved

SQL Server Linked DB Query creates multiple connections

Posted on 2013-11-22
2
37 Views
Last Modified: 2015-11-05
I created a linked server in SQL 2008 to an external DB without any issues. However, each time I run a query against this DB, there seem to be multiple connections to the DB it's linking to. For example, I ran a simple SELECT col1 FROM LINKTEST..SYSTEM.table
and there were two connections made to the external DB server.
Looking at the logging from the other server it looks like it first does a SELECT * from SYSTEM.table and then actually runs SELECT col1 FROM LINKTEST.SYSTEM.table

Is this to be expected? It seems like each time I add a table to the query I get two more processes on the external DB.
0
Comment
Question by:esb91077
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 27

Accepted Solution

by:
Zberteoc earned 100 total points
ID: 39669023
If you did everything according to documentation, I am referring to the linked server, and your query works then I wouldn't worry.  If you want to find out what the SQL engine does in the background then use the SQL profiler and you will see,
0
 

Author Comment

by:esb91077
ID: 39669864
It's an issue because the external DB that it's connecting to is a per connection license. So they are quickly running out licenses because complex queries are making a ton of connections. I'll look into SQL profiler to see what I can find.
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Stored Proc - Performance Enhancement 15 86
PolyServe for SQL server 13 52
selective rebuild of SQL Tables in scheduled job 10 48
Set a variable value in SQL Procedure 3 26
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question