Indexes are important for any database. For canned packages the application designer makes the best indexes they can. The problem is that indexes on any system go through a lot of change as data is added, updated or deleted. Data changes cause indexes to become fragmented, and statistics about the indexes to become inaccurate.
As your data is modified it can change in ways that the publisher of the software did not intend resulting in missing or inefficient indexes. Even with canned applications it is important to have regular scheduled activities to defragment your indexes and to update the statistics. Reviewing missing index reports is another good activity that may renew the performance of your canned application.
Some application vendors build into their systems database management features to address situations where their application is deployed where individuals with database skills are present. Applications do not always do enough to give your database to adjustments it needs. Maurice responds to this issue. He writes:
Canned database management solutions are solutions devised for problem that application creates with the database. Sometimes it is excessive log growth, and application turns temporarily database recovery to simple recovery mode during these operations. Sometimes it is statistics update because an application changes too much the data in one single operation which leads to incorrect access plan, but this is less an issue since SQL Server implemented auto-update statistics (which is not a grant to never do update statistics anymore, which is a more precise process).
In our first years of development with SQL Server we tend to solve these maintenance problems from the application. If a specific update stats for a reduced set of tables can be justified during a big data change repartition (with actual database engine it is less and less true), I think doing anything more is a wrong guess. With the years we became more used with best database management practices and I finally devised an external free open source tool to do proper maintenance (www.yourslqdba.codeplex.com).
All our customers use it and it does the job well for almost all database mix. Our experience is that it is better to have an external database maintenance solution, with a good design, that is able to handle most maintenance issues automatically, and leave database maintenance out of the application. Since that time customers have a lot less issues with database maintenance regarding our application portfolio and I never heard of any other problem with other apps on the same server.
YourSqlDba tool is a big script that deploys a database containing database maintenance tools and tables, schedules two maintenance task (daily) and logs backups every 5 minutes, and setup database mail to let know how maintenance goes.
It handles most typical problems encounter by applications. Frequent logs backup is a way to prevent them overfill, which help to leave production database in full recovery mode in almost all cases. Log backup also shrink logs if they become larger than a size which is ratio of the size of database data. Index reorganizes are performed only when a given index fragmentation goes beyond a given size. Update statistics are made full or on a sample of a table depending the size of the table, which makes update stats windows more predictable and they are spreadable over many days. Backups are taken with a clue in backup description indicating to which file it was put, etc., etc. Details of maintenance operations are logged into maintenance database and can be read directly or through a stored procedure the show maintenance history or error.
All this to say that canned database management solution have to be minimal and carefully designed. If they exist, the application should give the ability to turn them off with explanation on what they do and when so a DBA can decide to implement them accordingly with its maintenance or leave it to on. YourSqlDba was our answer to lack of proper knowledge about how to do proper database maintenance, and we improve it from time to time to cover more and more scenarios.
Thanks Maurice for sharing about YourSqlDba. I’m sure it will save a lot of people resources they could spend elsewhere.
Share your thoughts on the topic here or by Email to btaylor@sswug.org.
Cheers,
Ben