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 […] The post How to measure query runtime when developing/testing – part 2 appeared first on SQLGrease SQL Server Performance tips ..read more

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 […] The post How to measure query runtime when developing/testing – part 1 appeared first on SQLGrease SQL Server Performance tips ..read more

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 […] The post OLEDB waits: what they mean and a few ideas on addressing them appeared first on SQLGrease SQL Server Performance tips ..read more

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 […] The post STATMAN queries causing you timeouts? appeared first on SQLGrease SQL Server Performance tips ..read more

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 […] The post Query store queries losing forced plans appeared first on SQLGrease SQL Server Performance tips ..read more

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 […] The post Removing a table hint without changing code appeared first on SQLGrease SQL Server Performance tips ..read more

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 […] The post Adding hints to queries with query store appeared first on SQLGrease SQL Server Performance tips ..read more

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. […] The post Query store and plan guides for non parameterized SQL appeared first on SQLGrease SQL Server Performance tips ..read more

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 […] The post Filtered indexes and the new parameter sniffing option (spoiler alert it doesn’t help) appeared first on SQLGrease SQL Server Performance tips ..read more

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 […] The post Negative blocking session ids appeared first on SQLGrease SQL Server Performance tips ..read more