Linchi Shea : SQL Server 2005

Pause the passive node to apply SQL2005 Service Pack or hotfix

Linchi Shea — Mon, 05 Oct 2009 01:55:00 GMT

With SQL Server 2005, Microsoft tried to be helpful in enabling its installer to apply the service pack or hotfix automatically on the passive node(s) in a cluster. In my own experience, this has not worked out too well. Very often, when the installer...(read more)

Database snapshots -- are you using them?

Linchi Shea — Wed, 25 Mar 2009 22:09:00 GMT

Database snapshots were introduced in SQL Server 2005, and have been around for several years now. One of the main motivations is that you can create database snapshots to protect your database from user or application errors. It's one more weapon in your DR arsenal.

But looking at the environments I have worked in for the past few years, I don't recall seeing the feature being used for this purpose at all. In fact, I have not seen it being used for any purpose, period (other than internally by DBCC, etc). Perhaps, people should be using it, but for whatever reason they don't. Perhaps, I'm just hanging around with a wrong crowd.

So are you using database snapshots? If so, for what purpose?

Large Query Performance and Query Parallelism

Linchi Shea — Sun, 24 Aug 2008 17:32:00 GMT

[2008/08/25] This post has been modified significantly to correct some inaccurate statements because I mis-read Joe Chang's post.

Joe Chang posted some interesting results using the TPC-H queries with the scale factor set to 10. I happened to have done something similar, and naturally noticed a rather significant difference between his results and mine. [2008/08/25: Okay the difference is not as significant as I had thought.]

My results were obtained on SQL Server 2005 Enterprise x64 Edition running on Windows Server 2003 Enterprise x64 Edition. The test server, whose make and model will remain undisclosed, had four 2.93GHz quad-core Intel Tigerton sockets (Xeon X7350) with 64GB of RAM with 20GB allocated to the SQL Server buffer pool. The TPC-H scale factor was set to 10, and the data were generated with DBGEN from tpc.org.

My results are as follows:

Query	MAXDOP 1	MAXDOP 4	MAXDOP 8	MAXDOP 16
*tpcH Query1*	64845	17080	9373	4865
*tpcH Query2*	571	180	110	97
*tpcH Query3*	17687	3272	1792	1334
*tpcH Query4*	23079	3859	2293	1633
*tpcH Query5*	34255	5040	2646	1935
*tpcH Query6*	2250	595	332	204
*tpcH Query7*	18299	3562	1754	1513
*tpcH Query8*	9636	3245	2042	1809
*tpcH Query9*	50470	14094	7559	4460
*tpcH Query10*	19363	4432	2289	1630
*tpcH Query11*	4558	674	424	390
*tpcH Query12*	10522	3815	2718	982
*tpcH Query13*	26823	7577	4425	2121
*tpcH Query14*	2356	624	357	227
*tpcH Query15*	1705	1421	280	170
*tpcH Query16*	7960	3461	2155	1681
*tpcH Query17*	1070	325	220	276
*tpcH Query18*	68366	19719	10174	5829
*tpcH Query19*	2330	711	441	843
*tpcH Query20*	968	314	221	695
*tpcH Query21*	80858	14565	8149	6072
*tpcH Query22*	9418	1762	986	664

Total (second)	457	110	61	39

All the numbers in the above table are query elapsed times in milliseconds except the very last line highlighted in yellow, which are in seconds. The last line contains the total elapsed time for Query1 through Query22 for each MAXDOP setting. All the results were obtained with all the database pages cached in the buffer pool. The same TPC-H query stream (i.e. Query 1 through Query 22) was repeated for 10 times with all the results from the first run thrown out, and the max elapsed time and the min elapsed time for each query for each of the subsequent runs were also thrown out before the elapsed times were averaged.

So what's the difference between Joe's results and mine?

Although there are some differences in the elapsed times in terms of the absolute numbers, the differences are minor enough to be no cause for alarm, especially when you take into consideration the platform differences and probably some differences in the test setup.

