Relational database management system (RDBMS) performance problems are very costly to enterprises. Sometimes they reduce throughput or cause downtime; sometimes they even drive away customers. Enterprises are frequently willing to invest large sums to solve RDBMS performance problems quickly, before they lose customers. The key to preventing such problems lies in RDBMS performance monitoring and baselining. VARs can offer services that address these performance problems. By proactively monitoring the database management system, they can catch performance problems as they start, and performing scalability tests ensures that the RDBMS can scale sufficiently.
Solving critical performance problems is a great way to build your credibility with customers -- and build your business, since success in this arena will bring the opportunity to cross-sell a variety of other services. The need to solve performance problems is so great that some VARs (such as Scalability Experts Inc.) specialize in that service and have large customers on retainer. Other VARs have aligned themselves with monitoring tool vendors in mutually beneficial relationships.
Diagnosing performance problems in a busy RDBMS can be a very difficult undertaking. Here's why:
- The problem might be transient; in such a case, you'll need to collect a lot of historical data to even see the problem, let alone isolate it. Frequently, by the time the DBA is notified that there is a performance problem, it has already gone away.
- The bottleneck might be masked by other bottlenecks, and removing one bottleneck could reveal yet another one.
- Heavy activity in the database might make it very difficult to isolate the problem.
- Determining if the performance problem is related to hardware (due to such issues as faulty NIC cards or an inadequate disk subsystem) or the database (due to fragmentation, inefficient SQL code, poor indexes or an architecture design problem, for example) is frequently very difficult. For example, an apparent disk IO bottleneck could be caused by memory problems or poor index choices. Throwing hardware at a problem is seldom a good solution, since it involves downtime and frequently does not remedy the situation.
The challenges of taking on RDBMS performance problems using capabilities in the native platform are
not trivial. For instance, SQL Server 2005 includes DMVs (Dynamic Management Views), which provide a window into database engine internals. The amount of resulting information is frequently overwhelming. SQL Profiler, which is included with SQL Server 6.5 and above, and performance monitor counters also provide a wealth of information; however, these tools only provide metrics, which can be helpful in interpreting the problems but not identifying them. Frequently, DBAs need to supplement these native SQL Server tools with performance monitoring and troubleshooting tools.
Fortunately, there are several players with tools to address that need. The biggest players are Quest Software Inc., with Spotlight and Foglight; Idera, with Diagnostic Manager; Teratrax Inc., with Teratrax Performance Monitor; Embarcadero Technologies Inc., with Embarcadero Performance Center; Pearl Knowledge Solutions Inc., with SQL Centric; and BMC Software Inc., with Performance Manager for Databases.
Just as RDBMS performance monitoring and troubleshooting are very complex, these tools are also quite complex. Most vendors in this category will train you to use their products to diagnose and optimize SQL Server databases, and some of them do an excellent job with frequent webcasts and downloadable white papers. You can often install the tools, sometimes even the trial versions of these tools, and quickly see exactly the nature of the performance problems, if not the performance problems themselves. These quick wins will frequently pay for the cost of the tools themselves and will always pay for the cost of the performance problems.
You can quickly become adept with RDBMS performance monitoring tools and develop a specialty in solving performance problems. For those who do this kind of work on a regular basis, performance problems typically fall into three areas:
- Ones you have seen before.
- Ones that are similar to ones you have seen before.
- Ones that are completely new.
Most performance problems will fall into the first two categories. If you develop a specialty in performance troubleshooting and monitoring, you'll quickly become familiar with the most common performance problems and be able to leverage this knowledge to solve many of the performance problems your clients face.
Performance problems that are unfamiliar to you present a learning opportunity. The performance monitoring tools will pay dividends here, by helping you to isolate the problem to hardware, inefficient code or indexes. Most of the tools will collect extensive data during a period of poor performance and allow you to drill down to determine the root cause of the problem. You may need the help of outside experts, such as Microsoft, to help you with the diagnosis.
Specialized performance monitoring and troubleshooting tools are essential for most DBAs to supplement native RDBMS monitoring and troubleshooting capabilities. These supplementary tools enable VARs to solve their clients' performance problems, help clients react to nascent performance problems and cross-sell their own service offerings.
