28 June,2011 by Jack Vamvas
I read an interesting blog post yesterday by Brian Moran- Will SSDs Cause Performance Tuning Experts to Go the Way of the Milkman?
The author explores the trend of increased use of Solid State Disk (SSD) and will it make performance tuning experts irrelevant.
He approaches the question from an ROI perspective. Why would a company spend thousands on performance tuning experts when purchasing some SSD will achieve similar gains. ?
Think ahead to when SSD is comparable on price to current disk. The thought process by managers will be to replace hardware – purchase hardware that is faster.
It’s a thought provoking scenario and reflects a growing trend – that’s having a major impact on database server management . Beyond the obvious benefit of improving disk IO – which remains a regular bottleneck on systems, SSD will impact database servers in other ways, such as the types of content managed by SQL Server.SQL Server tempdb and solid state drives is already widely adopted
Added to the increased use of SSD – a similar argument can be given for other aspects of hardware . Server hardware is made of components – which can be configured according to the database server requirements.
The “Kill It With Iron” approach to performance tuning , has the obvious benefits to ROI , but has built in limitations . Usually improves performance by a linear criteria and does not consider future growth for scale.
I don’t want to overstate the ROI trade-off. Not every aspect of Database Server management relates to a trade-off between costs and benefits, sometimes servers are misconfigured!
From a SQL Server performance tuning perspective and how the SQL Server DBA time is spent there are some interesting questions.
Should the DBA spend more time analysing Bookmark Lookups on Execution Plans or should they master the performance tools? Increasingly SQL Server performance tuning tools offer the expert good advice. Along with a grasp on different performance troubleshooting methodologies , performance tuning becomes easier
Over the last couple of SQL Server Versions (2005 and 2008) , the performance monitoring features have improved . One example is DMV.
Self Tuning is one future scenario and SQL server is phenomenally good at implementing easy-to-use features . In SQL Server 2008 ,DMVs ,Policy-based management and Management data warehouse are laying the foundations for a self tuning system.
1) Reading SQL Server Profiler traces – can this be automated? And if it can be automated – can it become a daily maintenace task? Can Operations manage these maintenance tasks – and only notify a DBA on critical issues.?
2) SQL Server keeps introducing new features. Will a DBA be able to justify spending days on analysing Execution Plans , when there are challenges\demands set by the business. The role of the DBA is changing – the technical element is one of many skillsets required.
The new DBA has to absorb vast information very quickly. Having to maintain a DBA competitive edge in a competitive job market
The trend in the database server market is to add as many features as possible to maintain market share
3) Is there a new type of SQL Server Performance Tuning expert? Characterised by toolset expertise and having a wider grasp of the Performance environment. One who doesn’t spend all their time analysing internals. A deep knowledge of internals is important to a SQL Server DBA, but will this be as critical when the tools improve?
One scenario is that as SQL Server internals become easier to manage there is less DBA requirement to focus on SQL Server internals. The DBA focus will be on tools and performance troubleshooting methodologies
4) Will SQL Server become self-tuning? I mentioned earlier DMVs ,Policy-based management and Management data warehouse . How will SQL Server self-tuning progress in future versions?
To set up a self tuning system is not trivial and includes monitoring queries, analysis, execution of code. Reference IBM self-learning optimizer.
5) With the advances in hardware and decreasing costs , coupled with improving SQL Server performance tools , will managers be willing to accept performance tuning as a high ROI anymore?
The future of the SQL Server performance tuning expert is safe – but is changing rapidly. Consider the impact of hardware improvements relative to cost and how SQL server internals will exploit this hardware. This will require DBAs to understand and build into their decision making process.
The environment SQL Server database servers reside are complex , with disaster recovery , high availability and multiple database server platforms integration completely standard in large Data Centres.