Solved

SQL Query Hangs

Posted on 2012-03-22
15
1,679 Views
Last Modified: 2012-04-07
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?
0
Comment
Question by:enigmasolutions
15 Comments
 
LVL 25

Assisted Solution

by:jogos
jogos earned 167 total points
ID: 37756051
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. 
   SET TRANSACTION ISOLATION LEVEL READ 
      UNCOMMITTED
 

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

      -- 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
   END

Open in new window

0
 
LVL 1

Author Comment

by:enigmasolutions
ID: 37756056
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.
0
 
LVL 1

Author Comment

by:enigmasolutions
ID: 37756068
OK,

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).


BEGIN TRAN
DECLARE @PHASE INT, @CUSTERRMSG VARCHAR(250)
SET @PHASE = 0
WHILE @PHASE=0
BEGIN
  SET @PHASE = 1
--SaveUserFormSettings  (StkTreeForm)
UPDATE UserSettings SET DefValue='898/846'
WHERE UserNo=49
AND FormName='StkTreeForm'
AND CompName='StkTreeForm'
IF @@RowCount=0
BEGIN
  INSERT INTO UserSettings Values(49,
    'StkTreeForm','StkTreeForm','898/846')
IF @@ERROR<>0 BREAK
 
END
  SET @PHASE = 2
END
IF @PHASE=1
  ROLLBACK TRAN
ELSE IF @PHASE=2
  COMMIT TRAN
0
 
LVL 25

Expert Comment

by:jogos
ID: 37756072
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
http://www.mssqltips.com/sqlservertip/2429/how-to-identify-blocking-in-sql-server-2005-and-2008/
0
 
LVL 1

Author Comment

by:enigmasolutions
ID: 37756094
I get this error now when I run this SQL...
  select * from Stk where ParentStockNo=27024

"Unknown token received from SQL Server"
0
 
LVL 1

Author Comment

by:enigmasolutions
ID: 37756100
BTW, I have kept the blocking connection "connected" for the moment whilst testing.  It is not "doing" anything though.  ie no queries are running.
0
 
LVL 25

Expert Comment

by:jogos
ID: 37756102
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.

http://msdn.microsoft.com/en-us/library/ms175976.aspx
http://msdn.microsoft.com/en-us/library/ms179296.aspx
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 6

Assisted Solution

by:Peter Kiprop
Peter Kiprop earned 166 total points
ID: 37756179
Hi enigmasolutions ,

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

  Select * from Stk (Nolock) where ParentStockNo=27024
0
 
LVL 1

Author Comment

by:enigmasolutions
ID: 37756328
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.
0
 
LVL 25

Expert Comment

by:jogos
ID: 37757185
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?
0
 
LVL 1

Accepted Solution

by:
enigmasolutions earned 0 total points
ID: 37762621
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.

 xxx.sql

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

 yyy.sql

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.
0
 
LVL 1

Author Comment

by:enigmasolutions
ID: 37762655
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.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 167 total points
ID: 37762842
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.
0
 
LVL 1

Author Comment

by:enigmasolutions
ID: 37781502
acperkins

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

I will take a look at that book soon / buy it. Thanks.
0
 
LVL 1

Author Closing Comment

by:enigmasolutions
ID: 37818686
Fantastic help from all experts.  Thank you all very much.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now