Linchi Shea : Best Practices, SQL Server 2005

A Rose By Any Other Name

Linchi Shea — Tue, 20 Nov 2007 20:04:00 GMT

Regardless of the DBMS make or model, the transaction throughput curve of a database system is often shaped like a trapezoid. As the load level goes up initially, so does the transaction throughput. As the load level continues to go up, the transaction throughput will reach a plateau. After the load level is cranked up still further, the transaction throughput will inevitably start to drop and may eventually come to a halt.

With respect to the throughput drop under severe load, two questions are interesting: (1) what is the cause of the drop? and (2) what can we do to delay the drop until an even higher load level?

The answers are of course dependent on the nature of the workload, the system configurations, and a host of other factors.

So rather than trying to address the questions in general, let's look at one specific type of tests I often perform, which is to stress the processors, memory, and their interconnect of a server with a SQL Server read-only workload that effectively caches all the data pages in memory. In other words, this workload takes the disk I/Os out of the equation.

As expected and with no exception, I would see the transaction throughput start to drop--sometimes rather dramatically--as I increase the load level beyond saturation. When this may happen often correlates nicely with a dramatic increase in waits on Thread-safe memory objects waits under SQLServer:Wait Statistics. Unfortunately, this wait counter is documented with a rather useless tautology in that the explanation more or less repeats what the name already suggests without adding useful information. (BTW, the explanation is "Statistics for processes waiting on thread-safe memory allocators"). So, from looking at the wait stats, I can't really tell what wait or waits are killing the transaction throughput.

The point I want to illustrate, however, is the dramatic impact of a change to the workload--a change that merely gives the stored procedures different names.

Original workload: The database calls of the workload calls are even distributed between two stored procedures, spOrderStatus and spStockLevel.

Modified workload: In the same database, I created 100 stored procedures: spOrderStatus_1 through spOrderStatus_100, that are identical to spOrderStatus except the names. Similarly, I created another 100 stored procedures: spStockLevel_1 through spStockLevel_100, that are identical to spStockLevel except the names. I then modified the original workload to distribute the database calls evenly among spOrderStatus_1 through spOrderStatus_100 and spStockLevel_1 through spStockLevel_100.

With the modified workload, the same database was able to sustain the peak throughput for significantly higher load level (i.e. more users). The following chart summarizes the throughput behavior between the original workload and the modified workload on the test server:

I didn't specifically test how many identical but differently named stored procedures I had to create to see the impact on the transaction throughput. 100 for each stored procedure was picked randomly.

Note that this is a known technique and is used in some of the published SQL Server TPC-C tests. For instance, in the best SQL Server TPC-C results obtained on HP Superdome, 32 New_Order stored procedures are created with 32 different names but identical code inside. Stored procedures with different names but identical code are also created for several other transactions.

So, a rose by a different name may just smell better.

Nondeterministic UPDATE

Linchi Shea — Tue, 13 Nov 2007 19:15:00 GMT

Under the description for UPDATE in SQL Server 2000 and 2005 Books Online, you can find the following statement (thanks to SQL Server MVP Steve Kass for pointing me to this passage):

The results of an UPDATE statement are undefined if the statement includes a FROM clause that is not specified in such a way that only one value is available for each column occurrence that is updated, that is if the UPDATE statement is not deterministic.

BOL goes on to give a simple example of such an UPDATE. However, the example doesn't give you a real feel for the nondeterministic behavior. Although the result is supposed to be undefined and indeed you don't know what value UPDATE will end up assigning, the result does come out consistently the same no matter how many times you execute it. At least during my limited number of test runs, UPDATE seems to always pick the first value. But of course you can't count on that because a different execution plan may pick a different value.

Still, it would be nice to see a nondeterministic UPDATE statement actually picks a different value to drive it home that you should not use it in your app, unless of course your app logic wants to take advantage of this particular non-determinism (which is difficult to imagine).

Recently, however, I happened to run into an interesting case where the nondeterministic behavior of UPDATE can be easily reproduced on a multi-core server when query parallelism is used. Here's the script to see the behavior.

First, run the following script to create two tables and a stored procedure in any user database on a SQL Server 2005 instance:

CREATE TABLE t1 (
	c1 int         NULL,
	c2 varchar(50) NULL,
	rate int       NULL
)
go
CREATE INDEX c1_ind ON t1 (c1)
go

CREATE TABLE t2 (
	c1 int           NULL,
	c2 varchar(50)   NULL,
	rate int         NOT NULL
)
go

drop proc testProc
go
create proc testProc
as
set nocount on
begin tran
  UPDATE t1
     SET rate = t2.rate
    FROM t1 With (Index (c1_ind)), t2
   WHERE t1.c1 = t2.c1
     and t1.c2 = t2.c2

  SELECT 'count_0' = count(*) FROM t1 WHERE rate = 0;
  SELECT 'count_1' = count(*) FROM t1 WHERE rate = 1;
  SELECT 'rate_sum' = sum(rate) FROM t1
rollback tran
go

Second, populate the two tables with data using the following script:

WITH tmp (c1, c2,c3) as (
    SELECT 1,1,1
    UNION ALL
    SELECT cast(rand(c3)*1000000 as int)%200, 
           cast(rand(c3)*1000000 as int)%200, 
           c3 + 1
      FROM tmp
     WHERE c3 < 100000
)
INSERT t1
SELECT c1, CAST(c2 as varchar(50)), NULL
  FROM tmp
OPTION (maxrecursion 0);

WITH tmp (c1, c2, c3) as (
    SELECT 0,0,0
    UNION ALL
    SELECT c1 + 1, c2 + 1, c3 + 1
      FROM tmp
     WHERE c1 < 200
)
INSERT t2
SELECT cast(c1 as char(6)), 
       cast(c2 as varchar(50)), 
       0                         -- c3 value = 0
  FROM tmp
OPTION (maxrecursion 0);

-- give each c1 value a second c3 value = 1
INSERT t2
SELECT c1, c2, 1
  FROM t2;
go

Finally, run the stored procedure multiple times to see that different results are produced:

EXEC testProc;

Apparently, nondeterminism in query processing gives rise to the different results. A few observations are noteworthy:

The non-determinstic behavior is not a result of different query execution plan. At least, the reported execution plan remains the same for all my test runs.
If you disable query parallelism by setting max degree of parallelism to 1, the stored procedure will produce the same results.
On a SQL Server 2000 instance, you probably won't get a parallel plan, and thus won't see different results.
The size of the table t1 is significant. On my test server, populating table t1 with 70,000 still leads to a parallel query plan, thus different results in multiple executions of the stored procedure. But if I only populate the table with 50,000 rows, I don't get an query parallelism, and I get consistent results. So SQL Server query optimizer is sensitive to the table size in deciding whether to use a parallel plan.

Performance Impact: The Potential Cost of Read_Committed_Snapshot

Linchi Shea — Fri, 05 Oct 2007 03:49:00 GMT

In response to my previous blog post--Performance Impact: Setting a Database to Read Only, Shailesh Khanal mentioned that he observed significant performance degradation from READ COMMITTED SNAPSHOT OFF to ON for a read-only workload. This is counter intuitive since there is nothing in the version store if only SELECT statements are running, and the overhead of turning READ COMMITTED SNAPSHOT ON without maintaining any versions shouldn't be that high.

So I decided to conduct some tests myself with a different read-only workload on SQL Server 2005 SP2 (9.00.3042) Enterprise Edition. The read-only workload was the same as that described in the previous blog post--Performance Impact: Setting a Database to Read Only.

My results were mixed. On one hand, I confirmed Shailesh Khanal's observation that turning READ COMMITTED SNAPSHOT ON caused significant performance degradation. It's interesting to note that performance degradation was all observed on 64-bit 16-way machines. On the other hand, I didn't always observe heavy performance penalty when turning READ COMMITTED SNAPSHOT ON. On a 32-bit 4-way machine, I didn't see any degradation at all. The following two charts summarize my results:

In Fig 1, the performance penalty of READ_COMMITTED_SNAPSHOT for a read-only workload was astounding. Just by turning READ_COMMITTED_SNAPSHOT OFF, the transaction throughput gained a whopping ~60%. This was observed in repeated test runs, and on two different platforms. The common characteristics of these two platforms included: (1) the SQL Server 2005 instances were both x64 running on Windows 2003 x64, and (2) the servers both had 16 cores.

That's bad news. But there was good news. On a different platform, repeating the same test led to very different results as summarized in Fig 2 above. If there is any difference between the two lines in Fig 2, turning READ_COMMITTED_SNAPSHOT ON appears to have produced slightly better throughput, though the difference should probably be considered insignificant and within the margin of error. This test platform was 32-bit SQL Server 2005 running on 32-bit Windows 2003, and the server had 4 cores.

Now, I don't know the test environment that Shailesh Khanal used to obtain his results. But from my own tests, I'd venture a conjecture that the performance difference could be attributed to the difference between 64-bit and 32-bit, not the hardware difference between 4 cores and 16 cores. And I'd further venture to suggest that the performance degradation from READ_COMMITTED_SNAPSHOT OFF to ON in the case of 64-bit and read-only workload was most likely due to a bug because the degradation was simply too large to be a feature or by design.

So is there any practical significance to all this?

Clearly, you need to use READ_COMMITTED_SNAPSHOT with caution in performance tuning. Best practice recommends that you consider using it if contention is costing you throughput. For read-only workloads, there is no reader/writer contention for READ_COMMITTED_SNAPSHOT to help avoid, and there is no statement-level data consistency problem for it to prevent.

The other lesson is that you really can't assume anything, even if it intuitively makes sense. You should always confirm it through testing, if possible.

Finally, there could be a scenario where you have a read/write workload, and READ_COMMITTED_SNAPSHOT improves its performance by resolving your reader/write contention problem, but at the same time degrades the performance of its read-only queries. Overall, you may or may not see a net gain. Again, you'll have to run tests to be sure one way or another.