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: Parallelism

Common SQL Server Consulting Advice: Setting MAXDOP And Cost Threshold For Parallelism

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

Dirty Work


There are three classes of administrator when it comes to parallelism settings

  • Never changed the defaults
  • Changed the defaults to something wacky
  • Changed the defaults to something safe

The beginning of this post is for the first couple …

Posted in Consulting, Cost Threshold For Parallelism, MAXDOP, Parallelism, 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 sql1 Comment on Common SQL Server Consulting Advice: Setting MAXDOP And Cost Threshold For Parallelism

Software Vendor Mistakes With SQL Server: Not Getting Parallel Inserts Into #Temp Tables

Posted on January 19, 2022May 16, 2022 by Erik Darling

Do That, But Faster


Serial zones in parallel plans can leave a lot of performance on the table. One of the best ways to see that is with insert queries that do a lot of work. A big contrast …

Posted in Execution Plans, Parallelism, Software Vendor Mistakes, SQL Server, T-SQL, 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 sql6 Comments on Software Vendor Mistakes With SQL Server: Not Getting Parallel Inserts Into #Temp Tables

An Undocumented Restriction For Parallel Inserts In SQL Server That Can Kill Performance

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

Insert Card


I’d like to start this post off by thanking my co-blogger Joe Obbish for being lazy and not blogging about this when he first ran into it three years ago.

Now that we’re through with pleasantries, let’s talk …

Posted in Parallelism, Query Tuning, 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 sql4 Comments on An Undocumented Restriction For Parallel Inserts In SQL Server That Can Kill Performance

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

How Many Threads Can One Query Use In SQL Server?

Posted on August 13, 2021May 16, 2022 by Erik Darling

Overlook


Something that many people overlook about parallel plans is that while they are limited to DOP active CPUs, they can suck up way more threads.

How many more depends on how many branches can be active at the same …

Posted in Hardware, Parallelism, 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 sql7 Comments on How Many Threads Can One Query Use In SQL Server?

Common Table Expressions Are Useful For Rewriting Scalar Functions In SQL Server

Posted on June 23, 2021May 16, 2022 by Erik Darling

Punching


Thanks for watching!

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 Execution Plans, FROID, Parallelism, Plan Cache, Query Tuning, SQL Server, SQL Server 2019, T-SQLTagged 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 Common Table Expressions Are Useful For Rewriting Scalar Functions In SQL Server

Table Partitioning And Parallelism In SQL Server Standard Edition

Posted on March 22, 2021May 16, 2022 by Erik Darling

Jammed, Not Jellied


UPDATE 2021-04-14: Microsoft has updated the documentation for all 2016+ versions of SQL Server to indicate that parallelism is available for partitioned tables in non-Enterprise versions.

There’s been a note in the documentation since SQL Server 2016 …

Posted in Execution Plans, Parallelism, Partitioning, 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

How Useful Is Column Store Indexing In SQL Server Standard Edition?

Posted on March 17, 2021May 16, 2022 by Erik Darling

Speed Limit


When I’m blogging about performance tuning, most of it is from the perspective of Enterprise Edition. That’s where you need to be if you’re serious about getting SQL Server to go as fast as possible. Between the unrealistic …

Posted in Batch Mode, columnstore, Execution Plans, Parallelism, Query Tuning, 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 sql5 Comments on How Useful Is Column Store Indexing In SQL Server Standard Edition?

Multiple Distinct Aggregates: Still Harm Performance Without Batch Mode In SQL Server

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

Growler


Well over 500 years ago, Paul White wrote an article about distinct aggregates. Considering how often I see it while working with clients, and that Microsoft created column store indexes and batch mode rather than allow for hash …

Posted in Batch Mode, Execution Plans, Indexing, Parallelism, Query Tuning, SQL Server, T-SQLTagged 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 Multiple Distinct Aggregates: Still Harm Performance Without Batch Mode In SQL Server

What’s Really Different About In-Memory Table Variables In SQL Server?

Posted on February 16, 2021May 16, 2022 by Erik Darling

Kendra, Kendra, Kendra


My dear friend Kendra asked… Okay, look, I might have dreamed this. But I maybe dreamed that she asked what people’s Cost Threshold For Blogging™ is. Meaning, how many times do you have to get asked a …

Posted in Execution Plans, Parallelism, Query Tuning, SQL Server, T-SQL, 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 sql2 Comments on What’s Really Different About In-Memory Table Variables In SQL Server?

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