When I first started working with SQL Server, version 7.0 was the cutting edge. That version was released in late 1998, and the project I was working on at the time was still in the planning stages. The powers that be decided to move to 7.0 rather than 6.5, and it was only some time later when I had to support some 6.5 instances that I came to appreciate the paradigm shift that the new version represented. I think we can all agree that we all benefit from that brave new world to this day.

Since then, we have seen six new releases of SQL Server (2000, 2005, 2008, 2008R2, 2012 and 2014), and we now find ourselves on the cusp of the release of SQL Server 2016. We have seen over the past couple of releases an increase in the transparency of the development process with the release of beta version, and 2016 took it a whole new level. There have been, if my count is correct, eleven public preview versions released as of this writing, up to release candidate 1. That is absolutely incredible, and I think Microsoft deserves a ton of credit for opening up the process to this degree.

The new and updated feature set in SQL Server 2016 is equally impressive. I have downloaded, installed, and (to varying degrees) used every single one of those preview versions, and yet I feel that I’ve barely skimmed the surface of what I need to eventually learn. I have studied pretty much every new feature in the product, but I just haven’t been able to dig in nearly as deep as I would like. There’s just too much.

So, I guess that all of this just my way of saying that I’m really quite excited for the imminent release of SQL Server 2016. It is packed with new features, and I am sure that it will be some time until I get the opportunity to use some of these new bits of functionality in real systems (and until they mature enough to be ready for such use). Still, this is an exciting time for me — more so than for the past few releases.

I attended the SQL intersection conference in November 2014 in Las Vegas, Nevada.  I am including my notes from the sessions in this blog, primarily for my own reference later.  For anyone that comes across these notes, please take them with a grain of salt – I may very easily have misheard something or have written it down incorrectly.

Session Title:  Queries Gone Wild 2: Statistics and Cardinality Estimation, Versions 2008, 2008R2, 2012 and 2014
Instructor: Kimberly Tripp, Joe Sack

Part I: Statistics and Data Distribution (Tripp)

• Query execution
  • Standardization/normalization/algebrization creates a parse tree; this includes things such as expanding select * and views
  • Heuristics-based optimization (basically a guess) vs cost-based optimization
  • See Kimberly’s SP talk at this conference for limitations on SP performance
• Query rewrites sometimes help
  • Joins written as subqueries (and vice versa)
  • Derived tables
  • Intermediate materialization
  • Check SARGability of prefixes
• Selectivity
  • Tipping point is a very small percentage to drive SQL to use NCI with bookmark
  • This is why lots of narrow NCIs aren’t very helpful
  • Tipping point calculations – number of pages in CI * (between 1/4 and 1/3) = number of rows to do bookmark lookups
• What to look for in statistics
  • Header: Updated date, rows vs rows sampled
  • Density vector
  • Histogram
• “Bitmasks are evil”
• When stats are build, SQL starts to “compress” the histogram, meaning there may be fewer than 200 steps. This can result in a bit of skew in the stats where “earlier” row get compressed out earlier and more often, resulting more “lossiness” for those rows
• Leave auto-create stats on (except for very limited high-throughput environments that require very consistent timing)
• DBCC SHOW_STATISTICS (tablename, statname)
  • STAT_HEADER
  • DENSITY_VECTOR
    • Output STAT_HEADER and DENSITY_VECTOR to temp table to join
  • HISTOGRAM
  • STAT_HEADER JOIN DENSITY_VECTOR (undocumented)
• sp_autostats tablename
  • Not as useful except to quickly see if auto update disabled and last updated time
• stats
  • See Kimberly’s query to get an at-a-glance view of all stats on a DB
• STATS_DATE() function
• dm_db_stats_properties
  • Rows vs rows sampled
  • modification_counter
