Search results matching tags 'Performance' and 'SQL Server 2005'

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.

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.

Interview With Kalen Delaney About Inside Microsoft SQL Server 2005 Query Tuning and Optimization

Denis Gobo — Mon, 15 Oct 2007 13:58:00 GMT

I am a big fan of Inside Microsoft SQL Server 2005 The Storage Engine so when I saw that yet another book got published in this series I just had to check it out. Inside Microsoft SQL Server 2005: Query Tuning and Optimization is very well written gets to the point and give you the answers that you need. I decided to contact Kalen to see if she would be willing to do an interview, by now you know that the answer is yes of course ;-)

The question-and-answer session with Kalen that follows was conducted via email.

What is the audience for this book, is it the enterprise user or can a small department benefit from the tips in this book?

Because this book deals with query tuning, anyone who writes SQL queries for SQL Server can benefit. Very little in the book is geared towards system tuning, so the size of the machine doesn’t really matter. Now of course, if you have a very small system with very small tables, you won’t get as much benefit out of tuning your queries. However, if you have any tables of more than a few thousand rows, and you do any joins, you will need to tune your queries. In addition, the issues of blocking and concurrency control can impact any system, no matter how small.

What new technologies in SQL Server 2005 do you think are the most beneficial for performance?

For very large databases, the best new technology is partitioning. For any size system, if you have had serious performance problems due to blocking, you might find a big performance benefit by using one of the snapshot-based isolation levels, but you really need to understand the resource costs that come along with the improved performance. For your individual queries, I think the new optimizer hints and query level recompiles can make a big difference. For indexes, the ability to add included columns to nonclustered indexes can give some of your hard-to-tune queries a major performance boost.

What will a person who reads this book gain in terms of understanding how to performance tune a server?

The focus of this book is not so much on tuning the server, but on tuning queries. There is more in Inside SQL Server 2005: The Storage Engine on server issues such as memory and processor management. The biggest server wide issues are covered in Chapter 5, when I talk about managing the plan cache, and how and when query plans are reused.

Is the book geared towards a beginner/intermediate level user or do you have to be an advanced user to really utilize the information in this book?

The book is not geared towards beginners, but everyone should be able to get something out of it. It’s primarily geared to SQL Server developers and DBA’s who have been working with SQL Server for a while, and have encountered performance problems that they are trying to find solutions for.

With all the changes in SQL Server 2005, how critical has the tempdb become in regards to performance?

Tempdb has always been important. In SQL Server 2005, if you are using one of the snapshot-based isolation levels, you are going to have to be more aware of the demands placed on tempdb, both in the sizing requirements and the additional I/O resources needed. Fortunately, SQL Server 2005 provides tools to monitor tempdb, including a dozen new performance monitor counters, and a dynamic management view, sys.dm_db_file_space_usage, that keeps track of how much space in tempdb is being used for each of the different kind of object stored in tempdb.

I understand that this is the first time you wrote with a team of other writers; can you tell us something about that experience?

I initially thought that not having to write the whole volume by myself meant that I could get it done sooner, but that was not the case. Everyone had their own schedule and their own way of writing. The personal aspect of working with the other authors was great. I deeply respect all of the others and it was an honor to be working so closely with them. I had some concerns about the depth of coverage and I wondered whether all the chapters would end up being as deep as I hoped for, but that turned out not to be a major problem. The only real issues were agreeing on a common terminology and coding style, and even that wasn’t that big of an issue, because I got to do a final editing pass on everyone’s chapters.

What SQL Server books are on your bookshelf?

All of the Inside SQL Server books are there, of course, and all of Ken Henderson’s books. Bob Beauchemin’s book is in my car, to read while I am waiting for the ferryboat, and while on the ferry. I also have technical books that aren’t SQL Server specific, such as Jim Gray’s Transaction Processing, Russinovich’s and Solomon’s Windows Internals, Chris Date’s Introduction to Database Systems and Mike Stonebraker’s Readings in Database Systems.

Why do you write technical books?

I love working with SQL Server and trying to find out all I can about it. When I found that I could explain difficult concepts in a training environment, I thought I could do the same thing in a written format, and reach more people that way. I have always loved explaining things, ever since I was a teaching assistant for High School Math.

Will you be updating your books for SQL Server 2008?

I have just started meeting with my editors at Microsoft Press about SQL Server 2008, and it looks like a revision is in the plans. We’re really looking at it as just a revision, with the same structures as the current books, with straightforward changes and the inclusion of new features.

Name three things that are new in SQL Server 2005 that you find are the most valuable?

Dynamic Management Views, Dynamic Management Views, and Dynamic Management Views! Oh, you wanted three different things? ;-) How about XML query plans and optimization hints. (I’m also very fond of many of the new TSQL constructs, but I was only talking about things that I cover in my new book.)

Name three things which are coming in SQL Server 2008 that you are most excited about?

You’ll have to ask me this next time. I have actually been avoiding SQL Server 2008 while I was getting my Query Tuning and Optimization book finished, because I didn’t want to get distracted.

Can you list any third party tools that you find useful to have as a SQL Server developer/admin?

I’ve tried a few other products, but usefully I find that it is much easier to just stick with the Microsoft line and use the tools provided with the product.

Name some of your favorite non-technical books.

Oh, I love to read. It would be impossible to list my favorite books, but I can tell you my favorite authors, most of whom write science fiction: Lois McMaster Bujold, Ursula LeGuin, Sheri Tepper, Orson Scott Card, Octavia Butler, Elizabeth Moon. I also love to read historical fiction like Leon Uris.

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.