Often specialists, engineers, programmers forget about the importance of maintaining the database and faced with performance problems. First begin to analyze available software and hardware resources, application code, indexes, disk arrays as well as the network. In majority of the situations a sharp deterioration in performance. Best is to start with a thorough check of the performance of database maintenance procedures and in case of high-quality service to pass to other types of analysis.
A typical maintenance of databases consists of several main parts. Rebuilding and reorganizing indexes. Indexes are database objects that belong to tables which are necessary to speed up the search for required samples from the tables. Updating of statistics of databases. Statistics are the object of databases which is necessary for the formation of the optimal plan for the execution of the SQL query. Making sure to take into account the distribution of data within the table. Cropping or incrementing database files and transaction logs. Updating the cache procedures that contains compiled execution plans. Control over the free disk space associated with the increase in the size of data files. We will examine the main features and indicators by which you can determine that the database needs to improve maintenance activities.
Maintenance of indexes
In the index there is a parameter such as FILL FACTOR. This is the percentage of filling in the data pages of the index. For example: 80, this means that 80% of the index pages are filled with data and 20% remains to fill in the process of changing data on the index fields. What happens when there are multiple changes to the data in the index fields and 20% of the free pages are not enough to fill in the new values? Then the data will be stored in new index pages and the data sequence according to the sorting will be out of place. This phenomenon is called fragmentation of the index. It degrades the efficiency within the index, thus the data in the order necessary to obtain the sampling order are located in different places of the file.
Important indicators of the fragmentation within the indexes is ScanDensity. For the non-fragmented index the value of this indicator is 100 as the fragmentation value of this indicator decreases. Often the administrators monitor the quality of index maintenance for jobs to rebuild and reorganize indexes on a schedule. This is a very rough estimate since the script may not work correctly. It may not take into account new tables and indexes. More correctly is to evaluate through the use of the ScanDensity.
Let’s move onto the actual script for reorganizing / rebuilding the indexes:
Reorganization of indexes is defragmenting index pages. This process is similar to disk defragmentation. The overall process is not long and it does not block users. It’s almost impossible to achieve SCANDENSITY = 100.
Index rebuilding is the process of creating a new index within the actual data. The process can take a long time depending on the size of the fields, number of rows. This block users receiving samples using this index but SCANDENSITY = 100 can be obtained.
Normally it’s recommended to use reorganization for indexes with SCANDENSITY> 85 AND SCANDENSITY <95 and rebuild the index for SCANDENSITY <85.Update statistics
Why do we need statistics, why do we update them?
To obtain a sample SQL query there is no single option. A number of algorithms have been developed and depending on the distribution of data may differ in execution time. For example; if the expected number of rows in one of the tables is small at the intersection the optimizer will use the nested loops to obtain a sample. If it’s larger then merge or a hash function (hache join or merge join) is required. Then the statistics provide up-to-date information on the current distributions on the data. The statistics are constructed and somewhat more complicated but for understanding we have slightly simplified its meaning. If the statistics are irrelevant an incorrect execution plan will be built to execute the SQL query. Accordingly instead of executing the X query, the query will start to execute N * X where the value of N can be very large.
Without analyzing the quality of database maintenance you cannot begin to analyze the optimality of the SQL query code, index tuning as this can lead to unjustified waste of time and money.
