SQL view suddenly timeout expired!!

Hi Experts,

I'm suddenly getting an error "Timeout Expired" when opening a view.
I know earlier in the day it was working fine.
What should I do?

See attached.

Following is the view's SQL.
SELECT     TOP (99.9999) PERCENT dbo.View_OrientSchedOpenFillUnionQry.ID, dbo.View_OrientSchedOpenFillUnionQry.EmployeeID, 
                      dbo.View_OrientSchedOpenFillUnionQry.FacilityID, dbo.View_OrientSchedOpenFillUnionQry.FacilityDate, dbo.View_OrientSchedOpenFillUnionQry.Result, 
                      dbo.View_OrientSchedOpenFillUnionQry.FinalConfirmationYN, dbo.View_OrientSchedOpenFillUnionQry.FinalConfirmation, 
                      dbo.View_OrientSchedOpenFillUnionQry.FinalConfirmationDate, dbo.View_OrientSchedOpenFillUnionQry.FinalConfirmationInitial, 
                      dbo.View_OrientSchedOpenFillUnionQry.FinalConfirmationTime, dbo.View_OrientSchedOpenFillUnionQry.FileApproved, 
                      dbo.View_OrientSchedOpenFillUnionQry.ApprovalStatusDate, dbo.View_OrientSchedOpenFillUnionQry.CompleteFile, 
                      dbo.View_OrientSchedOpenFillUnionQry.CompleteFileDate, dbo.View_OrientSchedOpenFillUnionQry.DocMissing, 
                      dbo.View_OrientSchedOpenFillUnionQry.NotesPayment, dbo.View_OrientSchedOpenFillUnionQry.DateEntered, dbo.View_OrientSchedOpenFillUnionQry.Initial, 
                      ISNULL(dbo.Employeestbl.LastName, '') + ' ' + ISNULL(dbo.Employeestbl.FirstName, '') AS EmployeeName, dbo.Employeestbl.Title, 
                      dbo.Employeestbl.Initial AS HireInitial, dbo.TovInfo.Day AS LastDate, dbo.TovInfo.Facility1, dbo.Facilitiestbl.Name AS FacilityName, 
                      dbo.Employeestbl.ts AS EmployeeTS, dbo.Facilitiestbl.ts, dbo.View_OrientSchedOpenFillUnionQry.HC, dbo.View_OrientSchedOpenFillUnionQry.TableName, 
                      dbo.View_OrientSchedOpenFillUnionQry.reporttype, dbo.View_OrientSchedOpenFillUnionQry.Notes, dbo.Facilitiestbl.County, 
                      dbo.View_OrientSchedOpenFillUnionQry.Staffer, dbo.View_OrientSchedOpenFillUnionQry.SubmittedDate, dbo.View_OrientSchedOpenFillUnionQry.StafferDate
FROM         dbo.View_OrientSchedOpenFillUnionQry LEFT OUTER JOIN
                      dbo.Facilitiestbl ON dbo.View_OrientSchedOpenFillUnionQry.FacilityID = dbo.Facilitiestbl.ID LEFT OUTER JOIN
                      dbo.Employeestbl ON dbo.View_OrientSchedOpenFillUnionQry.EmployeeID = dbo.Employeestbl.ID LEFT OUTER JOIN
                      dbo.TovInfo ON dbo.View_OrientSchedOpenFillUnionQry.EmployeeID = dbo.TovInfo.EmployeeID
ORDER BY FacilityName, dbo.View_OrientSchedOpenFillUnionQry.FacilityDate DESC

Open in new window

Untitled.png
Untitled.png
LVL 6
bfuchsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
Right click on the DB Server -> Connections -> Remote Query TimeOUT ( In seconds, 0 = noTimeOUT)
-> SET 0
and try again.


Also are you guys updating stats regularly on the server as the part of DB Maintenance.?
bfuchsAuthor Commented:
@Pawan,

I changed that now (see attached).
However still getting Timeout expired.

Also are you guys updating stats regularly on the server as the part of DB Maintenance.?
Not really, what is the recommended?

Thanks,
Ben
Untitled.png
Pawan KumarDatabase ExpertCommented:
Close the SSMS and connect again and then try again...It should take affect.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

bfuchsAuthor Commented:
No, that didnt help!
What else could it be?
Should I change back value to 600?

Thanks,
Ben
bfuchsAuthor Commented:
When I remove the order by clause it works fine.
Trying to execute this SQL in new query window and its taking way too long..
Something must have happen today as it suddenly stopped to work!!

Thanks,
Ben
Pawan KumarDatabase ExpertCommented:
How are you opening the view?

You can use below-

select definition
from sys.objects     o
join sys.sql_modules m on m.object_id = o.object_id
where o.object_id = object_id( 'dbo.yourVIEWNAME')
  and o.type  = 'V'

or this

SELECT object_definition (OBJECT_ID(N'dbo.yourVIEWNAME'))
Pawan KumarDatabase ExpertCommented:
Stats may be the issue....in that case if it working after removing the where clause...
The SQL Engine may be choosing the sub optimal plan
bfuchsAuthor Commented:
SELECT object_definition (OBJECT_ID(N'dbo.yourVIEWNAME'))
I just get create view with the SQL I posted above.
in that case if it working after removing the where clause...
Not the where clause, the Order by clause.
BTW, its already 16+ min and the query is still running..

What can be done?

I need this fixed ASAP.

Thanks,
Ben
bfuchsAuthor Commented:
Hi Experts,

We had a consultant look into this, I think he just restarted the server and everything is back in track..

Thanks,
Ben

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Pawan KumarDatabase ExpertCommented:
Great.....Did we got any root cause? any blocking thing.. etc..
bfuchsAuthor Commented:
Hi,

No, guess sometimes you need a consultant onsite to solve an issue..

Thanks,
Ben
Pawan KumarDatabase ExpertCommented:
Hi Ben,
You are correct. :)

Thanks,
Pawan
bfuchsAuthor Commented:
Solved.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.