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
A garden hose nozzle with a red dial, one sharp jet and one wide gentle shower over flower beds

Catch-All Queries and Optional Parameter Plan Optimization in SQL Server 2025

January 18, 2019
Pinal Dave
SQL Performance
Dynamic SQL, Parameter Sniffing, Recompile, SQL Server

Compare catch-all queries with recompilation, parameterized dynamic SQL, and SQL Server 2025 optional parameter plan optimization.

Read More
Gouache painting of a cold cast-iron stove in a quiet cabin, kindling laid but unlit, with one vermilion box of matches on the hearth

Rarely Run Queries in SQL Server: What the First Run Pays

March 27, 2014
Pinal Dave
SQL Tips and Tricks
Recompile, SQL Cache, SQL Performance

Rarely run queries start with a plan that has left the cache. This test compiles a five table report procedure and measures the compile CPU on the first run, the second run, and a run after the cache was cleared. It also shows how to check whether a plan is still cached.

Read More
Cyclists riding in single file behind a leader in a vermilion jersey who breaks the wind for them.

OPTIMIZED_SP_EXECUTESQL in SQL Server 2025: Fewer Compile Storms

July 3, 2013
Pinal Dave
SQL Performance
Dynamic SQL, Recompile, SQL Cache, SQL Server

Test OPTIMIZED_SP_EXECUTESQL in SQL Server 2025, inspect compilation counters and cached plans, and compare a concurrent workload.

Read More
A hand-drawn map where a road has been redrawn in red around a new lake

Why Queries Recompile

March 24, 2013
Pinal Dave
SQL Performance
Execution Plan, Recompile, SQL Cache, SQL Extended Events, SQL Performance

Why queries recompile becomes clearer when you capture Extended Events causes and check schema changes, statistics, SET options, and temp tables.

Read More
A plain map spread on a wooden table with a red ribbon marking a new detour route, a brass compass beside it.

Stored Procedure Recompile Quiz: What Forces a New Plan?

January 15, 2012
Pinal Dave
SQL Puzzle
Execution Plan, Parameter Sniffing, Recompile, SQL Stored Procedure

This Stored Procedure Recompile Quiz asks which action makes SQL Server build a new plan for a procedure. The answer is a new index on a table it reads. A tested script uses plan counters to show what reuses the plan and what rebuilds it.

Read More
A cobbler's bench with a worn standard shoe last and a freshly carved custom last among wood shavings.

OPTIMIZE FOR and RECOMPILE Hints Compared

July 22, 2010
Pinal Dave
SQL Performance
Execution Plan, Parameter Sniffing, Query Hint, Recompile, SQL Server

OPTIMIZE FOR and RECOMPILE address parameter-sensitive plans differently; compare stable representative values with per-run compilation cost.

Read More
Previous 1 2
  • Books
  • Testimonials
  • Privacy Policy

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