sql server programming: Hello,



Does it make sense to update statistics after all indexes on a table have
been dropped and recreated? And another question... Maybe I should make
another post about this one, but here it is anyway:



What's the best way to keep queries performance optimized? What's happening
with this db I am working on is -- there're 2 tables that get constantly
populated with data, at about 10 thousands rows per week rate. Also one
field in a table gets frequently updated with new keys. These 2 tables are
indexed on the fields that matter for the queries (including that frequently
updated column). So, initially queries execution time is quite good, but
over time it degrades to the point where db and client Apps become unusable.
So, we're trying different techniques; like index defrags, update
statistics. I found that dropping and recreating indexes works the best of
all. But I am not a DBA, and I don't know the best or "acceptable" way of
keeping queries performance optimized; for example, does it make sense to
schedule drop/create indexes scripts on weekly basis? I want to hear for
SQL gurus. Also if any of techniques that we're already doing are
unnecessary or even dangerous for some reason, I'd like to know about that.



Thank you for your help.

read up on the following:

DBCC DBReindex
DBCC IndexDefrag
DBCC ShowContig

in short you'll want to defrag indexes periodically. When you Drop and
recreate indexes, this is what you are doing essentially. (Same thing as
DBCC DBReindex by the way).

This will help you.


Greg Jackson
PDX, Oregon

if you use DBCC DBReindex, then statistics are updated automatically.

If you use DBCC IndexDefrag, then you will need to update statistics
manually.



GAJ

What about update statistics?

[quoted text, click to view]

In addition to pdxJaxon's reply, I would discourage you to update
statistics after you have run DBCC DBReindex, because the reindex
command will have updated the statistics with full scan. By default, the
UPDATE STATISTICS command 'only' takes samples (although you can run
UPDATE STATISTICS WITH FULLSCAN).

HTH
Gert-Jan


[quoted text, click to view]