Poor performance with linked server when not using an sa account?

Question

Post reply

Poor performance with linked server when not using an sa account?

Shannon Roderick

SSC Enthusiast

Points: 177
More actions
November 10, 2005 at 2:37 pm

#89835

Ok,

When running a distributed query as a non-sa user, the query performance is consistently slow (8+ minutes.) Using a user in the sa role, the query performance is consistently quick (2-8 seconds.)

Select t1.FIELD1, t1.FIELD2, t1.FIELD3

From LINK1.DATABASE1.dbo.TABLE1 As t1

Where Exists (

Select ID From TABLE2 t2

Where t2.ID = t1.ID)

SQL Server 2000 to SQL Server 2000 linked server, using oledb. Login's made using current login's security context. The appropriate logins, with consistent rights, exist on both servers.All server options "checked". Both Servers are running Windows 2003.

No indications of problems in either server's event log.

Any ideas? We're stumped

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

I don't think this will increase the query time 60 times but who Table 2 belongs to? SQL Server first search for a table with the current user ownership, then DBO. So if SA is a DBO and SA is connecting the resolution time is less. But it could not account for the delay that long.

Try to select with DBO.Table2 in the subquery

Yelena

Regards,Yelena Varsha

Shannon Roderick SSC Enthusiast Points: 177 More actions · Answer 2

Thanks. dbo.table2 appears to have helped both a little.

In further testing, we've found that there are occasions when a user with the sa role also experiences slow performance. This doesn't happen often, and there seems to be no correlation for this to server or network load.

We still don't have a real solution, but it does seem to related to security context switching or something similar.