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