Having a stable database environment includes continuously making sure that things are running as they should. Load processes complete in the normal times, queries run in expected return times. Even as more and more data is added to the system, there is the expectation that things should run in the same times. Monitoring here is important to make sure queries and jobs are running in the times expected, and when slow downs occur you will be ahead of the game if you had been monitoring the times and noticed now additional minutes of run times.
So, what to do? Adding more data to the database is a normal occurrence, and just because things were tuned and indexes were being used previously, the increase in data could have changed things around. Good place to start is with statistics. Making sure that the statistics are current and the estimate percent provides the information for good query plans.
Next indexes, because a query that might have been just using the primary key might now benefit from a more focused index. Also, if possible, check and make sure the query still makes sense or if there is a more efficient way to write the query.
Not only statistics and indexes should be areas to look at for systems that are just continuing to grow, but memory settings, disk space and redo log sizing are all other potential areas.
If now the transactions are bigger and there are waits on log switches this would be something that can be adjusted quickly.
These are all good areas to check and monitor. One sure way I know that the database has been growing in size are the backups. Monitoring backup times and backup file size is a good way to compare, and if the size and timing of the backups have changed dramatically, it would be good to start checking on other areas for performance.
So, even if nothing is changing in the application and things appear to be stable, monitoring size and performance as things grow is part of keeping the database environment stable.
No comments:
Post a Comment