Is your database performing poorly? Are you losing data due to slow performance? Many people design their database without thinking about any long-term performance issues. For that reason this article provides 10 tips that can help speed up the performance of your SQL database.
Properly index columns - Creating an index on a column tells the database that you're going to reference it often in order to locate data. A well placed index can dramatically affect query speed, sometimes taking queries down from minutes to sub-second. Be careful though, creating too many indexes can have a negative result. Also, creating multi-column indexes can be effective, but more often than not is less helpful than expected. Any column that is often used to look up or narrow down data is a good candidate for an index. For example, almost every query on a historical table would reference the timestamp column, so indexing that column would be a wise idea.
Give your machine plenty of RAM, and use it - having a good amount of RAM on your database machine lets it hold more information in memory, reducing slow disk access. Given the low cost, it can be one of the most economical hardware based improvements, as well. Be aware, however, that you may need to configure the database to take advantage of it- many databases have settings that limit the amount of memory the system can use.
Select your "engine" wisely - Some database systems, such as MySQL, support multiple "data engines", or methods of storing data. The different engines each have their strengths and weaknesses, and by being informed and choosing wisely you can significantly improve system performance. For example, the InnoDB engine has good transactional support. However, in many cases this is not crucial, and the performance boost offered by the MyISAM engine is more important. For historical data, the Archive engine can drastically reduce the required disk space; at the trade off of some query speed (indexes aren't supported, though querying an archive table still tends to be faster than a non-index InnoDB or MyISAM table).
Exclude data directory from virus scanner - Many virus scanners include "realtime" components that constantly check changing files for viruses. Since the database interacts frequently with the disk, it can trigger the scanner to execute frequently, killing performance. It is highly advisable to exclude the database's data directory from the list of folders to monitor.
Check your data types - Having columns set to use data type that are larger than the actual data being stored can result in wasted disk space. By selecting the correct data types, you can save space and improve read/write performance.
Avoid sub-queries when JOINs will do - Utilizing JOINs in your SQL Queries allows the database to optimize much more than with sub queries. This results in quicker queries with less memory usage.
Check your SQL Server "auto grow" and "auto shrink" settings - Some database systems, such as Microsoft SQL Server, allow the database to dynamically grow and shrink as data is inserted and deleted. By default, the "auto grow" setting for Microsoft SQL Server is set to grow by 1MB at a time. When data is inserted at a fast rate, the database constantly has to increase the size, leading to disk fragmentation. A general rule of thumb to you can use for testing is to set your "auto grow" setting to about one-eight of the size the table will get. Also, turn off any "auto shrink" settings to prevent that database from constantly growing and shrinking, again leading to disk fragmentation.
Periodically defragment your hard drive - Over time as data is inserted and deleted from the database the disk can get fragmented causing your queries to take longer. Periodically check and defragment your hard disk to avoid this issue.
Check for periodically executing tasks - Some database systems, such as Microsoft SQL Server, can execute tasks, such as stored procedures and back ups, on a schedule. These tasks will run automatically and can affect the performance of the database if executed at the wrong times or if the tasks are not optimized. Periodically check these tasks to find out when they are running and how long they take to execute.
Use database profilers and query analyzers - Many database systems come with profilers or query analyzers that help you see how the database is performing. Profilers are graphical user interfaces that monitor all database events in what's known as a trace file. You can then analyze or use the trace file to troubleshoot logic or performance problems. You can also use the utility to do a stress analysis, fine tune indexes, auditing and reviewing security activity, etc. Query analyzers can be used to recommend indexes for specific tables, find out exactly how the database system will execute a given query, and statistics after the query is executed. This tool can help better optimize slower performing queries.