Timeout expired message running View in Enterprise Manager

Timeout expired message running View in Enterprise Manager

Post by Fred Zimmerm » Wed, 15 Nov 2000 04:00:00



I am running a complex View in Enterprise Manager involving multiple
table joins, and finding that I am getting following error message:

[Microsoft][ODBC SQL Server Driver] Timeout expired.

The rows of view do NOT display.

What might be causing this problem?  How do I fix the problem?

Thanks,

Fred Zimmerman

 
 
 

Timeout expired message running View in Enterprise Manager

Post by Brian Mora » Thu, 16 Nov 2000 04:00:00


SEM has a hard-coded timeout that as far as I know can't be changed. I don't
know if it's still in SQL2K, but I was told by a MS enginner that the value
can't be changed in SQL7.

--
Brian Moran
CrossTier.com
MS SQL Server MVP
SQL Server Mag Columnist


Quote:> I am running a complex View in Enterprise Manager involving multiple
> table joins, and finding that I am getting following error message:

> [Microsoft][ODBC SQL Server Driver] Timeout expired.

> The rows of view do NOT display.

> What might be causing this problem?  How do I fix the problem?

> Thanks,

> Fred Zimmerman


 
 
 

Timeout expired message running View in Enterprise Manager

Post by Fred Zimmerm » Fri, 17 Nov 2000 04:00:00


Brian:

Any suggestions on how to alleviate this problem?  My guess to
fix it is to simplify the view/schema -- does this sound right?  The
database is over 27 Gb (all on one drive along with log file).

Fred Z
On Wed, 15 Nov 2000 08:44:37 -0500, "Brian Moran"


>SEM has a hard-coded timeout that as far as I know can't be changed. I don't
>know if it's still in SQL2K, but I was told by a MS enginner that the value
>can't be changed in SQL7.

>--
>Brian Moran
>CrossTier.com
>MS SQL Server MVP
>SQL Server Mag Columnist



>> I am running a complex View in Enterprise Manager involving multiple
>> table joins, and finding that I am getting following error message:

>> [Microsoft][ODBC SQL Server Driver] Timeout expired.

>> The rows of view do NOT display.

>> What might be causing this problem?  How do I fix the problem?

>> Thanks,

>> Fred Zimmerman

 
 
 

Timeout expired message running View in Enterprise Manager

Post by Keith Kratochvi » Fri, 17 Nov 2000 04:00:00


Do you NEED to query the view from Enterprise Manager?
As Brian mentioned, EM has a hardcoded timeout....
Can you use Query Analyzer instead?
You will need to type in 'SELECT * FROM x' where x=name of the view, but
that should not be too difficult.

--
Keith
==============
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


> Brian:

> Any suggestions on how to alleviate this problem?  My guess to
> fix it is to simplify the view/schema -- does this sound right?  The
> database is over 27 Gb (all on one drive along with log file).

> Fred Z
> On Wed, 15 Nov 2000 08:44:37 -0500, "Brian Moran"

> >SEM has a hard-coded timeout that as far as I know can't be changed. I
don't
> >know if it's still in SQL2K, but I was told by a MS enginner that the
value
> >can't be changed in SQL7.

> >--
> >Brian Moran
> >CrossTier.com
> >MS SQL Server MVP
> >SQL Server Mag Columnist



> >> I am running a complex View in Enterprise Manager involving multiple
> >> table joins, and finding that I am getting following error message:

> >> [Microsoft][ODBC SQL Server Driver] Timeout expired.

> >> The rows of view do NOT display.

> >> What might be causing this problem?  How do I fix the problem?

> >> Thanks,

> >> Fred Zimmerman

 
 
 

Timeout expired message running View in Enterprise Manager

Post by Fred Zimmerm » Sat, 18 Nov 2000 04:00:00


Keith:

I tried doing Select * from [VIEW_NAME] from Query Analyzer,
and got a timeout here as well.

Any ideas?

Fred Z

On Thu, 16 Nov 2000 15:11:56 -0600, "Keith Kratochvil"


>Do you NEED to query the view from Enterprise Manager?
>As Brian mentioned, EM has a hardcoded timeout....
>Can you use Query Analyzer instead?
>You will need to type in 'SELECT * FROM x' where x=name of the view, but
>that should not be too difficult.

>--
>Keith
>==============
>Please reply only to the newsgroups.
>When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
>can be cut and pasted into Query Analyzer is appreciated.



>> Brian:

>> Any suggestions on how to alleviate this problem?  My guess to
>> fix it is to simplify the view/schema -- does this sound right?  The
>> database is over 27 Gb (all on one drive along with log file).

>> Fred Z
>> On Wed, 15 Nov 2000 08:44:37 -0500, "Brian Moran"

