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'
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.
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.
> 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
> >one spot or if the count jumps (cartesian product), take a look at that
> >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,
> >> >that should not be too difficult.
> >> >--
> >> >Keith
> >> >==============
> >> >Please reply only to the newsgroups.
> >> >When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
> >> >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
> >> >don't
> >> >> >know if it's still in SQL2K, but I was told by a MS enginner that
> >> >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
> >> >> >> table joins, and finding that I am getting following error
> >> >> >> [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