Maintenance Plans

Question

Maintenance Plans

Admingod

SSCertifiable

Points: 5997
More actions
August 12, 2014 at 12:28 pm

#294367

When scheduling maintenance plan on SQL server for e.g. index rebuild + Integrity check etc.
do we have to schedule these tasks to run on different time for different server. For e.g. Server A has maintenance plans and server B has maintenance plans. Can I run ServerA and ServerB maintenance plan at same time or it needs to be scheduled different time? Does SAN and server can handle that?
Thanks

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

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

Mike Good SSCertifiable Points: 7415 More actions · Answer 1

I think you're considering right things. If your SAN is powerful and DBs are small, maybe not an issue for you at all.

For boxes with their own storage, we schedule such jobs to least impact the relevant applications, without regard to scheduling on other boxes. For boxes using shared SAN storage, we do try to minimize impact on SAN and try to serialize these over time. But I know we have a few overlapping jobs, is ok for us at present.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

As Mike said, when looking at shared storage and maintenance activities that could provoke a lot of IO, I try to keep the jobs at different times.

If shared storage is not in play, then you can schedule as you wish to least impact the applications.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ShaunF SSChasing Mays Points: 612 More actions · Answer 3

Admingod (8/12/2014)
When scheduling maintenance plan on SQL server for e.g. index rebuild + Integrity check etc.
do we have to schedule these tasks to run on different time for different server. For e.g. Server A has maintenance plans and server B has maintenance plans. Can I run ServerA and ServerB maintenance plan at same time or it needs to be scheduled different time? Does SAN and server can handle that?
Thanks

Are you using SQL out of the box maintenance plans for index rebuilds? If so, to further reduce activity on your SAN, consider using Ola Hallagren's solutions. You will see alot of folks around here recommending this.

http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Nadrek SSC-Insane Points: 20039 More actions · Answer 4

Shaun Finnegan (8/13/2014)

Are you using SQL out of the box maintenance plans for index rebuilds? If so, to further reduce activity on your SAN, consider using Ola Hallagren's solutions. You will see alot of folks around here recommending this.

http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html%5B/quote%5D

Don't use the SQL Server built-in maintenance plans; they don't check what is actually worthwhile, and will happily work on the same table over and over, even if nothing at all changed.

Do use something - I use a variant on Ola's, and there are several other good ones out there; all of them check fragmentation levels before choosing to act or not act.

Also, it's not sharing the SAN that's the big deal, it's sharing spindles - if your servers have dedicated storage, unless you're going to saturate the SAN's storage network throughput, you can certainly do overlapping maintenance. If you're sharing spindles, yes, try not to overlap.