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 large cardboard box holding a terracotta pot with empty space around it beside a small snug box with a cream pot

IsMemoryGrantFeedbackAdjusted Values: What Each Status Means

November 28, 2019
Pinal Dave
SQL Performance
Execution Plan, SQL Memory, SQL Scripts

IsMemoryGrantFeedbackAdjusted values tell you what SQL Server did with the memory grant of one run. A tested demo produces every status: first execution, accurate grant, adjusting, stable, percentile adjusting and feedback disabled. The post also shows which run each status appears on.

Read More
Gouache painting of three wooden boxes of growing size, with a blue fabric bundle in the middle one and a vermilion bundle in the smallest

Memory Grant Feedback Explained: How SQL Server Learns

November 27, 2019
Pinal Dave
SQL Performance
Execution Plan, SQL Memory, SQL Scripts

Memory grant feedback explained with one small demo. A sort asks for 13256 KB, uses 160 KB, and gets a corrected grant of 1536 KB on the next run. The post also shows how to read the status in the plan and what the feature needs.

Read More
Gouache painting of an empty tray beside a vermilion crate packed with nails on a shop counter

Locally Aggregated Rows: Why a Columnstore Scan Shows Zero

November 25, 2019
Pinal Dave
SQL Performance
ColumnStore Index, Execution Plan, SQL Scripts

A columnstore scan shows zero actual rows when it aggregates rows itself, and the rows sit in the locally aggregated rows property. A tested demo on 1,000,000 rows shows 0 actual rows and 1,000,000 locally aggregated rows. The post also covers a scan that really is empty, and what STATISTICS IO counts.

Read More
Gouache painting of two wheelbarrows by a pile of bricks, one with a single brick and one stacked full in vermilion

Row Mode vs Batch Mode: Measuring the Speed Difference

November 21, 2019
Pinal Dave
SQL Performance
ColumnStore Index, Compatibility Level, Execution Plan, SQL Scripts

A fair test of row mode vs batch mode changes only the execution mode. This post uses a query hint to force row mode at the same compatibility level, checks that both modes return the same rows, and times five runs of each on a three million row table. It also shows a query where batch mode gains nothing.

Read More
A figure pushing a loaded red handcart from a smooth lane onto a new stretch of rough cobbles, slowing at the seam.

Finding Regressed Queries in Query Store After a Deployment

November 20, 2019
Pinal Dave
SQL Performance
Execution Plan, Query Store, SQL CPU, SQL Server

Find regressed queries in Query Store using weighted before-and-after duration and CPU, then compare plans and changed procedure text.

Read More
Gouache painting of a wheelbarrow with a full vermilion crate of apples and a single apple on a plate behind

Batch Mode on Rowstore: A Simple Example in SQL Server

November 18, 2019
Pinal Dave
SQL Performance
ColumnStore Index, Compatibility Level, Execution Plan, SQL Scripts

Batch mode on rowstore lets SQL Server process rows in groups, even when the table has no columnstore index. This post builds a million row table, reads the execution mode of each plan operator, and flips four switches to show when you get batch mode and when you don’t. It also answers why a plan can stay in row mode.

Read More
Gouache painting of an old stone bridge still open with a new footbridge beside it with a vermilion rail

Create an Index Online in SQL Server: What ONLINE = ON Locks

November 16, 2019
Pinal Dave
SQL Performance
SQL Index, SQL Lock, SQL Scripts, SQL Server

To create an index online in SQL Server, add WITH (ONLINE = ON) to CREATE INDEX, and writers keep working while the index builds. This post compares a plain build with an online build on a three million row table, measures what each one blocks and what the online build costs, and shows where it still waits.

Read More
Previous 1 … 32 33 34 35 36 37 38 … 128 Next
  • Books
  • Testimonials
  • Privacy Policy

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