This month's discount Comprehensive Database Performance Health Check · Testimonials
SQLAuthority with Pinal Dave
Home
  • Consulting
    • Health Check
  • Free Videos
  • All Articles
    • AI
    • SQL Performance
    • SQL Tips and Tricks
    • Interview Questions and Answers
    • SQL Puzzle
    • SQL Video
    • SQLAuthority News
    • Personal
  • Books
  • Consulting
Gouache painting: a row of eight windmills along a ridge at dusk, most sails turning and three standing still, one windmill with a vermilion sail

Threads With Zero Rows in a Parallel Plan, Measured

April 7, 2021
Pinal Dave
SQL Performance
Execution Plan, MAXDOP, Parallel, SQL DMV, SQL Scripts

A parallel plan can show threads with zero rows, and that is normal. This post reads the per-thread row counters of two real plans on SQL Server 2025 and shows where the empty threads come from. It also says when an empty thread points to a real problem.

Read More
Gouache painting of a long orchard row with one basket at the first tree holding a single vermilion apple

Top 1 and Index Scan: A Scan That Stops After One Row

April 1, 2021
Pinal Dave
SQL Performance
Execution Plan, SQL Index, SQL Scripts, SQL Top

Top 1 and index scan can sit in the same plan, and the scan still reads only a few pages. This post runs six queries on a table of 100,000 rows and reads their logical reads. It shows why TOP 1 reads three pages in one case and every page in another.

Read More
Gouache painting of a narrow water channel with a vermilion board across it and water flowing past

Disable Adaptive Join for One Query or a Whole Database

March 30, 2021
Pinal Dave
SQL Performance
Execution Plan, SQL Joins, SQL Scripts, SQL Server

To disable adaptive join for one query, add a USE HINT to it. This post also shows a Query Store hint for code you cannot edit and the database setting that switches the feature off for everything. Each switch is tested on a small demo database and checked in the plan.

Read More
Gouache painting of a canal lock with a vermilion sluice wheel between the closed gates

Enable Adaptive Join in SQL Server: Why It Does Not Appear

March 26, 2021
Pinal Dave
SQL Performance
Execution Plan, SQL Joins, SQL Scripts, SQL Server

To enable adaptive join in SQL Server, the compatibility level, the database setting and the query must all agree. This post builds a demo database and probes the plan under each condition. It also corrects older advice whose statements switch the feature off.

Read More
Gouache painting of a blue locomotive on a track beside a platform with small crates and a post with a round vermilion signal

Adaptive Threshold Rows: How an Adaptive Join Chooses

March 25, 2021
Pinal Dave
SQL Performance
Execution Plan, SQL Joins, SQL Scripts, SQL Server

Adaptive threshold rows is the row count at which an adaptive join switches from nested loops to a hash join. This post runs two queries on WideWorldImporters, reads each threshold from the cached plan, and shows which branch ran. The first query stays below its threshold and the second goes above it.

Read More
A kitchen table tilting on a level floor because one leg still sits on old folded red card.

Auditing Query Hints Left in Production Code

March 18, 2021
Pinal Dave
SQL Performance
Execution Plan, Query Hint, Query Store, SQL Server

Start auditing query hints in modules and Query Store, rank the busiest statements, and test removal with measured plans and rollback.

Read More
Different plowshare shapes arranged beside the distinct furrows they make

Ten Plan Operators Explained: An Operator Cheat Sheet

March 4, 2021
Pinal Dave
SQL Tips and Tricks
Execution Plan, Maintenance Plan, SQL Operator

Seek, scan, lookup, three joins, sort, aggregate, spool and parallelism, each shown with a tiny demo. Learn to judge a plan by rows and executions.

Read More
Previous 1 … 4 5 6 7 8 9 10 … 36 Next
  • Books
  • Testimonials
  • Privacy Policy

© 2006 – 2026 All rights reserved. pinal @ SQLAuthority.com