SQL MCM Training – Day 9

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

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



Copyright © 2018 by SQLpassion e.U. · Imprint · Offerings · Academy · Contact · Data Protection · Go to Top