Post reply

Question regarding SQL Server SP4

  • August 4, 2005 at 9:09 am

    #363365

    I was hoping someone might be able to shed some light on this...

    We have just moved from SP3a to sp4 on a test server.  After an update stats on the database we are seeing an issue where a process will block itself and eventually become a deadlock victim.

    In this particular case it is a stored procedure that works fine in production and worked in test, but now it will not execute without causing blocking on itself. 

    Any help would be greatly appreciated!

    Thanks

    Erich Brinker

  • August 4, 2005 at 10:01 am

    #579646

    What does this sp do?

    Do you have more than one processor on that server (or hyper threading)?

  • August 4, 2005 at 10:11 am

    #579653

    Not sure what you mean about what the sp does(Far as I know bug fixes and security updates)

    Server has 4 procs 1 gig of ram Windows 2000 SP4 OS

  • August 4, 2005 at 10:12 am

    #579656

    Are you sure the sp is not running ? are you using sp_who2 to check for process blocking itself ?

    FYI, SP4 now checks blocking spid for latch wait in the sysprocesses. The blocking may not necessary be the kind of blocking that we thought was. Please see the link below for more detail and look for feedback from Santeri Voutilainen.

    http://www.sqlnewsgroups.net/group/microsoft.public.sqlserver.server/topic1883.aspx

     

  • August 4, 2005 at 10:20 am

    #579663

    SP was in the context of stored proc .

    Anyways try to add the hint option (Maxdop 1). Sometime the query can block itself if multiple threads are running.

  • August 4, 2005 at 10:30 am

    #579673

    Sorry, been a long day...

     

    Nothing in the Stored Proc that should cause multiple threads. 

  • August 4, 2005 at 10:33 am

    #579682

    I did see the process blocking itself using sp_who2.

    I read the arcticle and had a question for those wiser than myself.  If the stored proc uses a cursor to go through a large table would it make sense it is not giving back the page and locking itself?

     

    Thanks again for the help..

  • August 4, 2005 at 10:39 am

    #579688

    It can't be blocking itself (same spid for blocked and blocked by??) with only one thread.... I'd really try the maxdop option if I were you... especially since I have no other idea .

  • August 4, 2005 at 10:41 am

    #579693

    I guess the cursor thing is possible, but I haven't seen that to be an issue. I thought that cursors would grab a copy of the data to hold it, not lock the pages.

  • August 4, 2005 at 10:52 am

    #579711

    I was thinking the cursor grabs the data and keeps it in temp table in TempDB and if the data is large enough that the temp table spans across pages it may be accessing but not giving back.

    I fully admit this is borderline over my understanding and I may be completely wrong.

     

  • August 4, 2005 at 10:56 am

    #579718

    I'm the third with the same understanding of cursors... so we must be right.

    Can you run the profiler and try to get more info?

  • August 4, 2005 at 10:58 am

    #579720

    I will try the maxdop option but wondered...The query works fine in production sp3a and maxdop is set to 0 on the server...Are you saying to try this with the changes introduced in SP4?

  • August 4, 2005 at 11:02 am

    #579725

    I think I read sometime like this and it might have been related to SP4 but I'm not even not too sure about that to begin to make a statement .

    I'm sure about the self blocking and maxdop though.

  • August 5, 2005 at 5:35 pm

    #580216

    I came across same issue (see my previous post at) http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=182428

    This was happening on a dual-processor server and does not occur on a single-processor server after SP4 upgrade.  Setting MAXDOP = 1 fixed the issue.


    Joseph

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply