If your SQL server is struggling to keep up with the load that it is under then your first instinct might be to try to upgrade the server, but this isn’t usually the only option according to experts Red9. There are things that you can do to get more performance out of your server without spending a lot of money, and many of them are nice ‘quick wins’ that will really help you to enjoy faster query execution times and better stability.
Optimizing Your Databases
The first thing to look at is how well designed the databases are. Use the Dynamic Management View to access statistics about the performance of the server. This will give you access to wait statistics and other information that will flag up major bottlenecks.
Poorly written queries can often be a performance issue. Use T-SQL to find the execution time of each of your queries. Don’t make the mistake of assuming that any query which is slow is automatically a bottleneck. The frequency of execution is important too. If a slow query is run once at the end of the day, for example, then that’s not going to have as much of an impact on the overall performance of the server as if the query was being run repeatedly throughout the day to perform actions on customer accounts in supposedly ‘real time’.
Often, the source of poor performance is querying a large table that does not have an index. Adding an index to a table is frequently a simple and high-reward change, and if you have sufficient control of the database to do so then it is worth making that change. Unfortunately, if a third party designed the database it may not be a simple option and the structure of the database could make optimization much more complicated.
Too Much of a Good Thing
Note that while indexes are incredibly useful for things that are frequently accessed, if you have indexes that are not being used then you may want to get rid of them. It is possible to have too much of a good thing! You should look at getting rid of indexes if there is no reason to have them because they use a lot of CPU and I/O time to maintain them. Every time data is inserted into a table, SQL server must update the index as well as the table itself. This means that the overhead for the index is worthwhile only when there is a lot of searching going on.
Log Files and Data Files
Another useful performance optimization is to make sure that your log files and database files are on separate drives. This is because writing and reading data files is not sequential, but writing and accessing log files is. If they are located on the same drive then you can’t easily optimize the I/O. Make sure that when you are setting your databases up you take into consideration the location of the files. Speak to your systems administrator if you aren’t sure which drives to put things on.