What to do if your database seems slow
Quite a few times during a business day or even after hours, you have noticed that your database requests or queries tend to be slow with the results. This could be that your database has become bloated with constant usage. There are a few things that the IT / DBA or you, can do to resolve these possible serious issues.
Databases work almost like people, they tend to keep inactive data that uses disk space, poor design and even the over-indexing of tables which could be some of the reasons for poor performance to outright collapse.
Step one – inactive data to archives.
The first step is to research whether there is too much inactive data. This is data that is no longer part of the active processing that takes place such as medical records from several years ago. You need to keep them but should something from 2001 be kept in the current production database. That data can easily be placed in a separate archive database that can be used for historical matters. It can be accessed at any time as well as cutting the backup time for the production database. In the long term this information can either be purged or put into archive backups saved off line.
Step two – Indexing
The second step is to research whether you have way too many indexes on tables that are not used, or on columns no longer important. Indexes slow down write operations, lead to frequent deadlocks or other different issues that occur. There are multiple websites that provide scripts to check on index performances, you can click here to view one of them.
Any sort of performance testing has to be done over time and cannot be concluded in minutes (naturally a bad index or query can be noticed by the time it took to completion). For instance performance might decrease if the indexes are heavily fragmented. For the Microsoft SQL server you can use the sys.dm_db_index_physical_stats system function to detect the index fragmentation especially if it is over 30 percent.
Final Step – Poor Design
Finally poor database design will always be the main culprit in any database package. Regardless of the testing you cannot make a bad structured layout look like a swan unless a major overhaul occurs. You can also simply have memory issues that affect the overall performance or older servers that need to be replaced.
The best thing for any developer to make sure that they lay out the design before going to work and this will cut down 60 to 70 percent of trouble. The leftover 30 percent is just good old data that needs to be dealt with loving care.