We have a v10.0.1 database that has had a lot of data removed, and has now been rebuilt. There are a few rather vague indications that the statistics may be out of date and causing some odd plans. We don't know this is the case but it seems something worth eliminating.

My question is whether it is safe to run CREATE STATISTICS when there is any other activity in the database or should we take it off line (to other users) while this is done. Obviously we would chose a quite time, but that isn't the same as no other activity.

The docs only say "The process of running CREATE STATISTICS performs a complete scan of the table. For this reason, careful consideration should be made before issuing a CREATE STATISTICS statement."

Any experience / thoughts appreciated.

asked 30 Jul '12, 16:35

Justin%20Willey's gravatar image

Justin Willey
7.0k115148219
accept rate: 21%

Comment Text Removed
1

Did the rebuild include LOAD STATISTICS statements? (check the reload.sql file if you have it)

FWIW the V10 Help topic Updating column statistics says this: "You can set whether to update column statistics using database options. The update_statistics database option controls whether to update column statistics during execution of queries, while the collect_statistics_on_dml_updates database option controls whether to update the statistics during the execution of data-altering DML statements such as LOAD, INSERT, DELETE, and UPDATE."

By default, collect_statistics_on_dml_updates is 'On'.

(30 Jul '12, 17:05) Breck Carter
Replies hidden

Stats did get reloaded and collect_statistics_on_dml_updates is still on, so the stats should be relatively up to date but the docs imply that drastic clearouts etc might upset things.

(31 Jul '12, 06:45) Justin Willey

I am unaware of issues related to running CREATE STATISTICS during production operation.

That said, however, I would think twice about running the statement(s) during peak production, especially on a larger database file. Executing the statement will alter the cache contents and consequently can have negative affects on production queries. It will also (naturally) cause considerable sequential I/O to the disk.

permanent link

answered 31 Jul '12, 06:22

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k571104
accept rate: 43%

Many thanks Glenn, that's how I read it but that "careful consideration" phrase made me pause. It sounds like we should choose a quiet time - that's much easier than throwing every last user out.

(31 Jul '12, 06:46) Justin Willey
Replies hidden

CREATE STATISTICS did indeed run happily with a low level of other activity. Thanks Glenn

(01 Aug '12, 07:40) Justin Willey

You're welcome!

(02 Aug '12, 20:51) Glenn Paulley
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×114
×12

question asked: 30 Jul '12, 16:35

question was seen: 1,370 times

last updated: 02 Aug '12, 20:51