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 roller blind at the end of its travel while daylight continues through the window

Query Store Read-Only: Size Limits That Stop Capture

March 15, 2018
Pinal Dave
SQL Performance
Change Data Capture, Query Store, SQL Server

Your Query Store may want READ_WRITE and still be READ_ONLY. Here is how it happens, how to read the reason, and how to get capture back.

Read More
A round-bottomed wooden wobble toy with a red base rocking back upright on a table.

Automatic Plan Correction: Reading SQL Server’s Tuning Advice

March 10, 2018
Pinal Dave
SQL Performance
Execution Plan, Query Store, SQL Performance, SQL Server 2017

Read automatic plan correction advice, compare regressed and recommended plans, and verify that forcing the last good plan held.

Read More
Gouache painting of a crowded shelf of tiles and a spacious table with tiles in neat rows and a vermilion tray

Sort in TempDB for Faster Index Rebuilds: When It Helps

February 20, 2018
Pinal Dave
SQL Performance
SQL Index, SQL Scripts, SQL Server, SQL TempDB

To sort in tempdb during an index rebuild, add WITH (SORT_IN_TEMPDB = ON) to ALTER INDEX. This post tests the option and shows that the user database log stays the same size, because only the sort workspace moves. It also explains when the option helps, how much tempdb space it needs, and when to leave it off.

Read More
Illustrated cover: Identity Jumps by 1000

SQL SERVER – Identity Jumping 1000 – IDENTITY_CACHE

January 24, 2018
Pinal Dave
SQL Performance
SQL Identity, SQL Scripts, SQL Server, SQL Server 2017

Has your identity column ever jumped by 1000 after a restart? SQL Server 2017 fixes it with IDENTITY_CACHE. Here is a demo with the setting ON and OFF.

Read More
Performance observation of TRIM function.

SQL SERVER – Performance Observation of TRIM Function

January 17, 2018
Pinal Dave
SQL Performance
SQL Function, SQL Scripts, SQL Server

Does the new TRIM function slow your queries like other functions in a WHERE clause do? Here is my performance observation of TRIM function in SQL Server 2017.

Read More
A stack of bamboo steamer baskets with one tier lifted out and steaming beside it.

Partition Elimination: Proving a Query Reads Only What It Needs

November 24, 2017
Pinal Dave
SQL Performance
Execution Plan, SQL Performance, SQL Server, Table Partitioning

Prove partition elimination with the actual plan, test date predicates, and use $PARTITION to check which slice SQL Server targets.

Read More
Gouache painting of an antique sewing machine beside two spools whose threads form a tight vermilion knot

NOLOCK Without WITH: Table Hint or Table Alias?

October 24, 2017
Pinal Dave
SQL Performance
Compatibility Level, Query Hint, SQL Lock, SQL Scripts

NOLOCK without WITH and without parentheses is not a hint, because SQL Server reads it as a table alias. This post proves it with a lock test, shows why the compatibility level doesn’t change it, and gives a query that finds the bad syntax in your code. It ends with the safer ways to write the hint.

Read More
Previous 1 … 50 51 52 53 54 55 56 … 128 Next
  • Books
  • Testimonials
  • Privacy Policy

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