I recently attend the SQLskills Immersion Training Event on Internals and Performance in Chicago.  I have to say I was already impressed with Paul Randal and Kimberly Tripp’s knowledge of SQL Server given my past experience at conferences and their blogs.  But that said was even more impressed after spending a week with them.  This class was well worth the price and despite having 12 years experience with SQL Server I still learned a tremendous amount of information.  The class is pure 8 hours lecture (with plenty of jokes) for 5 days and I liked that format because it allows to get more information (I have found most classes that have labs spend too much time on them).  It comes with a DVD with a VPC with labs that you can work on your own.  Paul and Kimberly both said we could email them with questions so not having the labs did not bother me.  I just now need to make the time to go through them.  Paul and Kimberly provided plenty of PowerPoint slides, white boarding, and demonstrations.  Paul and Kimberly answered every question we had, of course a lot of the answers were “It depends.”  But they took the time to explain why “It depends” and on what it depends on.

Any DBA that is serious about knowing SQL Server from the ground up should take this training.  I will definitely be taking another class next year with SQLskills.

Outside the training, the hotel was a great hotel, very comfortable and affordable.  The food was fantastic, I ate cheesecake everyday.  Paul and Kimberly hung out with us after the training.  We got a product demonstration of SQL Sentry’s Plan Explorer, which by the way is a great free tool.  We had a great time at the free-all presentations by some of the class attendees.

Below I am going to share my notes and the to do list I came away from the class with, if for no other reason than so I do not lose them.

Module 1 – Data Structures

  • RID (File:Page:Slot)
  • Heaps do free space search to decide what page a new record goes on
  • Forwarded record – hidden 10-byte for original location, if record becomes small enough it may move back to original location
  • ALTER TABLE REBUILD – gets rid of fragmentation in heap
  • Use Trace Flags 2512, 2513 to see if forwarded records
  • Version store adds 14-bytes pointer (Timestamp:Pointer)
  • Version store – every minute chunk created, if still needed it stays until first chunk note needed anymore
  • Version store – completely not logged
  • Ghost cleanup runs every 10 seconds in 2008+, 5 seconds 2005-
  • Ghost cleanup – only single threaded – Can be turned off if performance problem –
  • Ghost cleanup – Check sys.dm_os_waiting_tasks for PAGE_LATCH on a system process, could use XEvents, check log record for LOP_EXPUNGE_GHOST
  • Ghost cleanup – removes slot array, it unmarks as a record, not same thing as a delete
  • 2005 SP3+ – sp_clean_db_free_space – zero free space – can be used to overwrite ghost cleanup – do not use on TDE
  • Page header contains M_ghost_reccount
  • M_LSN most important thing on a data page
  • Slot array starts at the end of the page and goes backward
  • Types of Extents – Mixed and dedicated
  • PFS Pages – Tracks 8088 pages
  • Checkout sp_allocation_metadata on Paul’s blog post

Module 2 – New Database Structures in 2008

  • Backup and data compression are completely different
  • Compress backups allocate large space then shrinks – regular backups grows as needed
  • 2008 R2 supports unicode compression
  • Page compression – 25 changes before it recalculates  – PageModCount in page header
  • Vardecimal – Was SQL Server 2005 SP2+ version of compressions on numeric and decimal
  • Sparse columns was created for Sharepoint 15
  • You cannot have compressed SPARSE data
  • Always create new table to add SPARSE columns
  • Filestream – See whitepaper.
  • Filestream – Consider restore time for lots of files
  • Filestream – if config_value and run_value different need to setup in Windows
  • Filestream -Files named as LSN
  • Filestream -Do not run antivirus on FILESTREAM directory
  • Filestream -1 byte change to 10GB files causes all 10GBs to be backed up

Module 3 – Data Files Internals/Maintenance

  • Don’t look at Disk Queue Length
  • Look at sys.dm_io_virtual_file_stats – avg. latency <=10 ms
  • -E startup parameter to allocated 4 extents at a time on 2005, 64 on 2008
  • Disk Partition Alignment – Jimmy May has a slide desk for CIO/CTO – examine with diskpart/wmic
  • How does allocation work?  Each file in filegroup has a WEIGHTING based on free space – Weightings are recalculated after 8192 allocations
  • Reading Data in Buffer Pool – each buffer keeps last two datetime as smallint, also checks page checksum if clean
  • DBCC CHECKDB WITH ESTIMATE_ONLY – estimate tempdb size
  • Tempdb contention in sysmultiobjrefs, pages 2:1:1, 2:1:3, 2:1:103

