April 27, 2011 at 9:15 am
I am newbie to the SQL server DBA tasks and seeking help from you for few items.
We have some large databases which are more than 20GB in sizes. Due to their size and rate of the size increment, sometimes the database performs slowly and I need to run sp_updatestat to increase the speed. To avoid such situation, I would like to schedule some maintenance tasks on these databases to increase the performance.
When I see the Maintenance tasks (in SQL Server 2008 R2 and SQL server 2005), I can see following tasks apart from Update Statiscs Task
1. Rebuild Index Task
2. Reorganize Index Task
3. Shring Database Task
Are these are similar tasks? Can I increase the performance by rebuilding the indexes or Reorganizing the indexes? What is Shring database (Is it similar to defragmenting?)
and most important thing that I would like to know is, is there any Risk involves If I setup these taks?
Please give me some inputs. Basically, I am a .Net developer with good amount of years experience, but not into the DBA activities so far. Your inputs will help me to get to know about some of the DBA tasks
Thanks
April 27, 2011 at 9:34 am
One thing I would STRONGLY suggest is to NOT SHRINK THE DATABASE.
In the normal course of working it will only grow again, and shrinking can/may introduce more problems that I can list.
As for index maintenance, read books on line to understand how these tasks can assist/impede performance. As is typical with SQL Server the answer is "It all depends". To assist you in evaluating which to use
gather basic information on performance, save this in a table and then as you apply different techniques on improving index usage, after each one gather additional data, so that you can compare for your situation which best suits you.
The one task I would add as first to your list, is to understand the need /imprtance of backing up your databases, and how to test a restore of a backup, (doing the restore on another server, not truly restoring a production DB)
Further I would suggest you read this article here on SSC
http://qa.sqlservercentral.com/articles/Administration/61319/
April 27, 2011 at 9:41 am
Maintenance plans let you setup a sequence of tasks to help maintain database performance, integrity and backups.
You should have several more options available than those you have listed. There is an 'Update Statistics Task' in the toolbox. This would normally follow a reindex job. Choosing between reoraganising or rebuilding indexes is usually based on the level of fragmentation in the indexes. Reorgnising indexes can be done with the database online. Rebuilding indexes (unless specified ONLINE) is offline.
Try not to use the shrink database option if you can avoid it. You'll only introduce fragmentation, and the cost of subsequent regrowths is very expensive. There's several blogs out there why this shouldn't be performed
How are you managing your backups?
Once a maintenance plan is created, you then create a schedule for the plan. This creates a Job which invokes the plan on a given frequency. It's a good idea to schedule this stuff outside core business hours.
April 27, 2011 at 9:46 am
rajeeshunn (4/27/2011)
I am newbie to the SQL server DBA tasks and seeking help from you for few items.We have some large databases which are more than 20GB in sizes. Due to their size and rate of the size increment, sometimes the database performs slowly and I need to run sp_updatestat to increase the speed. To avoid such situation, I would like to schedule some maintenance tasks on these databases to increase the performance.
When I see the Maintenance tasks (in SQL Server 2008 R2 and SQL server 2005), I can see following tasks apart from Update Statiscs Task
1. Rebuild Index Task
2. Reorganize Index Task
3. Shring Database Task
Are these are similar tasks? Can I increase the performance by rebuilding the indexes or Reorganizing the indexes? What is Shring database (Is it similar to defragmenting?)
and most important thing that I would like to know is, is there any Risk involves If I setup these taks?
Please give me some inputs. Basically, I am a .Net developer with good amount of years experience, but not into the DBA activities so far. Your inputs will help me to get to know about some of the DBA tasks
Thanks
Thanks for the fast reply
Yes, I am currently doing this tests on test environment after restoring the full backup of few databases into Test server.
I will google for the topics and will check your link
Thanks again; I will keep on updating this thread
April 27, 2011 at 10:01 am
You might want to also read this blog posting of Paul Randal
It is a survey of what other DBA's feel is their most pressing performance problems.
http://www.sqlskills.com/BLOGS/PAUL/post/What-do-you-look-for-when-analyzing-query-plans.aspx
Edited to correct link to Paul Randal's blog post.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply