Typically when you see OLEDB waits they come from querying linked servers set up through OLEDB. If you are seeing a high number of these as a query runs, you should consider monitoring performance on the remote server to see how the query on the local server splits the work up. Unfortunately the optimizer is […]
Read MorePerformance Tuning
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 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 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 MoreEstimate rows Vs Estimated rows to be read what’s the difference
Starting with SQL Server 2016 sp1 execution plans from the cache now contain “Estimated Number Of Rows to be Read”. If you are interested in how this is different from “Estimated Rows Per Execution” and how this value can help identify a performance problem in your plan read on… How does it differ from Estimated […]
Read MorePerformance implications of using DATEDIFF function in WHERE
These might be narrow cases I’m describing, but as I’ve seen it happen often enough I figured it is worth writing a post about. In this post I’ll describe two different scenarios where the DATEDIFF function caused a query to perform poorly due to DATEDIFF not being sargable. The first scenario I’ll be describing is […]
Read MorePerformance implications of using ISNULL vs IS NULL
Quite often I see queries that are not sargable due to the use of the ISNULL function in the query’s predicate (WHERE clause). If you’re not familiar with sargable it means whether or not a condition in the predicate can take advantage of an index. I won’t dive too deep into sargability here, but I’ll […]
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 More