On occasion I’ll run into a production performance problem where hints were added to a query and the hint is having a negative effect on performance. In some cases this is the result of something buried in vendor code. In other cases this is related to custom developed code where it is not easy to […]
Read Morequery hints
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 and plan guides for non parameterized SQL
On occasion you might find a query that doesn’t perform consistently or just not at all. When you don’t have consistent query performance you might consider forcing a plan with query store. Similarly, if you’re trying to influence a plan that doesn’t behave the way you think it should you can apply a plan guide. […]
Read MoreDealing With “Bad” Plans
SQL Server does the best it can to generate an efficient execution plan when a query is executed. This process can be expensive. To avoid the expense, SQL Server attempts to cache a plan once it is generated. This eliminates the need for subsequent calls to the same query to generate the plan. In an […]
Read MorePlan guides made easy
Plan guides are a useful tool for changing an execution plan when despite your best efforts the optimizer just isn’t coming up with the optimal plan. This becomes useful for situations where you can’t make code changes to a query or stored procedure. People are sometimes hesitant to use them due to the complexity in […]
Read MoreHow to remove a hint from a query without code changes
On occasion I’ve been in situations where I’ve identified a query with a hint applied that was causing degraded performance. In the case of vendor code this can be a problem since removal or changes to hints would require a code change. This can also be a problem in shops where there are strict processes […]
Read MoreJoin Hints – Careful, They Force Order!
Recently I was looking at a query generating deadlocks as a result of a clustered index scan. I saw that someone forced a LOOP JOIN on one of the offending queries. At first glance it appeared as if the LOOP JOIN should have made an index seek more likely; however, after remembering a side effect […]
Read More