May 2012 - Posts

Read Committed Snapshot Isolation– Two Considerations
11 May 12 09:27 PM | GavinPayneUK | with no comments


The Read Committed Snapshot database option in SQL Server, known perhaps more accurately as Read Committed Snapshot Isolation or RCSI, can be enabled to help readers from blocking writers and writers from blocking readers.  However, enabling it can cause two issues with the tempdb database which are often overlooked.

One can slow down queries, the other can cause queries to fail.

Overview of RCSI

Enabling the option changes the behaviour of the default SQL Server isolation level, read committed.  By default, this isolation level means that if one session is updating a range of rows then another session cannot execute a select query against the same set of rows; the shared locks the select query needs are incompatible with the eXclusive or Update locks that the update query will be using; the session executing the select query ends up being blocked until the transaction involving the update query has committed.  That’s a very high level overview of what happens so those wanting to know more or become an MCM should watch this video here.


Configuring a database to use the Read Committed Snapshot option aims to stop this happening by using Row Versioning.  Essentially, before a row is locked in preparation for changes a copy of it is put in a special part of the tempdb database, known as the version store.  When a select query then wants to read that row it uses the copy in the version store rather than waiting for the actual row to have its locks released.  Again, I recommend watching the video linked to above to see just what happens as I’m just giving a high level overview of the feature before I discuss the primary points in this article.


Tempdb Performance

One of the first things people mention when you suggest enabling the Read Committed Snapshot option is that it will put pressure on the tempdb database which stores the version store.

That’s correct it will, but a properly configured tempdb database should never be a bottleneck in your environment.  You can read how super-tuning expert Thomas Kejser put the world’s biggest database workloads through SQL Server and never had to worry about tempdb performance here.

There are two considerations I recommend for configuring your tempdb database in order to ensure good performance:

  • The number of tempdb data files – in the absence of your own monitoring for PFS, GAM or SGAM contention within tempdb I hear people at Microsoft these days recommending having 8 equally sized data files which I’d agree with.  They don’t need to each be massive, their total size could still be 8MB(!), but having more increases the number of internal system objects within the database so reduces the changes a single system objects becoming contended.  (The rule in SQL Server generally tends to be that if you have any kind of resource contention add more of that resource; whether it be IOPS, locks or PFS pages etc.).  Having too many data files is much less likely to cause you problems than having too few, just be sensible about how many you have.


  • The type of physical storage for the tempdb data files – as always, configure the storage you use for tempdb like you would for your database files.  Fast, reliable and striped arrays, and while Thomas will tell you that using dedicated disks isn’t important for him, others will tell you its crucial.  The only additional comment I have is that some people deploy NAND storage specifically for tempdb, in fact SQL Server 2012 had some changes to support this with failover clusters.


Crucially though, having a poor performing tempdb database won’t break a user’s queries, they may just run slower than they could do, especially if you’re using the read committed snapshot option.


Tempdb Sizing

How much space your tempdb database needs depends on how you use SQL Server, some people need very little space whiles others need lots.  What’s relevant to this article is that the read committed snapshot option doesn’t just like tempdb space, it needs it.

We mentioned how rows get written to the version store in tempdb as they get locked in the source user database, but what happens if there’s no free space in tempdb when that process goes to happen?  I did a test to find out, you can email me for my demo script if you want to try it for yourself.

In summary, when tempdb is full update queries will still execute successfully but select queries which expect to find the rows they need in the version store will fail when they can’t find them there.  You’re likely to see this message:


Msg 3958, Level 16, State 1, Line 1

Transaction aborted when accessing versioned row in table 'dbo.t1' in database 's1'. Requested versioned row was not found. Your tempdb is probably out of space. Please refer to BOL on how to configure tempdb for versioning.

So how do you stop that from happening? 

The safety option I suggest is to place tempdb on a drive with lots of free space although its acceptable to configure tempdb to start with small initial data file sizes.  However, make sure you configure the date file auto-grow settings so that when tempdb runs out of free space SQL Server will automatically allocate it some more.  There are methodologies you can use for setting a good initial size for tempdb but for now enabling the autogrow option is a good failsafe.  Then, after a couple of months see what size the data files have grown to and consider setting that to be their initial sizes. 


General Recommendations, Test

Those are very general recommendations, along with setting the data file auto grow size to 500MB, but in the absence of specific testing or advice for your environment they might be something you want to try using in a test environment to help you learn more about tempdb behaviour in your environment.

Knowing when you’re ready to attempt to become an MCM of SQL Server
01 May 12 10:09 AM | GavinPayneUK | with no comments

Since I became a “Microsoft Certified Master of SQL Server” I’ve had lots of people asking me how I prepared, how I knew I was ready to take the exams and what they were like. At the SQLBits certification session recently I was invited on to the panel to answer questions about the “modern” MCM program. This article hopes to answer some of the questions people ask yet they will always have very personal answers. What feels difficult for me might be slightly less difficult for you yet I might make it sound really difficult etc. Despite this, I thought I’d share my experiences with you and hopefully help you decide when you’re ready to attempt to become a Microsoft Certified Master.

Before we start, I should point out a couple of things: this article has been reviewed by the former MCM program manager Robert Davies so don’t think it gives away secret information about how to prepare - none of that is covered by any NDA anyway, and also realise that the MCM is a very high level of certification, its not for everyone.  Read the experience the program assumes you have and ask yourself if that’s like your background.

How I Prepared

There were three specific steps I took for in my preparation and luckily I was able to make most of them part of my day to day role at Coeo. They were:

