Articles & Case Studies

Technology & Development

SQL Server Performance Tuning: Part 4 - Resolving Issues

Preface and Disclaimer

Welcome to the fourth part of our series on Microsoft SQL Server Performance Tuning.

The central topic of this article is the resolution of performance related issues, and we try to give you some of the main options - although occasionally you may discover a "silver bullet", in most cases, performance issues can only be solved by spending sufficient time researching and investigating the issues at hand. Quite often the code changes which must be made are not dramatic, but the results can be impressive.

Please note that we can accept no responsibility from any losses or damages arising from any attempt (appropriate or otherwise) to implement the recommendations below. Also, please note that this guide was primarily written with Microsoft SQL Server 2000 in mind, and although we still believe that the principles explained herein are equally relevant to SQL Server 2005, some of the specific suggestions made no longer be relevant.

Resolving Performance Issues

There are essentially three approaches to resolving a problem, once the main players have been identified and examined in depth – the choice as to which of these (or which combination of them) to use depends on a number of factors including the amount of time which is available to complete the work in, and the level of risk which is deemed acceptable.

Ultimately some problems can only be resolved by re-designing the affected areas of the system – but in general these sorts of problems can be alleviated sufficiently by lesser changes to keep things going until such time as this becomes a possibility. Early diagnosis is critical to keeping a system running as smoothly as possible.

Re-Factor

A continual process of re-factoring (many sets of minor changes) can be very effective in improving the performance of a specific query – this technique is most suitable when a small set of procedures are deemed to fit well into the application design, but do not perform adequately.

It is recommended to approach this by making several passes through each object, with basic unit testing (to ensure results and/or outputs have not changed due to code alteration) as well as additional examination in profiler and query analyser execution plan.

In general, the first pass should be the application of the best practices recommended in this document, if they are not already being followed by the code in question. Subsequent passes will involve actions based upon analysis. Some of the approaches which can be taken to re-factoring are as follows, but please bear in mind that the nature of the procedure in question means that some approaches will work better for certain pieces of code.

Split up large queries using table variables (particularly where long, explicit transactions are involved – prepare data first and perform only the final, vital steps within the transaction)
Use table variables to prevent repetition of work (e.g. if the results of a particular query are used more than two times, it is often worth preparing the data in a properly indexed table variable
Reduce the cardinality (number of joins) of queries where possible
Avoid repeated, small queries to the same tables – consolidate these events into a single query which retrieves all the required information in one go
Attempt to solve problems in a set-based way, rather than by iterative logic – SQL is not designed for iteration of recursion, and these operations are always slower than methods which can be resolved using discrete mathematics
Remove all unnecessary declarations and queries
Use IF/ELSE blocks (and other logic-flow or conditional operators) only when absolutely necessary – these impair the ability of SQL Server to prepare effective query plans for re-use
If necessary, place locking hints on targeted queries in transactions to reduce contention
Ensure that procedures which perform similar (transactional) operations on similar tables access these tables in the same sequence, in order to prevent dead-locking (and/or race conditions)

There are many more actions which can be undertaken – too many to describe here; these are simple a few examples of common (effective) approaches.

De-Normalise

Although it is sometimes treated as a "panacea" for slow-running queries, de-normalisation should never be considered a long-term solution unless it is carefully built into the application design and truly deemed to be necessary (generally it is only suitable for sets of data which are both expensive to build on the fly, and relatively static – seldom changing).

Sometimes, however, it can be a useful technique to employ in order to deal with a specific problem in a very short time scale, or to overcome a problem which cannot be solved any other way (e.g. when a set of computations cannot be made any faster within SQL by other techniques, and the long term aim is to perform them using another technology).

Always remember that maintaining de-normalised data can be expensive, and that databases are normalised for some very, very good reasons. Otherwise we"d just be using a single table for everything.

In general there are some key questions you must answer first:

How often will the de-normalised data need to be refreshed?
What percentage of the data will change when these refreshes are performed?
Will the data be expensive to compute (almost certainly yes), and if so, how will the system cope with the load of doing so?
How much space on disk will the data occupy, and therefore, how large will the table be? Remember – very large tables can be expensive to query, even if well indexed!

It almost goes without saying that if you are going to invest significantly more effort in updating de-normalised data than you save by having it available – do not choose this route.

One good thing about this approach is that it is relatively simple to prototype (and not much harder to implement), as you can build the table rapidly in a development environment, and test the new queries against it to see the impact which the changes have had – without having to complete an update routine for the data in question.

Re-Architect

In the end, many problems can only be fixed forever by re-designing a portion of the system. For this reason, it is vital to keep records of performance problems which have occurred, and how they were resolved, so that this information can be re-used in architectural decisions at a later stage.

Sometimes, an area of functionality may work very poorly and be difficult (or time-consuming) to "repair", so the choice may be taken to simply start again – when this happens, be sure that you will make enough of a gain by doing so, within the constraints of the system architecture and without jeopardising the stability of the rest of the application.

You should follow your normal design methodologies and processes to achieve this – from high level design downwards to detailed technical implementation analysis.

Analysing Results

Once a problem is "resolved", the work isn"t finished. A number of extra tasks need to be completed – least of all establishing the permanency of the fix – is there a risk of recurrence or further problems in the near or distant future?

You should attempt to answer the following questions:

Is there a high degree of confidence that no further work is required to resolve the problem in question?
Is any future work required to prevent further problems in the same area?
Have any other potential (current or future) problems been highlighted by this analysis, which should be addressed in the near future?

It may also be worthwhile to quantify the gains made by the work, in order to assess how effective a use of time it was – this is generally neither simple or precise, however. In most cases, either concurrency or execution time have been improved (if not both).

Conclusion

We hope that this brief glimpse into the complex realm of SQL Server Performance Tuning is useful to you, and that it gives you some ideas as to how you might begin approaching any performance issues within your own systems (or even better, that it helps you to avoid them ever occuring), but should you require further information, or if you wish to make an enquiry about the analysis and fault resolution services which we offer in this area, please contact us and we will be glad to assist you further.

We are currently planning a follow-up to this series of articles. If you have comments, feedback or suggestions to make, please get in touch with us via the contact form on our site.

Share on Facebook Tweet This Subscribe (Email)