Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Best Maintenance Plan Expand / Collapse
Author
Message
Posted Sunday, November 18, 2012 11:44 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, July 20, 2014 10:42 PM
Points: 45, Visits: 531
Hi,

I am currently tasked with an incident to optimize the current MP that we are running on one of our PROD server. The size of the DB is 125GB and are running the MP on a daily basis. Following are the tasks with the MP.

Steps Sequence-

Check DB Integrity -> Shrink Database -> Rebild Index

I am asked by the management to optimize the MP to achive better perfromance. Could you please let me know if the sequence that I am using has no issues? Also please advice if there are any steps that i am missing.

Version : SQL Serve 2005
OS: Windows 2003.

Thanks,
Nagarjun.
Post #1386168
Posted Monday, November 19, 2012 2:14 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:03 PM
Points: 42,485, Visits: 35,554
The sequence you're using definitely does have issues. Do Not shrink your DB on a regular basis. You're just forcing it to grow again, making the index rebuilds do huge amounts of works and spending a lot of time and resources for nothing.

That said, performance is not usually improved by maintenance plans, it's improved by tuning the queries running on the server.

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

p.s. You do have a backup strategy, right?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1386228
Posted Monday, November 19, 2012 4:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, July 20, 2014 10:42 PM
Points: 45, Visits: 531
Thanks Master for your reply.

I would alter my MP to the following Sequence. Please let me know if this is Ok to go with.

Check DB Integrity -> Rebild Index

Thanks,
Nagarjun.
Post #1386294
Posted Monday, November 19, 2012 4:38 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:03 PM
Points: 42,485, Visits: 35,554
That'll do. Again, it's not going to suddenly and magically make the server perform perfectly.

You do have some backups running?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1386298
Posted Monday, November 19, 2012 5:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, July 20, 2014 10:42 PM
Points: 45, Visits: 531
Not on a daily basis but during weekends. We dont have any Txn log bkps or Diffnt bkps running during business hours, so performance kill with backups is ruled out?

Post #1386329
Posted Monday, November 19, 2012 6:38 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:03 PM
Points: 42,485, Visits: 35,554
Backups are not about performance.

Backups are about recoverability. If you have no diff or tran log backups running in business hours, then I assume that losing all data for a business day is acceptable to your users



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1386360
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse