Through the Virtual Looking Glass

Many thanks to all those who helped produce and manage the SQLBits 9 conference in Liverpool this weekend and to everyone who attended.  Coeo as a team enjoyed the long weekend’s events and I’m especially grateful to all those who attended my session “Through the Virtual Looking Glass – Monitoring Virtualised SQL Servers”.  Or virtualized, depending on your choice of spelling.

The purpose of this session was to explain why the approach you take to monitoring a virtualised instance of SQL Server can be massively different to how you would monitor a traditional physical server running SQL Server, along with some supporting theory about how hypervisors work.

The slides from my session can be found here, with the dynamic memory checking T-SQL script I used below.

In summary, there are three areas to consider:

CPU – a big change if you’re interested in how much physical CPU utilisation your server is using, rather than logical CPU utilisation.

Memory – a big change if you’re using a hypervisor which supports Dynamic Memory or your vendor’s equivalent.  “Your physical memory may go down as well as up”.

Storage – no significant changes, although contention points such as HBAs, NICs and storage controllers should be reviewed.

Monitoring SQL Server’s Memory in a Dynamic Memory world

During my session I demo’d how Hyper-V reacts to SQL Server wanting more memory and how SQL Server in turn reacts to that memory becoming available. Explaining how that works is best left to the SQLCat team who have an excellent whitepaper here.

In the meantime, below is the query I used in my memory demo, this was written for Denali CTP3, the original buffer pool related column names can be found in sys.dm_os_sys_info for SQL Server 2008:

select
(m.total_physical_memory_kb /1024) as ‘Physical Memory (MB)’,
convert(decimal(3,1),(convert(decimal(10,1),m.available_physical_memory_kb / 1024)
/       
convert(decimal(10,1),m.total_physical_memory_kb / 1024))
* 100) as ‘Available Memory as % of Physical Memory’

from sys.dm_os_sys_memory m, sys.dm_os_sys_info i

select
convert(decimal(10,1),(convert(decimal(10,1),i.committed_target_kb / 1024)
/       
convert(decimal(10,1),m.total_physical_memory_kb / 1024))
* 100) as ‘Committed Target as % of Physical Memory’,
convert(decimal(3,1),(convert(decimal(10,1),i.committed_kb  / 1024)
/       
convert(decimal(10,1),m.total_physical_memory_kb / 1024))
* 100) as ‘Total Committed as % of Physical Memory’
           
from sys.dm_os_sys_memory m, sys.dm_os_sys_info i

Advertisements

One thought on “Through the Virtual Looking Glass

  1. Pingback: SQLBits 9–”Oops, I did it again” or “Come together” | Dirk Hondong's blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s