Skip to content

Erik Darling Data

SQL Server Consulting, Education, and Training

Erik Darling Data
  • Blog
  • Learn
    • Pre-Recorded Classes
    • In-Person Events
  • Scripts
    • sp_HumanEvents
    • sp_PressureDetector
    • sp_QuickieStore
  • Contact
  • Request a Call

Category: Spills

Using Views To Reduce Memory Grants In SQL Server

Posted on May 11, 2022May 16, 2022 by Erik Darling

We All Have It


You know those tables, right? The ones where developers went and got lazy or didn’t know any better and decided every string column was going to be gigantic.

They may have read, of course, that SQL …

Posted in Execution Plans, Indexing, Memory Grants, Query Tuning, Server Tuning, Spills, SQL ServerTagged create index in sql server, create temp table sql, DBA, Deadlock, Erik Darling, Erik Darling Data, how to create temp table in sql, Index Tuning, Indexing, Parallelism, Performance, performance tuning, Query, Query Plan, Query Tuning, sp_executesql, SQL, sql indexes, SQL Server, SQL Server Consultant, SQL Server Index, SQL Server Training, sql temp table, temporary table sql

When Query Spills Cause tempdb Contention And Performance Issues In SQL Server

Posted on October 18, 2021May 16, 2022 by Erik Darling

Temptables


I was helping a client troubleshoot a scalability issue recently, where during one point in their workload tempdb went out of control.

What was puzzling them was that nothing in the stored procedure did anything with temporary objects, cursors, …

Posted in Extended Events, Memory Grants, Spills, SQL Server, Table Variables, tempdb, Temporary TablesTagged create index in sql server, create temp table sql, DBA, Deadlock, Erik Darling, Erik Darling Data, how to create temp table in sql, Index Tuning, Indexing, Parallelism, Performance, performance tuning, Query, Query Plan, Query Tuning, sp_executesql, SQL, sql indexes, SQL Server, SQL Server Consultant, SQL Server Index, SQL Server Training, sql temp table, temporary table sql1 Comment on When Query Spills Cause tempdb Contention And Performance Issues In SQL Server

Wait Stats During Exchange Spills In SQL Server Parallel Query Plans

Posted on September 10, 2021May 16, 2022 by Erik Darling

Inorganic


Lots of people say these are rare — and perhaps they are becoming more so — but I still see them with some frequency. That’s not a terribly strong indictment of SQL Server, but that’s not the point of …

Posted in Parallelism, Spills, SQL Server, Wait StatsTagged create index in sql server, create temp table sql, DBA, Deadlock, Erik Darling, Erik Darling Data, how to create temp table in sql, Index Tuning, Indexing, Parallelism, Performance, performance tuning, Query, Query Plan, Query Tuning, sp_executesql, SQL, sql indexes, SQL Server, SQL Server Consultant, SQL Server Index, SQL Server Training, sql temp table, temporary table sql3 Comments on Wait Stats During Exchange Spills In SQL Server Parallel Query Plans

Wait Stats During Hash Spills In SQL Server Query Plans

Posted on September 9, 2021May 16, 2022 by Erik Darling

Jam Job


Hash spills are, as we’ll see, sometimes identified by a different wait than sort spills. In small quantities, spills are often not worth bothering with. But when they pile up, they can really cause some severe performance issues.…

Posted in Spills, SQL Server, Wait StatsTagged create index in sql server, create temp table sql, DBA, Deadlock, Erik Darling, Erik Darling Data, how to create temp table in sql, Index Tuning, Indexing, Parallelism, Performance, performance tuning, Query, Query Plan, Query Tuning, sp_executesql, SQL, sql indexes, SQL Server, SQL Server Consultant, SQL Server Index, SQL Server Training, sql temp table, temporary table sql1 Comment on Wait Stats During Hash Spills In SQL Server Query Plans

Wait Stats During Sort Spills In SQL Server Query Plans

Posted on September 8, 2021May 16, 2022 by Erik Darling

Over Under


Over the next few days, I’m going to look at the wait stats that show up during different types of spills of different sizes. For no reason whatsoever, I’m going to start with sort spills. I’ll also cover …

Posted in Spills, SQL Server, Wait StatsTagged create index in sql server, create temp table sql, DBA, Deadlock, Erik Darling, Erik Darling Data, how to create temp table in sql, Index Tuning, Indexing, Parallelism, Performance, performance tuning, Query, Query Plan, Query Tuning, sp_executesql, SQL, sql indexes, SQL Server, SQL Server Consultant, SQL Server Index, SQL Server Training, sql temp table, temporary table sql1 Comment on Wait Stats During Sort Spills In SQL Server Query Plans

