Can Maintenance plan slow down the application?

Question

Can Maintenance plan slow down the application?

xiaomaomao69

SSC Rookie

Points: 27
More actions
November 21, 2007 at 12:10 pm

#181316

I have a strange question, my company upgrade SQL2000 to SQL2005, running application was very fast when set up that day. BUT, after DBA running maintenance plan that night. it is slow right now. I know the maintenance plan shouldn't slow down the application. i use SQL profile to check. couldn't find the reason. Can somebody help me? really appreciate...

Viewing 8 posts - 1 through 8 (of 8 total)

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

Jeff Moden SSC Guru Points: 1004649 More actions · Answer 1

Depends on what the maintenance plan is doing... DBCC REINDEX, can crush performance on very large tables that have large indexes or a CLUSTERED index that doesn't follow a chronilogical order. Not just in 2k5, either.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

K. Brian Kelley SSC Guru Points: 114704 More actions · Answer 2

Can you post what is being done in the maintenance plan? That can give us a better idea. As Jeff indicated, there are some operations which can hamper performance depending on how the indices, etc., are used.

K. Brian Kelley
@kbriankelley

xiaomaomao69 SSC Rookie Points: 27 More actions · Answer 3

Thanks Jeff & K. Brian Kelley,

I am out of town couple of days. sorry for delay your reply, as i ask other programmer, they said the administrator running maintance plan most for Check database, shrink db, Reorganized index and Rebuild index.

thanks a lot for your help.

Steve Jones - SSC Editor SSC Guru Points: 739010 More actions · Answer 4

DO NOT SHRINK!

This is bad, ruins your index rebuild and can cause fragmentation. That could be part of your problem.

Can you better define how you know things are slow or what tests you've done to confirm this?

xiaomaomao69 SSC Rookie Points: 27 More actions · Answer 5

Hi Steve Jones,

The application writing in classic asp. using Stored procedure to retrieve data. when run a schedule report. pick up the from date and between date, it just take less 5 second to general a report if the date just in one month. but right now it takes over 15 seconds.

The story was: they tested when moved to new sever. it was quickly. but the night the DBA running the maintenance plan which it is with the SQL2005. thing getting slow. very weird.

I am really appreciate your help.

Steve Jones - SSC Editor SSC Guru Points: 739010 More actions · Answer 6

Make sure that statistics are up to date (and auto gen is on). I'd also rebuild the indexes and run the application a few times. This will help you determine if there is indexing you can do better.

It might be more data that's slowing things, maybe the selectivity has changed. Hard to know what else is wrong.

If you run the same query through SSMS, is it slow? Can you look at the plan and see what seems to be taking a long time? Maybe post the query and plan.

I doubt the maintenance plan is causing issues other than shrink potentially causing issues.

Brandie Tarvin SSC Guru Points: 173108 More actions · Answer 7

Stupid question, but when you upgraded your software, did you upgrade your hardware too?

I've noticed on some boxes that if the hardware remains at 2000 levels, that 2005 can run a LOT slower than it's supposed to.

Though, I believe Steve J. is on the right track when he's talking about index rebuilds & shrinkage. How often do your maintenance plans run? Once a day? Once a week? etc.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.