Mythbusting the Role of Hardware in SQL Server Performance

Some experts estimate that as much as 70% of application performance problems overall are caused by problems with a database, and there are a growing number of very public examples of how these problems negatively impact a company’s revenue and reputation. SQL Server performance is impacted by many factors outside of the database itself, including the CPU, disk I/O, memory and the network.

What are the primary causes of database performance issues that DBAs see currently in their real-world data environments? When database performance is declining, where do they first look? Are there any new features in SQL Server 2014 that could potentially improve performance?

To answer these SQL Server performance questions, we convened a Twitter chat with a panel of SQL Server and database performance experts, including Tim Chapman (SQL Server Dedicated Support Engineer (PFE) at Microsoft, Microsoft Certified Master), Joey D’ Antoni (Solutions Architect and Anexinet), Chris Bell (Founder, WaterOx Consulting), Robert Davis (SQL Server 2008 Certified Master and Principal DBA at OuterWall), Steve Karam (Oracle ACE and Technical Manager at Delphix),  Karen Lopez (SQL Server MVP and Sr. Project Manager / Architect at Infoadvisors), and Thomas LaRock (SQL Server MCM and MVP, and Technical Evangelist at SolarWinds).

Be sure to read Part 2  and Part 3 of this series, too!

The myth: hardware is the root of all (or most) performance issues

One of the most frequently referenced misperceptions is that whatever the SQL Server performance issue might be, adding new or more powerful hardware will solve it, as Tom LaRock explains:

 

Chris Bell agreed, noting that because hardware is so much cheaper now, it’s become all too common to try to solve performance issues by adding it:

 

Often, the first hardware component to be targeted is the CPU, yet in reality, the CPU is rarely a real bottleneck, according to LaRock:

 

The consequences of a CPU fixation can become costly, incurring additional costs for both new hardware and new software, as Joey D’Antoni notes:

 

This hardware-focused mentality can be seen in a number of other ways. For example, as datasets increase in size, some organizations make the mistake of assuming that the increase in size translates to a direct need for more hardware to support it:

 

Robert Davis offers a myth he has heard frequently that slow IO is usually caused by not using RAID10 for database files:

 

In one case that Tim Chapman shared, one organization that was on the verge of adding more CPUs(and more software licenses) succeeded in resolving their performance problem by making some changes to the code and indexes. After these changes, the need for new hardware disappeared:

 

In fact, while hardware sometimes doesn’t even solve the performance problem, code and design changes can make a significant difference in database performance, and Tom LaRock offers an estimate that these changes along can lead to as much as 10x improvement:

 

The fact: database performance impacted by many factors

Not surprisingly, database performance isn’t something that can be addressed in a vacuum. Many factors play into database performance, as Tim Chapman points out here, including bad code, resources that are over-utliized, poor configuration of resources, slow I/O, database statistics and much more:

 

One area sometimes overlooked when investigating database performance is the impact of different hardware and software configuration decisions, as Joey D’Antoni notes:

 

Sometimes these decisions have unintentional consequences, as Tom LaRock offers in the example of the so-called “green” configuration settings on some hardware. These settings, which can be good for managing power consumption, may also unintentionally impact how the database performs:

 

Another overlooked cause of poor database performance can be poorly written code, including generated SQL, ORMs (object-relational mapping techniques) and other similar code generators, which are not typically optimized for perfomance, according to Karen Lopez:

Look beyond hardware for impact on SQL Server performance

The many myths that are offered up when attempting to diagnose a performance problem often center around hardware, and can lead to unnecessary investments that delay solving the real problem. Be sure to read Part 2 of this series for insights on how to diagnose and address performance issues and Part 3 about key performance-enhancing features in SQL Server 2014.

And for tips on how to speed SQL Server performance without provisioning new hardware, see this helpful infographic.