Search results matching tags 'Performance' and 'Query Processing'

Performance impact: hyperthreading for OLTP queries -- II

Linchi Shea — Wed, 11 Jan 2012 18:51:17 GMT

This is in part a response to a comment by Paul White (@SQL_Kiwi) to my previous post on the performance impact of enabling hyperthreading (HT) on OLTP queries, and in part due to my desire to capture a more complete set of test data for future investigation on this very topic. I’m posting below the results of re-running the same exact test as described in the previous post but with the SQL Server instance bumped up to build 10.50.2500 from 10.50.1600. The former is SQL Server 2008 R2 with Service Pack 1, whereas the latter is SQL Server 2008 R2 RTM.

In addition, I have included the core count as a formal test parameter, and tested the performance impact of enabling HT at the following core counts: 16, 20, 24, 32, and 40. The core count was controlled through the BIOS on machine reboot. The exact BIOS feature for controlling the number of cores is primarily under System Options –> Processor Options –> Enhanced Processor Core Disable (Intel Core Select). This allows one to enter the number of cores to be enabled per socket.

The results are as follows:

In the previous post, enabling HT is reported to increase the throughput by 5~7.5%. In the above chart, the increase in the throughput varies between 9% and 14%. So it may appear that SQL Server 2008 R2 SP1 responds slightly better to enabling HT than does SQL Server 2008 R2 RTM. However, because there is always a margin of error in any test, I’d assign more significance to the overall trends and patterns than the exact numbers. And in both cases, the over trends and patterns are similar in that this particular OLTP workload responded positively to enabling HT on the E7-4870 processors.

Performance impact: hyperthreading for OLTP queries

Linchi Shea — Fri, 06 Jan 2012 04:44:00 GMT

My previous post focuses on the performance impact of enabling hyperthreading (HT) on a machine with four Intel Westmere-EX processors on reporting queries. Let’s turn our attention to OLTP queries.

To oversimplify it, reporting queries are generally processed by scanning a large number of pages, whereas quick index seeks are the hallmark of OLTP queries.

The OLTP queries used to check out the hyperthreading impact are the two TPC-C read-only transactions (Order Status and Stock Level), slightly modified to work properly in the test environment. The need to modify these two TPC-C transactions is discussed in an early post. Briefly, it’s because in their original forms they don’t scale properly on this 80-cpu test machine running SQL Server 2008 R2. You can find the modified code of these two transactions in the attachment of this post.

The benchmark described in the same post is used to drive the tests whose results are reported here. If you want to find out more about the benchmark, please check out that post, so I won’t duplicate its description here.

Essentially, with this OLTP benchmark (or workload) I want to see how it behaves when I increase the load level by increasing the number of concurrent users, with and without hyperthreading on a DL580 G7 with four E7-4870 processors (10 cores per processor) and 264GB of RAM. The OS is Windows 2008 R2 Enterprise x64 Edition with SP2 and DBMS is SQL Server 2008 R2 Enterprise x64 Edition (10.50.1600). There is no intra-query parallelism; maxdop is set to 1 in all the cases.

Four test configurations are examined:

40 core with HT. That is 80 logical cpus. HT is enabled in the BIOS.
40 cores without HT. That is 40 logical cpus. HT is disabled in the BIOS.
20 cores with HT. That is 40 logical cpus. HT is enabled in the BIOS.
20 cores without HT. That is 20 logical cpus. HT is disabled in the BIOS.

The following chart shows the results when 200 simulated users are driving the workload concurrently. Note that with 200 users, all the processors on this test system are pushed to ~100%.

Compared with the results for the reporting queries (see the previous post), the performance gain from enabling HT on the 40 cores is very much more marginal with this OLTP benchmark. We see an increase of about 5% from ~7900 batch requests per second to ~8300 batch requests per second. Enabling HT on the 20 cores produces an improvement of similar magnitude (about 7.5%).

Going from 20 cores with HT to 40 cores without HT, however, gives the OLTP queries a dramatic throughput gain of about 67% from ~4730 batch requests per second to ~7900 batch requests per second. We see similar behavior with the reporting queries. Knowing how HT works, I think it is intuitive we expect a big gain, whether or not it should be 67%.

So for both the reporting queries and the OLTP queries that I have tested, the hyperthreading results are positive. So far so good!

Performance impact: diminishing marginal return on the degree of parallelism

Linchi Shea — Fri, 16 Dec 2011 17:24:00 GMT

In commenting on my previous post, Greg Linwood and GrumpyOldDBA raised questions about various implications of parallelism. In this post, I’ll look at the impact of different degrees of parallelism on the query performance. I’ll limit my examination on the same query that uses a Cartesian product temp table, as discussed in the previous two posts ([1] and [2]). The details of the test setup can be found in [1]. The SQL Server instance is SQL Server 2008 R2 (10.50.1600) Enterprise X64 Edition. I’ll look at the performance impact of concurrent executions in a future post.

The law of diminishing marginal return dictates that as you increase the number of processors in a parallel plan, sooner or later you’ll reach a point after which you’ll see a decrease in the performance gain from adding another processor to the parallel plan. Note that it is the decrease in the performance gain, not the decrease in the performance itself.

The question is, for a specific query, does SQL Server know where that inflection point is and therefore make the right decision in picking the optimal degree of parallelism? SQL Server Books Online thinks so. It says:

“When SQL Server runs on a computer with more than one microprocessor or CPU, it detects the best degree of parallelism, that is, the number of processors employed to run a single statement, for each parallel plan execution.”

But how does that work out in practice? This is a valid question to ask because determining the optimal degree of parallelism is an incredibly difficult problem to solve. We obviously can’t answer this question in generality. Nor should we even attempt.

So let’s take a look at the following query (reproduced from the previous post, and see that post for more details):

select id_server_config,
          id_thread_name,
          id_tran_type,
          id_tran_status,
          id_users,
          id_cpus,
          COUNT(*) as cnt
from tran_stats t1 join #tmp t2
       on t1.server_config = t2.server_config and
          t1.thread_name = t2.thread_name and
          t1.tran_type = t2.tran_type and
          t1.tran_status = t2.tran_status and
          t1.users = t2.users and
          t1.cpus = t2.cpus
group by id_server_config,
         id_thread_name,
         id_tran_type,
         id_tran_status,
         id_users,
         id_cpus

Let’s run this query on a server with 80 processors with the max degree of parallelism (maxdop) set to different values, and see it performs. (Note that the tran_stats table has 200,000,000 rows, and the query runs with all the data already cached in memory). The following chart summarizes the result:

A couple of things stand out on this chart.

First of all, the diminishing marginal return on parallelism is no mistake. After maxdop = 8, the additional performance gain by increasing degrees of parallelism is rather small. And after maxdop = 24, the gain is virtually non-existent. Arguably, the optimal degree of parallelism for this query on this server is somewhere between 8 and 24.

The second salient point is that when maxdop is set to 0, SQL Server sets the actual degree of parallelism to 64 when running the query. As per Books Online, 64 is the maximum value SQL Server would set maxdop to.

Why is setting maxdop to 64 not optimal for this query? Let’s look at the following chart that shows the % total CPU usage on the server (with 80 processors) at various maxdop settings:

For this query, as we increase the maxdop setting, the total CPU usage steadily goes up. Even though after maxdop = 24, we practically gains no performance, the total CPU usage still goes up. We just end up wasting a tremendous amount of CPU cycles for nothing.

Obviously we need to gain more experience to be sure, but it doesn’t look like a good idea to leave maxdop at 0 on a box with a lot of processors.

Performance impact: Cartesian product comes to the rescue

Linchi Shea — Thu, 15 Dec 2011 06:11:00 GMT

Although Cartesian product as a concept is essential to the relational database theory, it is often a dirty phrase – something that is associated with bad performance and therefore should be avoided. But there are cases where a Cartesian product is highly desirable. This post shows you an example query—from the real world although presented here with artificial data—that can be significantly optimized with a manually created Cartesian product.

Here is the query in question:

select id_server_config,
       id_thread_name,
       id_tran_type,
       id_tran_status,
       id_users,
       id_cpus,
       COUNT(*) as cnt
from tran_stats t1
     join Dim_server_config t2 on t1.server_config = t2.server_config
     join Dim_thread_name t3 on t1.thread_name = t3.thread_name
     join Dim_tran_type t4 on t1.tran_type = t4.tran_type
     join Dim_tran_status t5 on t1.tran_status = t5.tran_status
     join Dim_users t6 on t1.users = t6.users
     join Dim_cpus t7 on t1.cpus = t7.cpus
