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.

The other day I ran into a very odd issue that when I execute this query:

select * from sys.dm_exec_cached_plans cp
inner join sys.dm_exec_procedure_stats ps
    on ps.plan_handle = cp.plan_handle

It would execute for a long time (10 minutes before I gave up which is much longer than I expected). At the time I ran it I had recently cleared the procedure cache so there were likely no more than 20-30 cached plans.

When I cancel the query it continues to run and never cancels.

I then tried to use the KILL command on the spid. When I look at the query in sys.dm_exec_requests the last wait type was SOS_SCHEDULER_YEILD and the command is KILL/ROLLBACK. Once again I waited for a long time (10 minutes or so)

In order to kill the query I tried to stop the SQL Server service through SQL Configuration Manager. This hung. The only way I could find to stop the query or the service was by using sysinterals tool pskill. Not ideal but it a test installation so no big deal.

I tried to search to see if anyone else has seen this but can't find a search criteria that filters out the noise.

Ive seen this on two boxes in my environment, one SQL Server 2008 R2 SP1 and another SQL Server 2008.

I executed this again just now and it returned quickly (milliseconds).

Can anyone shed any light about this? Am I missing something obvious or is this a bug.

[Edit: adding based on an answer from luvizuvi] I could find no obvious blocking processes or other bottlenecks related to memory, disk, cpu, other processes, etc. When looking at sys.dm_exec_requests this was the only command in a running state.

Thanks.

share|improve this question

migrated from stackoverflow.com Jan 19 '12 at 11:34

This question came from our site for professional and enthusiast programmers.

    
Did you look at the wait type before killing the process? –  Martin Smith Jan 19 '12 at 11:44
    
Sadly, I did not. –  Dale Burnett Jan 20 '12 at 10:52

3 Answers 3

I ran into the problem and found the bug article on MSDN. It has been fixed with cumulative updates for SQL 2008 and 2012. http://support.microsoft.com/kb/2803799

share|improve this answer
    
I think that kb2803799 could be the underlying cause. A "non-yielding scheduler" would fit the bill. If this is the case, there may be references to the non-yielding scheduler in the SQL Server error log. –  sql_handle Sep 10 '13 at 20:42

Are you sure it was 'running' (= actually doing sth) or was it just blocked?

Additionally, when you kill a running process, everything that the process did up to that point has to be rolled back (= everything that it wrote into the log file has to be undone).

All this behavior is certainly not a bug but the way SQL Server is designed to work. If the above query takes a long time, this hints to a variety of issues, problems or even just facts that could be going on on your server: (dead) lock issues, memory issues, disk issues, simply a lot of traffic, a lot of adhoc-queries being executed, slow cpu...

Look at this issue with a DBA. There may be many many reasons why this is happening. But one thing is for sure, it's not a bug in SQL Server.

share|improve this answer
1  
Disagree. If a simple SELECT query can't be killed without forcibly restarting the service it definitely sounds buggy to me. –  Martin Smith Jan 19 '12 at 12:44
    
I understand how SQL Server is supposed to work. I'm asking this question here because this behavior is incredibly odd to me. There was no load extra on the system. There were no other processes running on the system that I'm aware of, no memory issues, no disk issues, no other ad hoc queries being run, nothing at all that I could identify as a bottleneck or blocking. The first time I saw it I wrote it off as an odd issued related to that installation. I've seen it now on two systems. There should have been no more that 20 or 30 plans in the plan cache. –  Dale Burnett Jan 20 '12 at 10:58
    
I'm sorry. I didn't want to sound condescending. English is not my mother tongue, so some things might come out wrong. I just wanted to point out, that usually there is a reason for queries just 'hanging' forever. And especially if you have now two machines with this behavior, a sql server "bug" is unlikely. To me it sounds very much like a locking issue. Have a look at sys.sysprocesses table, especially the blockedby column, when this happens again. –  Heino Zunzer Jan 20 '12 at 17:14
    
@Heino - If it was just waiting on a lock any theories as to what is going on after the spid has been killed? There shouldn't be much/anything to roll back surely? –  Martin Smith Jan 20 '12 at 17:22
    
@Heino - I concur, I don't usually jump straight to "bug" but having found one before I don't rule it out completely. I don't understand what would be locked but I don't rule that out (and blocked for 10 to 20 mintues at that.) Why it gets hung up when I'm killing the spid and rolling it back is what I consider the real bizarre behavior. I didn't think about sys.sysprocesses. At the time I was running the query to troubleshoot a stored proc so I didn't have time to stop everything and troubleshoot this. I wasn't able to investigate as in depth as I'd liked to have. –  Dale Burnett Jan 21 '12 at 10:42

The rows in the cache plans dmv can at certain times have locks on them so running the queries in READ UNCOMMITTED will prevent this issue. The locks are held by long running queries and some other processes. See this blog for reference to this issue.

share|improve this answer
    
Any more details on this? On the query in your linked post I can see that it does acquire locks such as S lock on DATABASE: 23 [PLANGUIDE] (and isolation levels don't seem to make any difference). With the OP's query I just see some locks being taken out on objects in the resource database. –  Martin Smith Jan 20 '12 at 11:59
    
I'll give READ UNCOMMITTED a try next time this happens. Fortunately other spids and connections seem to still work. Even if blocking locks are the problem I don't understand what would keep me from being unable to cancel the transaction or roll it back. There were only 20-30 plans in the plan cache so it didn't have a lot to work on. –  Dale Burnett Jan 21 '12 at 10:50

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.