?
Solved

dbcc updateusage

Posted on 2006-05-09
9
Medium Priority
?
658 Views
Last Modified: 2008-02-01
In backup maintenance plans
> Update statistics by query optimzer
Updates information about the distribution of key values for one or more statistics groups (collections) in the specified table or indexed view.
read this article
http://www.sql-server-performance.com/statistics.asp

Is this the same as running dbcc updateusage
0
Comment
Question by:TRACEYMARY
  • 5
  • 4
9 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16638456
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.
0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16638479
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....
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16638545
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16638574
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.
0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16638798
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
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 16638945
You can use
DBCC SHOW_STATISTICS ( table , target )

table : table name
Target: index name (refer BOL)  

Which Displays all the statistical information in the distribution page for an index (index_name) on a specified table (table_name). The results returned indicate the selectivity of an index (the lower the density returned, the higher the selectivity) and provide the basis for determining whether or  not an index would be useful to the optimizer. The results returned are based on distribution steps of the index.

Run the above command, before and after, updating the stats
0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16639047
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..



   
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16639336
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
0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16639400
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.

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question