System Resources

The table above illustrates the wealth of Microsoft SQL Server objects that are available but it is important to begin any analysis of a Microsoft SQL server by looking and the system level performance metrics.  In particular, CPU utilisation, memory usage and disk activity.

The online utilisation peak is automatically calculated in RG Solutions and represents the percentage of CPU used (100% = 80% of total CPU).  This is key for measuring CPU utilisation, monitoring growth of any system and for estimating the lifecycle of the system. 

The following chart illustrates how CPU Utilisation could be reported over many days:

Next, the report provides a measure of the amount of memory available.  It is vital to ensure that a Microsoft SQL Server is configured with enough memory for optimisation and it is equally important to measure memory use over time to maintain the level of service expected.

The online utilisation peak is automatically calculated in RG Solutions and represents the percentage of CPU used (100% = 80% of total CPU).  This is key for measuring CPU utilisation, monitoring growth of any system and for estimating the lifecycle of the system. 

The final section of this report focuses on the disk sub-system.  Obviously, it is important to provide sufficient disk space so the report provides an overall measure of what is used (operational reports prioritise capacity reporting from either the processor or individual disk levels).  The measures of average IO time provide important insight into the Physical disk performance.  High average IO time indicate performance issues which need attention.  Detailed investigations can be performed in RG Solutions to identify the physical drive(s) suffering high average IO times and duration.  This can also be correlated with SQL actions that impact physical disk performance.

Use of a report like this provides an instant high level understanding of how a system is performing and, in the long term, allows measurement of growth and trends.  A sound understanding of system level performance provides the foundation for subsequent analysis of Microsoft SQL performance data.

SQL Process

The Process Information compares the processing activity (CPU used, data operations and page faults) for just the SQL Processes against that of all other processes. 

It is not only useful to be able to compare SQL process CPU utilisation against Total CPU utilisation, but in other sections of the report it is extremely useful to be able to scan across a row in the report and compare multiple items.  One such example might be comparison of CPU time and the number of Batch Requests.

Database Information

The Database section of the report provides information on the number of transactions for all databases instances and the data / log file sizes.

The ability to measure the total number of transactions for databases is very useful for determining trends and growth in database activity.  Knowing the log file size is also important information when determining the correct size of tempdb and for ongoing management of this to ensure sufficient space is available.

General Information

The General Information section of the report provides server wide information regarding the number of batch requests, compilations, recompilations and the cache hit ratio.

The number of Batch Requests that can be serviced is relative to the hardware but an increase in Batch Request will generally follow a step in CPU.  Using this report over a longer period of time provides a clear picture of how the number of Batch Requests affects CPU. 

Ideally, the number of SQL Compilations should be as low as possible.  High numbers of Compilations tend to indicate ad-hoc queries which should be handled in more efficient manner.

SQL Recompilations can cause deadlocks or compile locks so these should be as low as possible to prevent performance issues.  Cross checking Recompilations with the number of Transactions and Lock Information row of the report provides a means to check this. 

The effectiveness of Caching can be measured using the Cache Hit ratio.

Locks

The Lock Information section provides a key metrics for assessing SQL Locks for all resource Types. 

Using this section of the report, the number of locks and deadlocks can be closely monitored.  Further to this, analysis of the lock waits provides a measure of how many users waited to acquire a lock and is supported by the lock wait time.  Performance improvements can also be realised by reducing the number of locks because this can improve the level of concurrency.

The following screen shot illustrates the detail view of Lock data in which the different types of locks can be seen and the number of locks against each.  Any of these Types could be profiled by hour using filters built into RG Solutions.

Latches

The Latches section of the report provides the ability to measure the level of internal SQL Locks which are known as Latches.  Monitoring Latches to determine user activity and resource usage can help with identification of performance bottlenecks.

Using this section of the report, it is possible to determine if there is any growth in the the number of Latch requests and is supported by the average wait time for these.  High numbers of Latch waits can result in Physical IO activity.  The following chart provides an illustration of this:

Access Methods

The Access Methods section of the report provides a measure of how logical data within the databases is being accessed.  Metrics regarding the number of index searches, scans (full, range and probe) are shown.

The ability to report on methods used to access data within the databases is particularly useful for assessing query performance and for determining if any action is required. 

Measuring the number of Worktables and Workfiles created is very important because of the affect these can have on overall performance of the server.  If many Worktables are being created and dropped, the Tempdb database can become saturated. 

Understanding how data is accessed and how many worktables / workfiles are created are important factors in determining the configuration of the Tempdb database.  This is further supported by combining detailed data on the Tempdb database and physical disk activity for ac complete view of the data.

Buffer Manager

The SQL Buffer Manager section of the report provides information on how SQL physically accesses database pages on disk to store data pages and for the Procedure Cache. 

In this section of the report, the cache hit ratio is a key metric because it represents the percentage of pages that were found in the buffer cache without having to read from disk (this needs to be as high as possible to avoid costly disk reads).  It is also useful to be able to gauge this against the number of page lookups.  The number of list stalls should also be low otherwise it indicates a shortage of memory.

Lazy Writes is a useful metric when compared with Checkpoint because lazy writer eliminates the need to perform frequent checkpoints to create available buffers.

The SQL Buffer Manager section of the report provides information on how SQL physically accesses database pages on disk to store data pages and for the Procedure Cache. 

Want to Know More?

These reports are just a sample of the reports that could be produced to measure Microsoft SQL Server Performance. To download a whitepaper please register your interest via the Support Pages - http://www.cpiuk.com/support.php.