[The following repost of "legacy" yafla content is preparation for the long awaited publishing of part III, which will be published through this medium. To give consistency, I'm reposting Pt I and II in this format]
Inefficiency is a gluttonous thief. It burglarizes your server rooms at all hours of the day and night, demanding virtually limitless hardware sacrifices to satiate its endless thirst for clock cycles and disk rotations. In return it punishes your users with reduced performance and reduced satisfaction, and devastates your solution's scalability.
This inefficiency, materialized in the form of slow performance, is one of the primary causes of system abandonment. This is particularly troublesome in the SQL Server world where many systems servicing large user bases often run on low cost server boxes that leave little margin for performance waste. Many organizations have tossed out their SQL Server solution running on a $3000 PC because the performance wasn't satisfactory (not achieving so-called 'Enterprise' performance), to replace it with a multi-million dollar mainframe solution, overcoming embarrassing inefficiency with brute force.
Several years back, in a moment of nerdish bravado, I made a foolish blanket statement that I could reduce the runtime of virtually any element of a non-trivial SQL Server database solution by 95% (thus improving the performance by about 20x), doing so through some rudimentary changes requiring nothing more than some analysis, minor code changes (changing the underlying code, but not the functionality), indexing, and file group changes. To my surprise, and even greater dismay, this number actually proved to be remarkably accurate: From giant multi-hour organization wide reports, to simple security procedures run hundreds of times a minute, the obvious low hanging fruit alone often improved performance by 10x or more. With a little bit of elbow grease it has proven extraordinarily common to improve performance by 20x or more, significantly improving responsiveness and load handling of the respective systems at minimal cost.
The remarkable thing is that these weren't systems implemented by bad developers - many of them were extraordinary developers who implemented a lot of tricks and techniques that I've co-opted and added to my own bag of techniques. Instead there seems to be a dearth of real information on developing for performance in SQL Server, leaving many to guess about the best approach, not to mention that there isn't enough attention paid to performance efficiency in enterprise solutions. Many seem to be under the false impression that gross inefficiency requiring massive clusters to perform trivial tasks merits a capital-E Enterprise designation.
In software development there's an oft-referenced vice known as 'premature optimization'. This is the tendency to prematurely focus on code performance while code is still young and awkwardly growing, before the critical performance weaknesses have been identified and measured. The end result of this misguided effort is often convoluted code that is difficult to understand and maintain (for instance code including inline assembly or using specialized system hacks in seldom called edge functions). This is often a mistake of inexperienced programmers that haven't had the perfectionist engineering streak beaten out of them.
Consider also that performance truly isn't a concern for the vast majority of code in most client-side applications - it likely doesn't matter if the code that validates an input box in a Windows Forms application takes 3ms or 70ms to complete. As the processing is decentralized and isn't impacting other users who might be running the application elsewhere on the planet, it is basically making use of 'free' clock cycles available on the client PC, and generally is imperceptible to the user. If one thousand different users were running the application simultaneously, they're running it on a thousand powerful PCs, effectively throwing a massive 'cluster' at the problem. In other words, you can overcome application inefficiency on the client side through massive computational excess and a endless ability to scale-out. Even in cases where there is worthwhile performance issues identified, for example an image processing algorithms that takes several seconds to perform an operation, it's often best to wait until the project nears a release and the code has settled, at which point you can send a commando performance team to profile and then selectively improve the slowest sections of code that will have the most beneficial impact, focusing on the lowest hanging fruit, yielding a bounty of quick wins. (Taking one for the team because 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 the critical elements of these systems, and performance problems are one of the primary reasons why solutions are abandoned or re-architected. Consider that every clock cycle wasted on a shared resource, such as a database server, impacts the performance of the overall system and every other user. In most environments there is a massive asymmetry between the computational capability of client machines, and the computational capability of a shared system, such as a database server. There are usually some fixed financial and technological limits to the amount of hardware that a system can scale to, so your database server running on a lowly Dell two-way server is desperately trying to keep up with the demands of 500 user workstations pounding away at it. Even though Google is clustered on purportedly thousands of machines, they still have to develop efficiently to be able to economically service millions of users in a timely manner.
Thus, while it might seem irrelevant when taken alone that your stored procedure saturates the resource, taking 200ms to return a simple list of values to populate a drop list for Joe User, imagine 100 users all opening that form at the same time putting a shared demand on the database system. The performance impact starts to become significant and adversely affects the usability (and credibility) of the system. This is exacerbated by the fact that simultaneous performance demands aren't merely additive on shared resources, but rather contention and task sharing often means that these issues snowball into much more than the sum of the parts.
You should consider the performance of your database from day one with every table you add, every index you create, every trigger you concoct, and every relationship you define. While the misguided will argue that this amounts to premature optimization (as Ralph Waldo Emerson observed, a foolish consistency is the hobgolbin of little minds, and the belief that any performance concerns are premature is just such a foolish consistency), the reality is that the performance of a database system is largely defined by the fundamental design of the system, and as the system grows it becomes much more difficult and costly to solve fundamental performance problems. Furthermore, once an enterprise system reaches production even the simplest performance change, such as adding an index, requires complex analysis to determine how it impacts other parts of the system, or that it satisfies what could be hundreds of procedures accessing the object.
The cynical will wonder how one can predict the future when designing a database system, but the reality is that the access patterns are usually obvious by the time you're starting designing tables - you know how the tables relate, what data will be searched, how often you'll be selecting the records versus modifying them, and how big the fields and records should be. Use this information effectively when developing the tables to choose the appropriate clustered and secondary indexes, to minimize the size of each record, and to write efficient SQL. Don't leave it for a maintenance programmer to reverse engineer the system and apply best guesses in a moment of crisis in the future.
Part II and III will introduce a variety of common performance pitfalls and panaceas in the SQL Server world, touching upon (but not limited to) the following:
Tagged: [SQL Server], [SQL-Server]
