I recently did some drive upgrades in my personal laptops and as a result I had a couple of SSD drives sitting around that I wanted to make use of.  A few weeks ago I purchased a new OCZ Agility 3 240GB SATA III SSD from Newegg when it was on sale and at the same time I also purchased two MassCool USB 3 external enclosures to make use of the two 120GB OCZ Vertex 2 SSD’s that I would have sitting around.  The cost for the MassCool enclosures was only $14.99 so I figured it would be a good way to reuse the SSD’s at the time.  When the package from Newegg arrived, I immediately installed the SSD into one of my personal machines and then later I went about testing the performance of the USB 3 external enclosures with the older SSD’s.  Initially the performance was impressive, and I posted a few tweets that attracted some attention and a few people requested that I blog my results.

To make this blog post something worth looking at, I ran a number of additional tests over the last few days using SQLIO and a varying configuration of drives with external enclosures.  Specifically I tested two different USB 3 2.5” SATA enclosures, a separate eSATA 2.5” SATA external enclosure, as well as two USB 3 HDD’s that I already owned to get some performance information from each of the setups.

The external USB 3.0 HDD’s being tested are as follows:

• Toshiba CANVIO 3.0 500 GB,External,5400 RPM (E05A050CAU3XW)
• Western Digital My Passport Essential 500GB USB 3

The external enclosures being tested in these tests are as follows:

• MassCool (UHB-2233) USB 3
• Hornettek (Panther) USB 3
• Vantec NexStar CX (NST-200SU-BK) eSATA

The SSD’s used with the external enclosures listed above are:

• OCZ Vertex 2 120GB SATA II
• OCZ Agility 3 240GB SATA III

At the time that I bought the 120GB drive I paid close to $2.42 per megabyte for it, and at the time this was a good deal.  When I purchased the 240GB SSD I paid right around $1.08 per megabyte, showing how much the prices have decreased nearly 3 years later.  I happen to own a number of OCZ drives at home and they are one of my favorite brands personally because of the overall reliability I’ve had with them the last three years as well as the performance to cost ratio I’ve experienced. Initially I was just testing the MassCool USB 3 enclosures that I purchased, and I was incredibly happy with the performance that I had from them.  However, after tweeting about the results, my friend Jose Chinchilla (Blog | Twitter) mentioned that I should also try out eSATA because it performed significantly better in his own tests.  Based on this tweet, I set out to my local MicroCenter computer store to purchase a eSATA enclosure and while I was buying it, one of the sales representatives, a guy named Chris, approached me and asked what I was planning to do with the enclosure because USB 3.0 should be faster for SSD’s. The numbers he quoted didn’t match my previous testing, so he made a recommendation for a specific USB 3.0 enclosure, and I figured, for $20 it was worth testing, so I bought an extra Hornettek Panther USB 3.0 device for comparison testing along with the Vantec NexStar CX eSATA enclosure I had already selected.

For the tests, I ran a short set of tests using SQLIO that I previously blogged about on my blog post about the Powershell parser for SQLIO output.  The results from the tests are below:

The two external USB 3.0 HDD drives had very similar performance characteristics, and they beat their USB 2.0 counterparts performance wise significantly.  I’ve been incredibly happy with my USB 3.0 HDDs overall for the last few years and I only purchase USB 3.0 HDD drives based on my performance tests a few years ago.  However, when compared with the USB 3.0 external enclosures with the SSD’s, the performance difference is quite significant.  One of the problems I’ve had lately is being able to fit all of the virtual machine hard drives, VHD’s for Hyper-V when using my dual boot Hyper-V host VHD, as well as my original VMware Workstation VMDK’s for classes, and even the VirtualBox virtual disk images (VDI) from my blog series on building a completely free playground.  Using the SSD’s with USB 3.0 definitely makes storing my virtual hard disk files on an external array much more feasible performance wise, and my only real limitation from testing is the size of the external device. 

However, the performance of the eSATA external enclosure is incredibly better, with the side trade off that I can only have 1 of them attached to my laptops at a time, and for my personal laptop, the fact that the eSATA enclosure requires not only the eSATA port for throughput, but also the additional USB 2.0 port for power really limits what I can do as far as having multiple disks connected to the laptop.  To be honest, this is something that I can live with given that I also have swapped out my CD/DVD ROM bay for a replacement New Mode US second HDD conversion. This means that I can have two 240GB OCZ SSD’s in my E5420, which is a similar configuration to the dual 256GB Dell SSD configuration in my Precision M6500 for work, while being able to move VM images between the two machines using my older 120GB SSD’s with fantastic performance.

If you are looking at really high performance external hard disk configurations for scalability, I would highly recommend looking at either eSATA with an extra SSD drive or if you need multiple devices and you have USB 3.0, consider going that route.  Either will outperform your existing options significantly.  For the time being, I am going to stick with one eSATA device, which is compatible with both of my laptops, and one USB 3.0 device, which only works with my M6500 at USB 3.0 speeds.  My E5420 only has USB 2.0 onboard, so it makes more sense for me to stick with eSATA for the main shared drive.