>> >SEM has a hard-coded timeout that as far as I know can't be changed. I
>don't
>> >know if it's still in SQL2K, but I was told by a MS enginner that the
>value
>> >can't be changed in SQL7.

>> >--
>> >Brian Moran
>> >CrossTier.com
>> >MS SQL Server MVP
>> >SQL Server Mag Columnist



>> >> I am running a complex View in Enterprise Manager involving multiple
>> >> table joins, and finding that I am getting following error message:

>> >> [Microsoft][ODBC SQL Server Driver] Timeout expired.

>> >> The rows of view do NOT display.

>> >> What might be causing this problem?  How do I fix the problem?

>> >> Thanks,

>> >> Fred Zimmerman

 
 
 

Timeout expired message running View in Enterprise Manager

Post by Keith Kratochvi » Sat, 18 Nov 2000 04:00:00


Query Analyzer should not time out!?
Are statistics up to date?
Are the correct indexes in place?
replace the SELECT bla, bla, bla with SELECT COUNT(*) and run the select
adding one table at a time (I assume that multiple tables are involved).
Watch the times (and counts) at each run.  If the time takes a long time at
one spot or if the count jumps (cartesian product), take a look at that last
join.

--
Keith
==============
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


> Keith:

> I tried doing Select * from [VIEW_NAME] from Query Analyzer,
> and got a timeout here as well.

> Any ideas?

> Fred Z

> On Thu, 16 Nov 2000 15:11:56 -0600, "Keith Kratochvil"

> >Do you NEED to query the view from Enterprise Manager?
> >As Brian mentioned, EM has a hardcoded timeout....
> >Can you use Query Analyzer instead?
> >You will need to type in 'SELECT * FROM x' where x=name of the view, but
> >that should not be too difficult.

> >--
> >Keith
> >==============
> >Please reply only to the newsgroups.
> >When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> >can be cut and pasted into Query Analyzer is appreciated.



> >> Brian:

> >> Any suggestions on how to alleviate this problem?  My guess to
> >> fix it is to simplify the view/schema -- does this sound right?  The
> >> database is over 27 Gb (all on one drive along with log file).

> >> Fred Z
> >> On Wed, 15 Nov 2000 08:44:37 -0500, "Brian Moran"

> >> >SEM has a hard-coded timeout that as far as I know can't be changed. I
> >don't
> >> >know if it's still in SQL2K, but I was told by a MS enginner that the
> >value
> >> >can't be changed in SQL7.

> >> >--
> >> >Brian Moran
> >> >CrossTier.com
> >> >MS SQL Server MVP
> >> >SQL Server Mag Columnist



> >> >> I am running a complex View in Enterprise Manager involving multiple
> >> >> table joins, and finding that I am getting following error message:

> >> >> [Microsoft][ODBC SQL Server Driver] Timeout expired.

> >> >> The rows of view do NOT display.

> >> >> What might be causing this problem?  How do I fix the problem?

> >> >> Thanks,

> >> >> Fred Zimmerman

 
 
 

Timeout expired message running View in Enterprise Manager

Post by Fred Zimmerm » Sun, 19 Nov 2000 04:00:00


Keith:

Can you clarify what I need to do for the following 2 things
you specified:

*  Are the statistics up to date?

*  Are the correct indexes in place?

I'm fairly new to the role of DBA, and am not that familiar with
all the ins and outs of these things.

Thanks again,

Fred Z

On Fri, 17 Nov 2000 15:12:49 -0600, "Keith Kratochvil"


>Query Analyzer should not time out!?
>Are statistics up to date?
>Are the correct indexes in place?
>replace the SELECT bla, bla, bla with SELECT COUNT(*) and run the select
>adding one table at a time (I assume that multiple tables are involved).
>Watch the times (and counts) at each run.  If the time takes a long time at
>one spot or if the count jumps (cartesian product), take a look at that last
>join.

>--
>Keith
>==============
>Please reply only to the newsgroups.
>When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
>can be cut and pasted into Query Analyzer is appreciated.



>> Keith:

>> I tried doing Select * from [VIEW_NAME] from Query Analyzer,
>> and got a timeout here as well.

>> Any ideas?

>> Fred Z

>> On Thu, 16 Nov 2000 15:11:56 -0600, "Keith Kratochvil"

>> >Do you NEED to query the view from Enterprise Manager?
>> >As Brian mentioned, EM has a hardcoded timeout....
>> >Can you use Query Analyzer instead?
>> >You will need to type in 'SELECT * FROM x' where x=name of the view, but
>> >that should not be too difficult.

>> >--
>> >Keith
>> >==============
>> >Please reply only to the newsgroups.
>> >When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
>> >can be cut and pasted into Query Analyzer is appreciated.



>> >> Brian:

>> >> Any suggestions on how to alleviate this problem?  My guess to
>> >> fix it is to simplify the view/schema -- does this sound right?  The
>> >> database is over 27 Gb (all on one drive along with log file).

