SQL Server Performance Audit Overview

Jay McVinney
SQL Server Performance Audit

Contents

Summary

SQL Server Performance Audit Overview

Executive Summary

Critical Tasks with no Infrastructure Expense

Critical Tasks with Infrastructure Expense

Non-Critical Tasks with no Infrastructure Expense

Non-Critical Tasks with Infrastructure Expense

Complete Roadmap

Current Configuration

Hardware Current Configuration

CPU

Memory

Disk Controllers

Storage Details

Network Interface Cards

Windows Server Current Configuration

Disk Fragmentation Level

SQL Server Current Configuration

Findings and Recommendations

Methodology

Infrastructure

CPU

Memory

Disk Storage

Network

Operating System

Virtualization

Configuration

Database Server

User Connections

Location of SQL Server Executables

Location of System Databases

Location of the tempdb Database

Location of User Databases

Configuration Settings

Locks, blocks, and Latches

SQL Agent

Failed Maintenance Plans (Jobs)

Failed Jobs

Never Ending Jobs

Database Configuration

Current Settings

Recovery Model

Clustered Indexes

Duplicate Indexes

Rebuilding Indexes

Security

SQL Injection

Use of the SA account

Object Level Security

User Accounts with matching passwords

Attack Vectors

Access to system databases

Sample Databases

Application Access

Table Width

Direct Table Access

Opening, Reusing, and Closing Connections

Summary

The database assessment is a review of the data architecture and its current implementationacross existing databases. It examines the physical implementation from SQL Serverconfiguration to physical implementation of database files, server settings and configuration,database administration including database backup and recovery, and some issues specific toclustered environments. Performance and stability are iterative processes and changes aregenerally made followed by monitoring. Special attention is paid to integration of internalreporting systems to the centralized database.The goals of this assessment are to provide you with the information you need to:

• Provide a stable production environment
• Improve quality as perceived by their clients and user community
• Increase system uptime and reliability
• Decrease the business risk of lost knowledge and data
• Leverage the best use of funding for the right hardware
• Decrease the amount of unplanned time that is currently devoted to fighting fires by development and support personnel

SQL Server Performance Audit Overview

Database Performance Audits generally are iterative and evaluate the system as a whole todetermine specific performance bottlenecks and to be proactive in regard to scaling andcapacity planning. Initial analysis usually is followed by coarse adjustments to bring thesystem operability range away from the edge of unacceptable performance. Theseadjustments may include hardware changes or additions, configuration changes, or otherinitial measures that may be critical changes in code. Once this first phase of coarseadjustments are determined and implemented, an iterativeevaluation will then reveal the lesscritical and finer adjustments that will then be defined and implemented. The basic changesand order of changes implemented may be refined as new information is available or in the case that a severebottleneck is found. The individual phases will be in the form of a checklist to helpidentify any obvious performance problems with the SQL Server installation(s). This checklistwill help identify the issues which are most easily resolved and which have the largest impact. Once these issues are properlyaccounted for and resolved the more complex SQL Server performance problems can be addressed.

Optimal configurations rarely have defined absolutes when it comes to performance tuning. While one particular performance‐tuning tip may help boost performance one way, this same tip may hurt performance in another way.The broad categories for the initial audit are:

• Using various tools to aid in identifying SQL Server bottlenecks
• Server Hardware Performance Checklist
• Operating System Performance Checklist
• SQL Server 2005 Configuration Performance Checklist
• Database Configuration Settings Performance Checklist

Items belowwere out of scope for the initial assessment:

• Backups, high availability, recovery
• Application and Transact‐SQL Performance Checklist
• Data Architecture
• Workflow Assessment
• Application Assessment
• Testing and Optimizing
  

Executive Summary

A database audit was performed on the database server atfrom ______through ______. Overall, the server characteristics show that the server is underutilized and is suffering only minor issues with performance. The only major bottleneck detected was file I/O. Most of the performance related recommendations revolve around disk configuration and physical separation of data, log and system files. The relatively large number of databases will result in database management issues if the server is used for future needs.A future consideration may be that, when a project needs powerful server hardware, this server is a candidate for replacement with a less expensive server and the existing hardware can be repurposed to fill the need for the project to lower overall costs.

External security on the server is very good with access being blocked to all unauthorized access at the network level. Internal security shows many opportunities for improvement which are detailed below. These opportunities for improvement have impact to both internal and external security. The server shows vulnerability to external threats via leapfrog type attacks where an external threat exploits a server that is exposed externally and uses the exploited server to attack internal assets which are normally protected from external access. The most notable security issues are the installation of SQL Server Reporting Services on the same server as the database, the existence of logins which have the same value for both username and password and the lack of object level security.

Emphasis on improvements rising from this health check should be placed on avoiding future performance issues and providing stronger internal security as there are no indications of current issues other than minor I/O waits. Prioritization of tasks while preparing the roadmap was performed with a focus on security.

Initial empirical measurements suggest the following general improvements presented in order of technical priority and complexity. It is recommended to make the improvements listed as having no infrastructure costs immediately. Once these improvements have been implemented, those tasks which have direct costs and the dependent tasks should be accomplished based on funding. The intent is to provide a specific roadmap that can be implemented over time. The lists are in four categories to aid in the prioritization process:

• Critical Tasks with no Infrastructure Expense
• These are tasks which are required in order to mitigate risks of outages or availability of data. These tasks have no intrinsic cost other than the effort to perform the task. Typically the improvements in this section represent configuration changes, data storage changes and other items that can be performed with little effort and cost.
• Critical Tasks with Infrastructure Expense
• These are tasks which are required in order to mitigate risks of outages or availability of data. These tasks have some type of procurement cost associated, whether licensing or hardware. The improvements in this section include infrastructure upgrades and changes with major impact to systems, such as architectural changes or changes requiring extended outages to implement.
• Non-Critical Tasks with no Infrastructure Expense
• These are tasks which will provide performance enhancements. These tasks have no cost other than the effort to perform the task. These tasks will typically consist of configuration changes that will provide incremental gains over time.
• Non-Critical Tasks with Infrastructure Expense
• These are tasks which represent an investment that will maximize the benefits of a database server. These tasks are those which require an investment over time and will provide incremental improvement in the overall system health. An example of this type of task is to make a change to bring the server closer to best practices. While there is no immediate benefit, the strategic benefit justifies the investment.

Critical Tasks with no Infrastructure Expense

1. Resolve the issue with usernames that have the user name as the password.

Critical Tasks with Infrastructure Expense

1. Move SQL data files to SAN file storage utilizing RAID 5 for data and RAID 10 (0+1) for logs

Non-Critical Tasks with no Infrastructure Expense

1. Investigate and resolve job failures as detailed below.

Non-Critical Tasks with Infrastructure Expense

1. Upgrade Memory (RAM) in server
2. 10 9

Complete Roadmap

Task / Estimated Effort
(Hours) / Estimated Infrastructure Cost / Priority
Resolve issues with logins that have identical user names and passwords / 1 / $0 / Sec
Remove sample databases / 1* / $0 / Sec
The roadmap is a prioritized list of those action items that are identified during the health check

* Task will take less than 1 hour and will have a lower effort if bundled with other services

**Represents a project that will require discovery to determine costs