• Stats types
  • Auto-created
  • User-created
  • Hypothetical (what-if analysis (autopilot))
    • Ignored by query optimizer
    • DBCC AUTOPILOT (blog)
  • sp_update_stats (don’t use; it’s a sledge hammer)
    • Only one row modified triggers update
  • sp_create_stats (OK to use)
    • sp_create_stats ‘indexonly’, ‘fullscan’
      • (whether to user fullscan depends on the table size)
    • Updating
      • Leave autoupdate on, but manage yourself off-hours
      • Often based on sysrscols.rcmodified vs. entire table (3 to 8 percent)
    • Catch-all update statements, i.e.: SET col = ISNULL(@newvalue, col)
      • Causes counters to increase even if no actual change
      • Can greatly skew autoupdate thresholds
    • Auto updating
      • Can be done async (not enabled by default)

• Good for OLTP environments generally
• TF 2371 uses dynamic auto update threshold
• Leave on as a safety net

• Best to roll-your-own updating (possibly using Ola Hallengren scripts as a basis)
• Incremental stats (2014)
  • Partitioned tables, if 20% of records change stats recomputed for that partition and then rolled into overall table stats
  • Very lossy
  • Despite some information out there to the contrary, this is not partition level stats
• If for some reason auto_update_stats is off (on 2008R2 and earlier), updating stats does not invalidate plans
  • Recommend running sp_recompile to rebuild scripts
• Watch for cases where stats are bad and stats have been sampled; use full scan instead
  • Test out, then use full scan in scripts off hours
  • Additional benefit is that sampled scans are serial but full scan can be parallelized (subject to maxdop)
• Filtered stats
  • Session settings not required
  • Create stats over a range of values
  • Add WHERE clause to CREATE STATISTICS statement
  • Beware of using sampling (but weigh against full scan costs)
• Analyzing data skew
  • See sp_SQLSkills_AnalyzeColumnSkew and sp_SQLSkills_AnalyzeAllLeadingIndexColumnSkew
  • sp_SQLSkills_CreateFilteredStats
    • Splits histogram into 10 to 20 chunks
    • Drop and recreate every week or so
  • Filtered stats only autoupdate on the same schedule as the entire table – not nearly often enough
• No good way to track stats usage to see how often a stat is used or not used
• TF 9481 – Use legacy CE
• TF 3604 – Output to client
• TF 9204 – Which stat is used
• New CE does not use
  • Filtered stats
  • Multi-column column stats
  • Autopilot
• Limitations to filtered stats
  • Need to recompile or use plan guide to take advantage (OPTION(RECOMPILE))
  • Better to use partitioned views
  • Subject to interval subsumption problem when query predicate spans filter interval
  • But these are really designed for point queries
  • Same problem using IN clause where values span ranges. Workaround by using UNION ALL (even using dynamic SQL)
• See the whitepaper on When to Break Down Complex Queries
• Unevenly distributed data through the table
  • Can really only solve through indexing

Part IIA: Cardinality Estimation (Sack)

• Problem with underestimating
  • Memory grants too small, leads to spills
• Problem with overestimating
  • Memory grants too large, causes concurrency problems on other threads having to wait
  • Check sys.dm_exec_query_memory_grants for granted_memory_kb = null
• General problems
  • Serial vs. parallelism
  • Seek vs. scan
  • Bad table join order
  • Bad join algorithm
• Ways the CE comes up with estimates
  • Direct hit: Specific value in stats, uses EQ_ROWS
  • Intrastep hit: Uses AVG_RANGE_ROWS
  • Unknown runtime value: Uses density vector (all_density * row_count)
  • Distinct values: uses 1.0 / all_density
• XEvent inaccurate_cardiniality_estimate
  • See Nacho Portillo blog post
  • Very resource intensive; not good for use in production
• dm_exec_query_stats has actual row counts for last time executed
  • Can hide leaf-level skews
• Warnings about troubleshooting CE issues
  • Be sure to check number of executions (need to multiply by number of rows)
  • Some cardinality issues cannot be readily resolved (example: Entity-Value table that contains many data types) and require fundamental redesign
