In the past a problem we had when building SQLGrease was deadlock graphs only containing sql handles and statement start offsets. While this might give you the statement for parameterized adhoc sql or a stored procedure, it does not account for a cluttered plan cache due to non parameterized sql. This previous post explains the […]
Read MoreRESOURCE_SEMAPHORE waits caused by OBJECTSTORE_LOCK_MANAGER memory usage
Typically when I start seeing RESOURCE_SEMAPHORE waits (memory waits) I focus in on what queries have the largest memory grants. In this case I ran into an issue where most queries on the system were stuck in RESOURCE_SEMAPHORE waits but no queries had a particularly large memory grant. After digging deeper, the system had a […]
Read MoreOpening an actual plan in a separate SSMS tab
I generally find it irritating to try and scroll through a multi-query batch/stored procedure to look at a single execution plan because the scroll bar in a scroll bar is not a great experience. If you have a batch you are running that has some iterative logic it will generate a lot of plans and […]
Read MoreHow to measure query runtime when developing/testing – part 2
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 More