SQLGrease SQL Server Performance tips

How CURSOR processing logic shows up in Query Store

franklinyamamoto — Wed, 03 Dec 2025 03:52:28 +0000

Using standard DMV’s, it is difficult to get execution plans or query text from programs using cursors. You typically see an OPEN CURSOR statement, or a FETCH statement. This makes it difficult to identify what query was being run and what plan was being used. Query store actually tracks performance of both of these separately. […]

The post How CURSOR processing logic shows up in Query Store appeared first on SQLGrease SQL Server Performance tips.

Using GO in SSMS versus not – what is the difference

franklinyamamoto — Mon, 01 Dec 2025 00:05:51 +0000

You might see GO used in SQL text. The GO command marks the end of a batch. As a result, any statements executed after this are part of a new batch. This also means any context of the previous batch is missing. Take for example the following: DECLARE @CustomerID INT SET @CustomerID = 123152 SELECT […]

The post Using GO in SSMS versus not – what is the difference appeared first on SQLGrease SQL Server Performance tips.

Unmatched Indexes plan warnings

franklinyamamoto — Mon, 17 Nov 2025 17:19:34 +0000

In SQL Server 2008 a new feature was introduced – Filtered Indexes. A filtered index is supposed to introduce performance gains by reducing the size of the index to discrete values. In order for a filtered index to be used, the query must not parameterize the value associated with the filtered index. If you use […]

The post Unmatched Indexes plan warnings appeared first on SQLGrease SQL Server Performance tips.

No Join Predicate plan warning may be a red herring

franklinyamamoto — Sun, 16 Nov 2025 18:11:05 +0000

Whenever I open a plan, I keep an eye out for plan warnings. These are sometimes really useful, but at times they may contain some inconsequential or incorrect information. One in particular is the “No Join Predicate”. Typically, this would be cause for alarm since this is an indicator of a cartesian join. This would […]

The post No Join Predicate plan warning may be a red herring appeared first on SQLGrease SQL Server Performance tips.

Parallel plans can be selected with scalar UDF’s

franklinyamamoto — Sat, 08 Nov 2025 22:49:57 +0000

It’s a somewhat known thing that scalar user defined functions can prevent parallel plans from being selected. There is a catch. If the scalar function can be inlined, this rule does not apply. Inlining of scalar functions is available starting in SQL Server 2019 (compatibility level 150). Demo Query I will be using the following […]

The post Parallel plans can be selected with scalar UDF’s appeared first on SQLGrease SQL Server Performance tips.

What’s my parallel query waiting on?

franklinyamamoto — Wed, 22 Oct 2025 17:18:25 +0000

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 […]

The post What’s my parallel query waiting on? appeared first on SQLGrease SQL Server Performance tips.

Improving performance of SUBSTRING queries

franklinyamamoto — Sun, 19 Oct 2025 18:51:08 +0000

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 […]

The post Improving performance of SUBSTRING queries appeared first on SQLGrease SQL Server Performance tips.

Execution plan shortcuts causing random long executions

franklinyamamoto — Mon, 06 Oct 2025 17:57:09 +0000

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 […]

The post Execution plan shortcuts causing random long executions appeared first on SQLGrease SQL Server Performance tips.

SQL Server compile locks

franklinyamamoto — Thu, 02 Oct 2025 18:10:35 +0000

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 […]

The post SQL Server compile locks appeared first on SQLGrease SQL Server Performance tips.

Are you searching for a Precise I3 replacement?

franklinyamamoto — Thu, 02 Oct 2025 16:48:20 +0000

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 […]

The post Are you searching for a Precise I3 replacement? appeared first on SQLGrease SQL Server Performance tips.