Continuing our weekly series on building a free playground for SQL Server, this week we’ll take a look at configuring our iSCSI Target to create the LUNs necessary to build a SQL Server Failover Cluster, and then build out a two node SQL Server Failover Cluster in the environment.  A number of really good comments have been made by readers on the last two blog posts in this series and I want to call out of of them at the beginning of this blog post because it is fairly important to the environment and a step I skipped covering as a part of the blog posts. 

The VM Tools or Guest Additions should be added to each of the VMs that are configured in the playground environment.  This can be accomplished in VirtualBox from the Devices menu with the Install Guess Additions… menu item.

The Guest Additions provide seamless integration between the host environment and the VM guest for things like the clipboard, USB device support in the VM, and mouse integration.

The second item that I wanted to cover is that the iSCSI Target being used in these blog posts requires hardware virtualization to be able to create the 64-bit VM for Windows Server 2008 R2 that is required to use the Microsoft iSCSI Target.  Most modern laptops support hardware virtualization in the CPU, but if you are using an older laptop or desktop that doesn’t have hardware virtualization extensions in the CPU you won’t be able to use the same iSCSI Target that I am using in this blog series.  There is another free alternative available from Starwind Software, a third party software vendor that offers the Starwind Free Edition - iSCSI SAN with Deduplication, for download.  You can use this iSCSI target in lieu of the Microsoft iSCSI Target if you don’t have hardware virtualization support on the computer that you are using to build your playground environment. 

A few people have asked me why I chose to use the Microsoft iSCSI Target over Openfiler, Starwind’s free iSCSI SAN, or other third party products that can be used to build an iSCSI environment, and the reason for the decision to go with the Microsoft iSCSI Target is that it supports all of the features required for building a Windows Server 2008/2008R2 failover cluster for free, and it doesn’t require any kind of registration to download.  I’ve used the Starwind iSCSI SAN software in the past when I was first learning how to use failover clustering in Windows Server 2008 and SQL Server 2008, and it provides all of the functionality required, but you have to register on their site to download it.  I have also used Openfiler in the past to build a Windows Server 2003 failover cluster for SQL Server 2005, but OpenFiler does not support SCSI-3 Persistent Reservations, at least as recently as the last time that I looked at it, which are required for building a Windows Server 2008/2008R2 failover cluster, so it wasn’t a viable option for building out a playground environment for SQL Server clustering.

Creating the cloned VMs for the Failover Cluster

The first step in the process of creating a SQL Server Failover Cluster in our playground is to create two new VMs by cloning our template VM.  To do this, use the same steps described in part 2 of this series, making sure that the you check the box to Reinitialize the MAC address of all network cards as a part of the cloning process.  I am going to create two linked clones named SQL2K8R2CLSTR1 and SQL2K8R2CLSTR2 in my environment.  Once the clones have been created, power them on, and follow the same steps used to configure the Domain network of the iSCSI Virtual SAN and join the VMs into the AD Domain.  For this configuration, SQL2K8R2CLSTR1 will be assigned IP address 192.168.81.3 and SQL2K8R2CLSTR2 will be assigned 192.168.81.4 on the Domain network.

Once the VM clones are created, renamed, and joined into the domain using the same steps used to join the iSCSI Virtual SAN to the domain in part 2 of the series the iSCSI network adapters can be setup to begin the process of provisioning shared storage for building out the SQL Server failover cluster.

Configuring the iSCSI Network Adapters

To simulate an environment with segregated traffic and multiple paths to the iSCSI SAN, we can change the configuration of the vNICs on the iSCSI Virtual SAN, and both cluster nodes to mimic separate hardware networks using the VLANs.  In VirtualBox, this is really easy to do, but simply changing the name of the internal network that Adapter 3 and 4 are connected to.  In this case I am going to change the internal network name from iSCSI Network to iSCSI Network 1 for Adapter 3 and iSCSI Network 2 for Adapter 4 as shown in the below screenshot. 

This change will be made to all three servers, and then we can configure the network settings for each so that iSCSI Network 1 uses IP subnet 192.168.31.x and iSCSI Network 2 uses IP subnet 192.168.32.x.

iSCSI Virtual SAN

SQL2K8R2CLSTR1

SQL2K8R2CLSTR2

Even though we have laid the foundation for having multiple paths to the iSCSI Target from both of our cluster nodes, for the purposes of the this weeks blog post, we are only going to make use of a single path to the iSCSI SAN, reserving the concept and discussion of multiple paths for next weeks blog post.

Configuring the iSCSI Target

Configuring the iSCSI Target in Windows Server 2008 R2 is incredibly simple and can be accomplished in a few minutes.  In fact, you will probably spend more time reading the steps to configure the Target and create the necessary Virtual Disks to support a two node failover cluster, than you will spend actually performing the steps.  To begin configuring the iSCSI Target open the Microsoft iSCSI Software Target management console from Administrative Tools on the iSCSI Virtual SAN VM.  Then create a new iSCSI Target by right clicking on the iSCSI Targets node and clicking on the Create iSCSI Target menu item.

When the Create iSCSI Target Wizard opens click next and then provide a iSCSI target name and Description for the new target.  In this example, I am naming the iSCSI Target SQL2K8R2CLSTR and using a description of SQL Server 2008 R2 Failover Cluster Shared Disks

