Search results matching tags 'Best Practices', 'Performance', and 'Distributed join'

Linked servers: permissions and distributed query performance

Linchi Shea — Tue, 23 Nov 2010 04:58:00 GMT

In an earlier post, I highlighted that linked server security configuration can have a huge performance impact, and complained that this was not properly documented in SQL Server Books Online and filed a Connectitem for this. Good news is that in Books Online for SQL Server 2008 R2, Microsoft has revised the documentation, and included the following guideline forusing distributed queries:

To create the best query plans when you are using a table on a linked server, the query processor must have data distribution statistics from the linked server. Users that have limited permissions on any columns of the table might not have sufficient permissions to obtain all the useful statistics, and might receive aless efficient query plan and experience poor performance. If the linked serveris an instance of SQL Server, to obtain all available statistics, the user must own the table or be a member of the sysadmin fixed server role, the db_ownerfixed database role, or the db_ddladmin fixed database role on the linkedserver.

This is a major improvement.To drive this home, however, it would be nice to see the impact in action. In my presentation at the recent SQL Saturday #59 in NYC, I demonstrated, with a simple example, that the performance of adistributed query can be significantly impacted by the user permission. Unfortunately, due to time limit, I could not go into great details during the demo. Let me describe the example in this post.

To make the example work,some assumptions are in order:

·      You have two SQL Server 2005 (or 2008) instances:SQL01 and SQL02. There is a user database called TestDB on SQL02.
·      You have a SQL login called bob on SQL02, which is granted access to TestDB as a user in the public role only.
·      A linked server called SQLBOB is created on SQL01 as follows:

EXEC master.dbo.sp_addlinkedserver @server= N'SQLBOB',

@srvproduct=N'',

@provider=N'SQLNCLI',

@datasrc=N'SQL02',

@catalog=N'TestDB'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SQL02',

@useself=N'False',

@locallogin=NULL,

@rmtuser=N'bob',

@rmtpassword='########' -- replace withreal password for bob

EXEC master.dbo.sp_serveroption @server=N'SQLBOB',

@optname=N'data access', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=N'SQLBOB',

@optname=N'rpc', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=N'SQLBOB',

@optname=N'rpc out', @optvalue=N'true'

· A second linked server SQLSA is created on SQL01 as follows:

EXEC master.dbo.sp_addlinkedserver @server= N'SQLSA',

@srvproduct=N'',

@provider=N'SQLNCLI',

@datasrc=N'SQL02',

@catalog=N'TestDB'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SQLSA',

@useself=N'False',

@locallogin=NULL,

@rmtuser=N'sa',

@rmtpassword='########' -- replace with real sa password

EXEC master.dbo.sp_serveroption @server=N'SQLSA',

@optname=N'data access', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=N'SQLSA',

@optname=N'rpc', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=N'SQLSA',

@optname=N'rpc out', @optvalue=N'true'

The only difference between SQLBOB and SQLSA is that the former is configured to log into SQL02 with bob, whereas the latter is configured to log into SQL02 with sa.

Now, let’s create a table in TestDB on SQL02 and populate it with 100,000 rows using the following script:

use TestDB

drop table item

create table item(i int, j int, cchar(200))

;with tmp(i, j, c) as (

select 1, 1, replicate('a', 200)

union all

select i + 1, j+1, replicate('a', 200)

from tmp

where i < 100000

)

insert item

select * from tmp

option (maxrecursion 0)

create clustered index ix_i on item(i)

create index ix_j on item(j)

grant select on item to bob

Then on SQL01, let’s create a temporary table and populate it with 20 rows.

drop table #tmp

create table #tmp(i int, c varchar(10))

;with tmp(i, c) as (

select 1, cast('abc' as varchar(10))

union all

select i + 1, cast(i as varchar(10))

from tmp

where i < 20

)

insert #tmp

select * from tmp

option (maxrecursion 0)

create clustered index ix_c on #tmp(c)

Now, we are ready for ourdemo. On SQL01, run the following queries:

--Query 1

select t1.*, t2.*

from #tmp t1, SQLBOB.TestDB.dbo.item t2

where t1.i = t2.j

-- Query 2

select t1.*, t2.*

from #tmp t1, SQLSA.TestDB.dbo.item t2

where t1.i = t2.j

Note that the only difference between Query 1 and Query 2 is that the former accesses the item table on SQL02 via linked server SQLBOB whereas the latter does via linked server SQLSA.

You should find that both queries will come back relatively quickly with no big difference in duration.However, if you capture their query plans, you’ll see that the remote query forQuery 1 is something similar to the following:

SELECT "Tbl1004"."i""Col1010","Tbl1004"."j""Col1011","Tbl1004"."c" "Col1012" FROM"TestDB"."dbo"."item" "Tbl1004" ORDERBY "Col1011" ASC'

In other words, the optimizer on SQL01 has decided to bring all the rows of the table item from SQL02 to SQL01 in a single scan, and then process the query locally.

And if you capture the query plan for Query 2 or useSQL Profiler on SQL02, you’ll see that the remote query would look like thefollowing:

SELECT "Tbl1004"."i""Col1010","Tbl1004"."j" "Col1011","Tbl1004"."c""Col1012" FROM "TestDB"."dbo"."item""Tbl1004" WHERE "Tbl1004"."j"=?

