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 of an unmarked glass rain gauge on a wooden garden post with a vermilion float inside it and a small watering can at its foot

AUTO_UPDATE_STATISTICS_ASYNC Fixes WAIT_ON_SYNC_STATISTICS_REFRESH

January 23, 2020
Pinal Dave
SQL Performance
SQL Server, SQL Server Management Studio, SQL Statistics, SQL Wait Stats

AUTO_UPDATE_STATISTICS_ASYNC is the setting that cleared a new wait type, WAIT_ON_SYNC_STATISTICS_REFRESH, for my client. With auto update statistics on and stale statistics, the query halts while the statistics update. Switching the update to asynchronous removed the wait.

Read More
Illustrated cover: Temp Table vs Table Variable

SQL SERVER – Temp Table vs Table Variable – Cardinality Estimation

January 17, 2020
Pinal Dave
SQL Performance
Execution Plan, SQL Scripts, SQL Server, SQL TempDB, Temp Table

I had a very interesting conversation with one of my clients about Cardinality Estimation for Temp Table vs Table Variable. Let us discuss it today.

Read More
A tray of flat-head screwdrivers beside a closed wooden box, a hand hovering between them on a workbench.

Why a Filtered Index Is Ignored by Parameterized Queries

January 15, 2020
Pinal Dave
SQL Performance
Execution Plan, Parameter Sniffing, SQL Index, SQL Performance

Explain why a filtered index is ignored with parameters, inspect plan warnings, and compare literal filters, RECOMPILE, and coverage fixes.

Read More
A preparation lever is inspected separately from an empty storage bin and prepared tiles.

SQL SERVER 2019 – How to Turn On or Enable Instant File Initialization?

January 6, 2020
Pinal Dave
SQL Performance
SQL Server, SQL Server 2019, SQL Server Configuration

SQL Server 2019 Configuration Manager provides an interface for instant file initialization. My lab also used it with SQL Server 2017.

Read More
A single made-up guest bed facing a long row of sleeping bags in the hall.

Table Variable Deferred Compilation: Before and After SQL Server 2019

January 4, 2020
Pinal Dave
SQL Performance
Execution Plan, SQL Performance, SQL Server 2019, Temp Table

Compare table variable deferred compilation at compatibility levels 140 and 150, then decide when a temporary table gives better estimates.

Read More
Gouache painting of four garden tools hanging on a rail, the second one a vermilion trowel and the others grey and sage

Parameter Sniffing Fixes Compared: Which One to Use

January 1, 2020
Pinal Dave
SQL Performance
Parameter Sniffing, Query Hint, Recompile, SQL Scripts, SQL Stored Procedure

Parameter sniffing fixes compared on one table show a clear order, from a cheap guess to a better index. This post runs the plain procedure, a local variable, OPTIMIZE FOR UNKNOWN, a scoped setting, OPTION (RECOMPILE) and a covering index on the same skewed table, and prints the page reads of each in both call orders.

Read More
Gouache painting of a brass tap with a vermilion wheel feeding a trough that runs past a row of young apple trees

Database Scoped Configuration: Turn Off Parameter Sniffing

December 30, 2019
Pinal Dave
SQL Performance
Parameter Sniffing, SQL Scripts, SQL Server Configuration, SQL Stored Procedure

A database scoped configuration lets you turn off parameter sniffing for one database with a single statement. This post measures a procedure with the setting on and off, shows that the change clears the cached plans of the database, and explains what the switch does not fix.

Read More
Previous 1 … 29 30 31 32 33 34 35 … 128 Next
  • Books
  • Testimonials
  • Privacy Policy

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