Monthly Archives: August 2012

Memory Considerations for Setting up the Hyper-V for SQL

Posted by dpless on August 22, 2012 None comments

By Lisa Jackson-Neblett (PFE) – lisaneb@microsoft.com

There are many factors to consider when running SQL Server in a Hyper-V Virtual Machine, guest.  Being proactive with the Virtual Machines memory settings can assist you
in avoiding several memory issues that could lead to performance degradation.  Understanding how memory can impact SQL Server’s buffer pool is important.  Dynamic changes to the size of the buffer
pool can negatively impact performance of SQL Server.  In the event the buffer pool size changes due to external factors like memory being removed from the virtual machine it could
impact SQL Server’s performance.  The performance may be decreased by the hypervisor requesting memory from SQL Server.  Thus, SQL Server would perform
more physical disk reads vs. logical disk reads because the data is no longer in memory.  SQL Server also uses memory for a plethora of other activities like storing cached plans. Therefore,
optimizing memory in SQL Server Virtual Machine is important to achieve optimal performance.

Hyper-V has two main memory management settings for virtual machines.  The first is static memory.  When static memory is used the virtual machine gets a fixed amount of memory.
If the virtual machine uses static memory, the virtual machine memory should remain constant.

The second memory setting is dynamic memory.  Dynamic memory is used to allow the host to reclaim and redistribute memory to the virtual machines as needed.  There are three main dynamic memory settings,
Startup RAM, Maximum RAM and Memory Buffer.  Startup RAM is the amount the virtual machine is allocated on startup.  If there is not enough memory allocated or available on the host server the virtual machine will fail to
start.  I use the following formula for obtaining a starting point for configuring the Startup RAM memory.

Startup RAM = OS needs (typically 1-2GB but can vary) + min_server_memory (SQL Server Setting)

If the min_server_memory setting in SQL Server is set to its default of 0 you will want to have a minimum Startup RAM => OS needs (typically 1-2GB but can vary) + at least 1 GB which is the minimum requirement
for SQL Server. Theoretically you could run it with less but will most likely impact performance if not enough memory is allocated to SQL Server.

Maximum RAM is the maximum amount of RAM that will be available to the virtual machine.  I use the following formula as a starting point for configuring the Maximum RAM memory.

Maximum RAM => OS needs (typically 1-2GB but can vary) + max_server_memory (SQL Server Setting) + + memory requirements for other applications running in the virtual machine + 5% (Memory Buffer allowance).

The last setting for dynamic memory is the Memory buffer.  The memory buffer is used by Hyper-V to specify how much extra memory is needed for cache files to obtain peak performance.  Since SQL Server already manages its own memory and it will use all it is allowed to you will want to make this setting as low as possible which is 5%.

Once you have determined how you are going to configure your memory settings it will be important to check performance counters on the host and virtual machine to see if the memory settings are good or if they need to be “tweaked” to obtain optimal performance. Other performance metrics should be measured from within SQL Server as well to make sure you have the optimal settings.

How to Parse DBCC MEMORYSTATUS via Powershell

Posted by dpless on August 16, 2012 None comments
By Tim Chapman (DSE) - timchap@microsoft.com

SQL Server has many ways to dig deep into diagnosing memory related problems.  Today it is common to use Dynamic Management Objects (views/functions) to expose a large portion of this information.  However, some useful data isn’t consumable by DMVs, so we must use DBCC MEMORYSTATUS to get what we need.  The roadblock output from DBCC MEMORYSTATUS isn’t easily consumable and query-able.  So, my goal of this blog post is to create a Powershell script to load the output from the DBCC command into a local database table so that you can query/report off of it.

Note:  While you CAN dump the contents from DBCC MEMORYSTATUS into a temp table on the server using INSERT…EXEC, an important aspect of the data isn’t inserted into the temp table.

The script accepts 3 different parameters:

$FileLocation – if you have the DBCC MEMORYSTATUS output in a file, specify the location here.  Otherwise the script will grab the output from DBCC MEMORYSTATUS from the instance you connect to.
$SQLServer – this is the SQL instance to connect and load the output from DBCC MEMORYSTATUS.
$DBName – this is the name of the database you want the DBCC MEMORYSTATUS output stored.

I’ve attached the file instead of pasting the output here as I couldn’t get the formatting how I wanted it to be.  If I get it corrected I’ll make the change a later.

Once you’ve executed the script, you’ll have output very similar to the following, which will allow you to run any reports as needed.

I hope you enjoy, and I’d love to hear comments as to how to improve the script!

Note: Click below to get the script for Tim’s blog:
http://blogs.msdn.com/b/timchapman/archive/2012/08/16/how-to-parse-dbcc-memorystatus-via-powershell.aspx

Thanks,

Tim Chapman