Statistics updates ... 
Author Message
 Statistics updates ...
If a schema change is made to tables, does the temporarily cause any extra
work vis-a-vis the statistics kept by the server?

Thanks,

Bob Castleman
SuccessWare Software



Fri, 06 Jan 2006 18:51:18 GMT
 Statistics updates ...

In some cases, yes. Since the schema changes can affect the columns &
possibly if the key values are altered the statistics does not match with
the indexing keys. In such cases you'll have to do an UPDATE STATISTICS to
keep the statistics up to date.

--
- Anith
( Please reply to newsgroups only )



Fri, 06 Jan 2006 19:04:57 GMT
 Statistics updates ...
So if we did a DROP INDEX/ADD INDEX (we changed a non-clustered to a
clustered index) we should also have done an UPDATE STATISTICS?

Quote:
> In some cases, yes. Since the schema changes can affect the columns &
> possibly if the key values are altered the statistics does not match with
> the indexing keys. In such cases you'll have to do an UPDATE STATISTICS to
> keep the statistics up to date.

> --
> - Anith
> ( Please reply to newsgroups only )



Fri, 06 Jan 2006 19:27:55 GMT
 Statistics updates ...
Yes, that is correct since query optimization depends on the accuracy of the
distribution of key values in the index.

--
- Anith
( Please reply to newsgroups only )



Fri, 06 Jan 2006 19:30:45 GMT
 Statistics updates ...
Also if you have auto statistics set to on you do not have to do this
explicitly, since the recreation of the indexes force the statistics to be
up to date (out-of-date statistics are automatically rebuilt during
optimization). You can use sp_dboption or sp_autostats on a specific table
to set the automatic UPDATE STATISTICS setting. Refer to SQL Server Books
Online for more details on syntax and associated information.

--
- Anith
( Please reply to newsgroups only )



Fri, 06 Jan 2006 19:39:24 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Statistics updated date is where?

2. automatic statistics update

3. STATISTICS UPDATE?

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

5. ANNOUNCE: Another update statistics utility - latest update

6. An update statistics program - updated version for you

7. Update Statistics Question

8. auto update statistics

9. Update statistics

10. Error in updating statistics??

11. Updating statistics

12. Update Statistics


 
Powered by phpBB® Forum Software