Auto Create/Update Statistics and insert performance in SQL 7.0

Auto Create/Update Statistics and insert performance in SQL 7.0

Post by Nic Banist » Thu, 16 Aug 2001 00:25:23

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..



1. Auto create/ auto update statistics, server 7.0

        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.


2. MDB - SQL Conversion

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

4. Memory for locks

5. Auto Update Statistics and Large inserts

6. Help in figuring this error.

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

8. ontape/ondatartr restores -Reply

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

10. Auto create, auto update stats:

11. Create Trigger - Insert, Update - not updating record on Insert

12. nonclustered, statistics, auto create located on PRIMARY

13. statistics auto create indexes what are they?