Today Jonathan was talking the whole day about Consolidation and Virtualization. There were again a lot of new concepts, and new things to learn.

Module 4: Consolidation

• Event Notifications for Monitoring
  • Deadlocks, Locking/Blocking Scenarios
• Multiple Instances for TempDb bottleneck
• Soft-NUMA should be used to bound multiple instances to dedicated CPUs
  • CPU Affinity Maks should not be used, because they are not supported anymore in SQL Server Denali
  • CPUs can be only taken from the local CPU
    • there is no cross CPU exchange
    • Soft NUMA nodes can't be crossed
    • only local memory access
    • Improves performance
  • TCP ports can be bound to Soft NUMA instances
• Multiple concurrent Backups can lead to Buffer Pool Contention
• Multiple instances when you have high concurrent workloads and not enough worker threads
  • Number of worker threads is bound to the instance level
• SQL Server 2008 R2 will report if you run on a Hypervisor
• Detach, Copy, Attach
  • Maybe you're copying free space over the network
• Raw Device Mapping (RDM)
• Migrate Databases through Database Mirroring/Log Shipping
  • Restore a FULL backup
  • Initiate Database Mirroring/Log Shipping
  • Finally make a tail log backup and restore it
  • http://www.sqlservercentral.com/articles/Backup+%2f+Restore/66962/
• On-the-Fly VHD file migration
  • Put everything on a VHD
    • Including master database etc.
  • Move the VHD file from one VM to another VM
• There were no providers for 64bit Excel/Access, you can use the following workaround
  • Install SQL Express x32
  • Linked Server from SQL Server x64 to SQL Express x32
  • Linked server from SQL Express x32 to Excel/Access
• Multiple Instances because of security issues
  • Each database needs sysadmin/securityadmin role
  • Put each database on its own instance
• Virtual Machine Failover Cluster for Patching SQL Server Instances
  • Patch the Passive Node
  • Fail over
  • Patch the other Node
  • Fail back (if needed)
• The total of min server memory should be smaller than the total amount of physical memory available
• Max server memory is ALWAYS preferred for multiple instances
• Memory\Available MBs > 150-300MBs
• Thread Pool Starvation
  • Not too much worker threads are available
  • E.g.
    • Query runs with MAXDOP 4 across 100 different connections
    • Each query needs 9 worker threads (4 producer threads, 4 consumer threads, 1 coordinator thread)
    • These are almost 900 concurrent threads
    • Queries can timeout, because this amount of worker threads is not available once a time, see
      • select max_workers_count from sys.dm_os_sys_info
• SQL Server Consolidation at Microsoft
  • http://technet.microsoft.com/en-us/library/dd557540.aspx

Module 5: Virtualization

• Too much CPUs can lead to co-scheduling issues
• The RAM of a VM should not be larger than a NUMA node
  • The assigned number of CPUs should be also not larger than a NUMA node
• Min Server Memory setting should be set inside a VM, because of the Balloon driver
  • Balloon driver fakes memory pressure in the guests to prevent actual memory pressure on the host
  • Ballooning kicks in when you overcommit the host (the assigned memory to the VMs is higher than the physical available memory)
• Hyper-V Dynamic Memory not recommended for SQL Server!
  • http://blogs.msdn.com/b/sqlosteam/archive/2011/01/31/sql-server-and-hyper-v-dynamic-memory-part-1.aspx
  • http://blogs.msdn.com/b/sqlosteam/archive/2011/02/27/sql-server-and-hyper-v-dynamic-memory-part-2.aspx
  • http://blogs.msdn.com/b/sqlosteam/archive/2011/03/09/sql-server-and-hyper-v-dynamic-memory-part-3.aspx
• When you cluster SQL Server in a Virtual Machine, you have to put each SQL Server node on a different physical Virtual Machine Host node
  • Otherwise you have not gained any high availability, when you put each SQL Server node on the same physical Virtual Machine host node

Thanks for reading

-Klaus