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.

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.

Familiarity

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!

Sometimes you have a requirement which you don’t have a tool in your toolbox for.  When that happens the solution may not be to just get another tool and add it to your existing toolbox, it might be you have to use an entirely new type of toolbox with tools you’ve never used before in it.  Doing that maybe a bold architectural decision but is often where the biggest performance or functionality gains can come from.

Recently, in the SQL Server space there have been some technologies become available that showed how existing performance bottlenecks were removed by solving the problem in a completely new way to the previous successful but relatively temporary fixes.  Two examples we’ll consider are solid-state storage and Columnstore indexes.

10x faster can’t always come from 10x the power

Tonight I read the keynote presentation that Conor Cunningham gave at SQLBits recently about ColumnStore indexes (available here) but rather than read about how ColumnStore indexes work I read it from an architectural decision making perspective. 

Midway through the presentation is the line “Making something 10x faster is HARD”, followed by a slide questioning how could you make a query run 10x faster.  The answer wasn’t optimising the existing way of doing things, it was to implement a completely new way of working, in this case a new query optimiser mode and index format.  The existing options just couldn’t be tuned anymore to meet the requirement.

Game changing storage

The other example I quoted was the introduction of solid state storage in database servers.  Making storage fast was always a goal of infrastructure teams, they used different RAID levels, different caches and different numbers of disks until the industry realised that 5 and 10% gains weren’t always worth the effort or cost of implementing.  Instead, what we wanted was something that was 10000% faster and the answer wasn’t more disks, it was a totally different approach to persistently storing data, in this case using memory chips instead of spinning platters.

Conclusion

You’re probably never going to be asked to re-write a query optimiser or create a new type of storage hardware, however you maybe asked to keep making something go faster or scale further after having just done all you can do to make it faster.  It could be making reports run quicker or supporting more concurrent users.  My conclusion is that you shouldn’t just think about how to solve today’s problem using today’s options, think about a medium term option that may mean doing something radical but it won’t be a shock to you when you realise you need to do it.

At the recent SQLBits X conference I was invited onto the panel during a session about Microsoft certifications and I said I’d write a follow-up article about some of what we discussed.  While one of the session’s aim was to start communicating the changes the SQL Server 2012 certifications are bringing, most of the questions were about Microsoft certifications in general, although there was a disproportionately high interest in the Microsoft Certified Master.  I’ll cover MCM topics in a separate post.

These are just my views and the advice I give people who ask me similar questions, they may differ from yours, they may not.

Which level of certification should you aim for?

For the SQL Server database engine, there are four levels of certification, the BI track will soon have a similar number, and at the same time all of the certification names will change but for now I’m going to use the SQL Server 2008 era titles and list them in increasing order of “difficulty”.  They are: Technical Specialist (MCTS), IT Professional (MCITPro), Certified Master (MCM) and Certified Architect (MCA). 

Which level is right for you depends on your personal goals.  If you have only just started using SQL Server or want to get confidence through external validation that what you know is accurate then the Technical Specialist is a good first goal.  In order to prepare for this you’ll probably need to broaden your knowledge to include some SQL Server features you haven’t used before, preparing you for when you do use them.  This is an important step as it shows how certification will drive your professional development as well as deliver your certification goals. 

If you take a Technical Specialist exam and get a good pass mark then you’re probably already on the way to achieving the IT Professional certification.  I’d recommend this level for anyone who uses SQL Server all day everyday and has done so for 1-2 years.  Passing this level will not only validate your knowledge, but also give you confidence to talk about SQL Server .  If you’re in a role that deals primarily with SQL Server then you’re going to have people asking you questions about it, you’re on the road to becoming your department’s subject matter expert. 

When it comes to the Microsoft Certified Master there’s only one person who can tell you if that level of certification is a suitable goal for you, and that’s you.  Actually, your manager might have a view as well especially if you work for a company that’s passionate about SQL Server.  If you look at the requirements that microsoft.com gives for Microsoft's highest level of technical certification and think “I can do that” or “I want to pass that” then you’ve just set your next goal.  It’s true that there’s probably a lot more thought that goes into aiming for this difficult and expensive certification but ultimately you have to have that “I want to pass” motivation in you otherwise the 6+ months of preparation will just be something you start tomorrow.  If you’re serious about wanting to pass the MCM then it has to become part of your everyday life.  I’ve met a few people who are “18 months into a 6 month revision plan”, they’ve just got side-tracked apparently.

