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: SQL Server 2022

SQL Server 2022 Parameter Sensitive Plan Optimization: How PSP Works To Solve Some Parameter Sensitivity Issues

Posted on June 28, 2022June 24, 2022 by Erik Darling

Classico


In the bad old days, which are still your days (but at least you have better days to look forward to), SQL Server’s cost-based optimizer would take a parametrized query, come up with a query plan, and then reuse …

Posted in Parameter Sniffing, PSP, SQL Server, SQL Server 2022Tagged 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 sqlLeave a Comment on SQL Server 2022 Parameter Sensitive Plan Optimization: How PSP Works To Solve Some Parameter Sensitivity Issues

SQL Server 2022 Parameter Sensitive Plan Optimization: The Problem With Sniffed Parameter Sensitivity

Posted on June 27, 2022June 27, 2022 by Erik Darling

Long Time Coming


When Microsoft first started coming up with these Intelligent Query Processing features, I think everyone who cares about That Sort Of Thing© wondered when parameter sensitivity would get fixed.

Let’s take a brief moment to talk about …

Posted in Dynamic SQL, Execution Plans, Indexing, Parameter Sniffing, Parameterization, PSP, SQL Server, SQL Server 2022Tagged 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 SQL Server 2022 Parameter Sensitive Plan Optimization: The Problem With Sniffed Parameter Sensitivity

SQL Server 2022: Cardinality Estimation Feedback

Posted on June 23, 2022June 23, 2022 by Erik Darling

Quiet As Kept


I’ve been trying to take the general temperature when it comes to SQL Server 2022. At least from a performance perspective, some interesting things have been introduced so far.

There have been a few neat things:

  • Parameter
…
Posted in Cardinality, Query Store, Query Tuning, Row Goal, SQL Server, SQL Server 2022Tagged 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 SQL Server 2022: Cardinality Estimation Feedback

What’s The Point Of DOP Feedback In SQL Server 2022?

Posted on June 21, 2022June 13, 2022 by Erik Darling

Loud Engine


If you’re ever on a long flight and want something to fall asleep to, ask a DBA how to set MAXDOP.

Sometimes I even ask myself that question when I’m tossing and turning at night.

There are a …

Posted in Execution Plans, MAXDOP, Parallelism, Query Tuning, Server Tuning, SQL Server, SQL Server 2022Tagged 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 What’s The Point Of DOP Feedback In SQL Server 2022?

SQL Server 2022 Finally Adds Actual Non Parallel Plan Reasons To Query Plans

Posted on June 17, 2022June 4, 2022 by Erik Darling

Wayback Machine


SQL Server query plans have had, forever, pretty generic reasons embedded in the XML for why your query was prevented from going parallel.

Not for things like it not breaking the Cost Threshold For Parallelism barrier, which should …

Posted in Execution Plans, Parallelism, SQL Server, SQL Server 2022Tagged 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 SQL Server 2022 Finally Adds Actual Non Parallel Plan Reasons To Query Plans

Some Thoughts On Query Store Hints In SQL Server 2022

Posted on June 15, 2022June 15, 2022 by Erik Darling

Young And Old


Query hints seem to get treated like prescription drugs. You hear all the problems they can solve, but then you get a long list of problems they can cause.

“Your cholesterol will be lower, but you might …

Posted in Query Store, SQL Server, SQL Server 2022Tagged 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 Some Thoughts On Query Store Hints In SQL Server 2022

SQL Server 2022 Is Going To Mess Up Your Query Monitoring Scripts

Posted on June 14, 2022June 23, 2022 by Erik Darling

At Least For Now


SQL Server 2022 has a new feature in it to help with parameter sensitive query plans.

That is great. Parameter sensitivity, sometimes just called parameter sniffing, can be a real bear to track down, reproduce, and …

Posted in Dynamic SQL, Execution Plans, Parameter Sniffing, Parameterization, Plan Cache, Query Store, SQL Server, SQL Server 2022Tagged 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 SQL Server 2022 Is Going To Mess Up Your Query Monitoring Scripts

SQL Server 2022: What The Heck Is sp_copy_data_in_batches?

Posted on June 13, 2022May 26, 2022 by Erik Darling

Make It Or Not


I’m gonna be honest with you, dear reader, because without honesty we’ve got nothing.

Except lies — which you know — those can be comforting sometimes. Hm. I’ll have to think about that one for a …

Posted in SQL Server, SQL Server 2022Tagged 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 SQL Server 2022: What The Heck Is sp_copy_data_in_batches?

SQL Server 2022: GENERATE_SERIES Causes Parallel Deadlocks In A Transaction

Posted on June 10, 2022May 26, 2022 by Erik Darling

Many Times!


These table valued functions of the built-in variety have this problem.

This one is no exception. Well, it does throw an exception. But you know.

That’s not exceptional.

DROP TABLE IF EXISTS
    dbo.select_into;

BEGIN TRAN

SELECT
    id = 
        
…
Posted in GENERATE_SERIES, SQL Server, SQL Server 2022Tagged 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 SQL Server 2022: GENERATE_SERIES Causes Parallel Deadlocks In A Transaction

SQL Server 2022 GENERATE_SERIES: Some Notes At Release Time

Posted on June 9, 2022May 26, 2022 by Erik Darling

Yep, I know


It’s the first public CTP. Things will change. Things will get better. Think about the rich history of Microsoft fixing stuff immediately, like with adding an ordinal position to STRING_SPLIT.

That came out in SQL Server …

Posted in GENERATE_SERIES, SQL Server, SQL Server 2022Tagged 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 SQL Server 2022 GENERATE_SERIES: Some Notes At Release Time

Posts navigation

Older posts

Recent Posts

  • SQL Server 2022 Parameter Sensitive Plan Optimization: How PSP Works To Solve Some Parameter Sensitivity Issues
  • SQL Server 2022 Parameter Sensitive Plan Optimization: The Problem With Sniffed Parameter Sensitivity
  • How To Get Answers To SQL Server Performance Questions

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
  • GENERATE_SERIES
  • Hardware
  • IF Branching
  • Implicit Transactions
  • Indexed Views
  • Indexing
  • Interviewing
  • Isolation Levels
  • Joe Obbish
  • Local Variables
  • Locking
  • Managed Instance
  • MAXDOP
  • Memory Grants
  • Normalization
  • Office Hours
  • OPTIMIZE_FOR_SEQUENTIAL_KEY
  • OtherDB
  • Parallelism
  • Parameter Sniffing
  • Parameterization
  • Partitioning
  • PASS Data Summit
  • perfview
  • Plan Cache
  • Plan Cache Liars
  • Plan Guides
  • Postgres
  • PSP
  • Query Store
  • Query Tuning
  • Row Goal
  • SARGability
  • Server Tuning
  • Software Vendor Mistakes
  • sp_HumanEvents
  • sp_PressureDetector
  • sp_QuickieStore
  • sp_WhoIsActive
  • Spills
  • Spinlocks
  • 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