A query like the one below that is guaranteed not to return any rows, takes anything from 0 to 160 seconds on one of our servers:
select col1, col2, col3 from tab1 where 0 = 1
Two weeks ago, this happened six times in a 48 hr interval. Last week the same query took ~0 seconds. I have logs of our application's SQLs but haven't found any suspects yet. Besides, I thought a top 0/where 0=1 type query never hit the data pages, so it should be resistant to row/page/table-level data locks? The schema is not touched by any (known) SQLs.
Since the problem is not consistent, and the server is under very heavy load I'd like to understand the theory behind what is happening before attaching SQL profiler. Other queries run without problems during these delays. A known problem in the application is a high number of dynamically created SQL queries - around 200k unique queries of 850k total (logged) queries over a period of 48 hrs, can this cause problems like this?
The server is running SQL Server 2005 standard edition, 96 GB RAM, disks on SAN and 4 CPUs/16 cores. Database files and filegroups are well optimized and shouldn't be a problem (but we're looking into this separately).
Any pointers where to look is greatly appreciated.
Edit: Perfect! Replayed the query to add the execution plan, and it took 1min 35secs. Here's the execution plan and screenshot showing the query duration:
Edit 2: statistics time details for a second run. Seems to be consistently slow right now, so we'll be attaching profiler and perfmon:
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 97402 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.