Heh... Grant's article really strikes a note for me but not the way that most people would think.
I'm truly amazed at how much time people spend looking at the different types of waits and trying to tweak hardware and a whole raft of esoteric settings to, in a frequently vain attempt, reduce resource usage and improve performance. Don't get me wrong. There ARE some rote settings changes that should be made on most servers but even those will only produce relatively minor improvements in performance and resource usage.
Then there are the poor souls that scrap perfectly good hardware and buy the "the next generation" of hardware and go through the huge pain of migrating to the new server and, possibly, migrating to the latest version of SQL Server, only to find they've achieved little to no performance improvements. So, they spend more time and money upgrade their new hardware with SSD's and possibly invest heavily in 3rd party "solutions" only to be further frustrated (although I'll readily admit that SSD deployment in the right areas can really help in many cases).
We see people that write solutions on forums that use things like Recursive CTEs (rCTE) to replace Tally Tables or Itzik Ben-Gan's fabulous Cascading Tally CTE (my name for it) or continue to post splitters based on While loops. We continue to see posts by "experts" that explain supposed high performance solutions accompanied by a While loop or rCTE that generate a whopping 10 rows of test data. We continue to see multiple calls on CTEs, advice on how to solve problems that use Scalar and Multi-Statement UDF's, and a whole gambit of RBAR solutions and other poor code choices.
We have oodles of time to spend and the money to buy all sorts of hardware solutions to get performance "improvements" that are typically measured in trivial single digit or low two digit percentages but no time to spend on the real problem which can, many times, result in 3, 4, and sometimes even 5 digit percentages of improvement. Try buying hardware that will give you a 6,000% (60 times improvement). It's usually not possible through hardware even if your whole database actually does fit on an SSD.
Getting back to the point of this article and to make my point…
From the article
The environment in which your application runs is complex and there are lots of variables that can affect the advice or guidance that an expert might recommend. It's the reason we see DBAs often saying "it depends."
There is one variable in the equation where I never say "It Depends" in the guidance I try to offer and that is "Crap Code will cripple your server. Take the time to identify and fix it and then learn how to avoid writing it to begin with".
is pronounced ree-bar and is a Modenism for R
First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Helpful Links:
How to post code problemsHow to post performance problemsForum FAQs