SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Confessions of a DBA – Part 3

After reading this confession, I am sure almost every DBA is going feel a churn in their stomach. They are all going to say “How could he do that? Does he not know that it is not right? Why does he keep doing that still?".

My confession is that we use READ UNCOMMITTED all over the place. It is used regularly on the Report DB and the OLTP server. This is even part of our Review guideline (Yes, we review all DB objects before they hit production).  There I said it. Before you say “Are you nuts” let me give you some back ground information on our set up.

We have a web farm that currently has more than seventy-five servers. We also have a cluster for TS where other client applications are running from. Our customer base is over four hundred thousand and our website pulls an average bandwidth of 8 MB per sec peaking up to 15 MB at peak time. At given time, there are more than eight hundred SQL connections on the OLTP database. We do an average of 13K batch requests per second (peaks up to 21K).  Now you get an idea of how busy our SQL Server DB is.

In my previous blog I had stated that we did some performance test while upgrading our server. We were able to an average of 8K batch request per second. That was couple of years back. We had half the customer base as we have today. There fore that performance was good enough. But while doing some capacity planning, we were advised that we could expect double the traffic on the web site in two years time. That meant that our server would not be able to service the request from the website in a timely fashion.

I did some research on how we could get more out of the server. I was looking basically for a “Turbo switch”. I poured through blogs and articles trying to find out what could be done. Everyone was talking about tuning the queries that hit the DB. You can get only so much out of tuning queries. That was when I read the blog from Linchi Shea where he stated the performance between a READ_ONLY DB and a READ_WRITE DB. The more the load (User connections) the more the difference is. The difference was around 10%. Keep in mind that even though the tests were run on DB’s that were READ_ONLY and READ_WRITE, the queries were just Selects. But in a real world scenario, there are multiple reads and writes happening concurrently. There fore in our scenario, the performance gain would be much higher. I knew that I found my Turbo switch. But there are drawbacks in using READ UNCOMMITTED. The dataset returned to the application might not be accurate, you could get strange errors like “Could not continue scan with NOLOCK due to data movement”.

I went back to the management and advised them of all the issues that we would get by taking this approach. The management had no problem with showing data that might not be accurate but they wanted to make sure that when ever a transaction is done, it had to be based on the right values and if there was a change in any value, the customer should be advised of the change before inserting the data. That was not difficult to achieve. We started using READ UNCOMMITTED and we have not looked back after wards.
In addition to this, we did quite a bit of work on query tuning, moved all queries that looked at historical data to our replicated server and then doubled our RAM. Currently we have 256 GB of RAM. We review all objects that are to be released into production, maintain the indexes and monitor the DB for any irregularities. We have also introduced Caching of data in the application layer so that the database does not get a heavier load.  Our next project is to add an additional Drive shelf with its own IO controller and see if we can gain a bit more IO performance by moving certain tables and/or indexes into that drive by adding another file.

If anyone has better suggestions than what we are doing, please feel free to comment. All comments are welcome.


Posted by dwentzel on 14 April 2011

NOLOCK isn't as bad as everyone says as long as the developer understands the issues.  We use it everywhere to avoid lock escalations on a highly transactional system.  For instance, we often need to read from a big table into a temp table, do some laborious processing, and spit out the results at the end.  This causes lock escalations.  The fix 'sometimes' is to fetch from the table using NOLOCK with the necessary filtering.  The processing continues and the final SELECT joins back to the original big table by key with the original filter conditions.  The net effect is any rows that were modified by another process will not be returned in the final output, and of course, less escalations and shorter lock durations.  

Posted by Roy Ernest on 14 April 2011

I agree with you. In the example you stated it is pretty safe since you check back with the original table after the processing.

Posted by Malcolm Daughtree on 14 April 2011

