SQL MCM Training – Day 2

Today’s day was Jonathan Kehayias days, and it was pretty impressive. Jonathan covered the following 3 modules, and he can speak really continuously and forever J

• IO – Storage Area Networks
• IO – Installation and Testing
• SQLOS

I’ve already read few whitepapers in the past about SANs and also watched the MCM Readiness videos about it, but the material that Jonathan was presenting was pretty impressive. There were a lot of concepts new to me (like Multi Pathing), therefore I didn’t have taken any notes in this module, because I have listened to Jonathan and tried to follow him. He also did a very great demo about Multi Pathing with the iSCSCI Target and iSCSI Initiator provided by Microsoft. The SAN module was definitely a module, where I learned around 90% new things.

Important note:
Those notes are the things that I have written down for my personal remembering. So please don’t take anything for 100% sure and true in the way that I have written it. For some notes you also have to know the context in which we have discussed it, and why I have taken that note for me personally. Today morning we had a discussion about one specific note that I have taken about CXPACKET waits and why Hyper-Threading can be bad, but at always “it depends”, and some things/context information I haven’t written down, because I have it somewhere in my brain J.

So here are the notes that I have taken during the 2^nd day of SQL MCM training.

Module 5: IO – Installation and Testing

• GPT partitions can manage more than 2 TB of data
  
  • Max: 256 TB
    
• Spanned disk just provide more size, but not more performance
  
  • Each drive is used sequentially
    
• Allocation Unit Size should be the size of a single extent
  
• Tools
  
  • SQLIO
    
  • IOMeter
    

Module 6: SQLOS

• Each NUMA node gets its own Lazywriter
  
• MAXDOP: number of physical cores per NUMA node
  
• http://en.wikipedia.org/wiki/CPU_cache
  
  
• Soft NUMA usage: http://msdn.microsoft.com/en-us/library/dd537533(v=sql.100).aspx
  
  
• SOS_Worker: Thread
  
• SOS_Task: Operation that a SOS_Worker executes
  
• Startup parameter -P24: gets you 24 processors in SQLOS
  
• Hidden schedulers are processing background tasks
  
• Visible schedulers are processing user requests
  
• Built-In SQLCLR data types are not supported when Fiber mode is activated
  
  • SQL Server will gives you an error message when you access a SQLCLR data type column
    
• SQL Server Connections are bound to a scheduler
  
• Each scheduler has its own waiter list and runnable queue
  
• Scheduler Monitor
  
  • Generates automatically a mini-dump for post-mortem debugging
    
• Names instances are running on different ports for the DAC
  
• PAE (Physical Address Extension) changes the memory pointer size from 32 to 36 bits
  
  • 64 GBs are addressable in the user mode
    
  • Only usable for the Buffer Pool, NOT for the Plan Cache
    
• /3 GB Option
  
  • Uses 1 GB from the Kernel mode
    
  • That 1 GB stores normally PTEs (Page Table Entries)
    
  • Therefore it’s only possible to use a maximum of 16 GB RAM, because of the reduced PTEs
    
• Understanding VAS Reservation
  
  • http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/07/07/understanding-the-vas-reservation-aka-memtoleave-in-sql-server.aspx
    
    
• Locked Pages is only for Buffer Pool, not for Plan Cache
  
  • Can be used also by Standard Edition with Trace Flag -T845
    
  • Can be used for Windows System Cache problems
    
  • Startup time of SQL Server takes longer because SQL Server allocates all memory up to the Max Server Memory setting
    
  • The memory must be also contiguous, otherwise SQL Server gets less memory for the Buffer Pool
    
• Memory\Available Mbytes > 150 – 300MB
  
  • Otherwise it means that the Windows OS is under memory pressure
    
• Internal Memory Pressure
  
  • Another Memory Clerk wants to allocate memory
    
• Resource Monitor responds to memory pressure
  
  • Outputs information to the OS ring buffer
    
  • ring_buffer_type = “RING_BUFFER_RESOURCE_MONITOR”
    
• Activity Monitor adds a lot of load to an system which is in performance problems
  
  • Especially TempDb
    
• NUMA Memory Balancing is not shown through DBCC MEMORYSTATUS
  
• http://blogs.msdn.com/b/psssql/archive/2009/05/15/how-it-works-dbcc-memorystatus-locked-pages-allocated-and-singlepageallocator-values.aspx
  
  
• http://blogs.msdn.com/b/psssql/archive/2010/02/23/how-it-works-sql-server-2008-numa-and-foreign-pages.aspx
  
  
• Scheduler can be set offline through the Affinity Mask
  
• Unloading AppDomain on x32
  
  • Indication for Memory Pressure
    
• Interpreting DBCC MEMORYSTATUS
  
  • http://support.microsoft.com/kb/907877/en-us
    
    
• Max Worker Count should be changed to a higher value when you use Database Mirrroing with a large amount of databases
  
  • Mirroring uses at least 3 threads for principal and at least 5 threads for the mirror per database
    
• One worker per level of DOP (e.g. 24)
  
  • 24 Threads for the Producers
    
  • 24 Threads for the Consumers
    
  • 1 Thread for the Coordinator
    
  • For each executed query!
    
  • Those threads are bound to parallel queries and can’t be used by anything else!
    
• How To Diagnose and Correct Errors 17883, 17884, 17887, and 17888
  
  • http://technet.microsoft.com/de-de/library/cc917684(en-us).aspx

-Klaus