• Common CE-related problems
  • Missing or stale statistics
    • Verify auto_stats on
    • Check if stats have no_compute
    • Check stats date; manually update if needed
    • TF 2371 – decreasing dynamic threshold for auto updates
  • Sampling issues
    • Compare rows vs rows scanned
    • Will full scan work? Don’t know until you try it but can work in about 20% of cases
  • Hidden column correlation
    • Create multi-column column stats or indexes
    • Optimizer will only create single-column stats automatically
    • If value goes off stats histogram, reverts to density vector which can be worse
  • Intra-table column comparisons
    • e., where col1 < col2
    • Optimizer does not know how to correlate
    • Create computed column (i.e., col1 – col2) and create stats on it
      • Only works for pretty simple comparisons
    • Use self-joins, CTEs, or normalize data
  • Table variables
    • Generally not an issue for very small number of rows (less than about 100 rows)
    • Temp tables are better
  • Scalar or multi-statement table value functions
    • Convert to inline
    • Eliminate functions
    • Ask if the function is appropriate being in the data tier?
  • Parameter sniffing
    • SQL Server optimizing Stored Procedure (class on Pluralsight)
    • Check for ParameterCompiledValue vs ParameterRuntimeValue from actual plan
    • Standard parameter sniffing fixes
  • Implicit data type conversion
    • See Keyhias blog post on what causes conversions
    • Use correct datatypes
    • Use consistent naming conventions
    • Use catalog views to monitor for inconsistencies
  • Complex predicates
    • e., where left(firstname, 15) + ‘ ‘ + left(lastname, 15) = ‘xxxx’
    • Simplify when possible
  • Query complexity
    • Many objects references (implicitly or explicitly)
    • Break into smaller materialized sets
      • IO penalty, but can greatly make it worth it overall
    • Gatekeeper rows (DW scenarios)
      • See this blog post
      • Covering nonclustered index using FORCESEEK (usually a bad thing, but works here)
      • Dynamic SQL, columnstore index (still get skew, but speed of columnstore overcomes problems)
      • No really good resolution as of 2014
    • Linked servers
      • Prior to SQL 2012 SP1 cannot get permissions to get stats on remote server (with nonadmin accounts)
      • Can grant ddladmin role
      • Consider ETL or replication
    • Optimizer bugs
      • Very rare
      • Check Connect (either add to existing or create new with detailed reproduction)
    • See Joe’s list of troubleshooting questions

Part IIB: Cardinality Estimator Changes (Sack)

• Many trace flags (disabled by default)
• 0 to 2012 used essentially the same estimator
• New CE in 2014
  • Workloads may or may not benefit from it
  • Context DB compat level must be 120+
  • See this article about using trace flags
  • TF 2312 – Use new CE
  • TF 9481 – Use legacy CE
  • Root node of plan has CardinalityEstimationModelVersion
    • 120 = new; 70 = old
  • To override per query: OPTION (QUERYTRACEON 9481)
    • But this requires ‘sa’ rights
    • Use plan guides to work around
  • New behavior
    • Multi-predicate correlation
      • Old: p0 * p1 * p2 * p3
      • New: p0 * p1 ^ (1/2) * p2 ^ (1/4) * p3 ^ (1/8)
      • Exponential backoff, ordered by selectivity
      • Does not use multi-column stats
      • Only to the first 4 predicates (?)
    • Out-of-range value estimation
      • Old: Ascending key problem underestimates recently-added rows; estimates 1 row above top step in histogram
        • TF 2389, 2390 enable auto generation of stats on ascending keys
      • New: Assumes existence of out-of-range rows; estimate based on density vector
    • Join estimates / containment
      • Old: Correlation assumed if non-join filter specified
      • New: No correlation assumed
    • Join estimation
      • New makes assumptions that are best associated with uniformity of data
      • Data skews may result in estimation worse than legacy CE
    • Distinct value count estimation (ambient cardinality)
      • Geared at ORM-generated code that includes extraneous joins or query elements
      • Tends to decrease estimates
    • TF 2363 – shows internal optimizer steps
    • XEvent query_optimizer_estimate_cardinality – indicates what stats are used
      • Usually for edge cases / MS Support
    • Options for regressions
      • Use compat level < 120, TF 2312 for selected queries
      • Use compat level 120, TF 9481 for queries with regressions
      • Use compat level 120, server trace flag 9481 globally
    • Be sure to test! If cannot test, use legacy

