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 wooden flower press with a vermilion screw knob not yet tightened

COMPRESSION_DELAY Option for Columnstore Indexes: Test It

January 11, 2021
Pinal Dave
SQL Performance
ColumnStore Index, SQL Index, SQL Scripts, SQL Server

The COMPRESSION_DELAY option keeps a closed delta rowgroup uncompressed for a number of minutes, so rows that change soon after they arrive are cheaper to modify. This post loads two demo tables, updates fresh rows in each, and shows the deleted rows that early compression leaves behind.

Read More
Gouache painting of a wooden crate of oranges on a windowsill with one dark vermilion fruit among them

Fragmentation in Columnstore Indexes: Find and Fix It

January 7, 2021
Pinal Dave
SQL Performance
ColumnStore Index, SQL Index, SQL Scripts, SQL Server

Fragmentation in columnstore indexes means deleted rows that stay inside compressed row groups, and row groups that are too small. This post builds a demo table, measures the deleted rows with the row group view, and compares REORGANIZE with REBUILD.

Read More
Gouache painting of a shelf of unused trowels with one worn trowel with a vermilion handle

Table Usage in the Plan Cache: Count Queries That Touch a Table

January 4, 2021
Pinal Dave
SQL Performance
Execution Plan, SQL Cache, SQL Scripts, SQL Server

Table usage in the plan cache shows how many times the cached queries of a database touched each table. This post builds a demo database, shows why a text search of the cache misleads, reads the table names from the plan XML, and lists the queries behind one table.

Read More
Gouache painting of a football goal far across a lawn with a vermilion ball sitting much nearer

Disable Row Goal in SQL Server: When the Hint Helps and When It Hurts

December 28, 2020
Pinal Dave
SQL Performance
Execution Plan, Query Hint, SQL Scripts, SQL Server

To disable row goal for one query, add the DISABLE_OPTIMIZER_ROWGOAL hint to it. This post builds a table where the row goal chooses a bad plan, shows the reads with and without the hint, and then shows a table where the row goal wins.

Read More
Gouache painting of a wooden sieve beside a grey pail on a beach, with a heap of shells and one vermilion shell in the sieve

Number of Rows Read in an Execution Plan: What It Tells You

December 25, 2020
Pinal Dave
SQL Performance
Clustered Index, Execution Plan, SQL Index, SQL Server Management Studio

The number of rows read shows how many rows a scan or seek touched, and it can be far larger than the rows it returned. This post builds a demo table, compares a scan with two seeks, and explains why a seek can still read too many rows.

Read More
Gouache painting of a tightly packed shelf of jars above a shelf with spaced jars and one vermilion jar

Instance Level Fill Factor or Index Level: What to Set

December 23, 2020
Pinal Dave
SQL Performance
SQL Data Storage, SQL Index, SQL Server Configuration

An instance level fill factor is a default for every index that sets none, and an index level setting wins. This post reads the setting, measures what each fill factor costs in pages and saves in page splits, and gives a plan for choosing the level.

Read More
Gouache painting of a tortoise leaving a long winding trail in the sand with a small vermilion dot nearby

Slowest Cached Queries in SQL Server: Time, Reads and Plan

December 7, 2020
Pinal Dave
SQL Performance
Execution Plan, SQL DMV, SQL Scripts

To find the slowest cached queries in SQL Server, rank each statement by its average elapsed time and keep its plan beside it. This post builds a small workload, reads sys.dm_exec_query_stats with the plan, and shows why a database filter on the sql text hides every ad hoc query.

Read More
Previous 1 … 18 19 20 21 22 23 24 … 128 Next
  • Books
  • Testimonials
  • Privacy Policy

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