dbcc updateusage

Not the same

UPDATE STATISTICS updates the optimizer statistics that maintain information
about the distribution of key values in your indexes. It deals with actual
data values.
DBCC UPDATEUSAGE updates the space allocation information in sysindexes so
that you can get more accurate results when you run sp_spaceused. It deals
with the size of tables, indexes and your db, but has nothing to do with the
data values.

Im still confused whether i need to turn it on for all my databases and what the effect of doing it will be.
Could you explain a little more for me....will it increase log file what are the benefits .
I read the script but did not understand it.

I understand the UPDATEUSAGE....

aneeshattingal,

Hope this will help you
http://msdn.microsoft.com/library/default.asp?URL=/library/techart/statquery.htm


Aneesh

In otherwords Update stats will just rebuild the selectivity, density for all columns and distribution pages.  This will make the optimizer smarter on how to use your indexes.

Bear with me ...whilst i get the idea of this.
I like to know what im turning on and off before actually doing it.......

Before setting it to run each week....How can i measure it......

Just read the details.
SQL Server query optimizer to choose the most efficient plan for performing
By default, SQL Server is creating and maintaining the statistics without any user intervention...(without the switch on)....

So i think i got it ...its updating the rowmodctr
506 records added

t1 0 506 506
i1 2 506 -506
I2 3 506 -506

After another 213 rows are inserted, the counts are:

Name Indid rowcnt rowmodctr
t1 0 719 719
i1 2 506 -506
i2 3 506 -506

After the statistics for the index i1 are refreshed using UPDATE STATISTICS t1 (i1):

Name Indid rowcnt rowmodctr
t1 0 719 0
i1 2 506 0
i2 3 506 213


So you can see at any one time how many records were inserted into a table.

But the question is..........
      (If sql is maintaining this without the switch turned on in maintenance plan).
     
How can i truely see a performance gain................
What should i measure.

Thanks

Ok i think im getting there.....
If i run the following:

DBCC SHOW_STATISTICS ( table , target )
target = index name.
question...................(How do you know if the table has index.)
question.....Can you run this for the entire database rather than table and output to a txt file).


the lower the density returned, the higher the selectivity
  (Does this mean if it is a low number than this table is used alot of times....or
    if a high number).

Then if i run the option update statistics in the maintenance plan and then rerun
the following command DBCC SHOW_STATISTICS
 question.. What command is this running so that i can run it for one table as a test...

Now having these statistics updated....allow you to run the DBCC SHOW_STATISTICS
to determine whether or not you need indexes on your tables..

Did i understand correctly..



   

TRACEYMARY,
> question...................(How do you know if the table has index.)

Sp_help 'TableName' will give you so many information including the Index

>question.....Can you run this for the entire database rather than table and output to a txt file).
For this you have to write another sp

Thanks....
   Did i understand the theory above on it.............
   So running it allows the DBCC SHOW_STATISTICS to be correct and this can then determine that you can
   put indexes..on it.
   the lower the density returned, the higher the selectivity
  (Does this mean if it is a low number than this table is used alot of times....or
    if a high number).

Then i close this out...if i got it and my undestanding is correct.

You should be a teacher...i come to the class.