SQL MCM Training – Day 9

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
  • 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

Module 5: Virtualization

Thanks for reading


Attend my Live Training on September 29, 2022:

SQL Server Availability Groups

EUR 590 incl. 20% VAT