basic sp_recompile and update statistics question 1

[Bernadette]

Hi There

How often should I run sp_compile and update statistics on a SQL6.5 server ?

Also, should I run these jobs on SQL7.0 or are they handled automatically ?

What do you guys do ?

Any advice would be appreciated.
Bernadette

 

[RickCole]

It all depends on how much data is being updated and inserted onto your tables.

Low hit tables or tables with few rows won't need an update statistics on a regular basis. High hit tables with many rows will.

At my site we have a window overnight where there is no activity on the database. I use this window to run a scheduled job, which updates statistics on all tables regardless of size and activity. I also stop the SQL server once a week to flush the query plans.

Rick.

 

[Bernadette]

Thank you Rick for answering my question !!!!

Is your server a SQL7.0 or SQL6.5 ?

What should I do about sp_recompile ? Bernadette

 

[RickCole]

I run both 6.5 and 7.0

As with update statistics a scheduled job runs each evening on all SP's. Again not required on most of them but for completeness it's just easier to do. Stopping and starting the server will also have the same affect.

Rick.

 

[Bernadette]

Thanks for your help Rick, much appreciated.

Bernadette