<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
SQL Server 2005 Performance Tuning
»
Best Maintenance Plan
Best Maintenance Plan
Rate Topic
Display Mode
Topic Options
Author
Message
ArjunaReddy
ArjunaReddy
Posted Sunday, November 18, 2012 11:44 PM
SSC Rookie
Group: General Forum Members
Last Login: Tuesday, May 07, 2013 10:08 PM
Points: 39,
Visits: 388
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
GilaMonster
GilaMonster
Posted Monday, November 19, 2012 2:14 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 11:21 AM
Points: 37,686,
Visits: 29,943
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
ArjunaReddy
ArjunaReddy
Posted Monday, November 19, 2012 4:35 AM
SSC Rookie
Group: General Forum Members
Last Login: Tuesday, May 07, 2013 10:08 PM
Points: 39,
Visits: 388
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
GilaMonster
GilaMonster
Posted Monday, November 19, 2012 4:38 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 11:21 AM
Points: 37,686,
Visits: 29,943
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
ArjunaReddy
ArjunaReddy
Posted Monday, November 19, 2012 5:52 AM
SSC Rookie
Group: General Forum Members
Last Login: Tuesday, May 07, 2013 10:08 PM
Points: 39,
Visits: 388
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
GilaMonster
GilaMonster
Posted Monday, November 19, 2012 6:38 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 11:21 AM
Points: 37,686,
Visits: 29,943
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 »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
