You might run into a query that is showing parallel waits (CXPACKET, CXCONSUMER, etc). Typically, the parallel tasks executing are doing something like consuming CPU, waiting on IO, etc. If your parallel tasks get hung up waiting on something it will hold up the query execution as a whole. Most monitoring methods will show the […]
Read MoreImproving performance of SUBSTRING queries
Sometimes I see queries that use SUBSTRING in the query predicate. This makes the query non-sargable (i.e. indexes aren’t usable). Example query: SELECT * FROM CustomerAccount WHERE SUBSTRING(AccountNbr, 0, CHARINDEX(‘-‘, AccountNbr)) = ‘999999’ This query is searching for an account number where everything up to the hyphen is stripped out. Applying the SUBSTRING function causes […]
Read MoreExecution plan shortcuts causing random long executions
I recently worked with a customer where they had a query that despite taking a consistent execution plan had very different runtimes (as identified by the plan hash). This was not a case of parameter sniffing – instead this is what I refer to as plan shortcuts. The query I’m using for this example is […]
Read MoreSQL Server compile locks
While they don’t occur very often, compile locks can become a problem with stored procedures. This post will outline some common things to look for that can cause compile locks and how to address them. What is a compile lock? SQL Server keeps one version of an execution plan in the plan cache at any […]
Read MoreAre you searching for a Precise I3 replacement?
I occasionally do consulting engagements for customers although our primary business is SQLGrease (database observability, performance monitoring). I had a customer that used Precise I3. Not a bad product, but it needed some updating as it had some quirks as they migrated from adobe flash to a web version. They were acquired by Idera and […]
Read MoreHow to measure query runtime when developing/testing – part 3
This is the third in a 3 part post on measuring query runtimes (previous posts Part 1 Part 2) This post covers using live query plans. This feature is available on SQL Server 2016 or later. Live query statistics allow you to see where time is being spent as a query executes. It’s similar to […]
Read MoreDeadlock graphs now contain query hashes
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 More