>> >> Fred Z
>> >> On Wed, 15 Nov 2000 08:44:37 -0500, "Brian Moran"

>> >> >SEM has a hard-coded timeout that as far as I know can't be changed. I
>> >don't
>> >> >know if it's still in SQL2K, but I was told by a MS enginner that the
>> >value
>> >> >can't be changed in SQL7.

>> >> >--
>> >> >Brian Moran
>> >> >CrossTier.com
>> >> >MS SQL Server MVP
>> >> >SQL Server Mag Columnist



>> >> >> I am running a complex View in Enterprise Manager involving multiple
>> >> >> table joins, and finding that I am getting following error message:

>> >> >> [Microsoft][ODBC SQL Server Driver] Timeout expired.

>> >> >> The rows of view do NOT display.

>> >> >> What might be causing this problem?  How do I fix the problem?

>> >> >> Thanks,

>> >> >> Fred Zimmerman

 
 
 

Timeout expired message running View in Enterprise Manager

Post by Keith Kratochvi » Tue, 21 Nov 2000 04:00:00


The statistics that would be used by the optimizer might be out of date if
you have disabled auto create / auto update statistics.

These should both be 'on'

exec sp_dboption 'TheDBInQuestion', 'auto update statistics'
exec sp_dboption 'TheDBInQuestion', 'auto create statistics'

Indexes:
Do you have any?
exec sp_help <tablename>
will show you the lots of information about the specific table.
exec sp_helpindex <tablename> will display indexes on the table.

In general, it is good to have indexes on fields that you join on or specify
criteria (WHERE).  You can run the index tuning wizard on your query/stored
procedure and let it tell you what indexes (it thinks) would be helpful.
Look at the recommendations, look at your query, and see if you can see why
the wizard recommended these indexes.  You can create the indexes it
suggests, or have it crete the indexes for you.

--
Keith
==============
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


> Keith:

> Can you clarify what I need to do for the following 2 things
> you specified:

> *  Are the statistics up to date?

> *  Are the correct indexes in place?

> I'm fairly new to the role of DBA, and am not that familiar with
> all the ins and outs of these things.

> Thanks again,

> Fred Z

> On Fri, 17 Nov 2000 15:12:49 -0600, "Keith Kratochvil"

> >Query Analyzer should not time out!?
> >Are statistics up to date?
> >Are the correct indexes in place?
> >replace the SELECT bla, bla, bla with SELECT COUNT(*) and run the select
> >adding one table at a time (I assume that multiple tables are involved).
> >Watch the times (and counts) at each run.  If the time takes a long time
at
> >one spot or if the count jumps (cartesian product), take a look at that
last
> >join.

> >--
> >Keith
> >==============
> >Please reply only to the newsgroups.
> >When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> >can be cut and pasted into Query Analyzer is appreciated.



> >> Keith:

> >> I tried doing Select * from [VIEW_NAME] from Query Analyzer,
> >> and got a timeout here as well.

> >> Any ideas?

> >> Fred Z

> >> On Thu, 16 Nov 2000 15:11:56 -0600, "Keith Kratochvil"

> >> >Do you NEED to query the view from Enterprise Manager?
> >> >As Brian mentioned, EM has a hardcoded timeout....
> >> >Can you use Query Analyzer instead?
> >> >You will need to type in 'SELECT * FROM x' where x=name of the view,
but
> >> >that should not be too difficult.

> >> >--
> >> >Keith
> >> >==============
> >> >Please reply only to the newsgroups.
> >> >When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
which
> >> >can be cut and pasted into Query Analyzer is appreciated.



> >> >> Brian:

> >> >> Any suggestions on how to alleviate this problem?  My guess to
> >> >> fix it is to simplify the view/schema -- does this sound right?  The
> >> >> database is over 27 Gb (all on one drive along with log file).

> >> >> Fred Z
> >> >> On Wed, 15 Nov 2000 08:44:37 -0500, "Brian Moran"

> >> >> >SEM has a hard-coded timeout that as far as I know can't be
changed. I
> >> >don't
> >> >> >know if it's still in SQL2K, but I was told by a MS enginner that
the
> >> >value
> >> >> >can't be changed in SQL7.

> >> >> >--
> >> >> >Brian Moran
> >> >> >CrossTier.com
> >> >> >MS SQL Server MVP
> >> >> >SQL Server Mag Columnist



> >> >> >> I am running a complex View in Enterprise Manager involving
multiple
> >> >> >> table joins, and finding that I am getting following error
message:

> >> >> >> [Microsoft][ODBC SQL Server Driver] Timeout expired.

> >> >> >> The rows of view do NOT display.

> >> >> >> What might be causing this problem?  How do I fix the problem?

> >> >> >> Thanks,

> >> >> >> Fred Zimmerman