March 16, 2018 at 10:25 am
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
March 16, 2018 at 11:10 am
Statistics stale?
March 16, 2018 at 11:52 am
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?
March 16, 2018 at 12:42 pm
Would need to know which table(s) are the ones referenced by the synonym(s).
March 16, 2018 at 3:33 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 19, 2018 at 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)?
March 19, 2018 at 8:51 am
Data Cruncher - Monday, March 19, 2018 1:59 AMThank 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
) T2so, 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