I attended the SQL intersection conference in November 2014 in Las Vegas, Nevada.  I am including my notes from the sessions in this blog, primarily for my own reference later.  For anyone that comes across these notes, please take them with a grain of salt – I may very easily have misheard something or have written it down incorrectly.

Session Title:  SQLintersection Closing Panel and RunAs Radio Recording

SQL Server Compliance Guide (for 2008 security, mostly still applies)

• • 2014 improvements include separation of duties, etc.
• Multi-master replication guide
• Google “call me maybe” + another platform (especially NoSQL) (blog about trying to break systems)
• Filestream whitepaper; also blog on filestream
• Worst SQL Server advice?
  • If you see CXPACKET waits, set maxdop = 1
  • PLE 300 is bad
  • Put a non-clustered idx on every column

I attended the SQL intersection conference in November 2014 in Las Vegas, Nevada.  I am including my notes from the sessions in this blog, primarily for my own reference later.  For anyone that comes across these notes, please take them with a grain of salt – I may very easily have misheard something or have written it down incorrectly.

Session Title:  Troubleshooting SQL Server Memory, Part 2
Instructor: Bob Ward

• DMVs if something is going wrong
  • dm_os_memory_nodes
    • Non-NUMA = two nodes (0 and 64 [DAC])
    • NUMA = (0, 1, 2, …)
  • dm_os_memory_clerks
  • dm_os_memory_objects
• Permon
  • SQLServer:BufferNode – per NUMA node
    • DB pages, PLE per node
  • DBCC MEMORYSTATUS
    • Away committed, Taken Away Committed – only available on this command, other columns found elsewhere
  • On 64-bit systems, ignore VirtualMemoryReserved
  • Memory clerks
    • SOSNODE – startup fixed
    • SQLSTOENG
    • SQLGENL – startup fixed
    • SQLCP – ad-hoc queries
    • SQLLOGPOOL
  • Memory standard report – add AllocatedMemory, VMCommitted, AWEAllocated to get actual memory
  • NUMA
    • Local memory favored; execution favored over “out of memory”
      • In conventional memory model set thread affninity
      • In locked memory model uses AllocateUserPhysicalPagesNuma()
      • Does not guarantee local node access
    • Taken Away – when physical node is not the SQL node
      • Not actually used at the time allocated, but reserved for local use
      • Retries a certain number of times to try to get local memory
    • Foreign Memory – when Windows doesn’t ultimately give local memory
    • Only shown in DBCC MEMORYSTATUS
  • Memory leaks
    • Out of memory error (message 701 in error log)
    • Not a Windows out-of-memory condition
    • Causes (can be SQL or can be user)
      • KB 2803065 (SQL)
      • Not closing cursors (user) (CACHESTORE_SQLCP)
      • xml_prepare_document without unprepared (user)
      • Other user-caused errors (usually heap)
        • External stored procedures
        • Unsafe CLR assemply with native calls
        • Other DLLs
      • Error log dumps
        • Look for system physical memory low and process physical memory low
        • Error 701
      • Plan cache eviction
        • Memory pressure – external clock hand
        • Cache cap limit – internal clock hand
        • Clock hand rolls through cache and decrements a cost-based counter; when zero, plan gets evicted
        • DMV to monitor external vs internal
        • DBCC FREEPROCCACHE
        • Some sp_configure changes cause eviction
      • Hard limit for all cache/user stores
        • TF 8032 uses pre-2005 SP2 behavior
        • Each cache gets a soft limit of the hard limit
        • MEMORY_CLERK_SQLLOGPOOL can be a factor; fixes to this problem available
      • RMLUtilities Suite
        • Includes ostress
        • Recommended download
      • Aggressive memory grants (workspace reservation)
        • Sorts, hash joins, index builds
        • Reserved memory for query operators
        • Typically capped at 25% of target per query
        • If grant vastly exceeds actual needs other parts of SQL become memory starved
        • Particularly bad for
          • Large data types
          • XML queries
          • Batched sorts
          • Linked servers
        • Can use Resource Governor to decrease that 25% number, but then beware of spills
      • Blocking problems
        • Large number of queries requesting grants
        • Concurrent query compilations
        • Thread-safe scalability
      • Buffer Pool Extensions (BPE)
        • Intended for SSDs, but this is not validated
      • In-Memory OLTP
        • Not evicted, cannot respond to pressure
        • Still has to honor target
      • Many SQL Server memory architecture decisions in old MS blog posts
      • See Bob’s 2013 PASS session for detailed info about SQL memory