group by id_server_config,
         id_thread_name,
         id_tran_type,
         id_tran_status,
         id_users,
         id_cpus

This query joins a large table called tran_stats with six small lookup tables (or dimension tables). The dimension tables have dimension id’s and their corresponding values. The large table—which stores transaction statistics—is sort of a fact table but include the actual dimension values instead of dimension id’s. There is no index on any of these six dimension columns in the large tran_stats table. The following is a summary of the cardinalities of these tables:

Table Name	Row Count	Comment
tran_stats	200,000,000	The table is not very wide, and the total size of the table is about 36GB
Dim_server_config	9	Corresponding to the 9 distinctive values in the tran_stats.server_config column
Dim_thread_name	100	Corresponding to the 100 distinctive values in the tran_stats.thread_name column
Dim_tran_type	3	Corresponding to the 3 distinctive values in the tran_stats.tran_type column
Dim_tran_status	3	Corresponding to the 3 distinctive values in the tran_stats.tran_status column
Dim_users	5	Corresponding to the 5 distinctive values in the tran_stats.users column
Dim_cpus	8	Corresponding to the 3 distinctive values in the tran_stats.cpus column

In this post, I’ll remove query parallelism from consideration. I’ll cover how parallelism contributes to the discussion in a follow-up post. So for now let’s assume that max degree of parallelism has been set to 1.

When I run the above query on my test server, it comes back in about 740 seconds (or just over 12 minutes) with all the data already cached. The query plan is a series of hash matches shaped like a straight line going from the lower right corner to the upper left corner:

Question: Can we do better than this?

Answer: Yes! An approach is to create and materialize the Cartesian product of all the dimension first, and then join the large table with this Cartesian product.

On the test server, the following Cartesian product query takes about 1 second and populates #tmp with 324,000 rows (that is 9*100*3*3*5*8).

select * into #tmp
from Dim_server_config,
       Dim_thread_name,
       Dim_tran_type,
       Dim_tran_status,
       Dim_users,
       Dim_cpus

Then, the following join takes about 248 seconds to complete.

select id_server_config,
          id_thread_name,
          id_tran_type,
          id_tran_status,
          id_users,
          id_cpus,
          COUNT(*) as cnt
from tran_stats t1 join #tmp t2
         on t1.server_config = t2.server_config and
            t1.thread_name = t2.thread_name and
            t1.tran_type = t2.tran_type and
            t1.tran_status = t2.tran_status and
            t1.users = t2.users and
            t1.cpus = t2.cpus
group by id_server_config,
          id_thread_name,
          id_tran_type,
          id_tran_status,
          id_users,
          id_cpus

The query plan is a single hash match:

To help drive home the performance difference between these two queries, let me re-present their elapsed times in the following bar chart:

In this particular scenario, using a materialized Cartesian product results in a query that is three times faster than the query that directly joins with each of the lookup tables.

I have uploaded the DDL scripts and the script for loading the data into the large table and into each of the dimension tables. You can download and run the attached scripts and check out the performance difference for yourself.

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.

Performance impact: UNION ALL views, ANSI_PADDING, and bad query plans

Linchi Shea — Fri, 04 Dec 2009 19:43:00 GMT

Whether or not you specify it explicitly, ANSI_PADDING setting is there when you create a table, and can have an impact on the performance of some queries. If you are not careful, it can even hurt performance big time!

Let’s demonstrate that with an extremely simple example.

Assume that you have this simple view with a UNION ALL between two tables:

CREATE VIEW myView

select * from tb1

UNION ALL

select * from tb2

The two tables are created as follows:

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING OFF

-- Create the main table

CREATE TABLE tb1 (

[TicketID] [char](13),

[Edition] [smallint],

[FlowType] [char](3),

[Amount] [float] NOT NULL,

[Code] [char](3) NOT NULL,

[LogDate] [datetime] NOT NULL,

[id] [int] NOT NULL

)

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [tb2](

[TicketID] [char](13),

[Edition] [smallint] NOT NULL,

[FlowType] [char](3),

[Amount] [float] NOT NULL,

[Code] [char](3) ,

[LogDate] [datetime] NOT NULL,

[id] [int] NOT NULL

)

And you run the following query on the view to return a single row from tb1:

SELECT TicketID, Amount

FROM myView

WHERE FlowType ='2'

and TicketID = '200002000'

and Edition = 0

