SQL Server Performance

Database maintenance

Discussion in 'Performance Tuning for DBAs' started by trifunk, Dec 1, 2002.

  1. trifunk New Member

    Hi all,

    I work for a really small company developing software. As it is we're all developers, there's no DBA.

    I was wondering what kind of maintenance we could do with some sort of script that could be run against our clients databases maybe each week or month, something to update statistics and indexes?

    I realise we can rebuild all the indexes but I'm sure there's alot more that can be done to keep the performance sweet and keep the database in a decent state?

    On the other hand, not being a DBA, are there any kind of things that I should't do or that could adversely affect the performance by running such ascript?

    Any suggestions would be greatly appreciated.


    World Domination Through Superior Software
  2. bradmcgehee New Member

    There are two approaches I take to running maintenance scripts, each options has it pros and cons, and I use both of them where appropriate.

    One option is to use SQL Server's built-in maintenance plans for index rebuilding, backups, etc. The pros of using this tool is that it is easy to create and maintain. The cons are that sometimes the available options are not very flexible.

    The second option is to script all maintenance, and then create a job to run the script or scripts at appropriate times. The pros are that this is very flexible, and the cons are that it is more complex to set up and maintain.

    I am not sure what the best option is for you. For example, will your company, or the company who purchases your software, be responsible for installing SQL Server, setting up your software, and setting up their own maintenance, or will you be doing this for them (either on-site or locally)? If you do all of the installation and maintenance, either option will probably work. But if your customers do the work, then I would recommend that you include your recommendations on what you think is best and then document the steps they need to take. This would probably be best done using a Maintenance Plan as it is easier for non-DBAs to setup.

    Another issue to consider is if you want the liability of helping with a customer's maintenance and performance. What I mean by this is if you get directly involved with this, and it doesn't work, you will be blamed for it. But if you make recommendations, and these recommendations have to be done by the customer, and they don't do them, and then if the customer has a problem, they can't blame you (they will probably try, but they can't win the fight). This is a tough decision for you.

    Most of the vendors who provide software that runs against our SQL Server takes a hands-off approach, leaving us with all the issues of performance and maintenance.

    Of course, you may want to offer a "better service", which is also OK, assuming you get well paid for it.

    Specifically which maintenance options, such as backups, updating statistics, rebuilding indexes, which fill factors to use, are all important issues which depend on the database and how its used. I can't offer specific recommendations in this area unless I know more about the data, how much is there, is it OLTP or OLAP, how much the data will change each day, when the database will be busy, what the maintenance windows are, etc. If you want to provide more information in this area. I will try to help.

    Brad M. McGehee
  3. trifunk New Member

    Thanks Brad,

    On the installation and maintenance side of things we do it, remotely as we're on a private network with all our customers. What I've found is that if the response times to the application become slow, the users generally blame the application so in a way we end up having to maintain the database as well.

    There are a number of tables (about 350+) of which I think 60 could potentially need some frequent maintenance (they probably all could) as they contain a couple of million rows each. Some are updated daily with a couple of thousand rows at a time (bulk inserts) and are used for reporting, 2 are audit logs and almost every transaction that takes place is logged here (continuos inserts thoughout the day) and the others are used for basic transactions that occur. Kind of a mix and match of OLTP and OLAP which probably isn't the best solution but sometimes you walk into a job and there's not much you can do about it.

    The database has to be responsive in the day with not as much emphasis on it during the evenings.
    I can understand rebuilding the indexes and updating statistics but when you talk about fill factor I'm a little lost as what values to use on which tables and how frequent these kind of scripts should be run and is there a preferred order to run such tasks in eg rebuild indexes and then update statitics, does it matter?


    World Domination Through Superior Software
  4. bradmcgehee New Member

    In your case, I would probably use the Maintenance Plan Wizard (it keeps everything simple and works well in most cases and will automatically rebuild all indexes in all tables in a database). While it is hard to make a specific recommendation, here is what I would try based on your circumstances. You may want to start with this recommendation, then alter it as needed once you feedback on its success or non-success in maintaining performance.

    Once a week, rebuild the indexes. You don't need to update statistics because when you rebuild the indexes, the statistics are automatically rebuilt for you. Also, assuming that you have "Auto Create Statistics" and "Auto Update Statistics" turned for your databases (which they are by default), statistics generally don't have to be updated separately, in most cases. When you rebuild the indexes, selecting "Reorganize data and index pages" from the "Optimization" tab of the maintenance wizard, and then select "Change free space per page percentage" and then enter 10% (which is the default). This creates the indexes with a 90% fillfactor, which is a good starting point. If you watch the page splits, and you see that they are getting higher, you might want to change this number of 15 or 20 to get you a 85% or 80% fillfactor. Also, schedule the job to run when the database is not being used as it will lock tables during the rebuild process.

    This is a good first step to help ensure on-going maintenance of SQL Server.

    Brad M. McGehee
  5. trifunk New Member

    I'll definately give that a go, thanks.


    World Domination Through Superior Software
  6. gtupper New Member

    You may also want to look into these two stored procedures for index maint.

    dbcc dbreindex

    I use both of these procedures to perform a daily defragmentation of the indexes and then a rebuild of the index once a week. The indexdefrag is not as lock intensive so can be run during production with little effect to performance. The dbreindex will be more intrusive so i would recommend more of an off time run. The final thing would be to drop the index and then recreate. very intensive. I tend to not do the last phase. These procs can be setup in jobs and scheduled to run. They can also be setup to do all of the tables or only some of the tables by creating a cursor with the appropriate criteria.

  7. mjm11 New Member

    Running DBCC IndexDefrag on all tables can cause the tlog to grow and grow and grow. There is a script on BOL that I modified to only defrag the tables that are more that 25% fragged and I also added a tlog backup in the script so the tlog is backed up prior to defragging tables larger than 500mb. Another option is to place the db in simple recovery mode, create a full backup, run dbcc indexdefrag or dbcc dbreindex, place it back in full recovery mode backup again and then place it back in multiuser mode. Good luck
  8. satya Moderator

    MJM, your script will definetly helps the people who're having the issues. And even webmaster will consider to post on this website under tips section if you wish so.

    Satya SKJ
  9. mjm11 New Member

    I'll be glad to do so, let me generalize the scripts, add comments and I'll send it to the webmaster. For as much useful info as I've gotten off of this site, it is the least I could do.

Share This Page