Health Checks for Microsoft SQL

The Health Check for Microsoft SQL Server is a carefully crafted program designed to review the efficiency as well as effectiveness of a Microsoft SQL Server-based database management system. The efficiency of the system is evaluated by determining the extent to which the Microsoft SQL Server products have been utilized; essentially, is the system “firing on all cylinders”?

The efficiency of a system takes into consideration issues such as:

• Is the database and operating system properly tuned?
• Are there sufficient hardware resources for the various applications?
• Have schemas been normalized or denormalized where appropriate?
• Have the proper indexes been created?
• Are critical administrative utilities run regularly?
• Does the staff have the requisite skills needed to maintain an efficient system?
  

In a complex computing environment, the extent to which these and other issues are addressed will affect the overall efficiency and effectiveness of the systems in place. The SQL Server Health Check addresses these issues of efficiency and effectiveness by having a skilled SQL Server engineer assess your SQL Server-based computing environment over a one to five day period.

Ideal System Performance

To achieve the optimal performance for a given system one must ensure the optimal performance of each component of the system. The components addressed in this performance analysis are:

• Hardware utilization including CPU, I/O bottlenecks, and memory
• Operating System and storage configuration
• SQL Server Database Server installation and configuration
• Client Communications
• Application Implementation
• Operations and Maintenance including backup, recovery, consistency checks and others.
  

Each of these components is critically important to the optimal performance of the overall system.

The following are some of the issues related to each system component.

Hardware Resources:

The server hosting yourMicrosoft SQL Server database relies primarily on three hardware subsystems for efficient performance – CPU, Memory, and I/O.

A well-architected system will show optimal CPU utilization without queues. A fully utilized CPU is ideal, but an additional check for the number of processes waiting for the CPU is required.

The I/O subsystem of the server hosting your MS SQL system is critical to the performance of the database and applications. Ideally, the disks in the system will be responding to requests in less than 50 milliseconds, and there will be no queues forming on those disks. Additionally, for large decision support queries, the controllers will be analyzed for bottlenecks and limitations. If RAID is being used on-site, this will be analyzed for its effect on the database and applications. I/O configuration is the most flexible of the resources, since the database and system administrators can easily work together to balance the I/O load across all available resources.

A large server may contain several gigabytes of main memory. The use of that memory has to be carefully allocated among the critical functions of the server – applications, database, and operating system. The goal is to have ample free memory to meet the peak demands of the workload, while maintaining good cache hit rates in the database and avoiding process swapping.

Operating System:

The operating system is the least likely place to find performance issues; however, Microsoft Windows Server does have a number of memory configuration options. These will be analyzed and addressed.

SQL Server Database Server:

There are numerous items that relate to database performance. However, key elements of this score will reflect the use of indexes, table fragmentation and I/O balance, optimizer statistics, parameter settings, transaction logging, database layout, session activity, cache utilizations, and others. These areas will be investigated extensively based on the overall behavior of the database and application. Each component of the analysis will be detailed in the report that is delivered at the completion of the service.

Client Communications:

There are several optimizations available for client PCs connecting to your MS SQL Server. These will be evaluated for appropriateness in the environment. Additionally, if clients are connecting to SQL Server with older versions of software, there is tremendous opportunity for performance improvements through client library upgrades.

Application Implementation:

Database applications can often be enhanced through techniques that have been introduced since the application was originally designed. A discussion with the application developers will aid the DBA in making recommendations for improving the application.

While the above analysis strives to leverage hardware and software to its fullest potential, this area of the assessment strives to provide operational stability to the environment. Looking at batch jobs, backup and recovery strategies, logging strategies, upgrade strategies, and test platform capability will enable the DBA to provide recommendations for improving up time of the environment.

During the Performance Analysis, clients will provide access to pertinent systems and key personnel to ensure a thorough and productive assessment. Key individuals within the environment will be the DBA, the System Administrator, the Application Team Leader, and others identified as subject matter experts.

Health Check for Microsoft SQL (pdf/144kb)