SQL Maintainance plan - what do you use ? (check db integrity + update statistics)

Question

SQL Maintainance plan - what do you use ? (check db integrity + update statistics)

szejiekoh

SSCarpal Tunnel

Points: 4155
More actions
December 26, 2016 at 5:06 am

#316986

Hi all,
I am setting up monthly maintenance plan for my sql server.
So far i have only setup clean job history and clean backup files.
I am thinking if it is advisable to setup the following maintenance job monthly on probably a weekend morning.
-- 1 plan (2 steps) step1-> check db integrity + step2-> update index statistics
q1) is it advisable to do the above job monthly ?
q2) when checking db integrity, is checking user databases sufficient, or both system and user databases ?
q3) for update index statistics, should we use a full scan ? or 50% sampling will do ?
should we also update index statistics for system databases ? or just user databases will do ?
I am not very sure if the app team will create larger table/large index in future.. is it advisable to update statistics ?
-- if we do not update statistics, does that mean the statistics will be stale as of the time the index is created ?
q4) is it advisable to do any index reorganization or rebuild ? - i am quite worried about doing such operations.
I have 4 user databases and they add up to be around 80GB, the largest user db is around 40GB.
Hope to hear from you gurus soon.
Regards,
Noob

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 1

szejiekoh (12/26/2016)
Hi all,
I am setting up monthly maintenance plan for my sql server.
So far i have only setup clean job history and clean backup files.

Strongly suggest that you use Ola Hallengren's SQL Server Maintenance Solution[/url]

😎

I am thinking if it is advisable to setup the following maintenance job monthly on probably a weekend morning.
-- 1 plan (2 steps) step1-> check db integrity + step2-> update index statistics

Without further knowledge on activities on the server, it is hard to advice on this. I do though recommend more frequent integrity checks as if are hit with a serious corrubtion in one of your databases, often the only thing to do is to revert to the last known good backup.

I normally gather as much information as I can on the activities on a server and then create a "heat map" which allows me to schedule the maintenance on that server with minimum impact. Some maintenance operations such as index maintenance are fully logged, those are i.e. best to schedule before a full backup.

q1) is it advisable to do the above job monthly ?

Entierly depends on the nature of the activities, the type of databases etc. Whilst it could be fine for a data warehouse which can be recreated / repopulated from scratch, a monthly integrity check on a transactional (OLTP) database is not adviceable as it places the data at risk (see the above comment)

q2) when checking db integrity, is checking user databases sufficient, or both system and user databases ?

All databases, both system and users are subject to corrubtion, hence all should be checked.

q3) for update index statistics, should we use a full scan ? or 50% sampling will do ?

If you use the Ola Hallengren's Maintenance Solution, I recommend that you read Brent Ozar's article, "Tweaking the Defaults for Ola Hallengren’s Maintenance Scripts"[/url]

should we also update index statistics for system databases ? or just user databases will do ?

I normally leave out the system databases unless I have a good reason such as performance statistics indicating that there is a problem.

I am not very sure if the app team will create larger table/large index in future.. is it advisable to update statistics ?
-- if we do not update statistics, does that mean the statistics will be stale as of the time the index is created ?
q4) is it advisable to do any index reorganization or rebuild ? - i am quite worried about doing such operations.

Again I suggest you leave the logic of index maintenance to Ola Hallengren's Maintenance Solution.

I have 4 user databases and they add up to be around 80GB, the largest user db is around 40GB.

As you have relatively small databases, the maintenance operations should run quickly and give you amble room for scheduling those within your maintenance window.

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 2

Good advice above. I would recommend either Ola's scripts or the Minion scripts[/url].

szejiekoh SSCarpal Tunnel Points: 4155 More actions · Answer 3

thank you guys for the reply and so sorry for reverting so late!