READ_COMMITTED_SNAPSHOT.  Seeing as you don't mention the SQL Server version I'm assuming if you are using NOLOCK it must be SQL 2000.  If so I'd upgrade and use a better isolation level than NOLOCK. (READ UNCOMMITTED).   Because the SQL Server query optimizer typically selects the best execution plan for a query, MS recommend that hints be used only as a last resort by experienced developers and database administrators.  There are a couple of point you don't seem to have considered. (from B.O.L)

1. apply only to data locks.

2. This may generate errors for your transaction, present users with data that was never committed, or cause users to see records twice (or not at all). For more information about dirty reads, non-repeatable reads, and phantom reads.  We have a system that contractors maintain that uses the NOLOCK hint and causes trigger and internal cursor data errors.  USE with Caution.

Posted by asghar on 15 April 2011

Nice article

Posted by Steve Brett on 15 April 2011

Good article - you chose a risk based approach and got everyone on board. A friend of mine has done some interesting work using solid state drives as the IO was always the bottleneck. They got huge speed improvements and a cost saving as well !

Posted by Roy Ernest on 15 April 2011

Hi Malcolm,

I forgot to mention the version. This is a SQL 2008 SP1 (64 bit) running on Windows 2008 EE. When doing transactions, we use committed read. Yes, there are risks when using hints. Using a READ UNCOMMITTED hint will not change the execution plan.

Before we came to the decision of using NOLOCK, we tested with Row level version (Wrote an article about it). But that also does not give the performance we want. It is very heavy on the TempDB. CPU usage is higher. IO bottleneck is problem when the load goes higher than 15K batch request per second.

Posted by Roy Ernest on 15 April 2011

Thanks asghar. Thanks Steve. We are tempted to use SSD when we upgrade our server this year. HP has SSD that seems pretty stable. We could move our TempDB into the SSD.

I like the FusionIO card. They are planning to release a new product that is just like the card that they have but is Hot swappable (You do not need to open the back pane of the server). I would love to use that.

Posted by sjimmo on 15 April 2011

Thanks for the article. We do the same thin g for the exact same reasons. We have discussed SNAPSHOT ISOLATION and ruled it out do to the cons in our environment. I recently was able to convince our management staff to move towards a replicated system which is allowing us to strategically place databases in locations and have them tuned for that location. As for placing files on different drives, you will find, depending upon how you do it, a considerable improvement. We are also relying more and more on partitioned tables with SS2005 and up.

Posted by Robert L Davis on 15 April 2011

We usedto use the nolock hint on almost everything at one place I used to work at. We didn't care about dirty reads at all. We had real estate data for real estate web sites. The difference in the locking overhead was tremendous for us.

Posted by Roy Ernest on 15 April 2011

Thx for the comments sjimmo and Robert. NOLOCK is not bad as long as you know the cons of using it and the management/client are OK with it.

Posted by Glenn Berry on 15 April 2011

We have used NOLOCK hints quite a bit at NewsGator. It actually started before I got there...  As long as you are aware of the risks, and the business buys in, it makes sense in your scenario. Kudos to you for admitting your dirty little secret...

Posted by Roy Ernest on 15 April 2011

Thanks Glenn. It is DIRTY allright.. :-)

Posted by Todd.Everett on 15 April 2011

Where I work its used like its required for every query.  People just assume its something they must do to avoid locking.  But it still makes me nervous depending on the situation.

Posted by pl80 on 16 April 2011

My my 13K batches/sec, that is a busy server.  The busiest of mine does 2K with spikes up to 10.  Avg for a day is more like 200-300 batches/s.  But we have more connections - up to 2286 at a time.  Read uncommited is not possible in our environment, but we get to use READ_COMMITTED_SNAPSHOT.  It doesn't improve speed, but eliminates concurrency issues and any timeouts (in the application).

Posted by Roy Ernest on 16 April 2011

2286 sql connections? Or is it client connections? If it is 2286 sql connections then that is very intersting. We are trying to cut down the open sql connections as much as we can so that we can get a bit more out of the system

Leave a Comment

Please register or log in to leave a comment.