Case Study: Extremely Slow SQL Server

This is the story of a client who hired me to resolve a SQL Server performance problem that had plagued their organization for over five years.

After multiple resources failed to find a solution including the manufacturer, the client found my Upwork profile and contacted me.

It covers the problem, solution, and client feedback.

Problem

For the past 5 years, the company had been dealing with SQL Server performance issues that the application vendor had been consistently minimizing.

Performance was suffering at both the server level and within various parts of the application itself.

Despite months of complaints, no solution was offered by the application vendor or other SQL DBA resources.

The Upwork job details are depicted in the screenshot to the right.

Solution

I worked directly with the Director of IT on this project.

First, I analyzed the entire SQL Server instance and provided a detailed document with my findings and recommendations.

This included implementing SQL Server DBA best practices and automated database maintenance jobs: indexing, statistics, and integrity checks.

I then created extended events and specifically tailored trace files to track down the root cause of the application's slowness.

My extended events and trace files found a specific stored procedure that was being called too much, leading to poor application performance.

After conducting a meeting with the application vendor, I convinced them to modify the calls and refactor the stored procedure.

This opened an indexing opportunity. By analyzing the execution plan and query statistics, I was able to identify a covering index that had an estimated performance impact of 99%.

The results were dramatic - instead of a 2-minute query time, it went down to mere milliseconds.

Client Feedback

The client was impressed since many resources had tried prior but couldn't find this solution.

This is reflected in my 5-star review and amazing feedback from the client.