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 MoreAdding 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 MoreFiltered indexes and the new parameter sniffing option (spoiler alert it doesn’t help)
After writing a post on SQL Server’s new parameter sniffing option and how it helps performance of parameterized wildcard queries, I started to wonder if we can get similar benefits with filtered indexes. Filtered indexes come with the gotcha of requiring that the predicates of the index be hardcoded (non parameterized). I can see the […]
Read MoreNegative blocking session ids
On occasion while examining lock scenarios, I’ve seen a lead blocker with a negative session ID. After looking in the documentation for the blocking_session_id, it explained why I am seeing this (taken directly from Microsoft’s sys.dm_exec_requests documentation): -2 = The blocking resource is owned by an orphaned distributed transaction. -3 = The blocking resource is […]
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 MoreImproving performance of your RIGHT query
I always get a kick out of when a query needs to perform a look up on the last 4 digits of a column because it usually ends up being something radioactive like a credit card number or a social security number. Either way, trying to do a lookup by the RIGHT most characters of […]
Read MoreImproving performance of your LEFT query
If you have queries that search for the first X number of characters in a column you might be using a LEFT. Since LEFT is a function it makes the query predicate (WHERE) unsargable. In other words an index cannot be efficiently used. The demo query In this query we are searching for the LEFT […]
Read MorePerform RTRIM and LTRIM in your code not in your queries
On occasion I’ll see queries trim off whitespace from both the input parameters as well as against the columns in the predicate (WHERE clause). If you’ve been reading my most recent posts I’ll start to sound like a broken record – applying functions to columns in a predicate will make a query unsargable (unable to […]
Read MorePerformance of parameterized LIKE queries with new parameter sniffing option
A while back I wrote a blog post on how parameterized queries with a LIKE in the predicate (WHERE) performed slower than the non-parameterized version. In a nutshell, the parameterized version of the query can’t tell for certain what parameters will be passed and does not generate an optimal plan. While attempting to set up […]
Read More