There can be many culprits behind sub-standard SQL Server performance, and if poorly optimized queries are not pulling their weight, this should be at the top of your troubleshooting to-do list.
To that end, here are some tips that will help you remedy query complications and some advice on how best to handle performance tuning more generally in an SQL Server environment.
Contents
Use the right monitoring software
One of the biggest hurdles to efficient SQL Server query optimization is being able to pinpoint the queries that are causing problems in the first place. The best way to do this is with the right monitoring software, of which there are many competing options to consider.
The assets to look for in SQL Server performance tuning solutions are nicely covered here, and as you might expect, the identification of slow queries can be automated, which makes the life of the administrator much easier. Furthermore, the software should let you see a graphical representation of execution plans for queries so that those which are creating congestion can be tuned in a jiffy.
Consider locking priorities
Query conflicts can create sluggish SQL Server performance if too many processes are trying to simultaneously use the same resources.
The over-use of exclusive locks can exacerbate this issue, although it is also worth remembering that blocking is an entirely normal and ultimately essential part of the way that this type of database operates. Because of this, you should focus your attention on any deadlocks that occur, as this is indicative of a more serious issue and in this context, one of the two competing processes will be terminated, which is clearly suboptimal.
Indexes may be the issue
Queries taking too long to execute could be caused by an excess of indexes in your database, so by staying on top of your administrative responsibilities in this area, you could tune performance and avoid having to spend a lot of time rewriting the queries themselves when they are not at fault.
If possible, unnecessary indexes should either be deleted altogether or merged. Also remember that indexes can add to the efficiency of a database in the right circumstances; it is all about balancing this to avoid unwanted slow-down.
Limit records returned
The larger the database, the more information a given query may return when it is run, which of course means more processing time is required.
Because of this, it may make sense to explicitly limit the number of records that are called up by a query using the appropriately named LIMIT statement. You can set the limit to as many or as few records as you see fit, but it is another example of how solving slow SQL Server queries is not necessarily a hugely complex process, even if it is one which lots of DBAs need to address on a regular basis to keep up with their maintenance and optimization responsibilities.
