[dba-SQLServer] Timeouts executing views

Fri Feb 1 12:43:13 CST 2008

I know that ADO has a default timeout of 30 seconds. My guess is that SSMS
is using an ADO call in Query Designer (the tool opened when you Open View
from OE) to limit opening huge queries in the UI. Query Editor is a cleaner
connection. 

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Friday, February 01, 2008 11:37 AM
To: 'Discussion concerning MS SQL Server'
Subject: Re: [dba-SQLServer] Timeouts executing views

well, thanks for that anyway.  It sure makes sense.  No help with the
timeout value though eh? 

John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Paul
Nielsen
Sent: Friday, February 01, 2008 1:29 PM
To: 'Discussion concerning MS SQL Server'
Subject: Re: [dba-SQLServer] Timeouts executing views

Just 'New Query' and type 'Select * from ViewName'

I never ever open an object or execute from Object Explorer. 

-Paul

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Friday, February 01, 2008 11:04 AM
To: 'Discussion concerning MS SQL Server'
Subject: Re: [dba-SQLServer] Timeouts executing views

Paul,

I just ran a count PK group by income band on 50 million records.  It took
2:53 to complete and returned 31 records.  There are "cover queries on all
necessary fields etc. and I am quite happy with the time it takes, although
of course faster is always better.  ;-)

Since it will time out in under 30 seconds if I try to just run the view, I
have to do the whole "get Sql / open query / paste / execute" thing when I
should just be able to run the view.

John W. Colby
Colby Consulting
www.ColbyConsulting.com
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Paul
Nielsen
Sent: Friday, February 01, 2008 12:43 PM
To: 'Discussion concerning MS SQL Server'
Subject: Re: [dba-SQLServer] Timeouts executing views

How do you execute the view? 
Do you type "Select * from view" in a query editor and then press F5? 
Or Object Explorer>Highlight view>context menu>Open View? 

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Friday, February 01, 2008 10:02 AM
To: 'Discussion concerning MS SQL Server'
Subject: Re: [dba-SQLServer] Timeouts executing views

ROTFL, I can't.  I execute a view.  It times out.  What more can I tell you?
It does so on any of my machines, I can tell you that. 

John W. Colby
Colby Consulting
www.ColbyConsulting.com
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Paul
Nielsen
Sent: Friday, February 01, 2008 11:52 AM
To: 'Discussion concerning MS SQL Server'
Subject: Re: [dba-SQLServer] Timeouts executing views

SSMS times out? It shouldn't. tell me more. 
-Paul 

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Friday, February 01, 2008 9:33 AM
To: 'Discussion concerning MS SQL Server'
Subject: Re: [dba-SQLServer] Timeouts executing views

I am talking about inside of SQL Server 2005 Management Studio.  Beyond that
I don't understand the question.

John W. Colby
Colby Consulting
www.ColbyConsulting.com
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Paul
Nielsen
Sent: Friday, February 01, 2008 11:22 AM
To: 'Discussion concerning MS SQL Server'
Subject: Re: [dba-SQLServer] Timeouts executing views

How are you connecting when using a view?

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Friday, February 01, 2008 8:45 AM
To: 'Discussion concerning MS SQL Server'
Subject: Re: [dba-SQLServer] Timeouts executing views

Paul, forget I said hours.  Most of them in fact run in a minute or two.
Just long enough to cause the timeout.  I simply need to up that timeout.

Thanks,

John W. Colby
Colby Consulting
www.ColbyConsulting.com
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Paul
Nielsen
Sent: Friday, February 01, 2008 10:23 AM
To: 'Discussion concerning MS SQL Server'
Subject: Re: [dba-SQLServer] Timeouts executing views

The difference has nothing to do with the view, but how you're connecting.
ADO has a built in timeout of 30 seconds, SSMS does not. 

And if you show me the query execution plan, I'll bet we can make it run in
seconds not hours. 

-Paul 

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Friday, February 01, 2008 8:03 AM
To: 'Discussion concerning MS SQL Server'
Subject: Re: [dba-SQLServer] Timeouts executing views

Paul,

It isn't about how long the query takes.  No I can't show you the execution
plan because this happens for ANY query that takes longer than 30 seconds.
I understand that you want long running views to time out, but why does a
view time out and the query based on that SQL not time out?  It is
irritating to have to go get the sql, create a query, dump the SQL in there,
then run it in order to get results.  I just want to run the View and get
the results.

John W. Colby
Colby Consulting
www.ColbyConsulting.com
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Paul
Nielsen
Sent: Friday, February 01, 2008 9:53 AM
To: 'Discussion concerning MS SQL Server'
Subject: Re: [dba-SQLServer] Timeouts executing views

Even of it takes hours? Can you show us the SQL and the Query Execution
Plan? 

-Paul 

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Friday, February 01, 2008 5:55 AM
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer] Timeouts executing views

When I try to execute a view, if results aren't returned within some short
period of time (about 30 seconds) I get a timeout message.  OTOH, if I copy
the Sql, open a new query, paste it in and run, it will complete even if it
takes hours.

Does anyone know where to go to change the timeout period for views run
directly?

John W. Colby
Colby Consulting
www.ColbyConsulting.com 

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

__________ NOD32 2843 (20080201) Information __________