Post reply

tempdb extent locks running Views

  • April 20, 2006 at 10:36 am

    #167850

    I am getting tempdb extent locks when running a view, which is causing cascading

    locking issues and eventually brings the server to it's knees.  It's a big server too, 4 procs,

    8 GB ram, SQL 2000, patched and SP'd

    I set MAXDOP 1 in the Crystal Report that calls the suspect view, and it

    hasn't happened since (knock on wood).

    Has anyone heard of these tempdb extent locks being caused by "bad parallelism"

  • April 21, 2006 at 3:23 am

    #633441

    Bad parallelism seems to me a quite broad term

    I have encountered excessive tempdb locking quite some time ago but not because of running a view. Since you also referr to solving your problem in Crystal Report makes me doubt that you are jjust doing a SELECT ... FROM myView. How are you actually using the view?

    My locking problem was caused by:

    SELECT col1, col2 ..., colN INTO #myTab FROM bigTab

    When you do a select into you in effect are placing lock on system tabels for the WHOLE duration of your query.

    This was solved by shortening the locks by either creating the table first (with a CREATE TABLE) and then doing a INSERT INTO or if you are lazy you could do a SELECT TOP 0 INTO ... to get your table created and then do your INSERT INTO.

    But since your MAXDOP 1 solved your problem you have probably some other issues.

    Hans

  • April 21, 2006 at 3:56 am

    #633446

    in general terms poorly optimised sql, and views are regular culprits I've found ( not beacuse they are views but often views are written to disguise or simplyfy the underlying joins etc. - if you took the code out of the views would you really want to write that ?? - but that's another subject area )  if the sql is poor the optimiser will atempt to use parallel threads to improve performance - when the sql is poor the threads can block and wait upon themselves, look for cxpacket waits in sysprocesses. I guess it was your problem cos the maxdop removed it - you might be lacking some indexes on your view

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

Viewing 3 posts - 1 through 3 (of 3 total)

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