High Performance SQL Server Part I

[The following repost of “legacy” yafla content ispreparation for the long awaited publishing of part III, which willbe published through this medium. To give consistency, I’mreposting Pt I and II in this format]

def. Enterprise
adj. Terminology often used to excuse terribly inefficientsoftware designs, and to justify massive hardware overkills fortrivial solutions. e.g. Enterprise solution.

Introduction

Inefficiency is a gluttonous thief. It burglarizes your serverrooms at all hours of the day and night, demanding virtuallylimitless hardware sacrifices to satiate its endless thirst forclock cycles and disk rotations. In return it punishes your userswith reduced performance and reduced satisfaction, and devastatesyour solution’s scalability.

This inefficiency, materialized in the form of slowperformance, is one of the primary causes of systemabandonment. This is particularly troublesome in the SQL Serverworld where many systems servicing large user bases often run onlow cost server boxes that leave little margin for performancewaste. Many organizations have tossed out their SQL Server solutionrunning on a $3000 PC because the performance wasn’t satisfactory(not achieving so-called ‘Enterprise’ performance), to replace itwith a multi-million dollar mainframe solution, overcomingembarrassing inefficiency with brute force.

Reward

Several years back, in a moment of nerdish bravado, I made afoolish blanket statement that I could reduce the runtime ofvirtually any element of a non-trivial SQL Server database solutionby 95% (thus improving the performance by about 20x), doing sothrough some rudimentary changes requiring nothing more than someanalysis, minor code changes (changing the underlying code, but notthe functionality), indexing, and file group changes. To mysurprise, and even greater dismay, this number actually proved tobe remarkably accurate: From giant multi-hour organization widereports, to simple security procedures run hundreds of times aminute, the obvious low hanging fruit alone often improvedperformance by 10x or more. With a little bit of elbow grease ithas proven extraordinarily common to improve performance by 20x ormore, significantly improving responsiveness and load handling ofthe respective systems at minimal cost.

The remarkable thing is that these weren’t systems implementedby bad developers – many of them were extraordinary developerswho implemented a lot of tricks and techniques that I’ve co-optedand added to my own bag of techniques. Instead there seems to be adearth of real information on developing for performance in SQLServer, leaving many to guess about the best approach, not tomention that there isn’t enough attention paid to performanceefficiency in enterprise solutions. Many seem to be under the falseimpression that gross inefficiency requiring massive clusters toperform trivial tasks merits a capital-E Enterprisedesignation.

Motivation

In software development there’s an oft-referenced vice known as’premature optimization‘. This is the tendency toprematurely focus on code performance while code is still young andawkwardly growing, before the critical performance weaknesses havebeen identified and measured. The end result of this misguidedeffort is often convoluted code that is difficult to understand andmaintain (for instance code including inline assembly or usingspecialized system hacks in seldom called edge functions). This isoften a mistake of inexperienced programmers that haven’t had theperfectionist engineering streak beaten out of them.

Consider also that performance truly isn’t a concern for thevast majority of code in most client-side applications – itlikely doesn’t matter if the code that validates an input box in aWindows Forms application takes 3ms or 70ms to complete. As theprocessing is decentralized and isn’t impacting other users whomight be running the application elsewhere on the planet, it isbasically making use of ‘free’ clock cycles available on the clientPC, and generally is imperceptible to the user. If one thousanddifferent users were running the application simultaneously,they’re running it on a thousand powerful PCs, effectively throwinga massive ‘cluster’ at the problem. In other words, you canovercome application inefficiency on the client side throughmassive computational excess and a endless ability to scale-out.Even in cases where there is worthwhile performance issuesidentified, for example an image processing algorithms that takesseveral seconds to perform an operation, it’s often best to waituntil the project nears a release and the code has settled, atwhich point you can send a commando performance team to profile andthen selectively improve the slowest sections of code that willhave the most beneficial impact, focusing on the lowest hangingfruit, yielding a bounty of quick wins. (Taking one for the teambecause there’s no I in team, and no cliche unworthy)

Enterprise databases, or any centralized system for that matter,are entirely different beasts – performance is one of thecritical elements of these systems, and performance problems areone of the primary reasons why solutions are abandoned orre-architected. Consider that every clock cycle wasted on a sharedresource, such as a database server, impacts the performance of theoverall system and every other user. In most environments there isa massive asymmetry between the computational capability of clientmachines, and the computational capability of a shared system, suchas a database server. There are usually some fixed financial andtechnological limits to the amount of hardware that a system canscale to, so your database server running on a lowly Dell two-wayserver is desperately trying to keep up with the demands of 500user workstations pounding away at it. Even though Google isclustered on purportedly thousands of machines, they still have todevelop efficiently to be able to economically service millions ofusers in a timely manner.

Thus, while it might seem irrelevant when taken alone that yourstored procedure saturates the resource, taking 200ms to return asimple list of values to populate a drop list for Joe User, imagine100 users all opening that form at the same time putting a shareddemand on the database system. The performance impact starts tobecome significant and adversely affects the usability (andcredibility) of the system. This is exacerbated by the fact thatsimultaneous performance demands aren’t merely additive on sharedresources, but rather contention and task sharing often means thatthese issues snowball into much more than the sum of the parts.

Wisdom

You should consider the performance of your database from dayone with every table you add, every index you create, every triggeryou concoct, and every relationship you define. While the misguidedwill argue that this amounts to premature optimization (as RalphWaldo Emerson observed, a foolish consistency is the hobgolbin oflittle minds, and the belief that any performance concerns arepremature is just such a foolish consistency), the reality is thatthe performance of a database system is largely defined by thefundamental design of the system, and as the system grows itbecomes much more difficult and costly to solve fundamentalperformance problems. Furthermore, once an enterprise systemreaches production even the simplest performance change, such asadding an index, requires complex analysis to determine how itimpacts other parts of the system, or that it satisfies what couldbe hundreds of procedures accessing the object.

The cynical will wonder how one can predict the future whendesigning a database system, but the reality is that the accesspatterns are usually obvious by the time you’re starting designingtables – you know how the tables relate, what data will besearched, how often you’ll be selecting the records versusmodifying them, and how big the fields and records should be. Usethis information effectively when developing the tables to choosethe appropriate clustered and secondary indexes, to minimize thesize of each record, and to write efficient SQL. Don’t leave it fora maintenance programmer to reverse engineer the system and applybest guesses in a moment of crisis in the future.

Agenda

Part II and III will introduce a variety of common performancepitfalls and panaceas in the SQL Server world, touching upon (butnot limited to) the following:

  • Indexes – clustered and non-clustered
  • Fundamental table designs
  • Filegroups
  • Cursors
  • Materialized views, computed columns
  • Common Performance Problems
  • Surprizing SQL Server behaviours

Tagged: [], []