I attended the SQL intersection conference in November 2014 in Las Vegas, Nevada.  I am including my notes from the sessions in this blog, primarily for my own reference later.  For anyone that comes across these notes, please take them with a grain of salt – I may very easily have misheard something or have written it down incorrectly.

Session Title:  Troubleshooting SQL Server Memory, Part 1
Instructor: Bob Ward

Primary memory consumers in SQL Server

• • Buffer pool (BPool) – number 1 consumer
  • Plan cache – traditionally number 2 consumer
  • Other (log pool, token perm)
  • Hosted memory (CLR)
  • Fixed memory (fundamental operations of SQL Server, such as connections)
  • In-Memory OLTP
  • Columnstore
• As of 2012, max server memory = ALL server memory, not just the BPool
• Locked pages – must be enabled on the service account at the OS level group policy
• max server memory: 0 = min( 95% of physical RAM, 50% available RAM )
• min server memory
• Not coordinated between instances
• Paging – only the BPool is pagable to disk
• Windows paging
  • Working set trim operations
• Three memory models
  • Conventional
  • Locked pages
  • Large pages
• Locked pages memory model
  • Locked pages in memory privilege
  • Only Standard and Enterprise Editions
  • Memory is no longer part of the process’ working set
  • Recommended to use this feature especially if it is a dedicated box, including VM
  • Also take a hard look at max server memory
  • SQL will trim its own memory as requested by the OS down to min server memory
  • Recommend don’t set min unless running multiple instances
  • Private bytes include locked memory
• DMVs
  • dm_os_sys_info
  • dm_os_sys_memory (Win counters)
  • dm_os_process_memory (very valuable for seeing what SQL thinks it has)
    • physical_memory_in_use_kb
  • Useful perfmon counters
    • Target server memory (KB)
    • Total server memory (KB)
  • Private bytes include process-based memory (stack space, etc) so will be somewhat higher than total server memory
    • If it does not stay at about a fixed amount over total server memory, there is probably a memory leak (most likely in a DLL)
  • Memory consumption standard report in SSMS
  • Buffer pool disfavoring – pages from large scans get discarded earlier
  • dm_os_buffer_descriptors – advanced diagnostics
    • group by to see the number of hashed buffer pages
  • Plan cache troubleshooting
    • Formula to determine cache cap size
    • Watch perfmon compilations/sec – may indicate a high rate of eviction
    • dm_exec_cached_plans
    • Bloat can be caused by non-parameterized plans
    • TF 8032 – increase size of all caches (uses pre-2012 formula) – use with caution
  • Known issues with memory
    • NUMA with large memory (256+ GB), see KB article
    • Large memory model (TF 834)
    • Standard Edition size limits only apply BPool
    • Known memory leak bugs
    • Read recommended articles at end of resources

