SQL Server Science
Technical Articles for the DBA / Developer
Home » indexing

SQL Server 2025 Has Automatic Index Management Parts, but I Could Not Turn CREATE_INDEX On

2026-10-27 · by Hannah Vernon · in Internals

SQL Server 2025 ships automatic index management procedures and internal views, but CREATE_INDEX was not accepted on my local test.

How Approximate Are SQL Server 2022 XTP Hash Index Stats?

2026-10-20 · by Hannah Vernon · in Internals

I tested SQL Server’s undocumented XTP hash index approximation function against the documented DMV on one scratch table.

How Much Transaction Log Does REORGANIZE Really Generate? I Measured It

2026-08-19 · by Hannah Vernon · in maintenance

A while back I published , including the classic advice: reorganize in the middle band of fragmentation, rebuild above it, because “reorganize only logs the pages it actually moves.” In a LinkedIn discussion, Jeff Moden…

The Guard Clauses Your Index Rebuild Script Needs

2026-07-25 · by Hannah Vernon · in maintenance

Writing a loop that rebuilds fragmented indexes takes about ten minutes. Writing one you can safely leave running unattended at 2 AM takes considerably longer, and the difference is entirely in the guard clauses: the…

Finding Eager Index Spools in the Plan Cache

2026-07-23 · by Hannah Vernon · in performance

Somewhere in your plan cache right now, there is probably a query quietly building an index every single time it runs. Not once. Every execution. It builds the index, uses it, and throws it away,…

IS DISTINCT FROM: Comparing NULLs Without the Headache

2026-06-18 · by Hannah Vernon · in t-sql

It usually shows up in a code review. Someone writes a filter that is supposed to exclude voided rows, it looks completely reasonable, and yet a tester swears that records are going missing. The query…

1 2 Next »

Search

Categories

  • AI for DBAs
  • announcements
    • events
  • basics
    • localization
  • configuration
  • Data Architecture
  • DMVs
  • documentation
  • extended-events
  • Git for DBAs
  • High Availability
  • Hot Takes
  • Internals
  • maintenance
    • patching
  • Opinion
  • performance
  • PostgreSQL
  • Professional Development
  • recovery
  • reporting
  • ROLLBACK;
  • security
    • data security
  • service broker
  • SQL Server Agent
  • statistics
  • sys
  • t-sql
    • xml
  • tools
    • data masking
    • wsus
  • troubleshooting
  • Uncategorized

Pages

  • About Hannah Vernon
  • About SQL Server Science
  • Blog Series
    • Backup & Recovery from First Principles: Series Contents
    • Database as a Versioned Contract: Series Contents
    • Extended Events in Production: Series Contents
    • Git for DBAs: Series Contents
    • How SQL Server Stores Data: Series Contents
    • Logins, SIDs, and Kerberos from First Principles: Series Contents
    • Proving the Restore: Series Contents
    • T-SQL Style Guide, Tested: Series Contents
    • The UNKNOWN Problem: NULL Traps in T-SQL, Series Contents
    • Undocumented Changes in SQL Server 2022 and 2025: Series Contents
    • Work That Flows: Kanban for DBAs: Series Contents
  • Get Better Help with a Minimal, Complete, and Verifiable Example, or MCVE
  • Privacy Policy

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.

To find out more, including how to control cookies, see here: Cookie Policy

Copyright © 2026 SQL Server Science