SQL Server Performance

view hangs when called thro dts

Discussion in 'SQL Server DTS-Related Questions' started by ramasubbup, Feb 28, 2006.

  1. ramasubbup New Member

    Hi,

    I have a job which calls a DTS package
    This DTS package runs a view and inserts the result to a table

    The job step which calls the DTS package hangs occasionally. If the view is run in query analyzer at this time, it executes without any issues

    If the job is stopped and started again, then it works fine

    No other process is blocking this process and no orphaned transactions exists

    When the job hangs,I see a number of threads (around 50) for this process, out of which around 10 threads are showing as ‘runnable#%92 and others as ‘sleeping#%92



    Structure of the view v1
    -------------------------
    Create view v1 as

    Select * from v2 inner join table1…..



    Structure of view V2
    ----------------------

    Create view v2 as

    Select * from V3 inner join v4 …..



    In short there are nested views up to four levels

    Any pointers will be helpful

  2. dineshasanka Moderator

  3. satya Moderator

    CHeck the DTS package log about where it is hanging and using PROFILER during that operation might give some clues.

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page