Re: DTA questions

Tech-Archive recommends: Fix windows errors by optimizing your registry


"tshad" <toms@xxxxxxxx> wrote in message news:O5TvA0b#JHA.4944@xxxxxxxxxxxxxxxxxxxxxxx

"Bob Simms" <bob.simms@xxxxxx> wrote in message news:839273E4-E4A8-4DC8-B04E-B764CC2CDD49@xxxxxxxxxxxxxxxx
<inline>


You can do. The index recommendation above is covering a query with a non-clustered index. In other words, it is putting all the info a query wants into the index, so it doesn't have to go back to the table to find data, it's all in the index. This can dramatically reduce the number of page reads, and hence improve the performance.


Is that the same with the CREATE STATISTICS where it is specifying 3 fields?

No, SQL doesn't read statistics to get actual values out. It reads the statistics to decide whether it's better to use a table scan or an index to get the data out. The data still has to come from a table or an index.

Are the statistics also stored in one place so you don't have to go back to the table?

See above, The statistics are held in a system table you can view in sys.indexes. To get any data out, SQL must always visit an index or a table. but with no statistics it will always do a table scan.

Also, I read that it isn't a good thing to UPDATE STATISTICS all the time as it forces all the stored procedures to recompile.

It depends. Supposing you have a database that is heavily written to during the day, then between 0200 and 0600 you run a load of reports on the data. you don't want SQL to keep updating the statistics during the day when you're writing to the tables. however, you want the statistics to be up to date when you run your selects overnight. So before you run the reports you manually update the stats so that the SQL optimizer has the best stats to support its query plans.

However, if you are heavily querying the data and updating it too, it's easier to automatically update the statistics, otherwise the query plans that were optimal first thing in the morning could be inefficient in the afternoon.

In our Maintenance place we update statistics and the script it generates is something like:

Statistics:

use [ReportServer]
GO
UPDATE STATISTICS [dbo].[ActiveSubscriptions]
WITH FULLSCAN
GO
use [ReportServer]
GO
UPDATE STATISTICS [dbo].[Batch]
WITH FULLSCAN
GO
use [ReportServer]
GO
UPDATE STATISTICS [dbo].[CachePolicy]
WITH FULLSCAN
GO

Does that mean that if ActiveSubscriptions is updated that all the CREATE STATISTICS that I created with that table will all get updated?

Guess the answer, Yes, it depends. If you leave the database options to automatically update the stats, then the stats will be updated when a statistically significant proportion of the rows are updated. If it's a large table and a small update, it might not update the stats. If it's a small table and a large update, it almost certainly will.


--
Bob Simms
Senior Learning Consultant
QA - transforming performance through learning
www.qa.com

.

Relevant Pages

  • RE: How to link tables via the field names themselves, not their c
    ... you can query the database to do what you want easily. ... There are approximately 30 different aspects (or statistics) which are ... Beacuse there are so many stats per contract, ...
    (microsoft.public.access.queries)
  • Re: Strange Cost Based Optimizer Decision Making
    ... The statistics on this table are 100% completely up to ... > I run an identical SELECT clause to the last query against this table. ... > the stats are fully up to date for LARGE_TABLE. ... compare that number with clustering factor and number ...
    (comp.databases.oracle.server)
  • RE: How to link tables via the field names themselves, not their c
    ... you can query the database to do what you want easily. ... There are approximately 30 different aspects (or statistics) which are ... Mulitple audits are made per contract. ... Beacuse there are so many stats per contract, ...
    (microsoft.public.access.queries)
  • Re: Statistics....questions
    ... If you know you need the stats, why not explicitly create it yourself using ... >>> I have a query that will perform poorly from time to time. ... >>> an integer with a default of zero and can only zero or one. ... >>> Does SQL Server maintain statistics on columns that are not indexed? ...
    (microsoft.public.sqlserver.programming)
  • Re: We have the pitching, what about offense?
    ... "As an engineer I've always enjoyed ... suggest you look it up in a statistics book. ... although I took a course in it in college and have used/read stats ... Pedroia will do next year. ...
    (alt.sports.baseball.bos-redsox)