VIRTUAL-DBA: SQL SERVER PERFORMANCE AIRLINE CUSTOMER

CHALLENGE

An airline data center client was migrating from stand-alone Microsoft SQL Server® database servers to a Virtual Machine (VM) environment on their own, and creating one “right-sized” VM for each database serving an application. Client staff decided to add a second application to a clustered service. After going live, users reported timeouts and dropped connections. The company asked XTIVIA to investigate and recommend a solution.

SOLUTION

Because the company was an existing Virtual-DBA remote database administration services client, XTIVIA had existing connections into the environment and could get engaged quickly. The clustered server had just 2 GB of RAM, which made memory the chief suspect for the server’s failing performance. XTIVIA conducted a two-hour trace using performance monitor to track memory utilization. As expected, the server was starved for available memory, which dropped frequently during the monitoring period to 20 MB. Other evidence that memory was the primary problem included excessive paging, a low number of free pages, high disk queue lengths and a buffer cache hit ratio averaging about 70%. XTIVIA also examined SQL Server’s dynamic views for missing indexes and index fragmentation. Large indexes were heavily fragmented, and a number of indexes were available that could significantly improve performance. Finally, XTIVIA noted that the four-CPU server had a single tempdb file, which resided on the same drive as the other data files.

RESULTS

XTIVIA recommended adding memory, creating six new indexes, implementing index maintenance, creating three additional tempdb files, and upgrading SQL Server® 2008 R2 to SP1. The client implemented all recommendations except for the SQL Server upgrade. These measures fixed the dropped connections and timeouts. XTIVIA used its documentation practices and expertise on server configuration and SQL Server troubleshooting to quickly diagnose and solve the problem.

KEY COMPONENTS

DATABASE Microsoft® SQL Server® SERVICES SQL Server DBA Diagnostics and Monitoring Performance Tuning Download the Case Study PDF here