On the iSCSI Initiators Identifiers screen, we are going to click the Advanced button and specify the IP addresses of the iSCSI network adapters we previously configured for the iSCSI Network 1 adapters.

After clicking the Add button on the Advanced Identifiers screen, change the Identifier Type dropdown to IP Address and then type the IP address for the SQL2K8R2CLSTR1 iSCSI Network 1 connection, 192.168.31.11 in the Value box.

Repeat these steps to configure the IP Address of the SQL2K8R2CLSTR2 iSCSI Network 1 connection, by typing 192.168.31.21 in the Value box.  When the Microsoft iSCSI Software Target warning dialog pops up, click Yes to accept the configuration of multiple initiators for the target.

Then click OK to close the Advanced Initiators dialog, then click Next on the iSCSI Initiator Identifiers window, and then click Finish to configure the target.

Next, create a new virtual disk by right clicking on the Devices node and clicking on the Create Virtual Disk menu item. 

When the Create Virtual Disk Wizard pops up click Next and then specify a file location and name in the File box to store the VHD file for the new virtual disk that will be used as the iSCSI LUN for the failover cluster.

On the Size screen, specify a size for the new virtual hard disk, in megabytes, in this example I am creating a 5GB virtual hard disk which is sufficient enough for building a failover cluster for learning.  Click Next to move to the Description screen where you can provide a detailed description for the Virtual Hard Disk, and then click Next.

On the Access screen click the Add button to open the Add Target dialog, and assign the Virtual Hard Disk to the SQL2K8R2CLSTR iSCSI Target that we previously created, and then click OK.

Click Next on the Add Target dialog and then click Finish to complete adding the Virtual Hard Disk to the iSCSI Target.  Now repeat the steps to create another Virtual Hard Disk that is 1GB in size using the same iSCSI Target for the Failover Cluster Quorum Disk.

Configuring the iSCSI Initiators

Now that we have out iSCSI Virtual SAN configured with a clustered shared disk and quorum disk, we need to setup the iSCSI Initiator on both of the failover cluster node VMs so that they can access the disks being presented by the iSCSI Target.  The first step in configuring the iSCSI Initiator was to login to the SQL2K8R2CLSTR1 VM and then open the iSCSI Initiator from Start | Administrative Tools | iSCSI Initiator.  The first time that you open the iSCSI Initiator a popup dialog will open stating that the iSCSI Initiator Service is not running on the server.  Click Yes on the dialog to start the service and configure it to start automatically each time the server starts.

Once the iSCSI Initiator Properties dialog opens, type the network address to the iSCSI Network 1 connection for the iSCSI Virtual SAN, 192.168.31.1,  in the Target box, and then click Quick Connect.  The Quick Connect dialog will open up and you can click Done to connect to the iSCSI Target. 

Repeat these same steps on the SQL2K8R2CLSTR1 VM to connect the iSCSI Initiator to the iSCSI Target on the second cluster node.  Then open the Computer Management MMC from Start | Administrative Tools | Computer Management, and then expand the Storage node and click on Disk Management.  Right click on each of the iSCSI disks presented to the server and click on Online Disk to bring the disks online on the server.

Then right-click on one of the disks and click on the Initialize Disk menu option to initialize the disks.

Then right-click each of the disks and select the option to create a New Simple Volume, and format the disks.  Assign drive letter S to the Shared Disk that is 5GB in size and format the disk using a 64K allocation unit size, naming the disk SQLData.  Assign drive letter Q to the Quorum Disk that is 1GB in size, and format the disk using the default allocation unit size and name the disk Quorum Disk.

At this point the VMs are ready to begin the failover clustering build out in Windows which will be covered in the next post in this series.

To continue the build out of our Completely FREE Playground for SQL Server, we will begin by setting up an Active Directory Domain Controller and a separate iSCSI Virtual SAN, which are both requirements for setting up a SQL Server Failover Cluster.  To make things easier to follow, this post will break these two tasks up into separate sections, with each section focused on the specifics of the setup involved with each of the servers.

Active Directory Domain Controller

To begin the setup of the Active Directory Domain Controller, the first step is to create a Clone of the “base” VM that was created in the previous blog post.  To do this, right click on the VM and select the Clone option from the context menu.

To create the Clone, provide a common name to the VM and specify the option to “Reinitialize the MAC address of all network cards” on the first screen of the “Clone a virtual machine” wizard in VirtualBox.

When creating a clone of an existing VM, there are two options available; “Full Clone” which copies the existing virtual hard disk file entirely, and “Linked Clone” which creates a snapshot of the existing virtual machine and creates a differential virtual hard disk for the clone.  If you are cloning a VM on a laptop, or on a system with limited space, a “Linked Clone” can save space by reusing the base VMs virtual hard disk for the Windows OS, rather than having duplicated copies of the base OS installation.  However, when a “Linked Clone” is created, the base VM is required for the clone to be able to start up.  If the base VM is moved or lost, all of the “Linked Clones” will become unusable instantaneously.  This is the trade off between the two options; reduced storage vs. viability.

