Since early SQL Server versions Page Life Expectancy (PLE) was used as a benchmark of performance, or indicator of a problem.  PLE is a performance counter that can be misleading.   PLE is a measure of how long pages have been cached in memory.  If you use a performance monitoring tool that showcases this metric your […]

On occasion you might find the need to force a query plan.  As time goes on you might see the plan you thought was forced revert to a different plan.  This means you likely have a forced plan failure.  In this post I’ll give you some tips on how to spot if you have forced […]

If you have a query that shows it is being blocked by session ID -2, this means you have an orphaned distributed transaction.  You might see this surface in Activity Monitor or in To kill the orphaned transaction you will first need to get the transaction GUID.  You can find the owner GUID of the […]

If you are using a query wrapped in IF EXISTS(…) and you find that you need to force a plan, you might notice the plan won’t force.  You’ll see this show up in sys.query_store_plan showing is_forced_plan = 1, but there will also be a force_failure_count value > 0 as well as a last_force_failure_description of GENERAL_FAILURE […]

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

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

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

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

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

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