Since both tb1 and tb2 have a covering index on this query (see the attached repro script for the indexes), you’d expect an efficient index seek. But you may discover that SQL Server chooses to process the query with an index scan, which could easily sink the performance if the table is rather large.

What’s going on?

Well, notice that the only difference between tb1 and tb2 is that tb1 is created with ANSI_PADDING set to OFF whereas tb2 is created with ANSI_PADDING set to ON. If you create these two tables with the same ANSI_PADDING setting, SQL Server does generate a correct plan for the query.

So for some reason, the SQL Server 2005/2008 optimizer can’t deal with this type of mismatched ANSI_PADDING settings in a view intelligently. Although one may question whether the two tables should have different ANSI_PADDING settings, it’s clear that the generated plan is not optimal. And I’d argue that this is a bug in the SQL Server optimizer because there does exist an optimal plan that uses an index seek, and the optimizer fails to find it.

To see the details of the behavior for yourself, you can run the attached repro script in any test database. The repro demonstrates the bad plans on both SQL Server 2005 and SQL Server 2008. On SQL Server 2000, you should see a good plan with an index seek. But this does not mean that SQL Server 2000 is immune to the problem. In fact, with a different but slightly more complex repro script, I see the same bad plans on SQL Server 2000.

Note that to save time, the repro script populates the table with 100,000 rows, and can finish in several seconds on any decent machine. With 100,000 rows, you won’t feel any pain even with a bad plan that does an index scan. If the table had millions of rows, the pain would be quite acute.

Also, I have filed a bug report at Microsoft Connect. Please vote there to get some traction from Microsoft on this issue.

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.

Performance impact: Linked server security configuration and how it can hurt you

Linchi Shea — Tue, 21 Jul 2009 17:16:00 GMT

If you change the security configuration of a linked server from using login UserA to login UserB, would you expect that to significantly impact the performance of a query that uses the linked server, assuming that both users have permission to select from the remote table referenced in the query?

Many people probably would answer no. After all, as long as the connection has the permission to access the remote table, how the connection is authenticated should not matter, right? Well, it’s more complicated than that.

Let’s say you have the following the query:

-- select @@servername returns NYCSQL01

SELECT o.*

FROM orders o inner join NYCSQL02.sales.dbo.order_details od

on o.OrderID =od.OrderID

WHERE o.OrderDate = '20090717'

In the above query, NYCSQL01 is the SQL Server instance where the query is being executed and NYCSQL02 is a linked server pointing to a separate SQL Server instance.

To process this query, SQL Server optimizer on NYCSQL01 may choose between two strategies (among others). First, it may decide to retrieve one row at a time from the table order_details on NYCSQL02 for the rows that match the OrderID values found on NYCSQL01 for the given OrderDate value. The second strategy is to retrieve all the rows of order_details from NYCSQL02 and then perform the join locally on NYCSQL01. Depending on the data distribution of the rows in order_details, either approach can be efficient.

To make the correct decision as to which strategy to use, NYCSQL01 needs the distribution statistics for table order_details from NYCSQL02. So far, so good. The question is what permission NYCSQL01 needs in order to retrieve the distribution statistics from NYCSQL02.

It turns out that it needs the same permission as is required to execute DBCC SHOW_STATISTICS. Per Books Online, the permissions for DBCC SHOW_STATISTICS are:

User must own the table, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.

This is where the problem lies. You may think that as long as you can access the table order_details on NYCSQL02, you are all set with the query above. After all, if you just want to retrieve some data from the order_details table, it would not be a security best practice to be given a sysadmin, db_owner, or even db_ddladmin role.

If the connection to NYCSQL02 is not the table owner, or a sysadmin, db_owner, or db_ddladmin, you’ll still get the result back. But the query optimizer on NYCSQL01 will not have the stats on order_details, and may just decide to do a table scan on order_details. If order_details turns out to be very large table, the performance of the above query is shot, or worse, it could drag NYCSQL01 down with it.

Whether it is reasonable to require sysadmin, db_owner, or db_ddladmin in order to process a distributed query efficiently is an issue for a different thread of discussion.

My gripe is that the exact permission requirements for efficiently processing a distributed query, especially a distributed join, is not well documented, if it is documented at all. Given its enormous risk, I’d argue that this should not only be documented, but rather it should be highlighted in bold in SQL Server Books Online.