I attended the SQL intersection conference in November 2014 in Las Vegas, Nevada.  I am including my notes from the sessions in this blog, primarily for my own reference later.  For anyone that comes across these notes, please take them with a grain of salt – I may very easily have misheard something or have written it down incorrectly.

This is one of two development session I attended.

Session Title:  Conceptualizing, Navigating and Reasoning about Roslyn Trees
Instructor: Kathleen Dollard

• roslyn.codeplex.com
• Ways Roslyn will change your life
  • Enhance compilers (allows new language features, open source release)
• Source code
  • Parser -> Syntax Tree –> Symbol loader -> symbols -> binder -> semantic tree -> IL emitter
• Analyzing code
  • Diagnostics (SyntaxKind)
  • Code fix
  • Code action
  • Code refactoring
• Demo for adding rule to C# to require braces around if statements
• Kathleen’s open source wrapper around Roslyn

I attended the SQL intersection conference in November 2014 in Las Vegas, Nevada.  I am including my notes from the sessions in this blog, primarily for my own reference later.  For anyone that comes across these notes, please take them with a grain of salt – I may very easily have misheard something or have written it down incorrectly.

Session Title:  Top Customer Problems: How to Avoid Calling Bob
Instructor: Bob Ward

• Biggest issues (in order)
  • Performance
    • Is it a “running” problem (high CPU) vs a “waiting” problem (IO bottleneck, blocking, latch waits, other waits)?
      • Blocking – usually a problem within the app
      • IO bottleneck (PAGEIOLATCH or WRITELOG)
      • Latch waits, hotspots (PAGELATCH_XX)
      • RESOURCE_SEMAPHORE waits
      • ASYNC_NETWORK_IO can be a network problem, but usually a problem with client result processing
      • Other waits, check DMV
    • If the problem is current: Performance Dashboard Reports and DMVs
      • Performance Dashboard Reports
        • DMVs only
        • Create SP in master
        • 2005 version (use for 2005, 2008, 2008R2)
        • 2012 version (user for 2012, 2014)
      • Check for other process using lots of resources
        • High kernel time (i.e., anti-virus)
      • Within SQL Server
        • Background/system tasks
        • High recompiles
      • Query
        • Usually about cardinality estimation
          • Many root causes, see table
          • Tables variables (estimate = 1)
            • TF 2453 (2012 SP2+)
          • Data type mismatch (cannot make estimate, change data types)
        • Poor/missing indexes
        • Query design (lots of scans)
        • Lots of parallelism (check DOP)
      • Post-mortem problems: PSSDiag, SQLNexus
    • Backup/Restore
      • Backup
        • OS error 64 – Network problems (backup started, but connection dropped)
        • OS error 1450 – OS memory problems (backing up to a remote drive)
        • Transaction log growth (full recovery with no t-log backups)
        • VDI backup failure (3rd party app problems)
      • Restore
        • Disk bottleneck
        • Long-running recovery
        • VLF fragmentation
        • CHECKSUM failures
          • Use WITH CONTINUE_AFTER_ERROR to override
        • Consider restoring to a different DB vs replace (if error while restoring, still have fallback)
      • Consistency Issues
        • Message 824 error / DBCC CHECKDB
        • Checksum errors
        • Suspect DB
        • No “magic tricks” to fix
        • Almost always IO path error (try SQLIOSIM)
        • Can also be memory corruption
          • Constant page sniffing – thread that randomly check memory pages
        • Could be a SQL bug (replay the log)
        • Session id of -3 = recovery (deferred)
        • Page-level recovery
      • Stackdumps/Exceptions
        • Verify that the faulting module is SQL
      • Obscure errors
        • Message 1204 – Deadlock
        • Message 833 – IO delay warning (15 sec IO delay)
        • Message 17883 – Non-yielding scheduler (almost always a SQL bug)
        • Message 17884, 17888 – usually a long blocking chain
        • Message 844, 845, 846, 847 – latch time out
        • Use error_reported XEvent to track down a specific error
      • Startup problems
        • Permissions problem (may have changed service account followed by a hard restart)
        • Config (i.e., max server memory)
        • Damaged files
          • Keep file copies of master (shut down SQL, copy file off) and binaries
        • Backup tail of model causes restart problems
      • Memory issues
        • Out of memory
        • Memory grown (OK if below max server memory)
      • Hidden gems
        • Sytem health session persisted (2014)
        • NUMA guidance
        • System Center Advisor

