Following on from my previous blog on SQL Server Agent for SQL Server 2005 Express, this post is to run you through ensuring your systems performance is maintained by having your database statistics and indexes in an optimal state. This blog is 3 of 3 on Database Maintenance in SQL Server 2005 Express.
Database Performance
Database performance maintenance is an important factor in your maintenance scheduling to ensure that your queries are running optimally. This blog is not going to look at query performance due to poorly written queries, but look at the administration tasks you should undertake on your databases.
The 2 parts that we are going to look at are:
- Update Statistics – The process of updating the distribution of key values within Indexes on Tables or Indexed views and this helps determine the Indexes that should be used during query processing
- Database Reorganisation – Utilising the Alter Index statement to reorganise you indexes to reduce the fragmentation.
Depending on the nature of your application you could have the AutoUpdate Statistics turned on on your databases or you you can explicitly update the statistics. In this blog the agent job we are discussing is around manually running the update statistics via the SQLScheduler application as covered in my blog – SQL Server Agent for SQL Server 2005 Express . If you do have the Auto Update Statistics turned on on your database it does not hurt to manually force an update so do not be concerned about having both.
The Alter Index statement that I have used to perform the re-organisation of your indexes are the preferred statements as opposed to the older DBCC statements. For more information on this have a look at my blog – Alter Index Vs DBCC Index Defrag Vs DBCC DBReindex.
When performing Index maintenance you have 2 options to choose from, Either a Reorganise or a Rebuild. For the purposes of this blog and the script that is available for download I am only talking about the Reorganise option. The script is easily modified to use the rebuild option.
The frequency that you should look at performing these maintenance tasks are dependant on the nature of your databases with how frequently your data changes and how much data you have in your databases.
As a rule of thumb a good scheduling frequency is:
- Update Statistics – Daily
- Database Reorg – Weekly
Feel free to download any of the following scripts:
- Usp_ReorgDB
- Usp_UpdateStats
I hope this 3 blog series has given you an insight into the maintenance tasks for SQL Server 2005 Express that you can implement when using the SQLScheduler tool.
Leave a Reply