The Art of SQL

Chapter 12. Employment of Spies

Monitoring Performance

And he that walketh in darkness knoweth not whither he goeth..

Gospel according to St. John, 12:35

Intelligence gathering has always been an essential part of war. All database systems include monitoring facilities, each with varying degrees of sophistication. Third-party offerings are also available in some cases. All these monitoring facilities are primarily aimed at database administrators. However, when they allow you to really see what is going on inside the SQL engine, they can become formidable spies in the service of the performance-conscious developer. I should note that when monitoring facilities lack the level of detail we require, it is usually possible to obtain additional information by turning on logging or tracing. Logging or tracing necessarily entails a significant overhead, which may not be a very desirable extra load on a busy production server that is already painfully clunking along. But during performance testing, logging can provide us with a wealth of information on what to expect in production.

Detailing all or even some of the various monitoring facilities available would be both tedious and product-specific. Furthermore, such an inventory would be rapidly outdated. I shall concentrate instead on what we should monitor and why. This will provide you with an excellent opportunity for a final review of some of the key concepts introduced in previous chapters.

The Database Is Slow

Let’s first ...