Once I create a “Linked Clone” for the Domain Controller, I open the Settings for the VM and remove Adapter 3 and 4 from the Network settings of the VM.  The Active Directory Domain Controller will not have iSCSI targets configured on it, and does not need the iSCSI network adapters configured for it, so it is best to remove those adapters from the VM.  It would also be possible to remove the previously configured Features from Windows Server 2008 R2 from the Active Directory VM if you chose to do so, those features being .NET Framework 3.5.1, Failover Clustering, and Multipath I/O, but it does not matter for this particular setup.  The next step in setting up the Active Directory Domain Controller is to accept the License Terms and login/set a password for the local Administrator account.  Since this is going to be a Domain Controller, we will need to setup a static IP Address for the Domain network adapter.  To do this, click on the Configure networking link on the Initial Configuration Tasks window that pops up immediately after you login to Windows the first time.  Then open the properties dialog for the connection that is not using NAT and setup on the Domain Internal Network.  To determine which network connection is the Domain network in the VM, you can open the VM settings (Machine>Settings) and simulate unplugging the network cable from the vNIC by toggling the Cable connected checkbox.

For this playground setup, I am going to use 192.168.81.x for the Domain network, and 192.168.31.x for the iSCSI network.  In IPv4 TCP/IP Properties, set the IP Address to 192.168.81.1, the Subnet mask to 255.255.255.0, and the Preferred DNS server to 127.0.0.1.

Now the VM needs to be renamed so that it has the appropriate naming convention to match your locally setup environment.  For the purposes of this blog series, the AD DC will be named SQLskills-DC in the environment.  Once the servers name is changed the VM will require a reboot.

Once the reboot for the rename operation completes, the VM is ready to be configured as an Active Directory Domain Controller.  For the purposes of having a playground environment, a very basic configuration of Active Directory can be done, to minimize the steps required and keep things simple. The following steps will provide you with a fully function Active Directory Domain Controller in your playground, but there is no consideration for high availability of the Domain Controller, backups, best practices for configuration, or anything else that would qualify you to be an Active Directory Domain Administrator beyond the confines of the playground environment that is being built.  To begin the configuration, the Active Directory Domain Services role will need to be added to the server.

After the ADDS role has been installed, run dcpromo to begin the configuration of the AD Domain on this server.

Click Next and then Next again to get to the Deployment Configuration screen.  Choose the option to create a new domain in a new forest and click Next.

Provide a Fully Qualified Domain Name (FQDN) for the new domain and then click Next.  You don’t have to provide an actual domain for the FQDN.  In this series I am using SQLskills.Demos as the FQDN for the domain.

The Domain Functional Level and Forest Functional Level defaults of Windows Server 2003 can be used within the sandbox environment.  Click Next on both of these screens.  After the DNS Configuration check completes, the wizard will recommend additional options for the Domain Controller, including the option to configure it as a DNS server. 

Leave the boxes checked on the screen and click Next.  If you’ve followed this guide up to this point, there should be a Static IP Assignment warning that pops up due to the dynamically assigned IP address for the NAT connection.  Click Yes on this box to continue, and then click Yes on the pop up that tells you the delegation could not be created for the parent zone in DNS.

For the playground environment, there is no need to change the database and log file locations for AD.  Click Next on this Wizard window, provide a password for the Directory Services Restore Mode Administrator account and click Next again.

At this point everything is set to complete the configuration of Active Directory and the information is displayed in a final summary screen.  Clicking Next will begin the configuration of the Active Directory Domain on the server.

Once the server reboots, it will be a fully functional Domain Controller for your playground environment.

iSCSI Virtual SAN

To facilitate building failover cluster configurations in our playground we are going to need a virtual SAN to provide the shared storage between the failover cluster nodes.  One of the requirements for the virtual SAN is that it must support SCSI-3 Persistent Reservations, which are required for failover clustering using Windows Server 2008/2008R2.  There are a number of free virtual SAN products available online, but for the purposes of this setup I chose to use the Microsoft Windows Server 2008 R2 iSCSI Target 3.3 which is available as a free download.  This iSCSI target meets all of the requirements for failover clustering support, and can be configured in a few minutes.

The steps to clone the template VM for the iSCSI Virtual SAN are exactly the same as they were for creating the Domain Controller, with the exception that all of the vNICs will be left configured for the VM.  Once Windows starts up, the first step is to configure the networking for the Domain network so that the VM can be joined into the playground domain.  To do this, first identify the Domain network connection in Windows by disconnecting the cable in the vNIC settings as previously shown.  Then change the IPv4 TCP/IP properties for the connection and set the IP address to 192.168.81.2, the subnet mask to 255.255.255.0 and then the Preferred DNS server to 192.168.81.1 (the IP address of the Domain Controller).

Once this has been done, the server name can be changed and the server can be joined to the domain at the same time.  Once the domain information has been set, the server will attempt to connect to the domain and will request credentials for an account with permissions to join the server to the domain.

After the server joins the domain, it will require a reboot to complete.  When the server restarts, you have to use the Switch User option to change the user that you login with from being the local Administrator account to login with a domain based account.