If you agree, please go to Microsoft Connect and vote for the following item:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=476001

T-SQL Exercise: Simplest data-loading script to produce worst query performance – June 7th Update

Linchi Shea — Mon, 08 Jun 2009 01:31:00 GMT

All the previously posted results (May 25^th and May 29^th) on this exercise were obtained with query parallelism disabled (i.e. the sp_configure ‘max degree of parallelism’ option was set to 1).

Since the following test query is sensitive to query parallelism, we need to see what impact query parallelism may have.

DBCC DROPCLEANBUFFERS

SELECT COUNT(*) FROM dbo.test;

It turns out that with parallelism the test query exhibited significantly different performance characteristics with the three data sets we have been using as test vehicles than without parallelism. And again, the nature of the storage system proved to be a significant confounding factor. The following table shows the results with query parallelism on both the internal drive (the C: drive) and the drive presented from a departmental level disk array (the E: drive), the same two drives used in the May 25^th update and the May 29^th update.

Drive	Test Run	Elapsed Time (second)
Internal (C:)	Adam’s script – test run 1	90
	Adam’s script – test run 2	88
	Adam’s script – test run 3	87
	Tibor’s script – test run 1	217
	Tibor’s script – test run 2	219
	Tibor’s script – test run 3	226
	Linchi’s script – test run 1	325
	Linchi’s script – test run 2	324
	Linchi’s script – test run 3	324
Disk array (E:)	Adam’s script – test run 1	74
	Adam’s script – test run 2	75
	Adam’s script – test run 3	72
	Tibor’s script – test run 1	93
	Tibor’s script – test run 2	97
	Tibor’s script – test run 3	89
	Linchi’s script – test run 1	51
	Linchi’s script – test run 2	51
	Linchi’s script – test run 3	51

Note the dramatic change in the relative impact of the data sets on the test query when the storage performance and query parallelism were added to the mix. In particular, note how the test query was able to perform much faster on the faster drive (drive E:) with some data sets.

I just want to present the data points in this post, and will dive in a bit in the next follow up.

T-SQL Exercise: Simplest data-loading script to produce worst query performance – May 29th Update

Linchi Shea — Fri, 29 May 2009 16:23:00 GMT

This is another follow-up on the T-SQL exercise.

So the test query below is rather simple:

DBCC DROPCLEANBUFFERS

SELECT COUNT(*) FROM dbo.test;

But beneath its simple appearance, many factors are at play and interact in a complex way to influence the query performance. In other words, trying to predict its performance is not trivial. You may do better than predicting the stock market performance. But still it can be a rather unreliable business.

So in the May 25^th update, I reported on the results of running the test queries with the data file on a RAID-1 set with two internal disks. Yesterday, I triple checked the test result and the result could be re-produced at will.

To see whether or what impact a different storage system may have on the performance, I placed the data file on a departmental disk array behind a SAN. Unlike a typical enterprise class disk array, this departmental disk array does not have a huge amount of cache. In addition, the data block placement is effectively virtualized in that it uses a mapping table to place data across all the drives, making it effectively impossible to know or control where the data is placed at the OS level.

With the data file on this disk array, I repeated the same tests. Each time before the data was loaded, the test table was dropped and the database was empty with no user data.

Again, after the data was loaded by each script, the test query (including DBCC DROPCLEANBUFFERS) was run three times, and each time the query elapsed time was logged. Additional validation test runs were performed to ensure that the test results were re-producible, and they are re-producible.

The following table shows the recorded elapsed times.

Test run	Elapsed time (second)
Adam’s script – test run 1	74
Adam’s script – test run 2	82
Adam’s script – test run 3	81
Tibor’s script – test run 1	248
Tibor’s script – test run 2	235
Tibor’s script – test run 3	239
Linchi’s script – test run 1	155
Linchi’s script – test run 2	146
Linchi’s script – test run 3	148

First thing to note from the above table is that the ranking of the test query performance changed significantly. In this setup, Tibor’s data set caused the test query to have the worst performance.

But what is most striking is that while the test query performed significantly better with Adam’s and my data sets on the disk array than they did on the internal disks, the test query performed noticeably worse with Tibor’s data set on the disk array than it did on the internal disks.

This is getting more interesting. And I might add that this is yet another piece of evidence on the conspiracy of the every-table-must-have-a-clustered-index crowd :-)

I’ll come back to explore what might be the cause later.