tempdb bulk operation optimized (fix for 2012, built in to 2014)

I attended the SQL intersection conference in November 2014 in Las Vegas, Nevada.  I am including my notes from the sessions in this blog, primarily for my own reference later.  For anyone that comes across these notes, please take them with a grain of salt – I may very easily have misheard something or have written it down incorrectly.

Session Title:  In-Memory OLTP Overview
Instructor: Kevin Farlee

• Hekaton = In-Memory OLTP
• Columnstore = In-Memory DW
• CPU clock rates stalled out about 2005, but more power now coming in the form of multiple cores
• Memory costs continue to plummet
• Saying “It’s fast because it’s in memory” doesn’t really cover the Hekaton story
  • Can add enough memory to load file-based tables into buffer pool
  • Hekaton has many more speed optimizations
• Focus on data hotspots, and load that data into Hekaton. Doesn’t have to be the entire dataset or even a significant portion of the dataset
• Compiled stored procedures don’t have to context-switch (but still subject to quantum)
• Cannot now replicate from Hekaton to another system, but can replicate from a disk table to a Hekaton table
• Based on optimistic concurrency – assume that no one else will access simultaneously
• Validation errors cause rollback; have to treat like deadlock in code (i.e., retry)
  • SQL does retry in some very limited conditions
• Works with Always On, AG, backups, etc.
• Not recommended for work queues
• Can enable delayed durability on Hekaton tables
• If run out of memory, can still delete rows, but cannot update or insert
• Indexes are not persisted on disk; they are always built in memory
• SCHEMA_ONLY does not even write to transaction log (other than table creation)
• Case example of online gambling company that accessed web session state from SQL
  • Using Hekaton, went from 15,000 txn/sec to 250,000
• Uses more efficient logging to improve write throughput
• Need to set bucket count an order magnitude (or two) larger than expected row count
• Tables can be schema and data or schema only
• Can create natively compiled SPs
  • Calls out to C compiler to create
  • Entire SP is atomic
• Some transactions cannot run in read committed
  • Must use snapshot, repeatable read, or serializable
• Cannot comment on whether this will work its way into Azure, but wouldn’t be surprised
• Don’t necessarily go big bang – convert tables a few at a time until goals are achieved

I attended the SQL intersection conference in November 2014 in Las Vegas, Nevada.  I am including my notes from the sessions in this blog, primarily for my own reference later.  For anyone that comes across these notes, please take them with a grain of salt – I may very easily have misheard something or have written it down incorrectly.

Session Title:  Parallel Query Execution, Part 2
Instructor: Paul White

Batch mode parallel execution

