July 10, 2009 at 1:03 pm
Hi All,
Technology: SQL Server 2005
Script : Normal View
Brief about view:
We have written a view which almost reruns lakhs of records with 45 columns including int ,varchar,nvarchar datatype etc also this view is calling different functions to get the data.It has lots of joins in it it also uses many CTE.It normally taken 40 mins to run and get data.
Databases:
We have two databases(T & R) and replication is setup between them.Replication happens two times in a day.T stands for normal database and R stands for Replicated database.This view runs on R database.Both databases are on different server
Problem:
some time view runs for 2 hours and not returning any data....I dont know but most of the time it happen on friday...its strange 🙂
Our Analysis:
We have analysed it there is no locking problem in it.
Pleas suggest what to do ??
July 10, 2009 at 1:23 pm
What's the view for?
(Depending on that, I may have different suggestions for different purposes.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 10, 2009 at 1:27 pm
You mentioned the word "function" in there in conjunction with returning data. Without specifics it's going to be hard to be useful, but in something complicated like what you are describing, functions could easily sink your performance.
You may consider simply dropping the function out and see what your performance looks like. If you see big improvement, then perhaps inline whatever the function is doing directly into the query.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 10, 2009 at 1:32 pm
It would help to see the DDL for the base tables (including indexes) and the view. Anything we might do without those would just be shots in the dark.
July 10, 2009 at 3:10 pm
Thanks for both of yours reply.....
The view is basically used to extract the data from our system and send to other system.
The strange things is that the view normally runs in 40 mins and get all the data but some times it runs for 2 hours and not giving any data at all. We have other views also in the system which are working fine.The only difference I found in this view and other views is that, this view is made up of many CTEs
July 10, 2009 at 3:39 pm
Please post the view's definitions, table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply