Developer Insights — Dave Ballantyne
Older writings
Before Execution Integrity, the work was the engine room: SQL Server internals, query optimization, execution-plan behaviour and the small, unglamorous details that decide whether a system stays inspectable under load. This is the archive — the body of technical writing that the rest of Developer Insights is built on.
111 posts · 2009–2016 · SQL Server internals, T-SQL, execution plans, SSDT, Hekaton.
Many of these were originally published on sites that no longer exist (sqlblogcasts.com, dataidol.com); those links resolve to preserved snapshots in the Internet Archive Wayback Machine. The throughline is the same one that runs through CGEI today: systems fail when coherent local execution detaches from recoverable shared reality.
SQL Server internals, query optimization & execution plans
Originally published at sqlblogcasts.com (“SQL and the like”) · archived via the Wayback Machine
- 2009A Faster Tsql Random Length Random String GeneratorT-SQL technique to generate random strings of variable length more efficiently than naive approaches.
- 2009Catch All Queries And IndexingHow catch-all / parameter-sniffing queries interact with indexing and plan choice.
- 2009Direct Emailing Of Ssrs Reports Via SqlclrSQLCLR approach to email SSRS reports directly from SQL Server.
- 2009Fixed Headers With SsrsSSRS technique to keep table headers visible when scrolling long reports.
- 2009Optimal Query Plans With Unbalanced Data LoadsCardinality skew and how uneven data distribution affects optimal plans.
- 2009Sql And Contiguous Data RangesIdentifying and working with contiguous date/number ranges in T-SQL.
- 2009Bug Use Of Ranking Functions Result In An Inefficient Query PlanDocuments a plan-regression bug when ranking functions are used in certain patterns.
- 2009Datetime LookupsPerformance pitfalls when filtering on datetime columns and how to index/rewrite lookups.
- 2009Returning A Chain Of Events With A Recursive CteRecursive CTE pattern to reconstruct ordered event chains from relational data.
- 2009Udf Overhead A Simple ExampleDemonstrates scalar UDF overhead with a minimal reproducible example.
- 2009Another T Sql ChallengeCommunity T-SQL puzzle/challenge post with solution discussion.
- 2009And The Winner IsAnnounces/results for a prior T-SQL challenge (technical, not a user-group promo).
- 2009The Observer Effect In ActionHow measuring/querying a system (e.g. plan cache inspection) changes the behavior being measured.
- 2009My First Useful Powershell Script Capturing Performance CountersPowerShell script to capture Windows/SQL performance counters for troubleshooting.
- 2009Phil Factor Challenge Number 3Entry/solution write-up for Phil Factor's Speed Phreak SQL challenge #3.
- 2009Phil Factor Speed Phreak Sql Challenge Number 3Follow-up on Phil Factor challenge #3 with performance-focused solution.
- 2009Age Calculation With Sql ServerCorrect and efficient ways to calculate age from dates in T-SQL.
- 2010Microsoft Follow Best PracticesCritique/example where Microsoft sample code violates its own best-practice guidance (part 1).
- 2010Microsoft Follow Best Practices Part 2Second installment on Microsoft examples vs documented best practices.
- 2010Cleaning Up Sys Dm Exec Cached PlansScripting/approach to clean stale entries from sys.dm_exec_cached_plans safely.
- 2010Output Maxdop 1Using MAXDOP 1 with INSERT/OUTPUT patterns and plan implications.
- 2010Cross Apply AmbiguitySubtle semantics/ambiguity when using CROSS APPLY in certain query shapes.
- 2010Increasing Deadlocks With NolockShows how NOLOCK can paradoxically increase deadlock scenarios.
- 2010Microsoft Gives You Your Cache BackPlan cache behavior change / memory grant of cached plans in a SQL Server version update.
- 2010Challenge 19 An Explanation Of A QueryDeep walkthrough explaining a tricky query plan from challenge #19.
- 2010Sql Server Xml SchemasWorking with XML schema collections and typed XML in SQL Server.
- 2010Sql 101 Without Order By Order Is Not GuaranteedFundamental reminder that result order is undefined without ORDER BY.
- 2010Using A Udf As A Sarg Make A Hash Of ItWhy wrapping columns in UDFs destroys SARGability and leads to scans.
- 2010Sqlbits 7Notes from SQLBits 7 conference (session takeaways, mixed event/technical).
- 2010Count Or Count 1Compares COUNT(*) vs COUNT(1) myths and actual optimizer behavior.
- 2010Inconsistant Nullif BehaviourDocuments inconsistent NULLIF behavior edge cases across contexts.
- 2010An Impossible CasePuzzle case that looks impossible until you spot the optimizer/statistics angle.
- 2010The Best Things In Life Are FreeFree SQL Server tools/features worth using (tooling roundup).
- 2010Denali Paging Is It Win WinEarly look at paging improvements in SQL Server 'Denali' (2012) and trade-offs.
- 2010Denali Paging Key Seek LookupsPaging in Denali and when key lookups still dominate cost.
- 2011Apparently You Signed A Software Services Agreement Without Fully Understanding ItCommentary on licensing/SSA implications for DBAs (non-technical governance).
- 2011Sqlbits And Ssis Custom ComponentsBuilding/using custom SSIS components, with SQLBits context.
- 2011Column Order Can MatterHow column order affects storage/compression and sometimes plan choice.
- 2011Non Use Of Persisted DataWhy persisted computed columns/indexes may not be used by the optimizer (part 1).
- 2011Non Use Of Persisted Data Part DeuxPart 2 on persisted computed columns not being picked up in plans.
- 2011Denali Iif And ChooseNew IIF/CHOOSE functions in Denali and practical usage notes.
- 2011Rolling Your Own Index LookupsManual index seek/lookup patterns vs letting the optimizer do it.
- 2011Rows Or Range What S The DifferenceWINDOW frame ROWS vs RANGE semantics with examples.
- 2011Implicit Conversions WarningNew plan warnings for implicit conversions in Denali+.
- 2011More Denali Execution Plan Warning GoodiesAdditional execution plan warning icons/messages in newer versions.
- 2011Execution Plan Warnings All That Glitters Is Not GoldNot every plan warning indicates a real problem—interpretation guide.
- 2011Execution Plan Warnings The Final ChapterConcludes series on execution plan warnings and false positives.
- 2011Mythbusting Table Variables Have No StatisticsShows table variables can have cardinality estimates/statistics in modern versions.
- 2011Extended Events Inaccurate Cardinality EstimateUsing Extended Events to catch bad cardinality estimates at runtime.
- 2011Book Review Sql Server Secret Diary Know The Unknown Secrets Of Sql ServerBook review of a SQL Server internals/secrets title.
- 2012Bug Inserts To Index Views Can FailBug/limitation when inserting into certain indexed views.
- 2012Parsing T Sql The Easy WayTechnique/tooling for parsing T-SQL batches more reliably.
- 2012Offset Without OffsetSimulating OFFSET/FETCH paging before those keywords existed.
- 2012Query Cost Relative To The Batch Lt Gt Query Cost Relative To BatchExplains mismatch between batch-relative vs total plan cost percentages.
- 2012Indexed View DeadlockingDeadlock scenarios involving indexed views and how to mitigate.
- 2012Joining On NullsNULL join semantics and surprising plan/logic outcomes.
- 2012How To Prevent Select The Elegant WayPatterns to block ad-hoc SELECT access without heavy-handed permissions hacks.
- 2012Reporting On Common Code Smells A PocProof-of-concept for detecting T-SQL code smells across a codebase.
- 2012When Row Estimation Goes WrongCase study of bad row estimates causing poor plans and fixes.
- 2012My New Favourite TraceflagUseful trace flag for diagnostics (specific flag discussed in post).
- 2013When Is A Whitespace Not A WhitespaceUnicode/whitespace edge cases affecting string comparisons/parsing.
- 2013Ssdt Tsql Unit Testing In Multiple EnvironmentsRunning SSDT tSQLt tests across environments with variable substitution.
- 2013Photo CataloguerSide project using SQL + tooling to catalog photos (non-DBA app post).
- 2013Deploying Ssrs Artefacts Using Powershell SimplyPowerShell deployment pipeline for SSRS objects.
- 2013Sub Query Pass ThroughOptimizer/subquery unnesting and when predicates pass through.
Hekaton, SSDT, T-SQL Smells & performance deep-dives
Originally published at dataidol.com · archived via the Wayback Machine
- 2013What Price A Timeout 2Part 2 on cost of query timeouts—client vs server symptoms and diagnosis.
- 2013What Price A DeadlockAnalyzes real cost of deadlocks beyond just retry logic.
- 2013When Is Getdate DeterministicExplores when GETDATE() is treated as deterministic/non-deterministic for indexing/computed columns.
- 2013First Steps Into Hekaton Xe Slow Parameter PassingEarly In-Memory OLTP testing; XE shows parameter passing overhead.
- 2013Examining Hekaton CodeDissects generated C code / internals for a Hekaton proc.
- 2013Hekaton RoworderlimitationLimitations around row ordering in memory-optimized tables/procs.
- 2013How Full Are Your Hekaton BucketsHash bucket sizing/utilization for memory-optimized indexes.
- 2013Nearing Nirvana With HekatonProgress report tuning a Hekaton workload toward acceptable performance.
- 2013A Quickie Post On DebuggingShort debugging tips for In-Memory OLTP development.
- 2014Reading A Snapshot Are There Performance ImplicationsPerformance characteristics when reading under snapshot isolation.
- 2014Tsql Smells In Ssdt The Monster LivesSSDT add-in for static analysis of T-SQL 'smells' (TSQL Smells project).
- 2014Ssdt Fast DeploySpeeding SSDT publish/deploy in large databases via workflow changes.
- 2014Ssdt Cut N Paste Substituting CmdvarsTesting SSDT scripts with sqlcmd variables using cut-and-paste substitutions.
- 2014Whats This Command BarFinding internal Visual Studio command bar names for SSDT add-in development.
- 2014T4 Support In SsdtUsing T4 templates inside SSDT projects.
- 2014Reasons Why Your Plans Suck No 56536Case study of a bad plan and optimizer misunderstanding (error 56536 context).
- 2014Please Please Tell Me NowUsing explicit constraints/hints/info to help optimizer—'tell me now' about data distribution.
- 2014Non Use Of Persisted Data Part TroisPart 3: persisted computed columns still ignored—real-world AdventureWorks repro.
- 2015Sqlbits Incremental Statistics And Demo FailIncremental statistics feature overview plus a failed live demo post-mortem.
- 2015Increasing Columnstore Compression Ratio For Archival And Cold StorageTuning columnstore compression for cold/archival CSV replacement workload (~20TB).
- 2015Getting Out Of The Ground With Tsql SmellsGetting started developing/running the open-source TSQL Smells analyzer.
- 2016My Favourite Wait State Async_Network_IoASYNC_NETWORK_IO waits mean the client is slow consuming results—not always 'SQL is slow'.
- 2016A First Look At Sql Server 2016String_SplitEarly review of STRING_SPLIT in SQL Server 2016 vs old string-split kludges.
- 2016Who Did What To My Database And WhenFinding who dropped/changed objects without full auditing—DDL triggers/default trace/etc.
davebally.co.uk
Dave Ballantyne’s current personal site
- Tech Debt Vs Human Debt A Deep Dive For Technical ProfessionalsEssay contrasting technical debt with 'human debt' (team/culture/psychological safety costs).
- K I S SKeep-it-simple essay on avoiding over-engineering in tech/process.
- She Told Us SoNon-SQL essay on organizational/people themes (Duena Blomstrom-adjacent 'human debt' territory).
SQLServerCentral
Community columns at sqlservercentral.com
Where this leads
The same instinct — make execution legible, recoverable and honest about its own cost — is what GLASS vs SAND and structural opacity apply to AI-assisted development. The framework that operationalises it is Execution Integrity Infrastructure — applied research by People Not Tech.