Mythbusting the Role of Hardware in SQL Server Performance

By:


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:

Larock Bigger Box Is Myth

 

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:

Bell Throw Cheap Hardware At 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:

Larock Cpu Bottleneck Not Usually Problem

 

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

Dantoni Fewer Cpus Cheaper Licensing

 

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:

Dantoni Large Data Volumes Are Not Hardware Problem

 

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

Davis Slow Io And Raid10

 

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:

Chapman Add Cpus And Licenses Example

Chapman Code Changes And Indexes Cut Need For Hw

 

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:

Larock Simple Code Design Changes 10X Perf

 

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:

Chapman Bad Code Causes Bad Perf

 

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

Dantoni Poor Config Options Cause Perf Issues

Dantoni Misconfig Also Causes Perf Issues

 

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:

Lopez Green Settings Cause Perf Issues

 

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:

Lopez Generated Sql Not Optimized For Perf

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.

Comments

Trackbacks

  1. […] entrevista com um painel de especialistas determinou que o SQL mal-escrito pode causar até 70% de problemas gerais de desempenho. A adição de recursos pode mascarar vários dos problemas que acompanham um SQL mal-escrito, mas […]

Leave a Reply