Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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: Query plan

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.
share|improve this question
    
Could you please include the execution plan? CTRL+M in the query window before execution. –  Craig Efrein Mar 17 at 9:31
2  
Could it be a locking problem? DML statements from other sessions blocking the select on the table (which is the default behavior in SQL Server 2005) –  a_horse_with_no_name Mar 17 at 9:44
    
There aren't any -known- DML statements, which is the only cause I can think of, but we're still investigating this. The execution plan is added to the question. –  Dag Mar 17 at 10:39
    
From what I've read, this is just one of those trivial execution plans that MSSQL uses to avoid reading heaps and indexes when the optimizer knows there are no rows to be returned –  Craig Efrein Mar 17 at 10:41
1  
Saw a case that looked much like this. It turned out to be triggering update statistics (auto update statistics was turned on and the maintenance plan was broken). –  Joshua Mar 17 at 15:15
add comment

2 Answers 2

up vote 13 down vote accepted

It appears as though even with a ... WHERE 0 = 1 clause that there will still be a requirement for an intent shared (IS) lock on the table. Let's prove this:

I will start out by creating a test table:

use TestDb1;
go

create table dbo.MyTestTable1
(
    Id int identity(1, 1) not null,
    SomeInt int not null
);
go

insert into dbo.MyTestTable1 (SomeInt)
values (10), (20), (30), (40), (50);
go

Now that I have my test table, in one session (query window) I'm going to execute the following to put an exclusive (X) lock on dbo.MyTestTable1:

use TestDb1;
go

begin tran;
    select
        Id, SomeInt
    from dbo.MyTestTable1 with (tablockx);
--commit tran;

I can verify the exclusive lock by looking at the sys.dm_tran_locks DMV. Then in another session (new query window) I do exactly what your query does:

use TestDb1;
go

select
    Id, SomeInt
from dbo.MyTestTable1
where 0 = 1;

At first glance I see that it isn't completing. Looking at sys.dm_exec_requests, I see exactly why this is the case:

select
    r.session_id,
    r.status,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    r.blocking_session_id
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.sql_handle) st
where st.text like '%where 0 = 1%'
and r.session_id <> @@spid;

enter image description here

I can see here that my ... WHERE 0 = 1 query is waiting on an IS lock for this object (that object_id translates to dbo.MyTestTable1).

I am by no means saying that concurrency is your problem, but by the sounds of it you are exhibiting the symptoms. The example above is to prove that your aren't exempt from locking and blocking even with a WHERE clause that'll never return data.

All we can do is guess, so what you need to do when it's "taking a long time" is to see exactly what that request is doing that is taking so long. If it's waiting on something, then see what it's waiting on.

share|improve this answer
1  
You're right, we also found the offending query now with a simple sp_who2 since we could consistently reproduce the problem. We were blinded by the expectation that there shouldn't be any locks placed by the query and forgot checking the basics. Thanks! –  Dag Mar 17 at 12:51
add comment

Depending on how spiky and thread demanding your queries are, your system might simply be queuing that query. Default worker count (i.e. number of simultaneous SQL server threads) for your setup should be about 700.

Check out sys.dm_os_schedulers and sys.dm_os_waiting_tasks to see if that could be a problem.

share|improve this answer
    
A good suggestion, but the problem ended up being a silly lock after all. –  Dag Mar 17 at 12:54
    
I wasn't really convinced, since 700 workers is plenty, but it was easy to check (and thus exclude) –  Sascha Rambeaud Mar 17 at 23:27
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.