Joining in the current meme started by Paul Randal (blog | twitter), here is my list of five features or settings that SQL Server should not have.

The current SQL Server setup program. Several people have commented on this, but I just want to chime in. Many “accidental” DBAs, and Server Admins are handed a SQL Server 2005, 2008, or 2008 R2 setup DVD, and are asked to get a SQL Server instance installed. The setup program seems deceptively easy, just like installing Office 2007, but many of the default choices it makes are quite evil. For example, a default installation will set the default location for data files, log files, TempDB, and backup files on the C: drive. It also does not present an option to grant the “Perform Volume Maintenance Tasks” right to the SQL Server Service account (which is needed for Windows Instant File Initialization).

The “Boost SQL Server Priority” Instance Setting. This setting is like a shiny piece of candy that seems to attract many people. After all, who would not want to boost the priority of SQL Server and hopefully get better performance. Just like the Turbo button on ancient personal computers, right? It turns out that this setting does not help performance, and can actually destabilize the operating system, since the OS can be starved of CPU resources because of the priority given to the SQL Server process.

The current behavior of SQL Server Management Studio.  I always tell my students that SSMS lies to you. It is the tool that the DBA usually has open, all day, every day, with a connection to their most important database(s) in Object Explorer. Unfortunately, unless you periodically right click and hit refresh, you will have a false sense of security, believing everything is ok with your databases. In reality, all sorts of bad things may be happening, and SSMS will not tell you about it unless you ask.

The Auto Close Database Setting. Here is the BOL description:

This rule checks whether the AUTO_ CLOSE option is set OFF for SQL Server Standard and Enterprise editions. When AUTO_CLOSE is set ON, this option can cause performance degradation on frequently accessed databases because of the increased overhead of opening and closing the database after each connection. AUTO_CLOSE also flushes the procedure cache after each connection.

I don’t see any point in enabling this, but I do see how an accidental DBA might decide to enable it.

The Lightweight Pooling Instance Setting. Wow, this sounds cool, right? Wouldn’t lightweight pooling be faster and better than context switching? Well, probably not. Things like the CLR, replication, and XML support don’t work with lightweight pooling. Here is what BOL says about lightweight pooling:

Setting lightweight pooling to 1 causes SQL Server to switch to fiber mode scheduling. Fiber mode is intended for certain situations in which the context switching of the UMS workers is the important bottleneck in performance. Because this is rare, fiber mode seldom improves performance or scalability on the typical system. Improved context switching in Microsoft Windows Server 2008 has also reduced the need for fiber mode. The lightweight pooling option should only be enabled after thorough testing, after all other performance tuning opportunities are evaluated, and when context switching is a known issue in your environment. We recommend that you do not use fiber mode scheduling for routine operation because it can decrease performance by preventing the regular benefits of context switching, and because some components of SQL Server that use Thread Local Storage (TLS) or thread-owned objects, such as mutexes (a kind of Win32 kernel object), cannot function correctly in fiber mode

Unfortunately, there are not too many magic buttons or settings for SQL Server that will instantly solve all of your problems…