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)!

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:  Deploying SQL Server with Microsoft Azure Virtual Machine
Instructor: Bob Ward

Models

• • Azure portal (pay-as-you-go)
  • Bring own VM (use own license)
  • Management Studio (pay-as-you-go)
  • Azure VM Templates (capture Azure VM) (pay-as-you-go)
• Gallery images
  • Optimized for OLTP or DW
• VM Sizing
  • A series
    • Don’t use basic tier except for testing
    • A2+ for Standard Edition
    • A3+ for Enterprise Edition
    • A4, A7-A9 optimized
  • D series (faster CPU, SSD)
    • D4, D13-14 optimized
  • Can be changed later
• Cloud services for things like Availability Groups, Availability Sets, load balancing
• See MS decision tree for virtual networks
• Must create TCP endpoint for MSSQL port (1433)
• Takes about 10 minutes to provision
• Can provision via SSMS (Tasks, Deploy to Azure VM)
• Be sure to connect via RDP right after provisioning
• Storage decisions
  • 500 IOPS/drive limit, but can span across drives
  • Use Windows Server 2012+ to create storage pools
    • One for data, one for log
    • Span across multiple disks
  • Be sure not to use system drive ever (including for system databases)
  • Do not enable Azure options for caching on storage drives (disabled by default)
  • D drive – do not use on A series
    • On D series, use D drive (SSD) for tempdb or BPE
  • Enabled instant file initialization
  • Enabled locked pages
  • Turn off geo-replication (doesn’t guarantee write ordering, can cause transactional inconsistencies)
  • AZCOPY – like robocopy for Azure
  • Apply fix for tempdb eager writes (do this both on-premise and in Azure) for SQL2012
  • Disable SSAS, SSIS, SSRS if not used
  • Check that SQL authentication is enabled
  • Windows firewall – open up 1433 or SqlServer
  • Consider ACLs on TCP endpoints
  • Optimized images
    • 12 TB storage pool for data
    • 2 TB storage pool for log
    • Startup –E
    • TF 1117, 1118
    • 8 tempdb files
    • New DB appears to be on c: drive, but is on a mount point
    • Instant file initialization
    • Locked pages enabled
  • Disk transfers/sec on perf monitor to measure IOs
  • Configuration of Availability Groups
    • 3 primary options
      • All in Azure, same datacenter
      • Secondaries in different datacenter
      • Primary on-premise, replica in Azure
    • Requires DC in Azure
    • Coming soon: Always On template (about 5 images)
    • Add Replica Wizard in SSMS (requires hotfix)
    • Don’t try to upload AG VMs into Azure – won’t work and will have many problems
  • No failover clustering options (yet)
  • Can back up on-premise to Azure storage (2012+)
  • Can store data and log files on Azure storage (2014+)
  • Performance considerations
    • Batch transactions when possible
    • Autogrow considerations for log
  • Do not use autoscale for Azure
  • Future
    • G Series coming soon (more cores, RAM, larger SSDs)
    • Premium storage
    • Azure file storage (shared file system)