Server aliases/linked servers cause remote queries

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

Transact-SQL (2005)

Server aliases/linked servers cause remote queries

Author

Topic

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2008-04-04 : 15:11:06

Can someone please shed some light on what seems to me to be a common requirement.

If I create an alias or linked server to Server1 - say Alias1 - on Server1 and then use that name in a query on Server1, a remote/distributed query is always used (even though we are running on the local server and that overhead is completely unnecessary).

Is SQL Server really not capable of deciding that
select * from Alias1.db1.dbo.table1
and
select * from Server1.db1.dbo.table1
should be optimized and executed exactly the same when Alias1 is Server1, but that it is a distributed query ONLY when Alias1 is really referring to a remote server? I realize that the four part name is not necessary when I am referring to objects on the current server, but I am trying to write code that is server instance independent.

It just seems that if that is not possible, then the only way to create system independent stored procs that can run in dev, staging, and production environments and work with multiple databases on multiple servers is to create all sorts of scripts to regenerate all the procs whenever you move a database between servers?

If SQL Server is even close to the enterprise big iron server that MS now claims it is, it surely needs to support running in dev, staging, and production environments and work with multiple databases on multiple servers?!

I'm really looking for someone to tell me I'm missing something simple, and of course you can do this - but complex workarounds are invited too :-)
This is not something I am investigating as an academic exercise, I am already doing this, but I have to figure out how to do it better because with all these unnecessary distributed queries, performance is horrible.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-04 : 16:44:40

check this article:

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1281621,00.html

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2008-04-04 : 17:32:55

Thanks for the response, but unfortunately that article does not address the issue.

The article tells you how to use aliases for servers and databases, but I know how to do that.

My question is - if I am using an alias and it refers back to itself, then SQL Server uses a distributed query instead of a regular local query and this is really slow - I just tried one and the query took 22 seconds if I used the alias and 14 milliseconds without the alias.

If the data really was on another server I could understand that there must be overhead to run the distributed query, but if the alias is pointing to the local server surely SQL Server should be able to recognise that and use a local query?

Try this yourself, create a linked server that refers to itself (or create a server alias, they both result in the same thing). Then write two queries that both use four part names, one that uses the real server name, one that uses the linked server or alias name for the server.

Run the queries one at a time and trace them. You'll see that the one that uses an alias runs a whole bunch of distributed query code and it will run slower (how much depends on the specific query). The things that you'll see for the alias query will look like this:

SET XACT_ABORT OFF
go
declare @p1 int
set @p1=1
declare @p2 bigint
set @p2=168684858717458
exec [sys].sp_getschemalock @p1 output,@p2 output,N'"Polls"."dbo"."tblQuestions"'
select @p1, @p2
go
declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,NULL,N'SELECT "Tbl1002"."QuestionID" "Col1004"
,"Tbl1002"."label_url" "Col1005","Tbl1002"."QuestionText" "Col1006"
,"Tbl1002"."IsArchived" "Col1007","Tbl1002"."IsHidden" "Col1008"
,"Tbl1002"."DateToRelease" "Col1009","Tbl1002"."DateToExpire" "Col1010"
,"Tbl1002"."EntityID" "Col1011","Tbl1002"."DateCreated" "Col1012"
,"Tbl1002"."DateModified" "Col1013" FROM "Polls"."dbo"."tblQuestions" "Tbl1002"'
select @p1
go
exec sp_unprepare 1
go
exec [sys].sp_releaseschemalock 1
go

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources