SQL Server Slow Performance
-
Posted on May 31, 2009 by Derek Dieter
-
0
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
Here is an example of the query that hung on SQL Server 2008:
[cc lang=”sql”]
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)
)
)
[/cc]
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.
[cc lang="sql"]
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
)
[/cc]
- Comments (RSS)
- Trackback
- Permalink
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 ?
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?
