Why Won't PostgreSQL Use My Covering Index?

Dear Postgres, Why won’t you use my covering index?

Lately I’ve been learning to tune queries running against PostgreSQL, and it’s pretty delightful. One fun question that I worked through struck me something that most Postgres users probably encounter one way or another: sometimes you may create the perfect index that covers a given query, but the query planner will choose to ignore it and scan the base table.

Why in the world would it do that?

Continue reading

Fixing OPTIMIZATION_REPLAY_FAILED in Query Store: When Plan Forcing Breaks in SQL Server 2022+

Forcing plans with Query Store can be a powerful tool— until it mysteriously fails. In real production systems, plan forcing sometimes just… doesn’t work. One common culprit is the cryptic OPTIMIZATION_REPLAY_FAILED error.

If you’re hitting OPTIMIZATION_REPLAY_FAILED, try re-forcing the plan using @disable_optimized_plan_forcing=1.

Continue reading

Query Hash Values Are Meaningless in SQL Server: They May Be Reset to Be the Same Value as the Query Plan Hash

This is the worst bug I’ve found in SQL Server to date. Previously, my top find was SQL Server Online Index Rebuild sometimes happens offline without warning. This one has taken top slot because it makes my life more difficult on a daily basis.

Background: SQL Server generates a query_hash for each query. This is stored in sys.query_store_query and it’s one of the primary ways you can identify what a query is across different Query Stores, or even the same Query Store over time, as surrogate query_id values get reset if Query Store is cleared or data ages on. The query_hash is a ‘Zobrist hash over the shape of the individual query, based on the bound (input) logical query tree. Query hints aren’t included as part of the hash.’ (Source)

Continue reading

SQL Server Page Compression: Should You Worry About CPU Usage Increasing on Inserts, Updates, and Deletes?

Every time I share a recommendation to use data compression in SQL Server to reduce physical IO and keep frequently accessed data pages in memory, I hear the same concern from multiple people: won’t this increase CPU usage for inserts, updates, and deletes?

DBAs have been trained to ask this question by many trainings and a lot of online content – I used to mention this as a tradeoff to think about, myself– but I’ve found this is simply the wrong question to ask.

Continue reading

Script to Automate Unforcing Failed Forced Plans in Query Store (SQL Server)

tldr; I’ve published a script to loop through all databases on an instance, identify if there are any query plans in a problematic ‘failed" forced state (which can hurt query performance), and un-force them if found. Get the dbo.dba_QueryStoreUnforceFailed stored procedure on GitHub.

This script is designed to work on SQL Server on-prem, in a VM, or in Azure SQL Managed Instance or SQL Server RDS. Since the script is instance-level and loops through all databases, this isn’t really designed for Azure SQL Database – and you don’t get a SQL Agent there anyway, so you probably want to change this around for that use case. The script is shared under the MIT license, feel free to contribute code and/or adapt away for your own uses.

Continue reading

General Failure Failed Forced Plans in Query Store Cause Even Slower Compile Times

This post demonstrates two related bugs with plan forcing in Query Store which increase the likelihood of slower query execution and application timeouts in SQL Server environments.

These bugs are most likely to impact you if:

• You use the Automatic Plan Correction feature in SQL Server, which automatically forces query plans.
• Anyone manually forces query plans with Query Store.
• You have slow storage, which can increase your likelihood of having longer compilation times.

The General Purpose tier of Azure SQL Managed Instance and Azure SQL Database feature both slow storage and Automatic Plan Correction enabled by default. So, weirdly enough, your risks of suffering from this problem are high if you are an Azure SQL customer.

Thanks to Erik Darling for his help in diagnosing and reproducing these issues– and his ‘slow compiler’ query used in this post was incredibly helpful to isolate and narrow down these problems.

Continue reading

Slow Storage Can Cause Slow Compilation Time in SQL Server

Up till now, I’ve thought of compilation time in SQL Server as being dependent only on CPU resources– not something that requires fast storage to be speedy. But that’s not quite right.

Slow storage can result in periodic long compile time in SQL Server. And long compile time not only extends the runtime for the query, it can also result in blocking with waits for compile locks.

Thanks to Erik Darling for helping me figure this out, and explaining this all in his video, What Else Happens When Queries Try To Compile In SQL Server: COMPILE LOCKS!. For great details and demos, go watch that! I’ll be working through the topic with some simple flow charts here.

Continue reading