Module 4 – Log Files Internals/Maintenance

  • TRUNCATE TABLE uses DEFERRED DROP – minimally logged
  • VLFs (Virtual Log Files) – LSN = VLF SEQ NO:LOG BLOCK:LOG REC
  • LOG BLOCKS are 512 bytes to 60K in size
  • Log Block written to disk when transactions commits or LOG BLOCK is full
  • fn_dblog(NULL, NULL)
  • COMPENSATE is an undo transaction
  • Replication generates differently for log reader
  • CHECKPOINT occurs when recovery thinks it will take more than 1 minute, TempDB 70% full, at the end of minimally log operations completes
  • To watch CHECKPOINT turn on trace flags 3605 and 3602, to see more details 3504
  • Recommendation – Do not change recovery interval, instead manually issue CHECKPOINT command to help reduce load
    • Use counters BUFFER MGR:CHECKPOINT PER?SEC and LAZYWRITER PER/SEC
  • Crash recover and checkpoints – L is log records to disk and D is data written to disk
  • Research paper AIRES about crash recovery
  • No STOPAT in FULL or DIFFERENTIAL, Full back always to end of backup time
  • VLF 64/128 parity bits
  • 500 MB ideal size for VLF, growth size no greater than 8GB, and not even multiples of 4GBs
  • KB2544009 – too many VLFs
  • Too many – 200 or 300 VLFs
  • Write latency – 710 ms – bottleneck
  • DB Mirroring should monitor Send and Redo Queues

Module 5 – Locking and Blocking

  • Lock Escalation – have to set on partition tables – can lean to deadlocks across partitioning – AUTO
  • Lock Escalation threshold 5000 locks
  • KB271509
  • Consider off hours TABLOCK
  • READPAST – Good for queues
  • LOCK_TIMEOUT is for each individual statement not cumulative
  • Indexes generally use PAGE locks – can use sp_indexoption to disallow PAGE locks
  • There is no in-place update of KEY columns – DELETE, INSERT
  • SQL Server Blocked Process Threshold – article on SQL Server Central by Jonathan K.
  • sp_bind_token? pass token to have several connections use the same memory
  • Deadlock pick bases on less amount of redo except if using DEADLOCK_PRIORITY
  • Method to help avoid deadlocks is process tables in ABC order

Module 6 – Snapshot Isolation

  • Adds 10-15% cost – I/O and Memory
  • Instead of using NOLOCK or SET READ UNCOMMITTED
  • Nobody can be using the database when you turn it on
  • Transactional level read – have to make code changes
  • Default behavior in Oracle is statement level consistency
  • Kimberly has a 60-page whitepaper need to read
  • dm_file_space_usage to check version store

Module 7 – Table Design & Partitioning

  • Kimberly uses RAISERROR with Severity 10 instead of PRINT
  • Data Loading Performance Guide Whitepaper
  • Partitioned Views always use UNION ALL
  • Use RIGHT based partition to avoid datetime tick issue
  • 25% more space for sort when creating table partitions
  • To get more into partitioning use the following resources:
    • DVD PT Tables Lab
    • 2005 Whitepaper
    • Ron’s 2008 Whitepaper
    • Online Ops DVD Lab (Partial Database Availability and Online Piecemeal Restore)

Module 8 – Index Internals

  • Niladic timestamp same as getdate()
  • SAMPLED – forwarded records multiply by 100
  • Scans entire page to generated UNIQUEFIER – 8 bytes if not other variable width – else just 4 bytes
  • Filegroup – two files for read/write to spread load
  • SQL scans smallest nonclustered index

Module 9 – Internals & Data Access

  • Calculate Tipping point
  • Any index that is a LEFT based set of another is probably a waste
  • Nonclustered indexes are considered to be a small table that can be used for scans
  • Clustered index is the table at the leaf level
  • INCLUDE – columns in SELECT
  • dm_exec_sessions – to checks session settings
    • SQL Agent for ex. has QUOTED IDENTIFIER difference
  • BOL – Using sql_variant
  • Have same query_hash use FORCE PARAMETERIZATION
  • 2008 Plan Cache Whitepaper
  • For IS NOT NULL on filtered indexes add to index, if Active=1 then you don’t need it
  • “Narrow indexes STINK,” said Kimberly.