Note: At this point I would recommend creating a separate user account in the Domain that you use to work on the servers in the playground.  It is never a good practice to use the domain administrator account to work with SQL Server.

Once you’ve logged into the server using a domain account, download the Microsoft iSCSI Software Target 3.3 for Windows Server 2008 R2 and extract the contents to a location on the server.

Installing the iSCSI Target onto the server is a very straightforward process that is basically a series of check boxes for options like accepting the licensing agreement, whether to join the Customer Experience Improvement Program, and whether to use Microsoft Update or not.

Once the installation completes, an entry for the iSCSI Target will be added to the Start Menu and the target MMC can be opened up to begin configuring the iSCSI Targets for the environment.

The next post in this series will go through the configuration options for the iSCSI Target and how to create a new LUN to support configuration of a SQL Server Failover Cluster inside of the playground environment.

Lately I have been doing a lot of work on SQL Server’s that have had 24 or more processor cores installed in them. Many of these systems utilize hardware non-uniform memory architectures, or NUMA, while a few of them were not. This led me into a good bit of additional research into the differences between the two configurations. In this blog post, we’ll look at what NUMA is, and why it matters, and then in a separate blog post we’ll look at what this means to SQL Server and the differences between hard and soft NUMA configurations specifically for SQL.

Only a few years ago NUMA was synonymous with specialized hardware configurations that were designed to scale beyond the processor and RAM limitations of traditional Symmetric Multi-Processing (SMP) systems. The first NUMA system I learned anything about was the IBM x3950 series of servers that were used in a PASS Keynote Demo in 2009. The server being used in the demo was actually four x3950 servers that were interconnected as hardware NUMA nodes using the ScaleExpander Option Kit sold by IBM. Each of the hardware NUMA nodes was a very basic 4-way SMP system with its own processors, memory and I/O devices that could have also operated as a stand alone server. In a SMP configuration, all of the processors have equal access to the memory and I/O in the system as shown in the following diagram.

While SMP systems made it possible to scale up the number of processors available inside a single server, as more processors were added to the system the processor bus became a limitation to the overall system performance. In the past systems like the IBM x3950 series of servers were able to scale up the number of processors and amount of RAM available in a single solution by interconnecting multiple SMP systems together with a high speed interconnect that allowed two or more systems to function as if they were a single system. However, one of the problems associated with connecting multiple nodes with an interconnect was the memory access between the processors in one node to the memory in another node was not uniform; hence the name Non-Uniform Memory Architecture. The interconnect between the two systems introduced latency for the memory access across nodes. The traditional concept of NUMA using multiple interconnected SMP systems as NUMA nodes would conceptually resemble the following:

Technically this is more complicated, but the general concept is that each of the nodes has access to the memory available in the total system through the interconnect, but at a slight penalty performance wise. For this reason special considerations had to be taken when writing software to run on NUMA configured systems and the Windows Server OS and SQL Server both have NUMA optimizations built into their code base to optimize memory locality. While in the past most people that were using hardware NUMA configurations would have known about it, recent changes in processor architectures have made hardware NUMA configurations much more common place and most people I’ve talked to don’t even realize that their new server utilizes hardware NUMA. 

AMD Hyper-Transport (HT)

Where the older SMP architecture had a separate memory controller in the Northbridge of the motherboard, newer systems have an integrated memory controller built into the processor itself, and each processor has its own memory bank. The first processors to introduce an integrated memory controller were the AMD Opteron series of processors in early 2003. Since the introduction of the Opteron series, other AMD processor lines have included an integrated memory controller as well. Just as with traditional NUMA configurations, the hardware NUMA created by each processor having an integrated memory controller requires an interconnect to exist between the processors to allow for memory access by the other processors in the system. AMD processors share memory access through Hyper-Transport (HT) links between the processors.

The HT interconnects between processors allows for the remote memory access in one CPU’s memory bank by the processor cores in a different CPU, but there is a cost for accessing this remote data. To determine the most recent copy of the data probes must be sent out to all of the CPU’s to determine if the current CPU has the most recent version of the data, and this increases latency. To combat this, AMD introduced HT Assist in the Istanbul line of processors which reduces the traffic by tracking data in the processor caches to help direct the processors to the correct location of the most recent copy of the data. This reduces the bus traffic and increases the efficiency of the CPU. 

Intel Quick-Path Interconnect (QPI)

As the number of cores on a single processor increased, Intel followed AMD and introduced an integrated memory controller on its Nehalem-EX series of processors that share memory access through Intel’s Quick-Path Interconnect (QPI). The Intel QPI interconnects the processors with each other in a similar manner to the AMD HT interconnect. However, the QPI snoop based cache forwarding implementation can return remote data in as little as 2 hops. (It should be noted that this in no way is an endorsement of Intel over AMD as there may be similar optimizations in AMD processors that I have not yet read about.) 