The Microsoft Certified Architect is Microsoft’s highest level of certification for SQL Server and one which often gets overlooked.  Why?  I put it down to it not being a technical certification.  To me however its equally as important as any technical credentials you might have if you’re positioning yourself as one of the best in the world at deploying or architecting SQL Server solutions.  Being a Microsoft Certified Master is a pre-requisite for taking the MCA program which doesn’t have an exam.  Instead you have to submit a 40 page portfolio and attend a 6 hour Board in Seattle where you’re quizzed on design and deployment methodologies, communication and influencing styles, and risk and resource management amongst other things.  For me personally, its an exciting challenge.  After 2 years of tech-heavy MCTS, MCITPro and MCM preparation I need to balance my skills with the ability to walk into a blue-chip company and say “here’s the right SQL Server architecture for your business and technical requirements, and I’ll lead the implementation of it.”  That to me is powerful.  

Which version of certification should you choose?

Whenever a new major release of SQL Server appears people always starting asking whether they should stop revising for the previous version and start studying for the new version.  Again, that’s a question only you can answer as versions are only old if you start using something newer.  If you work in an environment where your software vendor’s support policy means you’ll never be able to upgrade from version x of SQL Server and you want to learn more about that version then there’s probably no benefit in you taking exams for a newer version that you’ll never use.  If however you want your knowledge to always be relevant and current then you’ll quickly get used to taking exams as soon as they get announced.  However, if you’ve taken that approach in the past make sure you maintain the real-world experience to back up text-book reading otherwise your success with the SQL Server 2012 exams and especially the MCM will be limited.

Strengths and Weaknesses

One of the perceptions newcomers to certification often worry about is that you need to be an expert and get every question in an exam right in order to pass.  If you look at the learning topics and begin to see un-familiar subjects, perhaps impersonation, XML or CLR, you might think you need to be as good at those areas as you are in your stronger areas.  The good news is that to my knowledge, no Microsoft exam requires you to get 100% to pass, in fact the published pass mark for most if not all is 70%.  I’m not suggesting you can ignore entire areas of the syllabus, but at the same time you don’t need to get every question right to pass.  If you go into an exam knowing more about indexing than required but less about XML than needed you might be lucky and pick up extra marks to balance out your overall score.  Of course, there are quite a few topics you' need to be familiar with the pass but knowing that its ok to get some questions wrong should help remove the fear that you need to be an expert in every part of the exam.  No one is an expert in everything, someone will always have a strength that makes up for a weakness elsewhere.

As a SQL Server architect/consultant/advisor/advocate/you name it, one of my roles is to advise clients on the best solution for their requirements and their budget.  That last piece may seem like common sense but people often forget with SQL Server where the dividing line between the Standard and Enterprise editions sits. 

Knowing what comes in each edition, or more commonly what doesn’t come in Standard Edition especially in the area of high availability, is critical for me when I’m designing solutions for clients.  So, with the recent public release of SQL Server 2012 my hunt for the “official SKU breakdown” began.  There have been licensing datasheets released since late last year but they were too high level for me and the marketing terminology didn’t always translate into technical features. 

Fortunately, TechNet has a feature by edition breakdown well documented on a web page here.  Some of what the page shows you could guess, such as AlwaysOn Availability Groups being Enterprise Edition but 2-node FCIs remaining in Standard, however there were lots of surprises, caveats and footnotes which caught my eye.

I’ve yet to test some of the exact scenarios the web page described, but here is a list of the licensing breakdown which caught my eye:

• Database mail, SSDT, Intellisense, and version control support are not available with the 64-bit version of Standard edition
  
• There are a lot of advanced SSIS features and adapters only in the Enterprise edition and not in the BI edition
  
• Database mirroring is still listed as being supported in Standard and BI editions, just deprecated
  
• Database Tuning Advisor has limited functionality in Standard Edition
  
• The BI and Standard editions are limited to lesser of 4 Sockets or 16 cores
  
• Standard edition also has a “Maximum Compute Capacity” limit, explained in a complex way here
  
• The database engine in BI edition is limited to 64GB of memory but SSAS and SSRS have no memory limitation

