SQL Server 2008 Performance

SQL Server 2008 has not yet been released but nevertheless there are already some test results from the CTP versions. Do note that the performance will probably improve even more when the final product arrives but the results today are already quite amazing. They can be found on https://www.microsoft.com/sqlserver/2008/en/us/benchmarks.aspx.

Apart from the performance improvements you get by installing SQL Server 2008 there has also been a close collaboration with the Windows Server 2008 team giving even better performance. Check out this post which discusses the performance improvement in replication because of the SQL Server 2008 / Windows Server 2008 combination.

This confirms that building on the strengths of SQL Server 2005 was certainly the right decision. For those of you who did not have the time to test Windows Server 2008, grab a server and install it! It offers some great enhancements on many different areas like security, high availability and more. As an extra benefit it has amazing performance. The I/O improvements together with more intelligent scheduling of threads and a rewritten TCP/IP stack make it blazing fast and the best choice for your new SQL Server 2005 and 2008 installations.

Windows Server 2008 RTM

This time I am not just trying to draw your attention to this post, it has really RTM'ed!
More information here.

Together with the announcement of Windows Server 2008 they have also announced Windows Vista SP1 here.

They will probably be available somewhere next month so just a little more patience.

Some I/O improvements in Windows Server 2008

Windows Server 2008 offers a lot of new features that are very visible like Internet Information Server 7.0, a whole new virtualization model, Server Manager and many many more. But being a SQL Server person I am interested in other things that have been improved. These not so visible features are mostly I/O related since databases are a synonym for I/O. Also note that many (if not all) of these changes also apply to Windows Vista.

I/O Completion Port improvement

Before Windows Server 2008 a thread that issued an async I/O also executed the I/O completion task causing a context switch which is expensive. The I/O completion is now deferred until the thread pulls the I/O off the completion port preventing this context switch.

I/O prioritization

This is completely new to the OS, not only do your processes have a priority but also the I/O's that are triggered by these processes have their priority. This priority is based on the thread priority but can be set on the I/O itself too.

I/O Performance improvements

Using the GetQueuedCompletionStatusEx API call enables Windows to retrieve multiple completion port entries in one call.

Prior to Windows Server 2008 the Memory Manager and I/O system limited every I/O request internally to 64KB, larger requests were divided into multiple 64KB parts. This limit has been removed so every request can now be issued as a whole, meaning less transitions to kernel-mode to send the I/O to your storage device.

NUMA improvements

When working with SQL Server 2005 and later you will most likely encounter more and more NUMA enabled machines. In Windows Server 2008 more memory allocations procedures have been updated to be NUMA aware and I/O interrupts direct their completion to the node that initiated the I/O. An addition to the NUMA APIs also allows applications to specify the preferred node.

SMB2

According to the people who designed SMB2 this should help performance of large file copies over the network with factor 30 to 40. This will not really help your SQL Server performance but it will certainly get your backups to other servers faster.

For more information about kernel changes in Windows Server 2008 check out this great webcast by Mark Russinovich.

Majority Quorum Model

Windows Clustering Services have been around for some time and most if not all critical servers in large enterprise are probably running on this technology. An important part of a cluster is the quorom disk which holds vital information to keep your cluster up and running. The two options in Windows 2003 for this quorum are shared disk quorum model where all nodes share the same quorum and the majority node set model where each node has a replicated copy of the quorum. The shared disk model is used most often because a lot of the clusters consist of two nodes. Do note that there is an update for Windows Server 2003 that enables a "File Share Witness" to create a majority node set cluster with just 2 nodes (KB921181 - steps required).

In Windows Server 2008 they have merged these two models which is now called Majority Quorum Model. Before Windows Server 2008 the quorum disk was a single point of failure. The risk was obviously low because quorum drives are usually installed on redundant storage devices but even highly redundant storage may fail sometime. In Windows Server 2008 however the quorum disk or as it is now called witness disk is no longer a single point of failure. Clustering now uses a 'vote' system where each node and the quorum device can be assigned a vote. A cluster remains online if just a single vote is lost meaning your cluster will continue to work even when the quorum is lost.

More information about failover clustering in Windows Server 2008 can be found here:
http://www.microsoft.com/windowsserver2008/failover-clusters.mspx

Windows Server 2008 RC0

You probably are all aware of this but since I am on a holiday I have a good excuse to be a bit later.

Microsoft has released Windows Server 2008 Release Candidate 0. This is great news since there are a lot of exciting features in Windows Server 2008 for you to play with. They have also included the long awaited Hypervisor platform for your virtualization needs.

Get this free download here.