Developer Insights — Dave Ballantyne
Dave's (mainly) SQL Archive
Before AI-assisted development, the work was the engine room: SQL Server internals, query optimization, execution-plan behaviour and the small, unglamorous T-SQL details that decide whether a system behaves under load. This is that body of writing — resurrected from blogs that no longer exist, reproduced here as proper pages and stamped with each post's original publication date.
134 posts · 2009–2024 · SQL Server internals, T-SQL, execution plans, SSDT, Hekaton.
2009
- 11 June 2009
- 227 August 2009Another TSQL random length random string generatorT-SQL technique to generate random strings of variable length more efficiently than naive approaches.
- 327 August 2009Catch all queries and indexingHow catch-all / parameter-sniffing queries interact with indexing and plan choice.
- 427 August 2009Direct EMailing of SSRS Reports via SQLCLRSQLCLR approach to email SSRS reports directly from SQL Server.
- 527 August 2009Fixed Headers With SSRSSSRS technique to keep table headers visible when scrolling long reports.
- 627 August 2009Optimal Query Plans With Unbalanced Data LoadsCardinality skew and how uneven data distribution affects optimal plans.
- 727 August 2009SQL and contiguous data ranges.Identifying and working with contiguous date/number ranges in T-SQL.
- 88 September 2009BUG - Use of ranking functions result in an inefficient query planDocuments a plan-regression bug when ranking functions are used in certain patterns.
- 911 September 2009DateTime LookupsPerformance pitfalls when filtering on datetime columns and how to index/rewrite lookups.
- 1018 September 2009Returning a chain of events with a recursive CTERecursive CTE pattern to reconstruct ordered event chains from relational data.
- 1115 October 2009UDF Overhead – A simple exampleDemonstrates scalar UDF overhead with a minimal reproducible example.
- 1229 October 2009Another T-SQL ChallengeCommunity T-SQL puzzle/challenge post with solution discussion.
- 1315 November 2009And the winner is….Announces/results for a prior T-SQL challenge (technical, not a user-group promo).
- 1424 November 2009The Observer Effect In ActionHow measuring/querying a system (e.g. plan cache inspection) changes the behavior being measured.
- 1530 November 2009My first useful powershell script – capturing performance countersPowerShell script to capture Windows/SQL performance counters for troubleshooting.
- 164 December 2009Phil Factor Challenge Number 3Entry/solution write-up for Phil Factor's Speed Phreak SQL challenge #3.
- 174 December 2009Phil Factor Speed Phreak SQL Challenge Number 3Follow-up on Phil Factor challenge #3 with performance-focused solution.
- 188 December 2009Age calculation with SQL ServerCorrect and efficient ways to calculate age from dates in T-SQL.
2010
- 1922 January 2010Microsoft – Follow best practices!Critique/example where Microsoft sample code violates its own best-practice guidance (part 1).
- 2025 January 2010Microsoft – Follow best practices – Part 2Second installment on Microsoft examples vs documented best practices.
- 2127 January 2010Cleaning up sys.dm_exec_cached_plansScripting/approach to clean stale entries from sys.dm_exec_cached_plans safely.
- 224 March 2010Output = MAXDOP 1Using MAXDOP 1 with INSERT/OUTPUT patterns and plan implications.
- 2326 March 2010Cross Apply AmbiguitySubtle semantics/ambiguity when using CROSS APPLY in certain query shapes.
- 2423 April 2010Increasing deadlocks with NoLockShows how NOLOCK can paradoxically increase deadlock scenarios.
- 2511 May 2010Microsoft Gives You Your Cache Back (snapshot only)Plan cache behavior change / memory grant of cached plans in a SQL Server version update.
- 2627 May 2010Challenge 19 – An Explanation of a QueryDeep walkthrough explaining a tricky query plan from challenge #19.
- 2715 June 2010SQL Server XML SchemasWorking with XML schema collections and typed XML in SQL Server.
- 2827 June 2010SQL 101 : Without “ORDER BY”, order is not guaranteed.Fundamental reminder that result order is undefined without ORDER BY.
- 292 July 2010Using a UDF as a SARG. Make a hash of it!Why wrapping columns in UDFs destroys SARGability and leads to scans.
- 3020 July 2010SQLBits 7Notes from SQLBits 7 conference (session takeaways, mixed event/technical).
- 3127 July 2010Count(*) or Count(1) ?Compares COUNT(*) vs COUNT(1) myths and actual optimizer behavior.
- 326 August 2010Inconsistent NullIF Behaviour ?Documents inconsistent NULLIF behavior edge cases across contexts.
- 3319 August 2010
- 346 September 2010An impossible casePuzzle case that looks impossible until you spot the optimizer/statistics angle.
- 3521 October 2010The best things in life are freeFree SQL Server tools/features worth using (tooling roundup).
- 3610 November 2010Denali Paging–Is it win/win ?Early look at paging improvements in SQL Server 'Denali' (2012) and trade-offs.
- 3719 November 2010Denali Paging–Key seek lookupsPaging in Denali and when key lookups still dominate cost.
2011
- 3824 February 2011“Apparently, you signed a software services agreement without fully understanding it.”Commentary on licensing/SSA implications for DBAs (non-technical governance).
- 3912 March 2011
- 4011 April 2011#SqlBits and SSIS custom componentsBuilding/using custom SSIS components, with SQLBits context.
- 4129 May 2011
- 425 June 2011
- 436 June 2011Column order can matterHow column order affects storage/compression and sometimes plan choice.
- 4413 June 2011Non use of persisted dataWhy persisted computed columns/indexes may not be used by the optimizer (part 1).
- 4517 June 2011
- 4620 June 2011Non use of persisted data – Part deuxPart 2 on persisted computed columns not being picked up in plans.
- 4719 July 2011Denali–IIF and CHOOSENew IIF/CHOOSE functions in Denali and practical usage notes.
- 481 August 2011Rolling your own index lookupsManual index seek/lookup patterns vs letting the optimizer do it.
- 4931 August 2011
- 5022 September 2011Rows or Range, What’s the difference ?WINDOW frame ROWS vs RANGE semantics with examples.
- 5117 October 2011Implicit Conversions warningNew plan warnings for implicit conversions in Denali+.
- 5219 October 2011More Denali Execution Plan Warning GoodiesAdditional execution plan warning icons/messages in newer versions.
- 536 November 2011
- 546 November 2011Execution plan warnings–All that glitters is not goldNot every plan warning indicates a real problem—interpretation guide.
- 5529 November 2011Execution plan warnings–The final chapterConcludes series on execution plan warnings and false positives.
- 562 December 2011MythBusting–“Table variables have no statistics”Shows table variables can have cardinality estimates/statistics in modern versions.
- 576 December 2011Extended Events - inaccurate_cardinality_estimateUsing Extended Events to catch bad cardinality estimates at runtime.
- 5831 December 2011Book review - SQL Server Secret Diary (Know the unknown secrets of SQL Server)Book review of a SQL Server internals/secrets title.
2012
- 596 January 2012[BUG] Inserts to tables with an index view can failBug/limitation when inserting into certain indexed views.
- 6018 February 2012
- 6113 March 2012Parsing T-SQL – The easy wayTechnique/tooling for parsing T-SQL batches more reliably.
- 6226 April 2012Offset without OFFSETSimulating OFFSET/FETCH paging before those keywords existed.
- 6310 May 2012“Query cost (relative to the batch)” <> Query cost relative to batchExplains mismatch between batch-relative vs total plan cost percentages.
- 6413 June 2012Indexed view deadlockingDeadlock scenarios involving indexed views and how to mitigate.
- 6526 July 2012Joining on NULLsNULL join semantics and surprising plan/logic outcomes.
- 669 August 2012How to prevent ‘Select *’ : The elegant wayPatterns to block ad-hoc SELECT access without heavy-handed permissions hacks.
- 6729 August 2012Reporting on common code smells : A POCProof-of-concept for detecting T-SQL code smells across a codebase.
- 689 September 2012
- 6928 October 2012
- 7026 November 2012When row estimation goes wrongCase study of bad row estimates causing poor plans and fixes.
- 716 December 2012My new favourite traceflagUseful trace flag for diagnostics (specific flag discussed in post).
- 726 December 2012
2013
- 734 January 2013When is a whitespace not a whitespace ?Unicode/whitespace edge cases affecting string comparisons/parsing.
- 747 January 2013SSDT – TSQL Unit testing in multiple environmentsRunning SSDT tSQLt tests across environments with variable substitution.
- 7511 January 2013
- 7620 January 2013Photo cataloguerSide project using SQL + tooling to catalog photos (non-DBA app post).
- 7712 February 2013Deploying SSRS artefacts using Powershell ( Simply)PowerShell deployment pipeline for SSRS objects.
- 7817 February 2013
- 7912 March 2013
- 8022 March 2013Sub query pass throughOptimizer/subquery unnesting and when predicates pass through.
- 8110 April 2013What Price A TimeOut ?Part 2 on cost of query timeouts—client vs server symptoms and diagnosis.
- 8214 April 2013
- 8311 May 2013What price a deadlock ?Analyzes real cost of deadlocks beyond just retry logic.
- 8417 May 2013When is getdate() deterministic ?Explores when GETDATE() is treated as deterministic/non-deterministic for indexing/computed columns.
- 8526 May 2013
- 8630 June 2013
- 873 July 2013First steps into Hekaton - XE Slow parameter passingEarly In-Memory OLTP testing; XE shows parameter passing overhead.
- 888 July 2013Examining Hekaton CodeDissects generated C code / internals for a Hekaton proc.
- 8912 July 2013Hekaton CTP1 – Row order limitationLimitations around row ordering in memory-optimized tables/procs.
- 9017 July 2013How full are your Hekaton Buckets ?Hash bucket sizing/utilization for memory-optimized indexes.
- 9124 July 2013Nearing Nirvana with HekatonProgress report tuning a Hekaton workload toward acceptable performance.
- 9230 September 2013A quickie post on debuggingShort debugging tips for In-Memory OLTP development.
- 9320 October 2013
2014
- 943 January 2014Reading a snapshot – Are there performance implications ?Performance characteristics when reading under snapshot isolation.
- 9528 January 2014TSQL Smells in SSDT – The monster livesSSDT add-in for static analysis of T-SQL 'smells' (TSQL Smells project).
- 961 March 2014SSDT – Fast deploySpeeding SSDT publish/deploy in large databases via workflow changes.
- 974 March 2014SSDT – Cut n Paste substituting CMDVARsTesting SSDT scripts with sqlcmd variables using cut-and-paste substitutions.
- 9813 March 2014Whats This Command Bar ?Finding internal Visual Studio command bar names for SSDT add-in development.
- 9929 March 2014T4 Support in SSDTUsing T4 templates inside SSDT projects.
- 10012 April 2014Reasons why your plans suck : No 56,536Case study of a bad plan and optimizer misunderstanding (error 56536 context).
- 10115 August 2014Please please tell me now…Using explicit constraints/hints/info to help optimizer—'tell me now' about data distribution.
- 1022 November 2014Non use of persisted data – Part ThreePart 3: persisted computed columns still ignored—real-world AdventureWorks repro.
2015
- 1038 March 2015SQLBits, Incremental Statistics and Demo FailIncremental statistics feature overview plus a failed live demo post-mortem.
- 10417 March 2015Increasing ColumnStore compression ratio for archival and “Cold storage”Tuning columnstore compression for cold/archival CSV replacement workload (~20TB).
- 10523 March 2015Getting out of the ground with TSQL Smells.Getting started developing/running the open-source TSQL Smells analyzer.
- 1064 October 2015
2016
- 1075 January 2016
- 10817 January 2016
- 10914 February 2016
- 11014 February 2016
- 11128 February 2016My favourite wait state– ASYNC_NETWORK_IOASYNC_NETWORK_IO waits mean the client is slow consuming results—not always 'SQL is slow'.
- 1123 March 2016My favourite wait state– ASYNC_NETWORK_IOASYNC_NETWORK_IO waits mean the client is slow consuming results—not always 'SQL is slow'.
- 11313 March 2016A first look at SQL Server 2016–String_splitEarly review of STRING_SPLIT in SQL Server 2016 vs old string-split kludges.
- 11413 March 2016A first look at SQL Server 2016–String_splitEarly review of STRING_SPLIT in SQL Server 2016 vs old string-split kludges.
- 11523 March 2016
- 11624 March 2016
- 11717 June 2016
- 11822 August 2016Who did what to my database and when…Finding who dropped/changed objects without full auditing—DDL triggers/default trace/etc.
- 11930 August 2016Who did what to my database and when…Finding who dropped/changed objects without full auditing—DDL triggers/default trace/etc.
2023
2024
- 12513 April 2024
- 12613 April 2024
- 12713 April 2024
- 12813 April 2024
- 12913 April 2024
- 13013 April 2024
- 13113 April 2024
Undated
- 132—
- 133—Repost Reasons Why Your Plans Suck No 56536 (snapshot only)
- 134—