November 6, 2008 at 12:54 pm
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]ON2008-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
November 6, 2008 at 12:57 pm
It depends on how much update activity occurs on the table. If there isn't a lot of activity, the statistics won't get updated unless you force it. autoupdate statistics does not do it based on time since last update but on the amount of activity.
November 6, 2008 at 1:01 pm
Lynn - Thanks for your response.
In my scenario after I updated the statistics on Employee table the query performance improved by 300% and hence I am wondering should the auto update on statistics be forced?
Thanks
November 6, 2008 at 1:07 pm
Lynn - Also the index on the column that we are interested is not updated, when the row gets inserted the column gets set to todays date getdate() as part of insert statement
Thanks
November 6, 2008 at 1:22 pm
That doesn't mean that the threshold for running an autoupdate for statistics has been reached. This doesn't happen with every insert.
If you want to ensure statistics are updated on a regular basis, you need to schedule a job to accomplish that, weither it is nightly, weekly, or on what ever other schedule you dtermine appropriate for your application.
November 6, 2008 at 1:56 pm
Is this a migrated database from sql2000 ?
if so:
- rebuild all indexes
- sp_updatestatistics
- dbcc updateusage(0) with count_rows
and if you can ... dblevel to 90 !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 6, 2008 at 2:18 pm
ALZDBA (11/6/2008)
Is this a migrated database from sql2000 ?if so:
- rebuild all indexes
- sp_updatestatistics
- dbcc updateusage(0) with count_rows
and if you can ... dblevel to 90 !
can you please elaborate what is
dbcc updateusage(0) with count_rows - what is the purpose of this statement
dblevel to 90 - what does this accomplish
Any suggestions or inputs would be helpful
Thanks
November 6, 2008 at 11:57 pm
am (11/6/2008)
ALZDBA (11/6/2008)
Is this a migrated database from sql2000 ?if so:
- rebuild all indexes
- sp_updatestatistics
- dbcc updateusage(0) with count_rows
and if you can ... dblevel to 90 !
can you please elaborate what is
dbcc updateusage(0) with count_rows - what is the purpose of this statement
dblevel to 90 - what does this accomplish
Any suggestions or inputs would be helpful
Thanks
So, is it a migrated db from sql7 or sql2000 ?
Bol states:
SQL Server 2005 Books Online (September 2007)
DBCC UPDATEUSAGE (Transact-SQL)
Reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure. In SQL Server 2005, these values are always maintained correctly. Databases created on SQL Server 2005 should never experience incorrect counts, however, databases upgraded to SQL Server 2005 may contain invalid counts. We recommend running DBCC UPDATEUSAGE after upgrading to SQL Server 2005 to correct any invalid counts.
Exec sp_dbcmptlevel @dbname = 'yourdb', @new_cmptlevel = 90 ;
Also in this case, BOL states
The Transact-SQL sp_dbcmptlevel system stored procedure can be used to set certain database behavior to be compatible with a specified earlier version of Microsoft SQL Server.
A special rule applies to the relationship among the database compatibility level option, the CONCAT_NULL_YIELDS_NULL database option, and the CONCAT_NULL_YIELDS_NULL SET option. The settings of both options are ignored when the value of compatibility level is for a release earlier than SQL Server version 7.0.
The compatibility level affects the behavior only in the specified database and not the whole server. For more information, see sp_dbcmptlevel (Transact-SQL).
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply