VIRTUAL-DBA: SQL SERVER PERFORMANCE AIRLINE CUSTOMER
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.
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.
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.
Microsoft® SQL Server®
SQL Server DBA
Diagnostics and Monitoring