Intel offers a really detailed demo of their QPI architecture on their site that really helps understanding the differences between SMP and NUMA configurations (http://www.intel.com/technology/quickpath/demo/demo.htm).

Node Interleaving

On newer server systems based on processors that have integrated memory controllers, the option still exists to run the server in an SMP like configuration known as Node Interleaving. This option is generally configurable in the system Bios and causes the system to be treated as if it were a traditional SMP system by the Operating System and any applications that are NUMA aware and optimized. When node interleaving is enabled, the system becomes a Sufficiently Uniform Memory Architecture (SUMA) configuration where the memory is broken into 4KB addressable regions and mapped from each of the nodes in a round robin fashion so that the memory address space is distributed across the nodes. When the system is configured for NUMA, the memory from each node is mapped into a single sequential block of memory address space for all of the memory in each node. For certain workloads, node interleaving can be beneficial to performance since the latency between the hardware nodes of a system using processors with integrated memory controllers is small. However, for applications that are NUMA aware and that have NUMA optimizations, node interleaving should remain disabled to allow the application to make optimized use of the hardware resources.

Why does this matter?

I’ve been working with hardware NUMA servers, using the newer processors with integrated memory controllers, for the last two years, and not once did I really have to worry about whether the server was using NUMA or not. Microsoft has done a lot of work in how they built Windows Server and SQL Server to make working with NUMA configurations relatively pain free. However, recently I ran into some issues while working with client systems that made me have to look deeper into NUMA to gain an understanding of how it affected SQL Server, and what options were available for configuring SQL Server for NUMA. I’ll discuss the specifics of these in my next blog post in a few days.

Today Joe Webb (Blog|Twitter) blogged about The Power of Regex in Powershell, and in his post he shows how to parse the SQL Server Error Log for events of interest.  At the end of his blog post Joe asked about other places where Regular Expressions have been useful in PowerShell so I thought I’d blog my script for parsing SQLIO output using Regex in PowerShell, to populate an Excel worksheet and build charts based on the results automatically.

If you’ve never used SQLIO, Brent Ozar (Blog|Twitter) has a article on SQLServerPedia titled SAN Performance Tuning with SQLIO that includes a lot of information as well as a video tutorial on its use.  I have been using SQLIO quite a bit this year, mainly to gather performance information for systems before and after reconfiguring their storage to show percent impact information to the business for making changes in storage like migrating from RAID 5 to 10, and aligning disk partitions used for database and log file storage.  I have also used it in benchmarking new SAN configurations and the SAN configuration of our Virtual Machine environment to show team members why I have recommended that a number of our physical servers be migrated onto Virtual Machines.

I generally use a param.txt file similar to:

G:\MSSQL\testfile1.dat 4 0x0 32768

which creates a 32GB test file and then have a DOS batch file that runs the actual SQLIO tests using a script like:

sqlio -kW -t16 -s10 -o8 -fsequential -b8 -BH -LS -Fparam.txt

sqlio -kW -t8 -s360 -o8 -frandom -b8 -BH -LS G:\MSSQL\testfile1.dat > post.txt
sqlio -kW -t8 -s360 -o8 -frandom -b32 -BH -LS G:\MSSQL\testfile1.dat >> post.txt
sqlio -kW -t8 -s360 -o8 -frandom -b64 -BH -LS G:\MSSQL\testfile1.dat >> post.txt
sqlio -kW -t8 -s360 -o8 -frandom -b128 -BH -LS G:\MSSQL\testfile1.dat >> post.txt
sqlio -kW -t8 -s360 -o8 -frandom -b256 -BH -LS G:\MSSQL\testfile1.dat >> post.txt

sqlio -kR -t8 -s360 -o8 -frandom -b8 -BH -LS G:\MSSQL\testfile1.dat >> post.txt
sqlio -kR -t8 -s360 -o8 -frandom -b32 -BH -LS G:\MSSQL\testfile1.dat >> post.txt
sqlio -kR -t8 -s360 -o8 -frandom -b64 -BH -LS G:\MSSQL\testfile1.dat >> post.txt
sqlio -kR -t8 -s360 -o8 -frandom -b128 -BH -LS G:\MSSQL\testfile1.dat >> post.txt
sqlio -kR -t8 -s360 -o8 -frandom -b256 -BH -LS G:\MSSQL\testfile1.dat >> post.txt

sqlio -kW -t8 -s360 -o8 -fsequential -b8 -BH -LS G:\MSSQL\testfile1.dat >> post.txt
sqlio -kW -t8 -s360 -o8 -fsequential -b32 -BH -LS G:\MSSQL\testfile1.dat >> post.txt
sqlio -kW -t8 -s360 -o8 -fsequential -b64 -BH -LS G:\MSSQL\testfile1.dat >> post.txt
sqlio -kW -t8 -s360 -o8 -fsequential -b128 -BH -LS G:\MSSQL\testfile1.dat >> post.txt
sqlio -kW -t8 -s360 -o8 -fsequential -b256 -BH -LS G:\MSSQL\testfile1.dat >> post.txt

sqlio -kR -t8 -s360 -o8 -fsequential -b8 -BH -LS G:\MSSQL\testfile1.dat >> post.txt
sqlio -kR -t8 -s360 -o8 -fsequential -b32 -BH -LS G:\MSSQL\testfile1.dat >> post.txt
sqlio -kR -t8 -s360 -o8 -fsequential -b64 -BH -LS G:\MSSQL\testfile1.dat >> post.txt
sqlio -kR -t8 -s360 -o8 -fsequential -b128 -BH -LS G:\MSSQL\testfile1.dat >> post.txt
sqlio -kR -t8 -s360 -o8 -fsequential -b256 -BH -LS G:\MSSQL\testfile1.dat >> post.txt

Which runs the gamit of tests using 8, 32, 64, 128, and 256K block sizes, with 16 threads, and 8 pending IO.  You can certainly expand the test set much further than this and do a very exhaustive testing regimen, I am by no means saying that my tests above are the best fit, but they have sufficiently met my needs for the tuning I have been doing.  Once the tests have been run the post.txt file will contain an output entry similar the following for every test that was run.

sqlio v1.5.SG
using system counter for latency timings, 14318180 counts per second
16 threads writing for 60 secs to file d:\MSSQL\testfile1.dat
    using 8KB random IOs
    enabling multiple I/Os per thread with 8 outstanding
    buffering set to use hardware disk cache (but not file cache)
using current size: 2048 MB for file: d:\MSSQL\testfile1.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 18527.91
MBs/sec:   144.74
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 6
Max_Latency(ms): 4900
histogram:
ms: 0  1  2  3  4  5  6  7  8  9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 78  6  8  1  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  2

Linchi Shea (Blog) wrote a blog post three years ago that showed how to Parse the sqlio.exe Output using Perl.  I found this in my search for a smarter way of parsing these long output files I was generating other than manually, but I don’t have Perl installed anywhere and I really felt that I should be able to do this natively using the tools readily available to me on my Windows 7 laptop, so I looked to PowerShell for a solution. 

Reading the file data in is accomplished using Get-Content which returns a collection of strings.  At first I tried casting this to a [string] object directly, but soon found out that when I did this the resulting string was missing the carriage returns and line feeds that existed in the original file.  This posed a couple of problems for me in parsing the outputs initially, but doing a [string]::Join operation using [Environment]::NewLine as the concatenater allowed an appropriately CRLF delimited string to be returned. 

$filedata = [string]::Join([Environment]::NewLine,(Get-Content $FileName))

Now that I had my file data loaded into a the $filedate object, the next step was to split this string based on the “sqlio v1.5.SG” header that is output at the beginning of each test run.  Since PowerShell is .NET based, this is easily accomplished by using the Split() method of the System.String object which is System.Type for the $filedata object.

$Results = $filedata.Split( [String[]]"sqlio v1.5.SG", [StringSplitOptions]::RemoveEmptyEntries )

At first I started to write a foreach loop with this object, lets face it old habits die hard, but instead I made use of piping which allows a collection like the one returned by the Split() method to be worked on in a single operation.  Piping in PowerShell is similar to a manufacturing process where an object gets transformed and then passed on until the ultimate result is achieved.  Once an object is piped, it becomes accessible through the use of the $_ variable name, allowing the object to be transformed along the pipeline.  By piping the result of the Split() I was able to write a SELECT statement that utilized Regex to get the information of interest out of each test run.

     select @{Name="Threads"; Expression={[int]([regex]::Match($_, "(\d+)?\sthreads\s(reading|writing)").Groups[1].Value)}},`
                @{Name="Operation"; Expression={switch ([regex]::Match($_, "(\d+)?\sthreads\s(reading|writing)").Groups[2].Value)
                                                {
                                                    "reading" {"Read"}
                                                    "writing" {"Write"}
                                                }    }},`
                @{Name="Duration"; Expression={[int]([regex]::Match($_, "for\s(\d+)?\ssecs").Groups[1].Value)}},`
                @{Name="IOSize"; Expression={[int]([regex]::Match($_, "\tusing\s(\d+)?KB\s(sequential|random)").Groups[1].Value)}},`
                @{Name="IOType"; Expression={switch ([regex]::Match($_, "\tusing\s(\d+)?KB\s(sequential|random)").Groups[2].Value)
                                                {
                                                    "random" {"Random"}
                                                    "sequential" {"Sequential"}
                                                }  }},`
                @{Name="PendingIO"; Expression={[int]([regex]::Match($_, "with\s(\d+)?\soutstanding").Groups[1].Value)}},`
                @{Name="FileSize"; Expression={[int]([regex]::Match($_, "\s(\d+)?\sMB\sfor\sfile").Groups[1].Value)}},`
                @{Name="IOPS"; Expression={[decimal]([regex]::Match($_, "IOs\/sec\:\s+(\d+\.\d+)?").Groups[1].Value)}},`
                @{Name="MBs_Sec"; Expression={[decimal]([regex]::Match($_, "MBs\/sec\:\s+(\d+\.\d+)?").Groups[1].Value)}},`
                @{Name="MinLat_ms"; Expression={[int]([regex]::Match($_, "Min.{0,}?\:\s(\d+)?").Groups[1].Value)}},`
                @{Name="AvgLat_ms"; Expression={[int]([regex]::Match($_, "Avg.{0,}?\:\s(\d+)?").Groups[1].Value)}},`
                @{Name="MaxLat_ms"; Expression={[int]([regex]::Match($_, "Max.{0,}?\:\s(\d+)?").Groups[1].Value)}}`

The select returns a new type of object that has Properties named Threads, Operation, Duration, IOSize, IOType, PendingIO, FileSize, IOPS, MBs_sec, MinLat_ms, AvgLat_ms, and MaxLat_ms.  This alone is sufficient to proceed to creating an Excel file for the information, but I wanted the information sorted in the same manner every time I ran this, primarily because I change how I run my tests sometimes, and I like consistency in the output.  Since I am so anal retentive thorough, I fixed this problem by piping the output from the select to the Sort-Object commandlet and sorted by the IOSize, IOType, Operation, and Threads.

Getting the data into Excel was actually fairly simple to do, thanks to Buck Woody(Blog|Twitter) and SQL Rockstar Tom LaRock(Blog|Twitter).  Buck wrote a blog post titled Create an Excel Graph of your Big Tables – With Powershell, and Tom wrote a Simple Talk Article, Monitoring SQL Server Virtual Log File Fragmentation, that use the Interop Excel objects to create an Excel workbook from PowerShell, populate the workbook with data, and build charts using the data.  My code for the Excel integration is based entirely on their examples.

$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $true
$WorkBook = $Excel.WorkBooks.Add()
$WorkBook.WorkSheets.Item(1).Name = "RawData"
$WorkBook.WorkSheets.Item(3).Delete()
$WorkBook.WorkSheets.Item(2).Delete()
$WorkSheet = $WorkBook.WorkSheets.Item("RawData")
$x = 2
$WorkSheet.Cells.Item(1,1) = "Threads"
$WorkSheet.Cells.Item(1,2) = "Operation"
$WorkSheet.Cells.Item(1,3) = "Duration"
$WorkSheet.Cells.Item(1,4) = "IOSize"
$WorkSheet.Cells.Item(1,5) = "IOType"
$WorkSheet.Cells.Item(1,6) = "PendingIO"
$WorkSheet.Cells.Item(1,7) = "FileSize"
$WorkSheet.Cells.Item(1,8) = "IOPS"
$WorkSheet.Cells.Item(1,9) = "MBs/Sec"
$WorkSheet.Cells.Item(1,10) = "Min_Lat(ms)"
$WorkSheet.Cells.Item(1,11) = "Avg_Lat(ms)"
$WorkSheet.Cells.Item(1,12) = "Max_Lat(ms)"
$WorkSheet.Cells.Item(1,13) = "Caption"

$Results | % {
    $WorkSheet.Cells.Item($x,1) = $_.Threads
    $WorkSheet.Cells.Item($x,2) = $_.Operation
    $WorkSheet.Cells.Item($x,3) = $_.Duration
    $WorkSheet.Cells.Item($x,4) = $_.IOSize
    $WorkSheet.Cells.Item($x,5) = $_.IOType
    $WorkSheet.Cells.Item($x,6) = $_.PendingIO
    $WorkSheet.Cells.Item($x,7) = $_.FileSize
    $WorkSheet.Cells.Item($x,8) = $_.IOPS
    $WorkSheet.Cells.Item($x,9) = $_.MBs_Sec
    $WorkSheet.Cells.Item($x,10) = $_.MinLat_ms
    $WorkSheet.Cells.Item($x,11) = $_.AvgLat_ms
    $WorkSheet.Cells.Item($x,12) = $_.MaxLat_ms
    $WorkSheet.Cells.Item($x,13) = [string]$_.IOSize + "KB " + [string]$_.IOType + " " + `
                                [string]$_.Operation + " " + [string]$_.Threads + `
                                " Threads " + [string]$_.PendingIO + " pending"
    $x++}

$WorkBook.Charts.Add() | Out-Null
$Chart = $WorkBook.ActiveChart
$Chart.SetSourceData($WorkSheet.Range("H1:H$x"))
$Chart.SeriesCollection(1).xValues = $WorkSheet.Range("M2:M$x")
$Chart.SetSourceData($WorkSheet.Range("H1:H$x"))
$Chart.SeriesCollection(1).xValues = $WorkSheet.Range("M2:M$x")
$Chart.Name = "IOPS"

$WorkBook.Charts.Add() | Out-Null
$WorkBook.ActiveChart.SetSourceData($WorkSheet.Range("I1:I$x"))
$Chart = $WorkBook.ActiveChart
$Chart.SeriesCollection(1).xValues = $WorkSheet.Range("M2:M$x")
$Chart.Name = "MBs Sec"

I don’t know why, but the first Chart doesn’t refresh properly unless I call SetSourceData() and set the SeriesCollection xValues properties twice.  Since the code block that loads the data into the worksheet tracks the rows with the $x variable, the Range for the chart is always row 1 to $x.  The H column happens to be the column that holds the IOPS values and the I column holds the MBs Sec values in the workbook.  The only thing missing at this point a parameter declaration at the head of the script file to require an input parameter $FileName which is passed to Get-Content to read the information.

param(    [Parameter(Mandatory=$TRUE)]
        [ValidateNotNullOrEmpty()]
        [string]
        $FileName)

The complete script is attached to this blog post.  If you use it let me know your thoughts.

SQLIOResults.ps1 (4.98 kb)