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 Moreplan guides
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 MoreHave you ever used sp_create_plan_guide_from_handle?
On occasion you may have a query that starts picking up a bad plan as a result of an execution plan regression. This can be for a variety of reasons (stats, parameter sniffing, etc). If this occurs on a regular basis and you don’t have the luxury of query store, you can use sp_create_plan_guide_from_handle in […]
Read More