Featured Article(s)
Configure and Implement Database Mail in SQL Server 2005
Configure and Implement Database Mail in SQL Server 2005
Performance Management – Hardware or Tuning?
The question was – for performance, do you "throw hardware at it" or "tune it?" Send in your feedback here.
I have received SO MUCH feedback on this that I really want to keep running some of these – they are very insightful and thoughtful responses – great food for thought. I hope it’s helpful!
Mark wrote in with some very intriguing thoughts on this whole question: "We see (finally) that the DBA has a viable, legitimate and dedicated role to play within the SQL Business Community. However, that role is only really recognised amongst the larger organisations. There are also a significant number of SQL sites that have the “accidental DBA” assuming a few roles within the business including that of DBA – meaning, it was not their first choice of profession, regardless of how proficient they may have become over time.
For the very large with the dedicated DBA or two, then performance tuning does become vitally important. I would seriously question their ability to make significant changes with anything structural within SQL so becomes a lot more dependant on the quality of the database itself. Imagine trying to find a 6 hour window (or 4 times that if doing it “online”) to do a clustered index rebuild on a large but vitally important table. Or partitioning tables and indexes once that “magic” table reaches the limit of mortal sanity. All of course happening on a large clustered environment, whilst keeping the system up and running and available 24-7. No, I think not, I think the typical DBA role in those environments is more about reliability and acceptable performance for the sake of dependability and most importantly availability.
Don’t get me wrong, those skills are crucial for a DBA in that role, because it is most likely that it is those types of sites that do (or will) use the more esoteric elements of SQL Architecture with partitioning, clustering, replication etc.
The less obvious areas or possibly more obscured as to tuning or simply better design are things like Filtered Views which can make a performance improvement, but is it really tuning, or is it a new feature of the engine and therefore a design feature more so than a tuning technique. We’ll give the DBA a break and call it a potential performance gain regardless.
Let’s also consider the environment. It is highly likely that it is a multi computer multi processor clustered type environment with large SAN, significant switching and network topology, dedicated roles for Network Engineers, Security etc. It is highly likely that the co-dependencies will make it more challenging to do anything significant, but the good DBA will be able to convince the network engineer that the default packet size needs tuning, or the 520 ip-hopping routes can be rationalised etc, or the SAN really can benefit from improved caching, or creating a new ZONE assign LUNs throw the transactions logs in there and gain more performance throughput. But that really is a rare DBA that can do that, and again, is that what we call performance tuning, or hardware tuning ?
For the medium and smaller sites, then without question, throwing hardware at the problem is most likely the “easier” option. No doubt there are a lot of systems out there put together without a full understanding of the architecture required, or, have ondergone some kind of growth or evolution where the hardware that once was should now be retired. I have most certainly come across a lot of systems that were designed from the hip pocket where budget played a significantly more influential part of the hardware configuration than there was the appropriate knowledge to design and convince the hip pocket of the better way.
New hardware, however, does not always result in a performance improvement because it really does depend on what the heck SQL is doing. Having said that, it is also highly likely that the first foray into SQL Server was under budgeted, under specified and the machine has been hobbled together over time, and yesterdays benchmark is now todays major complaint. Especially if we consider what has happened over the past few years and (for example) the additional expense of a SAN is now more readily justified etc. So, in almost all cases, yes, throwing hardware at it will be the better choice, but not always based on the right information, and possibly not resulting in the performance anticipated.
However, the DBA is then going to need to configure the SQL Server and Database properly in that new environment, and quite possibly will trip over some performance issues and then fix them (ever heard of memory problems in a 64 bit edition on a 64 bit platform). Is that what we call performance tuning, or configuration management ?
So, what really is performance tuning ? Is employing or ensuring good practical database design a “performance role” or fixing a problem – adding that index to the most often run query can make a huge difference, but surely that was a design oversight. Fixing that locked pages setting or enabling the AWE that we don’t need in full 64 bit environment – surely that must be configuration not performance.
I guess what I am really saying is performance is a result of doing other jobs properly including recommending and configuring the hardware environment and the SQL Server, it in it’s own right is not so much a job, more of a measure as to how well the job is being performed. So, instead of the more obvious “both”, think I am more inclined to say “neither”."
Featured White Paper(s)
SQL Server Fragmentation Explained
This technical white paper will help you understand SQL Server fragmentation and the performance benefits you can gain on yo… (read more)