November 2011 - Posts

Through the Virtual Microscope–SQL Server and Hyper-V
11 November 11 06:53 PM | GavinPayneUK | 6 comment(s)

(Version 1.1!)

In my recent SQLBits conference session, Through the Virtual Looking Glass available to watch here, I spoke about monitoring SQL Server in a virtualised environment.  We looked at good and bad contention, where resource pressures which can adversely affect SQL Server might come from and most importantly how we can monitor the environment to detect them.

Since then, I’ve been in discussions with some of the Microsoft product team who are focussed on optimising Windows 8 Hyper-V and SQL Server 2012, specifically Guy Bowerman and Serdar Sutay.  We’ve been talking about some of the challenges SQL Server professionals have today working in virtualised environments and how we could resolve them through future product functionality.  An example of a recent change they’ve engineered into the product has been the support for hot-add memory support in the standard edition of SQL Server 2012 when running in a virtual environment, still Enterprise Edition only for physical servers.  This will allow lower end users of SQL Server to still benefit from the Dynamic Memory feature of Windows Server 2008R2 service pack 1.

In both my presentation and conversations with Microsoft I’ve been discussing the importance of knowing as much as we can about the physical host server, its utilisation, the hypervisor’s configuration as well as what’s happening inside the virtual server – the SQL Server component.  To help, Serdar kindly sent me some information about Hyper-V information exposed inside the virtual machine’s registry.

As a result, I’ve written a Powershell script for DBAs which gives a bit more of an insight about the Hyper-V environment SQL Server is running within.  The script can be found at the bottom of this blog article which gives the following information:

  • The hostname of the virtual server the script is running on – for reference purposes.
  • The hostname of the physical host server the script is running on – this is useful to be able to tell if the virtual server has been Live Migrated to another physical host server without the DBA knowing.  Why?  Well this maybe an issue if the new physical host server is of a much lower spec than the one it was on previously; if SQL Server jobs start running more slowly than they did yesterday then the problem might have nothing to do with SQL Server at all.  Instead, the new host server might be slower or more highly contended.
  • A check to see whether or not its possible for Hyper-‘s Dynamic Memory feature to be used on the current host server.  While we may not be able to tell as easily whether  Dynamic Memory is actually configured for use, knowing whether or not it could be will at least help with memory related performance troubleshooting.
  • Finally, the last two checks give information about the physical CPUs in the host server.  This information may not directly be relevant to the performance of the virtual server as CPU resource maybe restricted or contended, but it is a good way to help identify whether you’re using the same physical server as you were yesterday.  If your virtual  server has been moved at least you can tell whether you should be getting the same CPU performance as you were previously.

It’s a fairly basic script which I’m sure could fail badly due to its lack of error handling, however it worked as expected from a Powershell session.

The script is below, apologies if the formatting is slightly odd:

#Get Hyper-V environment data from the registry
$Path = 'HKLM:\SOFTWARE\Microsoft\Virtual Machine\Guest\Parameters'
$Key = Get-Item -LiteralPath $path

$PhysicalHostname = $Key.GetValue('PhysicalHostName')
$VirtualHostname  = $Key.GetValue('VirtualMachineName')
$OsMajor          = $Key.GetValue('HostingSystemOsMajor')
$OsMinor          = $Key.GetValue('HostingSystemOsMinor')
$ServPack         = $Key.GetValue('HostingSystemSpMajor')

if (($OSMajor -ge '6') -and ($OSMinor -ge '1') -and ($ServPack -ge '1'))
{ $DynamicMemory = 'Supported'}
{ $DynamicMemory = 'Not supported'}

#Get physical CPU information
$CPUName = (Get-WMIObject Win32_Processor).Name
$CPUMHz  = (Get-WMIObject Win32_Processor).MaxClockSpeed

write-host 'The hostname of this virtual machine is               :' $VirtualHostname
write-host 'The hostname of the physical host server is           :' $PhysicalHostname
write-host ''

write-host 'Hyper-V Dynamic Memory support on this host server is :' $DynamicMemory
write-host ''

write-host 'The CPU type in the physical host server is           :' $CPUName
write-host 'The CPU speed in the physical host server is          :' $CPUMHz 'MHz'