1. Real-world experience

2. Reading materials

3. MCM readiness videos

Equally as importantly, you need to find a way to make the intense preparation more exciting to you than just sitting down in an evening and reading a book. If you take that approach you’ll run out of mental energy and begin cutting corners in your preparation which will reduce your knowledge and familiarity with features. I introduce the word familiarity there for the first time, that’s an important word for the MCM’s requirements of you.


This certification isn’t about knowing every syntax, command or trace flag ever published, it’s about knowing what features SQL Server has, how they work, how they work with each other and their limitations. No book will teach you all of that, only having used them a lot will you become familiar with them. For example, if the lab exam asks you to meet requirement X then you should immediately know that feature Y is the answer, then is not the time to be teaching yourself the difference between two similar features. Of course, if you also know there’s something which can stop a straightforward implementation of feature Y then your familiarity with it needs to be able to tell you in advance what that issue will be – and how to fix it.

Real-world Experience

Nothing can prepare you better for the MCM exams than real-world experience of having used SQL Server, that’s where your familiarity comes from. Of course, that probably doesn’t mean managing your company’s HR or Intranet databases as the MCM is focused at enterprise environments with requirements and issues which require MCM level experts to resolve them. I was lucky that my day job put me in those enterprise situations almost every day and while you may not think you have that luxury you’d probably be surprised.

My advice is to do the following regardless of what your involvement with SQL Server is:

  • become familiar with the MCM’s exam objectives and your understanding of what they require you to know (the readiness videos may help if you’re not sure) in terms of features and best practices, they are available here
  • generalise each SQL Server request or requirement you get given in your current role, remove what makes it specific to your environment and most importantly, the limitations your current environment might put on how you would deliver it
  • then, in your own time, consider how you might meet that requirement in a way that best shows your understanding of the MCM’s exam objectives. For example, using peer-to-peer replication might not be possible in your environment but it could be the most appropriate way an MCM would implement a solution in an environment with no legacy restrictions
  • with your chosen approach planned, implement it on some virtual machines. You don’t need to worry about having complicated database schemas or large volumes of data, nor do your virtual machines have to be fast. You should be able to create the databases and data for your tests in 60 seconds, keep them simple so you can focus on getting the feature to work so you can learn about it. Spending a whole evening just trying to replicate all of the tables in your intranet database is an un-necessary way to waste time
  • with the feature installed, remove it and repeat the setup, determine if its best to use the GUI or T-SQL setup steps in case there are options you can’t chose through the GUI, then find out how you troubleshoot and diagnose that feature, are there DMVs or log files etc.

That may seem like a lot of effort but that’s the level of preparation I put into learning about SQL Server for my exams. Of course, there will be some features you might know more than enough about already, great, tick those off and move onto those you don’t know so well.

Reading Materials

The MCM web site gives you a very thorough reading list, here, which covers almost every part of SQL Server in almost every depth you can imagine. How much of that reading list you can ever read is a very personal decision based on time, ability and ultimately your interest in some of the subjects. What I will say is that every little thing you know about SQL Server when you go into the exams will boost your confidence, especially if you find you know more than is required. Some of the books can be expensive and difficult to get hold of so don’t feel they’re compulsory reading. Instead, Books Online and blogs can give you the equivalent knowledge but you will have to spend more time searching for it. I’m not going to recommend any specific blogs, only that the types of blog you read should typically be authored by people who teach pre-cons or deliver level 500 training at conferences.

When you do read a book, make sure you have a good note taking method. There’s no point in reading a book, getting to the end and forgetting what you read 2 days ago at the beginning. I used OneNote but there are many variants of the productivity tool around.

MCM Readiness Videos

I found the videos, available here, very useful but not in the way you’re probably imagining.
I watched them at anywhere between 1.2 and 1.6x their normal speed depending on who was speaking. Why? Because I used them for revision and checking I knew what was being shown, not because I was using them as my only learning opportunity or because I wanted to emulate the classroom experience. If I didn’t know something that was being shown I either stopped the video and researched that subject, or watched that part at normal speed. However, you cannot pass the MCM exams just by watching 40 hours of videos! They are readiness videos, not a complete training course.

That was “all” I did for my preparation. Notice I didn’t call it revision, as I was honest and had to learn new things for the MCM exams, you will need to as well. I also didn’t do any MCM training courses, practice labs, webinars or use any discussion groups etc. However, watching the #sqlmcm hash tag on Twitter though may be of interest though.

Knowing When You’re Ready

If someone asked if you’re ready to take the exams you’d always say you need another month or 2 months. In fact, the more you prepare the more you realise you don’t know so the more time you feel you need, that’s just because you’re aiming for a perfection that’ll never exist. Below are three recommendations my mentor, an MCM, gave me to help determine if I was ready and prepared:

  • if your preparation plan is going to be more than 6 months you’re not ready to even begin. You can’t sustain the preparation intensity for any longer than that
  • you should be able to confidently talk about of a feature of SQL Server to someone else who works with the product for 90 seconds and not end wondering if the other person believed what you were saying because everything you said should have been true and something you’d learnt
  • you should be able to take all four of the pre-req exams needed for the MCM the day before you take your first MCM exam and not think anything of it. If passing an MCITPro exam is still a big hurdle for you, you’ll need to re-consider your approach to the MCM exams

So, that’s my advice, it’s not right, it’s not wrong, it’s just my approach that I used for taking, and passing first time, the MCM exams. Good luck with yours!