SQL Server Slow Performance

This post deals with a random hanging that sometimes happens with SQL Server 2005+. In order to troubleshoot SQL Server Slowness, go here.

The introduction of the new SQL Server 2005 Query Optimization engine has brought great things (including statement-level caching and smarter execution plan generation). There is however a little more overhead with the advent of this new technology. Aside from taking longer to generate an execution plan, I have noticed two separate instances where a query would appear to intermittently hang. From a database engine perspective however, the query is not hanging but generating an execution plan.

The two instances I’ve witnessed this in were in both SQL Server 2005 (sp2), and also now in SQL Server 2008. Both procedures were relatively small, however somewhat complex in their where clauses.

The physical indicators in both instances where very high CPU usage and very low IO usage for one particular SPID which we gathered by executing sp_who2.

High CPU, little or no IO

High CPU, little or no IO

Here is an example of the query that hung on SQL Server 2008:

SET @IsTrue =
(
    SELECT COUNT(1)
    FROM dbo.table1 t1
    JOIN dbo.table2 t2
        ON t1.ColumnID = t2.ColumnID
    WHERE (
            t2.ID = @ID
            AND t2.SomeDate < GETDATE()
          )
          OR EXISTS
          (
            SELECT 1
            FROM dbo.Table3
            WHERE ID = @ID
            AND AlternateID IN
            (1,2,3,4,6)
          )
)

Once the query would hang in generating the execution plan, the effect seemed to snowball and cause other executing instances of the same procedure to hang. Also, no blocking was occuring in the database, and these SPIDs did not show as ‘Runnable’ in sp_who2.

The fix we found was to make the query easier to digest for the optimizer. First, the GETDATE() within the where clause possibly made the query too non-deterministic in order for the optimizer to cache it’s execution plan. So we replaced the GETDATE() with a variable and set the variable to GETDATE(). This likely allowed the optimizer to ‘sniff’ the variable and determine a possible range of values in which to base an execution plan upon. Second, we removed the OR in the where clause and UNION’ed the two statements together. These two changes fixed our issue.

DECLARE @Today datetime = GETDATE()                        
                           
SET @IsTrue =
(
    SELECT SUM(t.Flag)
    FROM(
        SELECT COUNT(1) AS Flag
        FROM dbo.table1 t1
        JOIN dbo.table1 t1
        ON t1.ColumnID = t2.ColumnID
        WHERE t2.SomeDate < @Today

        UNION

        SELECT 1 AS Flag
        FROM dbo.Table3
        WHERE ID = @ID
        AND AlternateID IN
        (1,2,3,4,6)
    ) t
)

2 comments
Mujtaba Kably 12 Jun 2015 at 7:29 am

I am having sort of a opposite problem,
some of my queries, most of them simple Select Queries.
These Queries have very low CPU Time but Very High DISK IO, sometimes it goes over 7000.
i think it is because of faults in the HDD,
can u give me your opinion ?

James 10 May 2011 at 4:26 pm

In regards to your second ‘optimized’ example. I got a little confused initially, but then realized there might have been typo in your example:


SELECT COUNT(1) AS Flag
FROM dbo.table1 t1
JOIN dbo.table1 t1
ON t1.ColumnID = t2.ColumnID
WHERE t2.SomeDate < @Today

the Join statement should be JOIN dbo.table2 t2 surely?

Featured Articles

 Site Author

  • Thanks for visiting!