update statistics in sql 2005 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

update statistics in sql 2005

Author

Topic

amsqlguy
Yak Posting Veteran

89 Posts

Posted - 2008-11-06 : 14:53:19

Guys,

We are currently using sql 2005 while investigating a query performance we stumbled on Index statistics and its effect.

I know for sure if the below command is set to ON and autostats on index is set to ON in SQL 2005 the SQL engine updates the
statistics

ALTER DATABASE dbname SET AUTO_UPDATE_STATISTICS ON
SP_AUTOSTATS 'TABLENAME'

But when I run the sp

SP_AUTOSTATS 'EMPLOYEE' I get the following result

[IX_EMPLOYEE_1] ON 2008-01-01 10:05:28.477

which means the index statistics was never updated, is there any database setting to enable the statistics update more frequently.

And shouldnt the SQL engine in SQL 2005 update the statistics frequently.

Any suggestions and inputs should help.

Thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-06 : 15:17:49

It gets updated when current statistics is out of date or there is modification. Have you set manually sp_autostats to OFF for that particular index? You have to see how many index pages have updated and inserted or deleted for it.

Look at this one.
http://www.mssqltips.com/tip.asp?tip=1239

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources