We help IT Professionals succeed at work.

SQL view suddenly timeout expired!!

bfuchs
bfuchs asked
on
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
Comment
Watch Question

View Solutions Only

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
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.?
bfuchs

Author

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 Expert
Awarded 2016
Top Expert 2016

Commented:
Close the SSMS and connect again and then try again...It should take affect.
bfuchs

Author

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

Thanks,
Ben
bfuchs

Author

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 Expert
Awarded 2016
Top Expert 2016

Commented:
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 Expert
Awarded 2016
Top Expert 2016
Commented:
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
bfuchs

Author

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
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
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Great.....Did we got any root cause? any blocking thing.. etc..
bfuchs

Author

Commented:
Hi,

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

Thanks,
Ben
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Hi Ben,
You are correct. :)

Thanks,
Pawan
bfuchs

Author

Commented:
Solved.