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 a beagle sitting beside a wicker picnic basket with a vermilion cloth, looking away

DISABLE_PARAMETER_SNIFFING Hint: Turn Off Sniffing for One Query

February 12, 2020
Pinal Dave
SQL Performance
Execution Plan, Parameter Sniffing, Query Hint, SQL Scripts, SQL Stored Procedure

The DISABLE_PARAMETER_SNIFFING hint makes one query ignore the parameter value it was first compiled for. This post compares it with the default, OPTIMIZE FOR UNKNOWN and RECOMPILE on a table where one customer owns 40 percent of the rows. The reads show which variant fits which workload.

Read More
Gouache painting of three fresh rolls in a vermilion basket on a counter in front of a tray of dough still waiting

OPTION FAST N: First Rows Sooner, Total Reads Higher

February 11, 2020
Pinal Dave
SQL Performance
Execution Plan, Query Hint, SQL Scripts, SQL Server

OPTION FAST N tells SQL Server to plan for the first N rows, so the first rows arrive in milliseconds and the whole result costs much more. This post measures both sides on 300,000 rows with logical reads and time to the first row. It also explains why the hint belongs to paging and not to tuning.

Read More
Gouache painting of a slow mill wheel at a stream with a stick, a stone and leaves blocking it, one leaf vermilion

Reasons for Slow Performance in SQL Server: The Top Five

February 10, 2020
Pinal Dave
SQL Performance
SQL CPU, SQL Function, SQL Index, SQL Memory, SQL Server Configuration

The reasons for slow performance in SQL Server fall into five groups: schema design, T-SQL code, indexing, server deployment and hardware. This post tests three of them on a demo database, with logical reads as proof. It also shows a query that checks four deployment settings.

Read More
Gouache painting of a basket of red apples under an apple tree in an orchard row

DISABLE_OPTIMIZER_ROWGOAL: How a Row Goal Changes the Plan

February 7, 2020
Pinal Dave
SQL Performance
Execution Plan, Query Hint, SQL Scripts, SQL Server

DISABLE_OPTIMIZER_ROWGOAL switches off a row goal, the optimizer’s bet that a query needs only a few rows, and TOP, FAST, IN and EXISTS all place that bet. This post reads the estimates behind it, shows how the plan shape changes, and shows when the bet fails. A covering index fixes the failure, and the hint does not.

Read More
Gouache painting of a cello, a violin and a harp with a small music stand and a single vermilion tuning fork between them

Dynamics SQL Server Settings: Five Checks for NAV, AX and CRM

February 5, 2020
Pinal Dave
SQL Performance
MAXDOP, SQL Index, SQL Scripts, SQL Statistics, SQL TempDB

Dynamics SQL Server settings decide most of the speed you get from NAV, AX and CRM, because you rarely control the application code. Five read-only checks cover tempdb, autogrowth, MAXDOP, automatic statistics and statistics age. Each one comes with a short query and a clear rule.

Read More
Gouache painting of small round cafe tables on a stone terrace, two sage and one vermilion, with a stack of chairs by the shed door

TempDB Metadata Tables: Check, Enable and Count Them

January 29, 2020
Pinal Dave
SQL Performance
In-Memory OLTP, SQL Scripts, SQL Server 2019, SQL Server Configuration, SQL TempDB

The tempdb metadata tables can live in memory, and four short steps tell you whether yours do. This post checks the setting, shows how to turn it on and off, and counts the tables SQL Server moves. It also says when the feature is worth a restart.

Read More
RID Lookup

SQL SERVER – Heaps, Scans and RID Lookup

January 25, 2020
Pinal Dave
SQL Performance
Clustered Index, Execution Plan, SQL Heap, SQL Index, SQL Scripts, SQL Server

The question was do heaps are very bad and always force a table scan? Let us learn about RID Lookup, Heaps and table scan.

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

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