I don’t intend to review each observation, instead make a general observation that if you’re looking in Microsoft’s words to deploy more than “non-mission critical” workloads on Standard or BI editions you should check the SKU breakdown first.

As many of you know, I run a small SQL Server community event here in the UK, SQL Server in the Evening, with the help of Coeo colleague and MVP Justin Langford.  There’s been half a dozen evening events in the last 16 months and recently it got to the point where I needed to start putting proper tools in place to communicate with my event’s followers.  As well as telling them when the next event is it’d also be nice to share some of the Chapter Leader mails I get from Pass etc.

Chaining Together Disparate Services

What amazed me was not only how many of the essential services I needed were available for free but also how they integrated with each other.  I’ll now talk you through what I used and how it connected.

Web Site

I have a really really simple web site for the event (link) which just lists information about the next event.  I host this using a free blog provided by Wordpress (link) but rather than creating blog posts, I just have a single “page” which I edit with each event’s details.  Wordpress have a content management system so I can create and edit the page in a web browser, no need to upload files like a traditional web site.  I should one day make something more of the web site but I’ll save that for the future. 

Domain Name

One of the nice things Wordpress also do is sell and host domain names, before integrating them seamlessly with a blog site.  For about $20 a year I have my domain name (sqlserverintheevening.com) linked to my free blog site and hosted by Wordpress for me.  That may not sound that impressive today but setting all of this up took about 5 minutes and was done through a couple of simple web pages. Use of the domain name isn’t just limited to the web site though, its also used by email.

Email

Wordpress have a setup web page in place that will do all the domain name configuration needed to accept email for your domain.  What’s even better is that they a step by step guide for configuring a Google mailbox to send and receive using your domain name.  This is a pretty complicated process but Wordpress’s and Google’s configuration pages together make it a 5 minute process.  5 minutes later I had a Google Mail mailbox setup to send and receive email for sqlserverintheevening.com.

Event Registration

I’ve used EventBrite (link) to meet my venue’s requirements of providing a list of who’s coming to each event.  This is a very slick web site which allows you to customise your event listing, the information you require, and more importantly store and provide the lists of who’s attending each of your events.   

Email Mailing Lists

Finally, after the event I want to email those who attended and tell them about the next event.  For this I use Mail Chimp (link) and their free service is good enough for almost everyone I’d imagine.  The web site does everything you need to create, manage and report on an email “campaign” as they call it and most importantly, people can un-subscribe without my involvement from the list. 

The best part about Mail Chimp for me though was it can integrate into my EventBrite events and extract the names and email addresses of everyone who’s attended one of my previous events.  That was really straight forward and took about 30 seconds, priceless.  I also love being able to see how many emails bounced, were opened and who clicked the web link to the registration site.

The Best Part

There were two best parts for me, firstly all of the services apart from the domain name are free.  The vendors sell premium services but the free versions are more than good enough for my SQL Server community event.  Secondly, setting everything up and sending my first email to everyone took about an hour, and I never saw an error message or have to resort to reading the help pages.  It all just worked.

Finally, I should clarify that for everyone who’s been emailed by me I’ve got multiple ways for them to opt out and to never get an email from me again, you should always do that too.  Mail Chimp is almost too helpful in making sure you don’t spam people.

With SQL Server 2012, your licensing requirements can choose to use or be required to use a per-core model depending on the edition you’re deploying. This is a change to previous editions which used a per-CPU socket model that made counting how many per-CPU licences you needed easier – cores and HyperThreading didn’t influence the CPU socket count.  Any complications which people did have typically came from running SQL Server in a virtualised environment, was a vCPU a socket or did licensing related to the host’s CPU configuration etc.

SQL Server now shares with us for the first time in 2012 its understanding of the server’s CPU configuration on start-up.  In the first few entries in the errorlog you’ll see an entry like these:

SQL Server detected 1 sockets with 4 cores per socket and 4 logical processors per socket, 4 total logical processors; using 4 logical processors based on SQL Server licensing. This is an informational message; no user action is required.

This doesn’t replace the need for accurate licensing purchases based on the hardware architecture you’re deploying but it should at least help you understand what SQL Server’s view of the hardware looks like.

Recently, in preparation for my SQLBits NUMA internals session I began looking at some of the SQLOS DMVs and trying to understand how their contents directly related to the physical server architecture that SQL Server was running on.