• • Geared toward DW scenarios
  • Row mode model not as efficient on modern CPU architecture
    • Frequently has to access memory rather than L1, L2, L3 cache
  • Batch mode
    • Batches contain 64-900 rows
    • Code designed to fit on L1 cache
    • Data sized to fit on L2 cache
    • Geared toward 64-bit register use
    • Uses push model (whereas row mode is a pull model)
    • Avoids expensive exchanges with low-contention shared structures
    • Has high startup cost, so not good for OLTP workloads
    • Threads are dynamic and can move between operators
  • Batch is a 64KB structure designed to fit in L2 cache
    • Contains a column vector that fits in L1 cache
    • Allocated one time, reused many times
  • TF 9453 – Disable batch mode
  • Must keep columns small so that at least 64 rows will fit into 64KB structure
  • Batch mode not implemented on string aggregates
  • Columnstore
    • High compression, reduces IO
    • Stored as LOBs, optimizations for LOB
    • Created by partitioning into row groups of 1024^2 (about one million)
      • Partitioned into segment; reordered for compression
      • Dictionary per column per segment, plus global dictionary per column
    • SQL 2012 had many limitations, including being read-only
      • Also had very limited plan operators that were supported
      • Small changes to query could change to row mode and be much slower
      • Can often rewrite query to stay in batch mode, but can get ugly and requires a fair bit of internals knowledge – not well suited for end users
      • If operators spill to tempdb, query could start over in row mode
    • SQL 2014 has many improvements
      • Writeable, updateable, better compression, DDL allowed, more column types
      • More operators added, but still limited
        • No sort still
      • Spills stay in batch mode
      • Allows mixed mode (i.e., plan contains row mode, switch to batch mode, back to row mode) leaving more options for optimizer
      • Improved bitmaps
        • Any supported datatype
        • Both simple and Bloom filters
      • Bookmark support (this is not clustered index/RID lookups)
        • Unique key for columnstore indexes
      • Delta store for inserts; tuple mover to merge when full
        • Deleted rows stay in columnstore (marked as deleted) until REBUILD
        • Update = delete + insert
      • Still has limitations
        • No PK, FK, nonclustered indexes, unique constraints (can work around with materialized views), replication, CDC
        • Not readable on AG secondaries
        • (Interesting note – bug in error message when attempting to create FK – error message lists wrong table!)
      • TF 8607 – Output text of internal execution plan
      • TF 3604 – Output to client
      • TABLESAMPLE SYSTEM (15 PERCENT) – sample 15% of a table

I attended the SQL intersection conference in November 2014 in Las Vegas, Nevada.  I am including my notes from the sessions in this blog, primarily for my own reference later.  For anyone that comes across these notes, please take them with a grain of salt – I may very easily have misheard something or have written it down incorrectly.

Session Title:  Parallel Query Execution, Part 1
Instructor: Paul White

Within a parallel plan, each thread is effectively running a serial plan

• Parallel seeks and scans
  • 2005 and earlier use parallel page provider
  • 2008+ use range enumerators
    • May see ACCESS_METHODS_DATASET_PARENT latch waits
  • Only forward scans and seeks are supported
    • Backward scans can only happen serially
  • Exchange types
    • Hash – Distributed based on hash function
    • Round Robin – Sent to next available consumer
    • Broadcast – Pushed to all consumers
    • Demand – Row at a time (rare)
    • Range – Mostly index build plans
  • Exchanges can be sort order preserving or not
  • Root node has property called ThreadStats
    • Will show number of threads reserved and number of threads used
    • May not use all reserved threads depending on timing of thread allocated in the query (can reuse threads that are already complete)
  • exec_context_id is the thread number
    • 0 = serial
  • Query to identify node id where CXPACKET wait may be happening
  • Parallel merge join requires order preservation
    • Does not scale well to high DOP
  • Parallel hash join
    • Generally scales well to high DOP
    • Bitmap reduction – can only appear in parallel plans, and can greatly reduce work to be done (i.e., early reduction)
  • Parallel nested loops join
    • Inner side runs serially
    • Optimizer is “biased” against them
    • TF 8649 – Force parallel plan
    • Various tricks or plan guides to force usage
      • Such as specifying row goals
      • Express queries as APPLY – As query is pushed toward parallel nested loop queries, can push toward smaller memory grants and more efficient use of resources
    • Parallel sorts
      • Observer memory fraction percentage
      • Tricks to avoid spills
        • Cast strings to larger strings (i.e., select cast(str as varchar(100))) to force larger memory grant
        • Split sort into smaller sorts (initial select into temp table, then cross apply to temp table)
      • Avoid too much parallelism – set the cost threshold for parallelism
      • Parallel deadlocks
        • Once deadlock is detected, contents of CXPACKET buffers spill to tempdb
        • Interesting crafted demo that causes spills only at even DOPs (but not with odd)!