A common question I hear when discussing forcing of plans in query store is, “Why did a plan I previously forced quit using the forced plan?”. In most cases this is related to having a database in an Always On Availability Group. If the database IDs in the availability group differs across the servers, plan […]
Read Morequery store
Adding hints to queries with query store
Up until recent, if you wanted to add a hint to a query without making code changes you had to use plan guides. Plan guides can be tedious to create. With SQL Server 2022, Azure SQL Database, or Azure Managed Instances you can now add hints to queries via Query Store. Using sp_query_store_set_hints The simplest […]
Read MoreQuery store doesn’t work on read replicas
While attempting to find a query that ran on a read only replica, it got me wondering whether I needed to be querying the secondary, the primary, or the always on listener. Turns out the answer is none of the above. Query store only collects information from the primary. If you query any of these […]
Read MoreHow to view query store id’s related to a sql/plan handle or query/plan hash.
On occasion you will have a sql_handle, plan_handle, query_hash, or query_plan_hash and need to know the query store ID related to it. One such example is with a deadlock. Deadlocks contain sql_handles. A key part of diagnosing deadlocks is viewing the execution plan. Query store is a good place to view the execution plan and […]
Read More