SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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


Klaus Aschenbrenner

Klaus Aschenbrenner provides independent SQL Server Consulting Services across Europe and the US. Klaus works with the .NET Framework and especially with the SQL Server 2005/2008 from the very beginnings. In the years 2004 - 2005 Klaus was entitled with the MVP award from Microsoft for his tremendous support in the .NET Community. Klaus has also written the book Pro SQL Server 2008 Service Broker which was published by Apress in the Summer of 2008. Further information about Klaus you can find on his homepage at http://www.SQLpassion.at. He also twitters at http://twitter.com/Aschenbrenner.


No comments.

Leave a Comment

Please register or log in to leave a comment.