Reasons Why SQL Servers Don’t Get Configured to Industry Best Practices

Throughout my career, I’ve worked on a LOT of different SQL Servers. Many that were installed and configured by application administrators, developers, vendors, and data professionals. I’ve seen servers with straight defaults and others with various industry accepted best practices applied.

One of my favorite sessions I present is “Common SQL Server Mistakes and How to Avoid Them”. Part of that session goes into SQL Server instance level changes that are typically recommended to change from the default values. (maxdop, cost threshold for parallelism, optimize for adhoc workloads, etc). Others topics in that session go into maintenance and backups. Through giving that session, I’ve had a healthy number of conversations around “Best Practices” and why so many people aren’t following them. My top 3 reasons for why are below.

1. People just don’t know. Many application engineers or developers don’t follow industry experts on how best to optimize SQL Server. They simply install SQL Server and assume it is already configured the way it should be by default. I must admit that over the last few builds of SQL Server, things have gotten much better in regard to tempdb. Also, for a lot of customers, the default behavior is ‘good enough’. If it just works, then is there really a problem?
2. Another scenario I’ve seen is when SQL Server is tightly bundled with a vendor product. Organizations assume that with a vendor product that requires SQL Server to be installed, that when they install SQL, then install the vendor application, that the vendor is taking care of ‘fine tuning’ the SQL Server instance. I’ve yet to find a vendor that then ‘owns’ the SQL install and manages it in that respect. I’m sure there are some out there, but every case I’ve come across, the vendor expects the company to have the DBA expertise to ensure SQL is handled properly, the vendor simply requires SQL Server for the back end.
3. The more complex scenario I come across is the contention as to what is ‘best practice’. Most everyone will agree that setting X should be turned on, yet there will be a blogger who says that enabling X isn’t needed or caused an issue with some edge case scenario. I’ve come across this situation more times than I’d like to admit and then have to fall back to “based on my experience, turning on X provides a positive experience and helps performance based on these factors”. There is a lot of ‘it depends’ in the life of a DBA.