Auto create/ auto update statistics, server 7.0

Auto create/ auto update statistics, server 7.0

Post by Ivan Lali » Mon, 18 Jun 2001 21:42:26



        I was inspired by another posting to this NG and checked the statistics
on a few tables in my DB. I am having problems for quite a long time
that SQL server sometimes chooses very stupid execution plans. I found
out that statistics on one of the very frequented tables was updated
last time at the end of March. But auto create/update statistics is
turned on. Is this normal? At what moments SQL server decides to update
the statistics? Should I rather schedule the job that would force an
update of the statistics at that moment?
        I could hardcode the execution plan into my SQL queries, but there are
those rare moments when SQL server comes up with really good plans (even
better than mine :) and I also do not fancy the idea of writing hints
into the code.

Ivan

 
 
 

Auto create/ auto update statistics, server 7.0

Post by Bob Pfeif » Tue, 19 Jun 2001 09:16:14


When you say "very frequented table", does that mean that the data is
modified heavily, or read heavily?  The auto update statistics should happen
based on changes to the data.  If you think that most of the execution plans
are poor, have you tried the index tuning wizard on a trace of the workload
on the server?  I have found systems that are over indexed whose performance
suffered because the query optimizer wasn't using the best execution plan in
some cases, but it was directly related to an out-of-control indexing
strategy.  In one e-commerce system I worked on (SQL 7.0), performance was
greatly increased with a significantly simplified set of indexes that the
index tuning wizard recommended.

 
 
 

Auto create/ auto update statistics, server 7.0

Post by Tibor Karasz » Tue, 19 Jun 2001 15:44:38


Ivan,

You might want to check out http://msdn.microsoft.com/library/techart/statquery.htm.
The article is for 2000, but the basics remain (except for statistics layout).

--
Tibor Karaszi, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com


Quote:> I was inspired by another posting to this NG and checked the statistics
> on a few tables in my DB. I am having problems for quite a long time
> that SQL server sometimes chooses very stupid execution plans. I found
> out that statistics on one of the very frequented tables was updated
> last time at the end of March. But auto create/update statistics is
> turned on. Is this normal? At what moments SQL server decides to update
> the statistics? Should I rather schedule the job that would force an
> update of the statistics at that moment?
> I could hardcode the execution plan into my SQL queries, but there are
> those rare moments when SQL server comes up with really good plans (even
> better than mine :) and I also do not fancy the idea of writing hints
> into the code.

> Ivan

 
 
 

Auto create/ auto update statistics, server 7.0

Post by Ivan Lali » Tue, 19 Jun 2001 19:18:01


Thanks for hint. I'll check the article. Yes, there are some silly
(legacy :) indices on tables, but I am working on it. I also find out
that optimizer is much smarter now when I updated the statistics. But
the problem remains why the statistics was not updated automaticaly -
the table is both modified and selected frequently.

Ivan


> Ivan,

> You might want to check out http://msdn.microsoft.com/library/techart/statquery.htm.
> The article is for 2000, but the basics remain (except for statistics layout).

> --
> Tibor Karaszi, SQL Server MVP
> FAQ from Neil & others at: http://www.sqlserverfaq.com



> > I was inspired by another posting to this NG and checked the statistics
> > on a few tables in my DB. I am having problems for quite a long time
> > that SQL server sometimes chooses very stupid execution plans. I found
> > out that statistics on one of the very frequented tables was updated
> > last time at the end of March. But auto create/update statistics is
> > turned on. Is this normal? At what moments SQL server decides to update
> > the statistics? Should I rather schedule the job that would force an
> > update of the statistics at that moment?
> > I could hardcode the execution plan into my SQL queries, but there are
> > those rare moments when SQL server comes up with really good plans (even
> > better than mine :) and I also do not fancy the idea of writing hints
> > into the code.

> > Ivan

 
 
 

Auto create/ auto update statistics, server 7.0

Post by Tibor Karasz » Tue, 19 Jun 2001 20:06:11


I guess that you'd start looking at the column in sysindexes which keep track on
number of modifications done. As you'll see in the whitepaper, this is the info that
the optimizer goes by when determining whether it is time to do auto-update.