Module 10 – Statistics

  • Steps is row in histogram
  • 201 maximum number of steps – 200 distinct, 1 for NULL
  • Small systems use auto create and auto update
  • Bigger systems – maintain yourself possibly
  • Use sp_autostats tablename to check to see if auto on
  • sp_createstats ‘indexonly’,’fullscan’
  • Asynchronous statistics update – so it doesn’t wait until it runs – updates after – don’t generally use
  • 500 + 20% of data changes then status update
  • Manually manage filter stats updates in a job yourself
  • Sometimes rebuild stats before rebuild of indexes
  • Tune TOP 10 tables

Module 11 – Indexing Strategies

  • At design time index a good clustering key, foreign keys, primary and unique keys
  • You can add columns in the INCLUDE for a unique key
  • Data creates HIND____ or _DTA_Stats that the optimizer does not use the hypothetical indexes or stats
  • XML based command line DTA – Reducing production server tuning load – to copy stats but you really should copy production to a test server
  • Hash Match = Index Intersection
  • WITH INDEX(O) – Force table scan
  • When force index intersection specify smallest set first
  • Work table – can only see in plan
  • Option for indexing for OR – rewrite as UNION
  • For troubleshooting, create query with hints that match plan, so you can compare to after
  • Using indexed views in OLTP – not recommended – look at smaller it is creates a hotspot
  • Be careful with index views with JOINs
  • WITH NOEXPAND – to use indexed views on Standard edition
  • Schema bound views create solid dependency chain

Module 12 – Index Fragmentation

  • Readahead in Enterprise is 1 to 1024 pages, Standard 128
  • Estimate more than 10 millions rows will read ahead at double level
  • Less than 1000 pages don’t bother
  • Data warehouse – single threaded reindex on separate filegroup – E
  • Page density, logical fragmentation, extent frag only things you need to look at in DMV
  • db_operational…, Extended Events
  • fn_dblog(NULL, NULL) – LOP_DELETE_SPLIT means not doing a nasty page split
  • Don’t use system wide fillfactor
  • IN_ROW_DATA – alloc_unit_type_desc filter stats dmv
  • PAD_INDEX – for non-leaf spacing
  • Can override instance MAXDOP
  • REUBILD takes 1.2 size, REORGANIZE 8K
  • Online index rebuild takes S lock to block writers, bumps the minor version schema no

To Do List

  • Look at trace flag 1118
  • Look at # of VLFs
  • Don’t use bulk recovery plan
  • Use online rebuilds on Enterprise systems
  • SQL Server Blocked Process Threshold – Jonathan on SQLServerCentral
  • Cycle error log max to 99
  • Check quoted identifier property from sys.objects
    • SELECT OBEJCTPROPERTY (OBJECT_ID, ‘IsQuotedIdentOn’)
    • SELECT OBEJCTPROPERTY (OBJECT_ID, ‘IsExecQuotedIdentOn’)
  • Two files for filegroups to spread SGAM and GAM page contention
  • sp_helpindex8
  • Calculate tipping points
  • Analyze for duplicate indexes
  • Check stats on SFA database
  • Job to update stats on weekend
  • Write process to check out statistics
Tracy Boggiano
Follow me

Tracy Boggiano

Database Administrator at ChannelAdvisor
Tracy has spent over 20 years in IT and has been using SQL Server since 1999 and is currently certified as a MCSE Data Management and Analytics. She has worked on SQL Server 6.5 and up including currently SQL 2017 RC1. She enjoys monitoring, performance tuning, and high availability and disaster recovery technologies. Tracy is currently a co-organizar the for special interest group for Advanced DBA Topics for the TriPASS user group.

She also tinkered with databases in middle school to keep her sports card collection organized.

Tracy has volunteered through the NC Guardian ad Litem program since 2003 advocating for abused and neglected foster children in court.This is her passion outside of SQL Server and favorite job.More information about this program in North Carolina can be found at http://volunteerforgal.org or the national organization CASA at http://www.casaforchildren.org.
Tracy Boggiano
Follow me