While their contents used regular terms such as node and affinity mask the results were often in an “internals” format that can be distracting to the human reader.  An example of this is the DMV sys.dm_os_nodes (link to Technet here), or more specifically the column cpu_affinity_mask which returns a bigint value that bears no resemblance to the server’s hardware configuration.  Although, as many of you will probably know, the bigint value is a decimal representation of a bitmask that represents the processor value.  To show what I mean, below is an example of the output from a server we manage:

So how do we turn the –281474976710656 value into something meaningful?  The answer comes from a query I found on the SQLServerCentral forums which I then modified, the original link is here, while my version is shown below:

For various (good and positive) reasons I never seem to get the time to write blog articles these days, however with the authoring of my next SQLBits session now on my todo list I thought I’d take a moment to explain the background behind it and what its likely to contain.

What is NUMA?

NUMA stands for Non-Uniform Memory Architecture and is a server hardware architecture which any modern server with more than 1 CPU will use.  In two sentences, to make servers scale better each CPU in a server is now only directly connected to specific pieces of the server’s physical memory.  If a CPU needs to access memory which it isn’t directly connected to it needs to go via a memory controllers, this adds latency to the memory request so in an ideal world modern software should do its best to avoid ever needing to read “foreign memory”.  There’s also something called Soft-NUMA which can be misleadingly positioned as being similar to Hard-NUMA, I’ll talk about that more in my session.

What’s this got to do with SQL Server?

Today, SQL Server is regularly deployed on servers with several CPUs and large amounts of memory and as a consequence both SQL Server and Windows are NUMA aware.  They each do their part in trying to ensure these expensive “foreign” memory requests are kept to a minimum, Windows at the CPU instruction level, SQL Server at the SQLOS scheduler level.  

Curiousness develops

You can’t influence how SQL Server works in the NUMA world, it all happens deep within the internals, but you can see NUMA “in action”.  A lot of information about how SQL Sever is internally load balancing its workload and caches between the server’s NUMA nodes is exposed in the DMVs.  And, if you’re anything like me then when you start looking into these DMVs you’ll be curious about what’s actually happening internally.  How do parallel queries run across multiple schedulers when there is a “buffer pool” per NUMA node, why does all the memory seem to be assigned to one NUMA etc etc.

Friday session

It was these questions which made me curious enough to research the topic for myself earlier this year and I hope for those who come to my session on the Friday in March you’ll leave equally as impressed as I was about how SQL Server “floats” across your server’s motherboard.

For various (good and positive) reasons I never seem to get the time to write blog articles these days, however with the authoring of my next SQLBits session now on my todo list I thought I’d take a moment to explain the background behind it and what its likely to contain.

What is NUMA?

NUMA stands for Non-Uniform Memory Architecture and is a server hardware architecture which any modern server with more than 1 CPU will use.  In two sentences, to make servers scale better each CPU in a server is now only directly connected to specific pieces of the server’s physical memory.  If a CPU needs to access memory which it isn’t directly connected to it needs to go via a memory controllers, this adds latency to the memory request so in an ideal world modern software should do its best to avoid ever needing to read “foreign memory”.  There’s also something called Soft-NUMA which can be misleadingly positioned as being similar to Hard-NUMA, I’ll talk about that more in my session.

What’s this got to do with SQL Server?

Today, SQL Server is regularly deployed on servers with several CPUs and large amounts of memory and as a consequence both SQL Server and Windows are NUMA aware.  They each do their part in trying to ensure these expensive “foreign” memory requests are kept to a minimum, Windows at the CPU instruction level, SQL Server at the SQLOS scheduler level.  

Curiousness develops

You can’t influence how SQL Server works in the NUMA world, it all happens deep within the internals, but you can see NUMA “in action”.  A lot of information about how SQL Sever is internally load balancing its workload and caches between the server’s NUMA nodes is exposed in the DMVs.  And, if you’re anything like me then when you start looking into these DMVs you’ll be curious about what’s actually happening internally.  How do parallel queries run across multiple schedulers when there is a “buffer pool” per NUMA node, why does all the memory seem to be assigned to one NUMA etc etc.

Friday session

It was these questions which made me curious enough to research the topic for myself earlier this year and I hope for those who come to my session on the Friday in March you’ll leave equally as impressed as I was about how SQL Server “floats” across your server’s motherboard.