Latest SQLComics

Meet the SQL Server Query Optimizer

on March 11, 2024

Thanks to Erik Darling for pointing out that it needed a little teeth.

LOOK HERE

Plan Caching in SQL Server v1

on March 4, 2024

I drew out a first visualization of how the shared plan cache in SQL Server is used when you run a query. I’m pretty sure I’ll refine and and elaborate on this in the future, so let’s call this the v1.

LOOK HERE

Compare SQL Server Isolation levels

on February 17, 2024

Most modern hardware supports RCSI and Snapshot isolation beautifully.

LOOK HERE

Indexed Views Koala

on January 17, 2024

Indexed views are amazing: they’re like an auto-updating table based on the definition of the view. Whenever rows change in the tables defined in the view, those changes are also applied to the indexed view.

LOOK HERE

Latest blog posts

You Won't Find Long Compilers Who Time Out in Query Store

By Kendra Little on March 5, 2024

Last November, a puzzle was really bothering me. Some queries from an application were timing out frequently after running for 30 seconds, but they were halfway invisible in the SQL Server.

Continue reading

How to Start an XEvents Trace on a Read Scale-Out Azure SQL Managed Instance

By Kendra Little on February 29, 2024

It took me more than half hour to figure out how to start an XEvents trace on a read-scale out instance of Azure SQL Managed Instance. It’s hard to monitor read scale-out instances, so tracing is desirable! I started with a simple trace of sql_statement_completed. Hopefully this saves other folks some time.

Continue reading

Should You Use SQL Server Readable Secondaries if Queries Can Fail Repeatedly at Any Time?

By Kendra Little on February 21, 2024

If you use readable secondaries in Availability Groups or Read-Scale out instances in Azure SQL Managed Instance, you may have queries fail repeatedly if there is a glitch and statistics are not successfully “refreshed” on the secondary replica. Those queries may keep failing until you manually intervene.

It’s unclear if Microsoft will ever fix this. There is a well established support deflection article which documents the issue and provides ‘workarounds’.

Continue reading

Ugly Bug: SQL Server Online Index Rebuild sometimes happens offline without warning

By Kendra Little on February 7, 2024

I found a nasty bug in SQL Server and Azure SQL Managed Instance recently: sometimes an “online” index rebuild of a disk-based rowstore clustered index (basically a normal, everyday table) isn’t actually “online”. In fact, it’s very OFFLINE, and it blocks both read and write queries against the table for long periods.

If you manage to make it through a rebuild successfully, the problem goes away for future rebuilds of that clustered index – likely leaving you bruised and bewildered.

Continue reading