Also, make sure that you didn't turn it off, using UPDATE STATISTICS (or similar).

And of course, there can be a bug (or other reasons that I've missed).

--
Tibor Karaszi, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com


> Thanks for hint. I'll check the article. Yes, there are some silly
> (legacy :) indices on tables, but I am working on it. I also find out
> that optimizer is much smarter now when I updated the statistics. But
> the problem remains why the statistics was not updated automaticaly -
> the table is both modified and selected frequently.

> Ivan


> > Ivan,

> > You might want to check out

http://msdn.microsoft.com/library/techart/statquery.htm.
> > The article is for 2000, but the basics remain (except for statistics layout).

> > --
> > Tibor Karaszi, SQL Server MVP
> > FAQ from Neil & others at: http://www.sqlserverfaq.com



> > > I was inspired by another posting to this NG and checked the statistics
> > > on a few tables in my DB. I am having problems for quite a long time
> > > that SQL server sometimes chooses very stupid execution plans. I found
> > > out that statistics on one of the very frequented tables was updated
> > > last time at the end of March. But auto create/update statistics is
> > > turned on. Is this normal? At what moments SQL server decides to update
> > > the statistics? Should I rather schedule the job that would force an
> > > update of the statistics at that moment?
> > > I could hardcode the execution plan into my SQL queries, but there are
> > > those rare moments when SQL server comes up with really good plans (even
> > > better than mine :) and I also do not fancy the idea of writing hints
> > > into the code.

> > > Ivan

 
 
 

Auto create/ auto update statistics, server 7.0

Post by Serge Van Kampe » Thu, 21 Jun 2001 21:19:55


I am experiencing some weird things as well.

I am migrating a database from SQL Server 7.0 to 2000 and
get different (also very stupid) execution plans on 2000.
Result: a query that takes 7 seconds on 7.0 takes 2
minutes (!) on 2000.

The index tuning wizard won't accept my query in 2000, but
does accept it in 7.0. The query is a select on a view
with many values in the WHERE IN-clause.

I've already installed SP1 and all statistics are uptodate.

Anyone?

Quote:>-----Original Message-----
>    I was inspired by another posting to this NG and

checked the statistics
Quote:>on a few tables in my DB. I am having problems for quite
a long time
>that SQL server sometimes chooses very stupid execution
plans. I found
>out that statistics on one of the very frequented tables
was updated
>last time at the end of March. But auto create/update
statistics is
>turned on. Is this normal? At what moments SQL server
decides to update
>the statistics? Should I rather schedule the job that
would force an
>update of the statistics at that moment?
>    I could hardcode the execution plan into my SQL

queries, but there are
Quote:>those rare moments when SQL server comes up with really
good plans (even
>better than mine :) and I also do not fancy the idea of
writing hints
>into the code.

>Ivan
>.

 
 
 

1. Auto Create/Update Statistics and insert performance in SQL 7.0

Does anyone know if there is a FAQ entry on how turning on auto
create/udpate statistics in SQL Server 7.0 for tables affects insert
performance? Migrating from 6.5 -> 7.0 and using these options has
increased the performance on 'select based' queries, but big inserts
(not BCP or BULK INSERT) have slowed noticably.

As I understand from the docs, even though when you do a sp_help on a
table and all the auto created statistics show as non-clustered
indices, they are actually statistics objects..

Thanks

2. Restore stops before completing

3. Auto create, auto update stats:

4. MO-MISSOURI-104747--ORACLE-Java-C++-Visual Basic-IT PROFESSIONALS

5. Indexes created when auto update statistics is set to On

6. Setting Oracle configuration options from VB

7. Performance Issues with UPDATE STATISTICS WITH FULLSCAN on _WA_Sys auto statistics

8. OLEDB Catastrophic failure

9. Forcing auto-update statistics to fire (SQL Server 2000)

10. Auto-update a number field w/ Auto-Enter Calculation

11. auto created indexes by SQL Server 7.0

12. nonclustered, statistics, auto create located on PRIMARY

13. statistics auto create indexes what are they?