This is the second part of a three part post on measuring query runtime. In this post I’ll go over using Actual Query Plans in SSMS to view runtime information. This is available in SQL Server 2016 and later. Prior to this actual plans do not have these details. To capture the actual query plan […]
Read MoreHow to measure query runtime when developing/testing – part 1
In this first of a 3 part post I’ll go over some ways to get accurate timing of query runtimes. This is geared towards the situation where you are making query tweaks in SSMS and want to quickly see an accurate runtime that your tweak resulted in. I’ll start this post off by suggesting don’t […]
Read MoreOLEDB waits: what they mean and a few ideas on addressing them
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 MoreSTATMAN queries causing you timeouts?
If you have a query executing having unexpected slowness or timeouts but at the same time you see queries referencing statman, you might be running into an issue of synchronous statistics updates. By default SQL Server performs automatic statistics updates synchronously. This means when the threshold for requiring a statistics update is reached statistics are […]
Read MoreWhat does the status column of a session mean in SQL Server?
You might notice there is a status column when you look at active executing requests in either sys.dm_exec_requests, sys.dm_exec_sessions, or sysprocesses. The status column indicates a general status of what the session is doing. background Usually these sessions aren’t a concern. Not much interesting happening as they are typically housekeeping tasks. rollback You’ll see this […]
Read MoreQuery store queries losing forced plans
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 MoreRemoving a table hint without changing code
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 More