September 21, 2011 at 10:17 am
Hello All,
Just want to know, if anyone is facing issues relating to running sql server 2008R2 on Hyper V (OS: Windows server 2008R2).
Here is the scenario:
I have a virtual machine running windows server 2008R2 which has 1 instance of sql server 2008 R2. The VM is allocated 4GB on start-up and configured to add extra 4GB dynamically using the Hyper V settings of Maximum RAM (8GB) in total.
so 4GB on startup and maximum RAM set to 8GB (making 4GB dynamic).
Last night, sql server crashed and generated dumpfiles.
Not sure if any one is facing the same problem.
Is dynamic memory setting safe to use with SQL server 2008R2 running on Hyper V ?
I need some explanation as to why static vs dynamic RAM allocation will be beneficial ? Any ideas or links will be highly appreciated.
I know that dynamic RAM allocation is not good, but need some thoughts/explanation.
The windows error log says:
@@version:
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
max memory set to: 3000 MB (before crash it was set to 6GB)
Below are the log file details:
SQLDump0001.txt
ted 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 2944
MultiPage Allocator 0
2011-09-20 23:07:57.75 spid61
MEMORYCLERK_XE_BUFFER (node 0) KB
---------------------------------------- ----------
VM Reserved 4224
VM Committed 4224
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 0
2011-09-20 23:07:57.75 spid61
MEMORYCLERK_HOST (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 104
MultiPage Allocator 64
2011-09-20 23:07:57.75 spid61
MEMORYCLERK_SOSNODE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 102952
MultiPage Allocator 16000
2011-09-20 23:07:57.75 spid61
MEMORYCLERK_SOSNODE (node 64) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 2336
2011-09-20 23:07:57.75 spid61
MEMORYCLERK_SOSNODE (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 102960
MultiPage Allocator 18336
2011-09-20 23:07:57.75 spid61
MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 48
MultiPage Allocator 0
2011-09-20 23:07:57.75 spid61
MEMORYCLERK_XE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 80
MultiPage Allocator 168
2011-09-20 23:07:57.75 spid61
CACHESTORE_OBJCP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 71232
MultiPage Allocator 1968
2011-09-20 23:07:57.75 spid61
CACHESTORE_SQLCP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 42968
MultiPage Allocator 3152
2011-09-20 23:07:57.75 spid61
CACHESTORE_PHDR (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 49392
MultiPage Allocator 32
2011-09-20 23:07:57.75 spid61
CACHESTORE_XPROC (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 160
MultiPage Allocator 0
2011-09-20 23:07:57.75 spid61
CACHESTORE_TEMPTABLES (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 48
MultiPage Allocator 0
2011-09-20 23:07:57.75 spid61
CACHESTORE_NOTIF (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
2011-09-20 23:07:57.75 spid61
CACHESTORE_VIEWDEFINITIONS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
2011-09-20 23:07:57.75 spid61
CACHESTORE_XMLDBTYPE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2011-09-20 23:07:57.75 spid61
CACHESTORE_XMLDBELEMENT (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2011-09-20 23:07:57.75 spid61
CACHESTORE_XMLDBATTRIBUTE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2011-09-20 23:07:57.75 spid61
CACHESTORE_STACKFRAMES (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 8
2011-09-20 23:07:57.75 spid61
CACHESTORE_STACKFRAMES (node 64) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 8
2011-09-20 23:07:57.75 spid61
CACHESTORE_STACKFRAMES (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 16
2011-09-20 23:07:57.75 spid61
CACHESTORE_BROKERTBLACS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 384
MultiPage Allocator 0
2011-09-20 23:07:57.75 spid61
CACHESTORE_BROKERKEK (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2011-09-20 23:07:57.75 spid61
CACHESTORE_BROKERDSH (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2011-09-20 23:07:57.75 spid61
CACHESTORE_BROKERUSERCERTLOOKUP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2011-09-20 23:07:57.75 spid61
CACHESTORE_BROKERRSB (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
2011-09-20 23:07:57.75 spid61
CACHESTORE_BROKERREADONLY (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 120
MultiPage Allocator 0
2011-09-20 23:07:57.75 spid61
CACHESTORE_BROKERTO (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2011-09-20 23:07:57.75 spid61
CACHESTORE_EVENTS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
2011-09-20 23:07:57.75 spid61
CACHESTORE_SYSTEMROWSET (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 5872
MultiPage Allocator 0
2011-09-20 23:07:57.75 spid61
CACHESTORE_CONVPRI (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 32
MultiPage Allocator 0
2011-09-20 23:07:57.75 spid61
CACHESTORE_FULLTEXTSTOPLIST (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 32
MultiPage Allocator 0
2011-09-20 23:07:57.75 spid61
USERSTORE_SCHEMAMGR (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 101752
MultiPage Allocator 11096
2011-09-20 23:07:57.75 spid61
USERSTORE_DBMETADATA (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 103808
MultiPage Allocator 0
2011-09-20 23:07:57.75 spid61
USERSTORE_TOKENPERM (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 2368
MultiPage Allocator 616
2011-09-20 23:07:57.75 spid61
USERSTORE_OBJPERM (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 1408
MultiPage Allocator 0
2011-09-20 23:07:57.75 spid61
USERSTORE_SXC (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 96
MultiPage Allocator 0
2011-09-20 23:07:57.75 spid61
USERSTORE_SXC (node 64) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2011-09-20 23:07:57.75 spid61
USERSTORE_SXC (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 104
MultiPage Allocator 0
2011-09-20 23:07:57.75 spid61
OBJECTSTORE_LBSS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 64
MultiPage Allocator 256
2011-09-20 23:07:57.75 spid61
OBJECTSTORE_SNI_PACKET (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 688
MultiPage Allocator 56
2011-09-20 23:07:57.75 spid61
OBJECTSTORE_SNI_PACKET (node 64) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 56
2011-09-20 23:07:57.75 spid61
OBJECTSTORE_SNI_PACKET (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 688
MultiPage Allocator 112
2011-09-20 23:07:57.75 spid61
OBJECTSTORE_SERVICE_BROKER (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 448
MultiPage Allocator 0
2011-09-20 23:07:57.75 spid61
OBJECTSTORE_LOCK_MANAGER (node 0) KB
---------------------------------------- ----------
VM Reserved 16384
VM Committed 16384
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 904
MultiPage Allocator 0
2011-09-20 23:07:57.75 spid61
OBJECTSTORE_LOCK_MANAGER (node 64) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 24
MultiPage Allocator 0
2011-09-20 23:07:57.75 spid61
OBJECTSTORE_LOCK_MANAGER (Total) KB
---------------------------------------- ----------
VM Reserved 16384
VM Committed 16384
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 928
MultiPage Allocator 0
2011-09-20 23:07:57.82 spid61
Buffer Pool Value
---------------------------------------- ----------
Committed 512000
Target 524288
Database 449118
Dirty 2607
In IO 0
Latched 2
Free 0
Stolen 62882
Reserved 284
Visible 524288
Stolen Potential 434907
Limiting Factor 13
Last OOM Factor 20
Page Life Expectancy 4294952
2011-09-20 23:07:57.82 spid61
Process/System Counts Value
---------------------------------------- ----------
Available Physical Memory 1138262016
Available Virtual Memory 8791406075904
Available Paging File 5290520576
Working Set 4435443712
Percent of Committed Memory in WS 100
Page Faults 1145292
System physical memory high 1
System physical memory low 0
Process physical memory low 0
Process virtual memory low 0
2011-09-20 23:07:57.82 spid61
Procedure Cache Value
---------------------------------------- ----------
TotalProcs 950
TotalPages 21113
InUsePages 239
2011-09-20 23:07:57.82 spid61
Global Memory Objects Pages
---------------------------------------- ----------
Resource 840
Locks 116
XDES 43
SETLS 4
SE Dataset Allocators 8
SubpDesc Allocators 4
SE SchemaManager 7329
SE Column Metadata Cache 6436
SQLCache 248
Replication 2
ServerGlobal 50
XP Global 2
SortTables 2
2011-09-20 23:07:57.82 spid61
Query Memory Objects (internal) Value
---------------------------------------- ----------
Grants 1
Waiting 0
Available 373909
Current Max 374277
Future Max 374277
Physical Max 374277
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
2011-09-20 23:07:57.82 spid61
Small Query Memory Objects (internal) Value
---------------------------------------- ----------
Grants 0
Waiting 0
Available 19698
Current Max 19698
Future Max 19698
2011-09-20 23:07:57.82 spid61
Optimization Queue (internal) Value
---------------------------------------- ----------
Overall Memory 3442679808
Target Memory 2922610688
Last Notification 1
Timeout 6
Early Termination Factor 5
2011-09-20 23:07:57.82 spid61
Small Gateway (internal) Value
---------------------------------------- ----------
Configured Units 16
Available Units 16
Acquires 0
Waiters 0
Threshold Factor 380000
Threshold 380000
2011-09-20 23:07:57.82 spid61
Medium Gateway (internal) Value
---------------------------------------- ----------
Configured Units 4
Available Units 4
Acquires 0
Waiters 0
Threshold Factor 12
Threshold -1
2011-09-20 23:07:57.82 spid61
Big Gateway (internal) Value
---------------------------------------- ----------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
Threshold -1
2011-09-20 23:07:57.82 spid61
Memory Pool Manager Pages
---------------------------------------- ----------
Reserved Current 368
Reserved Limit 435259
2011-09-20 23:07:57.82 spid61
Memory Pool (internal) Pages
---------------------------------------- ----------
Allocations 63182
Predicted 156383
Private Target 0
Private Limit 0
Total Target 498073
Total Limit 498073
OOM Count 0
2011-09-20 23:07:57.82 spid61
MEMORYBROKER_FOR_CACHE (internal) Pages
---------------------------------------- ----------
Allocations 47740
Rate 0
Target Allocations 389430
Future Allocations 0
Overall 420249
Last Notification 1
2011-09-20 23:07:57.82 spid61
MEMORYBROKER_FOR_STEAL (internal) Pages
---------------------------------------- ----------
Allocations 15074
Rate 0
Target Allocations 356764
Future Allocations 0
Overall 420249
Last Notification 1
2011-09-20 23:07:57.82 spid61
MEMORYBROKER_FOR_RESERVE (internal) Pages
---------------------------------------- ----------
Allocations 368
Rate 0
Target Allocations 420249
Future Allocations 93569
Overall 420249
Last Notification 1
2011-09-20 23:08:21.38 Server Using 'dbghelp.dll' version '4.0.5'
2011-09-20 23:08:21.45 Server **Dump thread - spid = 0, EC = 0x0000000000000000
2011-09-20 23:08:21.45 Server ***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL10_50.INSTANCE1\MSSQL\LOG\SQLDump0001.txt
2011-09-20 23:08:21.45 Server * *******************************************************************************
2011-09-20 23:08:21.45 Server *
2011-09-20 23:08:21.45 Server * BEGIN STACK DUMP:
2011-09-20 23:08:21.45 Server * 09/20/11 23:08:21 spid 1776
2011-09-20 23:08:21.45 Server *
2011-09-20 23:08:21.45 Server * Non-yielding IOCP Listener
2011-09-20 23:08:21.45 Server *
2011-09-20 23:08:21.45 Server * *******************************************************************************
2011-09-20 23:08:21.45 Server * -------------------------------------------------------------------------------
2011-09-20 23:08:21.45 Server * Short Stack Dump
2011-09-20 23:08:21.66 Server Stack Signature for the dump is 0x0000000000000184
Current time is 23:08:21 09/20/11.
=====================================================================
BugCheck Dump
=====================================================================
This file is generated by Microsoft SQL Server
version 10.50.1600.1
upon detection of fatal unexpected error. Please return this file,
the query or program that produced the bugcheck, the database and
the error log, and any other pertinent information with a Service Request.
Computer type is Intel(R) Xeon(R) CPU X5560 @ 2.80GHz.
Bios Version is VRTUAL - 3000919
BIOS Date: 03/19/09 22:51:32 Ver: 09.00.04
4 X64 level 8664, 10 Mhz processor (s).
Windows NT 6.1 Build 7601 CSD Service Pack 1.
Memory
MemoryLoad = 82%
Total Physical = 5963 MB
Available Physical = 1068 MB
Total Page File = 10057 MB
Available Page File = 5026 MB
Total Virtual = 8388607 MB
Available Virtual = 8384125 MB
**Dump thread - spid = 0, EC = 0x0000000000000000
***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL10_50.INSTANCE1\MSSQL\LOG\SQLDump0001.txt
* *******************************************************************************
*
* BEGIN STACK DUMP:
* 09/20/11 23:08:21 spid 1776
*
* Non-yielding IOCP Listener
*
* *******************************************************************************
* -------------------------------------------------------------------------------
* Short Stack Dump
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
September 21, 2011 at 3:23 pm
We run a few SQL 2008R2 instances on Server 2008 R2 VMs in Hyper-V. It's not ideal to use dynamic memory presently (Denali promises better support), but if you absolutely must, check out this useful paper from MSDN:
September 21, 2011 at 7:38 pm
joffwilliams (9/21/2011)
We run a few SQL 2008R2 instances on Server 2008 R2 VMs in Hyper-V. It's not ideal to use dynamic memory presently (Denali promises better support), but if you absolutely must, check out this useful paper from MSDN:
Hi Williams,
Thanks for your reply.
Actually, I have already gone through that white paper, but thanks for pointing it out.
I did lot of research on Hyper-V's dynamic memory feature and its compatibility with sql server 2008 R2.
This is best resource for understanding as I spend lot of time in researching and this might be useful for sql server community on SSC 🙂
http://blogs.msdn.com/b/sqlosteam/archive/2011/01/31/sql-server-and-hyper-v-dynamic-memory-part-1.aspx">http://sqlblog.com/blogs/sqlos_team/archive/2011/03/09/sql-server-and-hyper-v-dynamic-memory-part-3.aspx
Guest Operating System Requirements
In addition to this Dynamic Memory is only supported in Standard, Web, Enterprise and Datacenter editions of windows running inside VMs. Make sure that your VM is running one of these editions. For additional requirements on each operating system see “Dynamic Memory Configuration Guidelines” here.
SQL Server Requirements
All versions of SQL Server support Hyper-V Dynamic Memory. However, only certain editions of SQL Server are aware of dynamically changing system memory. To have a truly dynamic environment for your SQL Server VMs make sure that you are running one of the SQL Server editions listed below:
· SQL Server 2005 Enterprise
· SQL Server 2008 Enterprise / Datacenter Editions
· SQL Server 2008 R2 Enterprise / Datacenter Editions
Unfortunately, we use SQL server 2008R2 standard edition and it does not support (or is aware ) of hot add memory also called dynamic memory and hence it crashed.
We have opened a case with MS and waiting for what they come up with.
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
September 21, 2011 at 9:05 pm
If you don't mind and are able to share, I'd be interested to hear what they have to say to you. Since we have the VM hosts per-processor licensed for Enterprise edition all our instances on that host are Enterprise edition and I have no experience running a VM with running Standard edition.
Dynamic memory management is tricky with SQL Server as Hyper-V is waiting for available OS memory to take back and SQL Server's performance benefits come from having plenty of data in the buffer pool!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply