SQL Query Hangs

If have a table called Stk with about 39,000 records.
The Stk table has a primary key field called StockNo.
There is also a (Non-Unique) index on ParentStockNo.
Using SQL 2008 R2.

This query hangs...
    Select * from Stk where ParentStockNo=27024

This query returns the number 23 instantly
    Select count(*) from Stk where ParentStockNo=27024

Other users may be using the system.  But currently no-one is doing editing.  The Database is a test database  - so not high volume yet & only me and another perseon testing it).

What is going on?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

There will be a lock on your table, maybe by another query-window in your management studio where you didn't had done a commit (or GO) to release the lock.

How to see what is locked and by what
  -- Do not lock anything, and do not get held up by any locks. 

   -- If there are blocked processes...
   IF EXISTS(SELECT 1 FROM sys.sysprocesses WHERE 
      blocked != 0) 

      -- Identify the root-blocking spid(s)
      SELECT  distinct t1.spid  AS [Root blocking spids]
         , t1.[loginame] AS [Owner]
         , master.dbo.dba_GetSQLForSpid(t1.spid) AS 
            'SQL Text' 
         , t1.[cpu]
         , t1.[physical_io]
         , DatabaseName = DB_NAME(t1.[dbid])
         , t1.[program_name]
         , t1.[hostname]
         , t1.[status]
         , t1.[cmd]
         , t1.[blocked]
         , t1.[ecid] 
      FROM  sys.sysprocesses t1, sys.sysprocesses t2
      WHERE t1.spid = t2.blocked
        AND t1.ecid = t2.ecid
        AND t1.blocked = 0 
      ORDER BY t1.spid, t1.ecid

      -- Identify the spids being blocked.
      SELECT t2.spid AS 'Blocked spid'
         , t2.blocked AS 'Blocked By'
         , t2.[loginame] AS [Owner]
         , master.dbo.dba_GetSQLForSpid(t2.spid) AS 
            'SQL Text' 
         , t2.[cpu]
         , t2.[physical_io]
         , DatabaseName = DB_NAME(t2.[dbid])
         , t2.[program_name]
         , t2.[hostname]
         , t2.[status]
         , t2.[cmd]
         , t2.ecid
      FROM sys.sysprocesses t1, sys.sysprocesses t2 
      WHERE t1.spid = t2.blocked
        AND t1.ecid = t2.ecid
      ORDER BY t2.blocked, t2.spid, t2.ecid

Open in new window

enigmasolutionsAuthor Commented:
when I ran your script I got this error...

Msg 4121, Level 16, State 1, Line 13
Cannot find either column "master" or the user-defined function or aggregate "master.dbo.dba_GetSQLForSpid", or the name is ambiguous.
enigmasolutionsAuthor Commented:

The blocked script was this...

    select * from Stk where ParentStockNo=27024

And this was the blocking script
(which has run fine millions of times on other installations of our software).

  SET @PHASE = 1
--SaveUserFormSettings  (StkTreeForm)
UPDATE UserSettings SET DefValue='898/846'
WHERE UserNo=49
AND FormName='StkTreeForm'
AND CompName='StkTreeForm'
IF @@RowCount=0
  INSERT INTO UserSettings Values(49,
  SET @PHASE = 2
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Obvious, yes.
If you leave that function out you can see if there are blockings, you only don't get the text of the sql

Other ways
In management studio in Activity Monitor or by using the sp_who2 -procedure

Details see
enigmasolutionsAuthor Commented:
I get this error now when I run this SQL...
  select * from Stk where ParentStockNo=27024

"Unknown token received from SQL Server"
enigmasolutionsAuthor Commented:
BTW, I have kept the blocking connection "connected" for the moment whilst testing.  It is not "doing" anything though.  ie no queries are running.
It safer to use try catch to catch an error , the commit is then in the normal flow and a the rolback in the catch. Getting the error-number and message must always be the first thing to do in your catch-block.

Peter KipropCommented:
Hi enigmasolutions ,

Try running the below querry and see if there will be aa output

  Select * from Stk (Nolock) where ParentStockNo=27024
enigmasolutionsAuthor Commented:
Interesting the query runs instanntly with NOLOCK.

So there is a lock on the Stk Table within SPID 54.  


damn I accidentally stopped my app running SPID 54.


but I am on to something...

... a process in my application is causing the error.  At least now I know the source of the trouble.  

Stay tuned.  Thanks for everyones help so far.  I will be back in a while.  Points will go to contributors.
The blocking proces is contains an insert and an update .... if that isn't putting a lock on your table what else will?

The question is why did it keep the lock?
enigmasolutionsAuthor Commented:
OK I worked it out.

Pthepebble, that was an awesome tip.  Helped me to realise it was the Stk table that had the lock on it (and nothing to do with the UserSettings Script).

jogos, that was also an awesome tip (actually I knew about it... but it gave me ideas).

Now here are some interesting facts that I learned...

A) jogos' tip does not report on the blocking script, but rather the last script to be run by the blacking SPID!!!!!  So the usersettings query had nothing to do with the problem.  Unfortunately I don't think there is such a script that does this job properly.

B) The actual blocking script (see below) had various problems:
* index hints not specified
* parameter sniffing not avoided
* poor sequencing of SQL statements

Comments for Microsoft:
1) It would be nice if there was a simple utility procedure that does what jogos sugested except that it reported the blacking script?
2) Sometimes SQL does not use indexes when it should.  MS has more work to do when it comes to smarter interpreting of SQL.
3) Sometimes execution plans for stored procedure get stuffed up and stay that way until you delete and re-save the SP

So... here is the blocking script that was timing out (taking more than 30 seconds).  BTW, this script updates a tree structure stock/cetegory system for a website.  The query updates a branch with about 3000 records in a Stk table with about 50,000 records.


And... here is the script after I made some changes (to help MSSQL find indexes, table joins and, parameters etc).


I got my improved script to run instantly (0 seconds).  

Actually I got it down to 6 seconds, then I started working on the UpdateStkPar stored procedure (not shown) that runs at the end.  I was playing with it, then deleted it, then restored it back as it was, and suddenly my script ran instantly.  Go figure???

Now a really odd thing is that if I re-run the ORIGINAL script it now runs in 1 second.  But I swear I have been solely working on this one script (and the stored proc) the whole time & there are no other users logged in or processes running.  It is like MSSQL just woke up.  Unfortunately things started to work before I got deeply into SQL profiler (to learn more).  Anyway I am convinced that MSSQL just went Ga Ga.  (And if you don't believe MS SQL Server goes GaGa, then type "SQL Parameter Sniffing" in google).

Anyway... in summary....
* Blocking scripts are sometimes not that easy to identify with an SQL statement
* I need to be careful to construct "MS SQL Friendly" scripts

Finally, thank you to all contributors.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
enigmasolutionsAuthor Commented:
FYI - I believe that the 6 second performance delay with my UpdateStkPar stored procedure was due to something like Parameter Sniffing (or Garbage Collection in SQL).  The odd thing is that there are no parameters in this proc.  But it is clear to me that SQL's cached execution plan for this proc was stuffed up.  So deleting it and re-creatign it fixed the execution plan.  

Also, I am fairly convinced that the delay for my original script must have been caused by a similar problem.
Anthony PerkinsCommented:
2) Sometimes SQL does not use indexes when it should.  MS has more work to do when it comes to smarter interpreting of SQL.
I suggest you pick up a good book on SQL Server, such as SQL Server 2008 Internals by Kalen Delaney and in particular the Query Optimizer chapter written by Connor Cunningham (if you do not know those names you may want to Google them).

In any case, all of this could have been avoided very simply by replacing the BEGIN TRANSACTION..UPDATE...INSERT... COMMIT/ROLLBACK TRANSACTION with a MERGE SQL statement.  There is just no need for a transaction here.
enigmasolutionsAuthor Commented:

Awesome tips thank you.  The MERGE command is particularly interesting.

I will take a look at that book soon / buy it. Thanks.
enigmasolutionsAuthor Commented:
Fantastic help from all experts.  Thank you all very much.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.