One day @ Bob Ward’s SQLOS Workshop

We had the opportunity with David Barbarin, to fly to Wroclaw (Poland) to participate in the SQLDay 2014 (annual conference of the Polish SQL Server User Group) for the first time. We don’t speak Polish but we met Bob Ward who held a very interesting level 500 workshop titled “Inside SQLOS and Memory”. This pre-conference seminar session was for us the opportunity to benefit from Bob Ward’s Expertise.

This event was held at the football stadium in Wroclaw where the European football 2012 cup took place

I will not explain or expose the entire content of the workshop but I will just give you the basics of the workshop program.

Inside SQLOS

After a good review of fundamentals like preemptive and non-preemptive scheduling, we started with an introduction of SQLOS.
As a reminder, SQLOS is not just an executable or a Windows Service, but an abstraction layer of SQL Server and an API with two main libraries:

• SQLDK.dll (SQL Server Development Kit)
• SQLOS.dll (main dll)

Of course, we have other libraries like sqllang.dll that contains the T-SQL language and the Query Processor and sqlmin.dll that contains well-known components of SQL Server such as LazyWriter, the checkpoint process, the lock monitor etc.

After a basic presentation of the SQLOS, Bob Ward showed us how to perform some debug stuff using WindDbg.
His debugger comes from the Debugging Tools for Windows here.

After the installation, you find the executable windbg.exe in C:Program Files (x86)Windows Kits8.1Debuggersx64.
In addition to this debugger, you must use “symbols”. Symbols are used to decode memory addresses to their corresponding function names then we can easily understand the memory. The command line to use is:

windbg -y srv*g:symbols*http://msdl.microsoft.com/download/symbols -pn sqlservr.exe

I will not explore this point further.

Then, after seeing the context switching (SQLOS and Windows) and how SQLOS executes threads, a good point is to know how many worker threads are available for SQL Server processes. Based on the number of logical CPUs and platforms, the instance setting is ‘Max worker threads’.

By default, this value is 0 and let SQL Server to SQL Server to automatically choose the maximum number of worker threads.

• For 64 bits and less than 64 CPUs, SQL Server calculates 512 + (16* (number of CPU-4))
• For 64 bits and more than 64 CPUs, SQL Server calculates 512 + (32* (number of CPU-4))
• To optimize max worker threads, you may want to read this msdn article.

During the break, there was a quick exchange between David and Bob, with the computer sticking out his tongue … it is of course a pure coincidence 😛

Inside Memory

The next part of the workshop concerned the memory management of SQLOS. As you maybe know, there are some fundamental changes with SQL Server 2012. First, Bob Ward explained that the single and multi-pages allocators concepts do not exist anymore.

Now, we have virtual allocators, workspaces, fragment manager, block allocators and buffer pool (this last on does not act as an allocator by itself). These fundamental changes provide a more consistent model for all memory management. Max server memory now really means max server memory and does not concern only the buffer pool!

Then, Bob Ward gave us a good overview of the memory nodes with NUMA architecture. There are some improvements with SQL Server 2012 (affinity, mapping of SQL Server memory numa nodes directly to the hardware NUMA nodes, etc.)

Unfortunately, after this chapter, it was the time to go back home. We did not have the opportunity to see the last part of the workshop but we left with a lot a good things in mind!

Finally, I would like to thank our Polish colleagues for this event and Bob for this wonderful workshop day.
I hope that in the future, we can organize the same event in Switzerland or in France with the participation of Bob Ward.:-D