Taking the time to tune your SQL server can deliver big performance improvements and bring with it a whole flood of other benefits that make it entirely worthwhile. Here are words of wisdom from an expert.

Of course, you may not know where to get started or how to go about making adjustments that will actually have a tangible impact on responsiveness and reliability. If you are looking for an in-depth explanation of all the ins and outs involved, this performance tuning guide is a very useful reference. If you are just after a few tips to help you to enhance your existing SQL admin skill set, here are just a handful of pieces of advice that it makes sense to keep in mind as you approach this process.

Trace the issue to its root

This is arguably the most important thing to do when troubleshooting SQL server performance, since you could spend hours scratching your head trying to work out what is causing server issues, only to find out that the problem is external.

For example, the fault may lie with the network connection, the way access permissions are managed or how security is being handled in the wider ecosystem. By tracing the issue first, rather than simply assuming that the SQL server is the culprit, you will save yourself a whole lot of time and frustration.

Use wait stats wisely

SQL server wait stats can be a very fruitful source of information when you are in the process of pinpointing performance snafus. The most effective way to harness them is to align them with other data that indicates how the server is performing from minute to minute to see if there is any clear correlation which could ultimately lead you to determine what is going wrong.

For example, looking at I/O performance alongside wait stats will give you an idea if this is the area in which a bottleneck is occurring, thus indicating where you should focus your performance tuning efforts.

Optimize queries to overcome conflicts

If an SQL database is performing sub-optimally, chances are that queries are at the core of this complication.

There are several ways to optimize SQL queries, often by adjusting clauses and operators to make sure that they are executed as efficiently as possible without putting unnecessary strain on the server as a whole. Conversely if a query is not properly written it could end up becoming a resource hog, especially if it is used very frequently.

Once again the effective analysis of wait stats will be able to give you insight into which queries are to blame for sluggishness in an SQL server environment; you just need to take the initiative and scrutinize them regularly to avoid serious snarl-ups occurring.

Address index inefficiencies

Index-level performance issues are another common area of SQL server maintenance that should be investigated by conscientious DBAs frequently.

Index fragmentation is perhaps the most prominent problem in this particular niche, which is why you should schedule in regular sessions to address this if you want to smooth out performance and prevent this problem snowballing if left unattended.

Inefficiencies can also arise as a result either of index over-use, or an inadequate number of indexes being created within your SQL database environment. It is all about finding the right 

balance and ensuring that information can be retrieved quickly and precisely without creating unnecessary strain on the system.

Once again there is a plethora of approaches to index optimization and as you become more experienced as a database admin you will be able to pick and choose from the most suitable solutions that you have up your sleeve, rather than having to resort to guesswork.

Use testing wisely

When you think that you have found the problem that is causing performance issues and taken action to fix it, you are arguably entering the most dangerous phase, since the temptation to rush ahead and implement changes as soon as possible will be overwhelming. However, it is crucial to remember to test any changes made to your SQL server before rolling them out wholesale. If you do not, you could be exposing yourself to a whole range of dilemmas that are easily avoidable with a little double-checking.

Talk to any experienced DBA and you will hear horror stories about how a seemingly innocuous change made to a query that was implemented without thorough testing lead to data being deleted, server outages occurring and widespread ramifications for the individual and the organization at large.

Learn from the mistakes of others rather than making the same slip-ups yourself and your efforts to improve SQL server performance will not be penalized.

Anticipate hardware faults & eventual limitations

Even server-grade hardware is not entirely bulletproof and you can expect that at some point there will be performance issues related to a fault or outright failure of a component within the physical server itself.

Major faults are obviously straightforward to spot; if a hard drive stops spinning or a stick of RAM gives up the ghost, then the effects will be immediate and unambiguous. Of course others are slow-burning and subtler, so you will need to delve into the performance metrics once more to seek them out. Furthermore, the shelf life of SQL server hardware is limited not only by the physical integrity of key components, but also by their ability to keep up with the workloads that they are required to handle.

As the server fills up and the storage capacity is gradually exhausted, performance will deteriorate. Likewise elements like the CPU and memory may gradually become less effective as they are required to encompass more rigorous workloads.

Outlining a suitable upgrade path and even future-proofing your SQL server hardware during the procurement phase can make your life easier. You can also use your performance tuning and maintenance sessions to benchmark whether the hardware configuration is still up to the task and plot out at which point it will no longer be fit for purpose, allowing you to plan ahead.

• Kevin Kline is Principal Proogram Manager at SentryOne and author of many books on data, including SQL in a Nutshell (O'Reilly).

Related articles:

Identifying your slowest SQL queries

Improve SQL Performance – Know Your Statistics

Improve SQL performance – An Intelligent Update Statistics Utility

Improve SQL performance – find your missing indexes

A Generic SQL Performance Test Harness

To be informed about new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on Twitter, Facebook or Linkedin.

Comments

Make a Comment or View Existing Comments Using Disqus

or email your comment to: comments@i-programmer.info

<ASIN:0596518846>