Even canned software solutions using a SQL Server database may benefit from tuning. SQL Server does a pretty good job of hiding these details from you with “out of the box” configurations performing database maintenance operations, sometimes without users even knowing they exist.
Automatically creating or updating statistics helps keep your database tuned as your data grows. When a canned product is first deployed the amount of data is usually smaller than after it has been used actively for a few days, weeks or months. When the data grows, if the statistics are not maintained, the query optimizer will not have the information it requires to make good index selection or join operations.
If a company has a DBA on staff they will often disable some of the automatic creation or updating of statistics and move those tasks to be performed during a low utilization period. If a canned database is implemented without automated updates, and steps are not taken to keep them current, then database performance can degrade. Conversely, if automated create and update of statistics is implemented, that can slow down performance as the data grows during heavy data manipulation loads.
If automated management of stistics is turned off make sure they are updated through a scheduled job on a frequent basis. If they are turned on, and you database modifications become sluggish, you may need to consider moving statistics tasks to an off-hour scheduled process.
Tomorrow we can take a look at indexes, and how they can impact the performance of a canned database application.
Cheers,
Ben