However, the deline in the elapsed times in my test results is more significant than it is in Joe's results when MAXDOP is changed from 1 through 16. The difference is not as dramatic as I had throught, and initially commented on in the original version of this post (because I was reading the wrong line for whatever reason).

Given that TPC-H has been around for a long time and all DBMS vendors have tried very hard to optimize their products to performance well on TPC-H, I do not expect SQL Server to be an exception, and am rather pleased to see that it scales very well with MAXDOP on these queries.

I'm a bit embarrassed that I didn't read Joe's results correctly first time around, but felt that it's time well spent to be able to cross check independent tests results from realistic envrionments.

Performance Impact: Bookmark Lookup is Expensive - Even in Memory

Linchi Shea — Mon, 07 Jul 2008 01:54:00 GMT

It’s well known that bookmark lookup (aka key lookup in case of a clustered index) is not cheap, especially when it comes to retrieving a lot of data. So I’m not going to rehash the pros and cons of bookmark lookup or why bookmark lookup is expensive. But I’ve noticed that when it comes to discussing bookmark lookup, all the literature seems to being focusing on their implications on storage I/Os. There is nothing wrong with that. In practice, it ultimately does come down to the fact that when reading a large amount of data, random I/Os are much more expensive than sequential I/Os, and bookmark lookup tends to incur random I/Os.

What I’d like to highlight in this post is that even if all or most of the pages are in memory already, bookmark lookup is still very expensive compared to scan.

To show this is the case, I piggybacked on the test setup described in my last post. Let me briefly recap the test setup. A single table was used, and its definition is as follws:

CREATE TABLE test (
      i           int NOT NULL,
      j           int NOT NULL,
      dt          datetime,
      filler      char(5000) NOT NULL
)

Two indexes were created on the table:

CREATE CLUSTERED INDEX cix_test ON test(i);
CREATE INDEX cx_test ON test(j);

And the table was populated with 2,000,000 rows with the following INSERT statement in a loop (local variable @i going from 1 to 2,000,000):

INSERT test(i, j, dt, filler)
SELECT @i,
       CASE WEHN @i % 2 = 0 THEN @i ELSE 2000000 - @i END,
       getdate(),
       'abc'

Then, the following two queries were separately run multiple times on a server with 32GB of RAM (25GB of which was allocated to the SQL2005 instance buffer pool):

SELECT max(dt) FROM test;
SELECT max(dt) FROM test WITH (index(cx_test));

After the second run, all the pages were cached in memory (the table was about 16GB in size). Notice that with the first query SQL Server used clustered index scan to produce the result, whereas with the second query the index hint forced SQL Server to scan the nonclustered index and then use bookmark lookup to produce the result.

I’ll look at the storage I/O implication of these two access paths in a separate post, but for now what do you think might be the difference in the elapsed time of these two query-processing methods when all the pages were cached in memory?

The following chart shows the difference:

For this test, the bookmark lookup method took almost three times as long as did the clustered index scan method.

Why such a huge difference? When all the pages were cached in the buffer pool, we can’t explain the query elapsed time difference with the difference in storage I/O efficiency. We can, however, explain the difference with the difference in the number of pages SQL Server engine must visit in the buffer pool.

The output from SET STATISTICS IO ON for each method is as follows:

Clustered Index Scan:

Scan count 1, logical reads 2008931, physical reads 0, read-ahead reads 0 …

Bookmark Lookup:

Scan count 1, logical reads 8130363, physical reads 0, read-ahead reads 0 …

Clearly, the access method with bookmark lookup was not efficient, visiting four times as many pages as did the access method with clustered index scan.

Also note that compared with the size of the table, the size of the nonclustered index was insignificant. There were about 5,000 pages in the leaf pages of the nonclustered index, whereas there were 2,000,000 leaf pages in the clustered index. So the cost of processing the query with the nonclustered index was dominated by the bookmark or key lookups.

Of course, what I’ve discussed in this post is probably more academic than practical as you can’t expect all the pages being cached when processing large reporting queries in most real environments, and therefore the real dominate factor is not how many pages SQL Server needs to traverse in memory, but how many pages SQL Server has to bring into memory and how these pages are brought into memory.

