CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Nycky
Nycky

Reputation: 31

Is it useful to use Auto Create Statistics = "ON" on a SQL Server 2005 DB?

After restoring a large DB (70 Gb) from SQL Server 2000 onto a SQL Server 2005 Server we had encountered loss of performance while running queries, functions or procedures that on SQL Server 2000 worked ok.

After some troubleshooting we found that SQL Server Option "Auto Create Statistics" was ON, and after set it to OFF, the performance was improved very much.

Why was this option so bad to use in my case?

When is it OK to use Auto Create Statstistics = ON?

Thx

Upvotes: 3

Views: 2556

Answers (1)

John Sansom
John Sansom

Reputation: 41839

I think what you really need to do is address why you are experiencing frequent statistic creation/update operations.

Did you update ALL of your statistics as part of your Upgrade process from SQL Server 2000 to 2005? You should have.

If you did not perform this as part of your upgrade process, then SQL will be creating the required statistics that it needs to perform your queries, as and when you execute them, when you have the AUTO CREATE STATS setting enabled. This of course creates resource overhead.

Upvotes: 1

Related Questions