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. 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 […]Continue reading ...
- How to cache stored procedure results using a hash key There are a lot of different design patterns that lend themselves to creating
- SQL Server Database Optimization Guide In the troubleshooting guide we went over the different physical bottlenecks that can
- Yet Another Temp Tables Vs Table Variables Article The debate whether to use temp tables or table variables is an old
- Using Union Instead of OR Sometimes slow queries can be rectified by changing the query around a bit.