There was an interesting post from Erik Darling recently about a way to prevent anyone from creating an index with a low fill factor. Essentially, Erik posted code for a DDL trigger that would fire on the CREATE or ALTER INDEX events and if there is a value for the fillfactor < 80, this will roll back the creation. As Erik notes, however, this doesn't prevent the index from being created. Rather, it just rolls it back, which is an issue in and of itself. I wish we had a "before" DDL trigger for this.

Ultimately, changes that we want to make to our systems need to be tested and evaluated early on. We would want changes like this to be made in a dev environment, tested in QA or a Load Testing system, and then deployed to production. We could write tests to ensure that changes are made within known boundaries that won't substantially impact the performance of the system, but that's not reality. Even if we had a well designed and followed process, we rarely have the ability to properly load test our application to completely understand the impact of changes like a fill factor alteration.

The point, though, is that we want to be proactive and prevent issues like this from causing us problems. Rather than wait until this causes us issues with our production system, we want to try and educate other DBAs that might not know better. While there are lots of controls in SQL Server, there aren't a lot that prevent someone with sysadmin or serveradmin from changing settings that might cause issues.

My wish is that the SQL Server would include controls that lock down certain settings and choices to prevent alterations from any single administrator. I'd really like to have some sort of two user authentication for certain changes built into the platform. A way for an admin to request a change, and another admin to approve it. That, along with the ability to limit actions separately from a rollback, which becomes a less and less viable option as data volumes grow.

SQL Server has dramatically improved as a platform, with more capabilities, enhancements, and maturity with each version. There is still a long way to go, and a recognition that administration might need to become more of a team process, just like development, would be a good next step.