It's not my intention to leave you an impression that I'm picking on the bookmark lookup operation because it's just a bad method to retrieve data. That's not the case at all because for some queries, it is an excellent method. But that's not the focus of this post, and there are plenty of discussions on the advantages of the bookmark lookup operation you can find elsewhere.

Test Environment

SQL Server 2005 Enterprise x64 Edition ran on Windows Server 2003 Enterprise x64 Edition with Service Pack 2. The SQL2005 build was 9.0.3239 (i.e. SQL Server 2005 CU7) with 25GB allocated to the buffer pool. The server was a HP BL680 G5 including four 2.4GHz quad-core Xeon E7340 processors (aka Tigerton) with 2x4MB L2 cache and 32GB of RAM.

Performance Impact: Some Data Points on Read-Ahead

Linchi Shea — Fri, 04 Jul 2008 19:24:00 GMT

In the next series of posts, I'll focus on SQL Server I/O, revisiting some common issues and taking a closer look at some others. In each post and as always, I'll make the case with specific data points from my tests. For the first two posts in this series, let me check out the read-ahead technique used by SQL server.

Read-ahead is an important I/O optimization technique used by SQL Server. Intuitively, if SQL Server can correctly forecast the need for more pages and read these pages ahead of time when they are needed for query processing in memory, your query is expected to perform better. Bob Dorr in his classic SQL Server 2000 I/O Basics whitepaper has an excellent description on how SQL Server read-ahead works. Every SQL Server professional with any interest at all in the storage engine should read that paper.

But just how important is the read-ahead technique in query processing? Let's look at some results from an extremely simple test.

First of all, a single table was used in the test, and here is the definition:

CREATE TABLE test (
      i           int NOT NULL,
      j           int NOT NULL,
      dt          datetime,
      filler      char(5000) NOT NULL
)

I then created a clustered index on the i column (for this test column j was not used and you can ignore it):

CREATE CLUSTERED INDEX cix_test ON test(i);

And the table was populated with 2,000,0000 rows with the following INSERT statement in a loop with @i going from 1 to 2,000,000:

INSERT test(i, j, dt, filler)
SELECT @i, CASE WEHN @i % 2 = 0 THEN @i ELSE 2000000 - @i END, getdate(), 'abc'

With the statistics updated, I then ran the following SELECT query in two test scenarios:

DBCC DROPCLEANBUFFERS;
go
SELECT max(dt) FROM test;

The two test scenarios are as follows:

Test Scenario	Description
Read-ahead enabled	This is the default SQL Server behavior.
Read-ahead disabled	Read-ahead was disabled with trace flag 652: DBCC TRACEON(-1, 652)

First, let's look at the elapsed time of the above SELECT query in the two scenarios (the numbers reported in the following table are averages over several test runs):

Test Scenario	Elapsed Time of SELECT (second)
Read-ahead enabled	80
Read-ahead disabled	210

In both scenarios, the SELECT query was correctly processed with a clustered index scan. When read-ahead was disabled, the clustered index scan took almost three times as long as did the clustered index scan when read-ahead was not disabled. For this query, the performance difference was astounding.

I don't have access to the source code that controls read-ahead so I can't tell exactly why and how it made such a huge difference from the code logic perspective. However, from Bob Dorr's description in the SQL Server 2000 I/O Basics whitepaper, it's rather clear that SQL Server read-ahead is quite aggressive in exploiting the performance capacity of the storage I/O subsystem. So we can turn to observing the storage I/O behavior for explanation. The following table summarizes the values of the key I/O counters observed during the test runs:

I/O Counter	Value When Read-ahead is Enabled	Value When Read-ahead is Disabled
Bytes/Read	> 350KB/read	~ 8KB/read
Megabytes/sec	180 ~ 200MB/sec	~ 80MB/sec
SQL Server Readahead	~20,000 readahead pages/sec	0

