View runs for ages but the internal select executes in seconds

  • I have an issue that bugs me for a while now.

    When I create a view it runs in seconds, as expected. After a while (3-4 weeks) the view breaks: running select * from V_MyView from management studio the query is in execution until I manually stop it (after several minutes) and no results are shown.
    The side effect is that my SSRS reports that use V_MyView are unable to display required data.
    - The sp_who2 doesn't show flags in "blocked by" column for V_MyView.
    - V_MyView gets the data by joining few simple tables.
    - I can solve the issue by dropping and re-creating the view but I'd like to understand why this is happening.
    - Recently the issue repeated with another view in the same database.
    - Both views join tables that are passed by synonyms (synonyms point to tables referenced in a linked server).

    Any clues?
    Thanks

  • Statistics stale?

  • What could be happening is just what Joe mentioned, stale statistics on either server.  The result is that more data has to be pulled from the table over the linked server before the join is actually done on the local server.  Would it be possible to see the view currently having the problem?

  • Would need to know which table(s) are the ones referenced by the synonym(s).

  • If you're literally using "*" in the view, refreshing the view is often needed too.  Not sure if that works for remote tables, but presumably it would (or SQL would give you some type of error stating that it can't be done).

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Thank you all for the answers:
    @scottpletcher: no worries, no '*' in the view 🙂
    @Joe_Torre: Before posting I run sp_updatestats but nothing has changed. Am I missing something else?

    However, I just got back in the office (on monday morning) and now I can query the views instantly,  so I don't know how to 'debug' the issue at this point.

    @Lynn_Pettis: a rough blueprint of the view is something along the lines of:
    CREATE VIEW [dbo].[V_MYVIEW]
    AS
    SELECT col1, col2, col3
    FROM
    ( SELECT colA, colB, colC
        FROM (
            SELECT COLONE, COLTWO, COLTHREE FROM SYNONYM_TABLE
        ) T1
    ) T2

    so, IMHO nothing special.
    The second view is a bit more complex and has few joins and outer apply statements.

    Any ideas on how can I handle the issue next time when it happens (and I'm afraid it will)?

  • Data Cruncher - Monday, March 19, 2018 1:59 AM

    Thank you all for the answers:
    @scottpletcher: no worries, no '*' in the view 🙂
    @Joe_Torre: Before posting I run sp_updatestats but nothing has changed. Am I missing something else?

    However, I just got back in the office (on monday morning) and now I can query the views instantly,  so I don't know how to 'debug' the issue at this point.

    @Lynn_Pettis: a rough blueprint of the view is something along the lines of:
    CREATE VIEW [dbo].[V_MYVIEW]
    AS
    SELECT col1, col2, col3
    FROM
    ( SELECT colA, colB, colC
        FROM (
            SELECT COLONE, COLTWO, COLTHREE FROM SYNONYM_TABLE
        ) T1
    ) T2

    so, IMHO nothing special.
    The second view is a bit more complex and has few joins and outer apply statements.

    Any ideas on how can I handle the issue next time when it happens (and I'm afraid it will)?

    Sorry, but this doesn't really help.  Based on this you are pulling all rows of data from the linked server.  Without seeing the actual code there isn't a lot we can do to help.

Viewing 7 posts - 1 through 6 (of 6 total)

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