SQL Silver Bullets and Easy Fixes - SQL Antipattern #003

When discussing or writing about advanced features in SQL Server – it’s always worth remembering that in the hands of inexperienced DBAs, using these features can completely destroy excellent SQL Server performance.

A Silver Bullet or Easy Fix is a classic SQL Antipattern . Read more on how to spot a SQL Antipattern

One of the strengths of SQL Server over the last few versions is the fantastic work they’ve done on improving the GUI features . I work with multiple RDBMS – and in my opinion SSMS remains the best out of the box client interface. One of the unfortunate side effects of these GUI advances is newbies pressing buttons at almost every opportunity , without thinking about the real long term impact.

Back before Database Tuning Advisor was available – or other easily accessible changes – it was more difficult to make these changes. Quite often a higher level of skill was required to enable features and analyse issues. Maintaining SQL Server has become a lot easier – but still one of the main reasons of outages is the human factor. I’ve seen plenty of examples where a developer or junior DBA has executed a script which grinds a server to a halt – without a deeper understanding of the impact.

A Silver Bullet is a solution where the DBA assumes will solve the larger problem. The keyword in the sentence is assumes. No serious effort of solving a RDBMS problem should be based  on assumption. Measurements should taken at every point. Typically a DBA will have multiple systems to manage – they need to base decisions on historical workload data , as opposed to assuming the systems works in a certain way.

I’m not arguing there isn’t a requirement for immediate action. A Production outage can easily turn into a national crisis . Sometimes a silver bullet or easy fix or some sql rapid tuning will alleviate the stress, and allow some breathing space for DBAs to apply root cause analysis.

These are some powerful techniques but require a wider view. They should be applied in the context of the problem.

Assign more memory to SQL Server - max server memory (MB)

Missing Indexes

Configuration of log and tempdb files

IO subsystem changes

SQL Indexed Views

Read More on Silver Bullets and ways of improving (or destroying) SQL Server performance

Performance Tuning – The wrong way - SQL Server DBA

SQL server – 5 things SQL Server developers should know about SQL Server

SQL Server Performance Killers - SQL Server DBA