Clearly, with read-ahead, SQL Server was able to take advantage of large sized I/Os (e.g. ~350KB per read). Large-sized I/Os are generally much more efficient than smaller-sized I/Os, especially when you actually need all the data read from the storage as was the case with the test query. From the table above, it's evident that the read throughput was significantly higher when read-ahead was enabled than it was when read-ahead was disabled. In other words, without read-ahead, SQL Server was not pushing the storage I/O subsystem hard enough, contributing to a significantly longer query elapsed time.

That is, the table was about 16,000MB in size. At ~200MB/sec, it would take about 80 seconds to read 16,000MB, and at ~80MB/sec, it would take about 200 seconds to read the same amount of data. And these numbers (i.e. 80 seconds and 200 seconds) match nicely the recorded query elaped times which are reported in the second table above.

In the next post, I'll check out the impact of disabling read-ahead on bookmark or key lookups when a nonclustered index is used.

Dedicated Admin Connection

Linchi Shea — Tue, 20 May 2008 16:31:00 GMT

Dedicated Admin Connection (DAC) is one of the highly touted features introduced in SQL Server 2005. Now that it has been around for a few years, I wonder how it is actually being used or whether it's being used at all in the real world.

I have not run into any real production scenarios where I'm forced to use DAC. But that may just be due to my own narrow exposure to SQL Server problems. If you have used it to solve real problems (not just for testing or folling around), I'd very much like to know what kind of problems they were.

SQL Server Checkpoint I/O Behavior

Linchi Shea — Sat, 19 Jan 2008 07:11:00 GMT

Andrew Kelly in a recent post here advised visiting/revisiting the SQL Server I/O basics, and I completely agree. A firm grasp of the basics can make it easy to understand some system behaviors that otherwise may be puzzling at times.

A question that is often asked is how SQL Server performs the I/O writes in its checkpoints. More specifically, some folks are puzzled at why SQL Server checkpoints don't seem to write to disks using a constant block size. For the basics, Bob Dorr has a detailed description on the checkpoint I/O behavior in his two articles: SQL Server 2000 I/O Basics and SQL Server I/O Basics Chapter 2. You can read these articles for all the information. What I want to highlight is that, "SQL Server 2005 extends the capability of WriteMultiple up to 32 pages (256 KB)." And SQL Server checkpoints make calls to this WriteMultiple internal routine.

Now, note that one of the most fundamental disk I/O rules is that large sequential I/Os are more efficient than small random I/Os. So if SQL Server can write 32 pages in a single I/O, it would try to do it. As a matter of fact, as Bob Dorr mentioned in his article, SQL Server 2005 has gotten more aggressive in finding as many contiguous pages as possible and lumping them into a single I/O request. If SQL Server can't find contiguous pages, it would write one page at a time in its checkpoints, and that would be very inefficient.

To see this in action, you can run a simple test yourself. First, create the following table:

create table PageCheck (

c1 int identity,

c2 char(5000) not NULL

)

And then populate the table with one million contiguous pages:

declare @i int

set @i = 1

begin tran

while @i <= 1000000

begin

if @i % 100 = 0

begin

commit tran

begin tran

end

insert PageCheck(c2) values(' ')

set @i = @i + 1

end

commit tran

Now effectively disable automatic checkpoints:

sp_configure 'recovery interval', 32767

reconfigure with override

Case 1

Run the following SQL to update the first 500,000 rows (corresponding to 500,000 contiguous dirty pages):

update PageCheck

set c2 = 'abc'

where c1 <= 500000

Now we are ready to see how a checkpoint may behave by doing a manual checkpoint and observing the Avg. Disk Bytes/Write performance counter.

The Avg. Disk Bytes/Write counter would show that each request would be around 256K, or 32 8K pages.

Case 2

To see how a checkpoint behaves when there is no contiguous dirty pages, run the following SQL to update every other page:

update PageCheck

set c2 = 'xyz'

where c1 % 2 = 0

Do a manual checkpoint now, and observe the Avg. Disk Bytes/Write counter. The counter value would be around 8K.

The first case represents the best scenario for checkpoint I/O performance, and on my machine the manual checkpoint took ~21 seconds to complete. The second case however represents the worst scenario, and on my machine the manual checkpoint took ~71 seconds to complete. The difference was more than three times.

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.