Why You Need a SQL DBA or Consultant

Many organizations do not have in-house DBAs. This can be due to a number of reasons. Most commonly it is smaller organizations that cannot justify a full time DBA. Others are larger organizations that do more development work or organizations that run a lot of vendor supported software that don’t understand the value of having a full time DBA.

Most always with these organizations, I find common issues that would be addressed and cared for if they had a full time DBA or would at least use a consultant for regular spot checks on their environment.

Some of the more critical issues that I find are below:

Missing Backups

I have found organizations with missing backups way more times than I would like to admit. This is such a scary situation to find. If that organization had experienced a server crash, database corruption, ransomware, or any number of events that would have needed a restore, they would have been in a really bad situation.

I also find organizations that think they have proper backups, however all they have are nightly snapshots. “We have backups right”, well yes and no. You can restore but have up to a day of data loss. For most business units, that is not acceptable. They need minimal data loss which would require transaction log backups at a regular interval.

Statistics and Index Maintenance

The query optimizer uses statistics to generate the execution plan, so having up-to-date statistics is very important. Out of the box, the general rules for stats getting updated isn’t very likely often enough for your workloads. For this reason, you should be updating statistics manually with a scheduled job.

Index fragmentation is still an issue out there. Fragmentation can be thought of as wasted space in your data pages. These pages are read into memory when data is accessed. The more fragmentation, the more wasted space is read into memory. I rarely find a SQL Server that has more memory than it needs, I really don’t want that limited resource to have a lot of empty space being consumed in the buffer pool.

Checking for Corruption

Corruption can and does happen. Two common ways this is detected is a user trying to access the data that is corrupt and getting an error, or by running DBCC CHECKDB on a regular basis. Depending on the level of corruption, your only option for a repair is a restore. It is very important to detect and remediate any corruption as quickly as possible. I like to schedule this daily if I can, weekly as a worse case.

Missed Opportunities with Configuration Changes

Out of the box, SQL Server is great, however there are numerous things that can drastically improve your end user experience with some small modifications. These are things often referred to as “industry best practices”. Example, changing auto growth settings, enabling instant file initialization, enabling optimize for adhoc workloads, adding more tempdb database files of equal size, increasing cost threshold for parallelism, and much more. Having someone that geeks out on SQL Server to manage your environment can make a big difference and in most cases, is much cheaper than just throwing hardware at the problem.