Signs Your SQL Server Execution Plan Is Lying To You: Spills and Memory Grants

Posted on February 13, 2020May 16, 2022 by Erik Darling

Milky


Going Further


If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for …

Posted in Memory Grants, Plan Cache, Plan Cache Liars, Spills

Signs Your SQL Server Execution Plan Is Lying To You: Exchange Spills

Posted on February 6, 2020May 16, 2022 by Erik Darling

Since They Wanna Know


Going Further


If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m …

Posted in Parallelism, Plan Cache, Plan Cache Liars, Spills

Why Spills In SQL Server Parallel Plans Can Hurt Performance

Posted on November 1, 2019May 16, 2022 by Erik Darling

Go Along, Get Along


This is a somewhat complicated topic. There’s not a great TL;DR here, but I don’t want anyone to walk away from this post thinking that parallelism or indexes are “bad”.

What I do want to show …

Posted in Execution Plans, Parallelism, Spills, SQL Server2 Comments on Why Spills In SQL Server Parallel Plans Can Hurt Performance

Spills Week: How Exchange Spills In Parallel Query Plans Crush Query Performance

Posted on August 2, 2019May 16, 2022 by Erik Darling

Many Have Entered, Few Have Left


For some background on Exchange Spills, check out this Great Post™ by Joe.

The root of this demo was trying to show people silly things about CTEs, how TOP can fence things off…

Posted in Execution Plans, Parallelism, Spills, SQL Server1 Comment on Spills Week: How Exchange Spills In Parallel Query Plans Crush Query Performance

Spills Week: How Hash Join Spills Hurt SQL Server Query Performance

Posted on August 1, 2019May 16, 2022 by Erik Darling

Thirsty Thursday


If you’ve made it this far, you’ve learned a few things:

  • Not all spills are worth trying to fix
  • The more columns you select, the worse spills get
  • The larger your string datatypes are, the worse spills get
…
Posted in Execution Plans, Spills, SQL Saturday

Posts navigation

Older posts

Recent Posts

  • SQL Server IF Branches And Query Performance Part 5: Does Dynamic SQL Work?
  • SQL Server IF Branches And Query Performance Part 4: Do Stored Procedures Work?
  • SQL Server IF Branches And Query Performance Part 3: Local Variables Still Suck

Subscribe

Categories

  • Accelerated Database Recovery
  • Angry Aggregates
  • Batch Mode
  • Bugs
  • Cardinality
  • Change Data Capture
  • Change Tracking
  • CHECKDB
  • Cider
  • columnstore
  • Common Query Plan Patterns
  • Common Table Expression
  • Computed Columns
  • Consulting
  • Cost Threshold For Parallelism
  • Cursors
  • Deadlock
  • Dynamic SQL
  • Error Handling
  • Execution Plans
  • Extended Events
  • Filtered Indexes
  • Filtered Statistics
  • Forced Parameterization
  • FROID
  • Functions
  • Hardware
  • IF Branching
  • Implicit Transactions
  • Indexed Views
  • Indexing
  • Interviewing
  • Isolation Levels
  • Joe Obbish
  • Local Variables
  • Locking
  • Managed Instance
  • MAXDOP
  • Memory Grants
  • Office Hours
  • OPTIMIZE_FOR_SEQUENTIAL_KEY
  • OtherDB
  • Parallelism
  • Parameter Sniffing
  • Parameterization
  • Partitioning
  • perfview
  • Plan Cache
  • Plan Cache Liars
  • Plan Guides
  • Postgres
  • Query Store
  • Query Tuning
  • Row Goal
  • SARGability
  • Server Tuning
  • Software Vendor Mistakes
  • sp_HumanEvents
  • sp_PressureDetector
  • sp_QuickieStore
  • sp_WhoIsActive
  • Spills
  • Spools
  • SQL Saturday
  • SQL Server
  • SQL Server 2019
  • SQL Server 2022
  • SQLBits
  • SSMS
  • Stack Column Store
  • Starting SQL
  • Statistics
  • Streaming
  • T-SQL
  • Table Variables
  • Tasting Menu
  • tempdb
  • Temporary Tables
  • TOP
  • Trace Flag
  • Training
  • Triggers
  • Variables
  • vNext
  • Wait Stats
Copyright © 2019-2023 Erik Darling Data