This time, SQL01 is using a different strategy. Basically, it is fetching only those rows from SQL02 where column item.j matches the column i values from the local temporary table. If you look at the incoming traffic on SQL02with SQL Profiler, you’ll see the following:

declare @p1 int

set @p1=1

exec sp_prepexec @p1 output,N'@P1 int',N'SELECT"Tbl1004"."i""Col1006","Tbl1004"."j""Col1007","Tbl1004"."c" "Col1008" FROM "master"."dbo"."item" "Tbl1004" WHERE "Tbl1004"."j"=@P1',2

select @p1

And this is followed by 20 executions of sp_execute. Essentially, SQL01 has decided to parameterize the calls to SQL02 through the linked server SQLSA by creating a temporary stored procedure on SQL02 and then call the proc 20 times, one for each value of column i in #tmp.

So what is the implication? Well, you’ll see a huge performance difference if table item on SQL02 is not populated with 100,000 rows but 10,000,000 rows. In that case, 20 calls with index seek will absolutely beat a scan of a large table any time. The larger the table on SQL02, the more significant the performance difference between Query 1 and Query 2.

Why is there such a performance difference between Query 1 and Query 2?

The root cause is explained by the quotation from SQL Server 2008 R2 Books Online (reproduced at the beginning of this post). With SQLBOB, the user accessing SQL02 is bob, who only has the SELECT permission on the item table and does not have permission to see the distribution statistics. With SQLSA, however, the user is sa and has all the permission to access everything on SQL02, including the distribution statistics for the item table.

Note that the worst case scenario for the Query 2query plan would be scanning the item table 20 times if the column item.j turnsout to be not selective, whereas with Query 1, a single scan of the item table is the worst case. In a way, it makes sense to choose the query plan as seen with Query 1 when the optimizer on SQL01 has no knowledge of the distribution statistics of the item table on SQL02. In terms of the worst case scenario, it is actually safer.

If you run this example in your environment, you may not see the exact behavior as I have described. But if you play with different numbers of rows or different column widths in the test tables, you should eventually see the above two different plans.

Bad database practices: abusing linked servers

Linchi Shea — Sat, 07 Nov 2009 00:56:00 GMT

In SQL Server, it is rather handy to retrieve data from a different SQL Server instance and use the result locally in another SQL statement for further processing. In theory and in the set purists’ fantasy land, it shouldn’t matter where you get your data or even how you get the data as long as you can use them to further compose a set-based solution.

That is all fine. After all, in a distributed environment the data you want may not be on the same instance where the processing takes place. Well, that is fine until it comes to performance and troubleshooting. If you use linked servers indiscriminately, sooner or later you’ll run into these issues, and they are not pretty.

In the real world, you may have seen different kinds of linked server abuses than what I have seen. For me, two types of abuse stand out rather prominently. (For brevity, I’ll limit the discussions primarily to remote procedure calls, although they apply as well to any other use of linked servers such as openquery and heterogeneous joins.)

The worst kind of linked server abuse is in making an excessive number of linked server hops. Let me clarify what I mean by that. If ProcA makes a call to ProcB via a linked server ServerB and ProcB does not make any further cross server linked server calls, it would be one hop. If ProcB itself makes another one-hop call to ProcC on ServerC, there would be two hops from ProcA to ProcC, and so on and so forth.

Generally speaking, I would consider a two- or three-hop call stretched, but in reality I have seen calls that span four hops. I can only classify that as abusive, even though a lot of times the developer who added the very last hop did not know that the proc he was calling had three hops already. He was just making use of the resultset, a practice perfectly in alignment with the set-based approach.

So what is the problem? The headache lies in performance and troubleshooting.

Although getting data across several server hops does not necessarily mean poor performance, there is no doubt that increasing the number of hops significantly increases the susceptibility to poor performance. Unless you are getting a tiny amount of data, retrieving data across server is relatively expensive, and if that is subject to some implicit loop in an execution plan, your chance of poor performance has just been dramatically magnified. As pointed out in my previous post, you may end up processing data on the more expensive side of the server boundary, and by adding another hop, you only manage to further obscure that danger rather than expose or highlight it. This is especially dangerous when you have layers of views on top of cross-server queries and that view is hidden several hops down stream. You may think you are just doing a local join when in fact you are doing a distributed join.

In addition to real or potential performance problems, excessive number of server hops makes it very difficult to troubleshoot an issue, whether it’s performance or function related. The fact that SQL Server error messages rarely identify which server they come from only help exacerbate the grief. Often, this forces you to have to execute the calls at different levels in order to see where the problem may come from, a task that may be hard to perform in many circumstances. Just getting the right parameters for a call at each level can become time consuming.

The second type of linked server abuse that I consider among the worst is to combine multiple layers (or hops) of linked server calls with dynamic SQL. For instance, to gain flexibility, one may store linked server names in a table, and construct calls dynamically at the run time. This solves the problem of keeping the code stable when one changes the linked server names. But this approach makes it nearly impossible to troubleshoot when there is a problem somewhere in one of the hops because looking at a message you would have no idea where it comes from, and you can’t just scan through the code to spot problematic location. It’s not a pretty situation, and I wouldn’t wish that on anyone.

It is often beyond your control as to why there are so many linked server hops. And once they are there, they can quickly become such a tangled web that any attempt to shrink the number of hops is too painful to be worth any benefits such a successful attempt may bring about. Hopefully, by highlighting the pain one may suffer from the abuse, we can help deter its future practice.