Five Things SQL Server Should Drop

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…

This entry was posted in SQL Server 2008 R2. Bookmark the permalink.

One Response to Five Things SQL Server Should Drop

  1. Aaron Kempf says:

    I agree with getting rid of SQL installer..
    I think that it’s been ‘generally successful’- but when it screws up, I don’t think that it’s good enough at recovering / cleaning up.

    I have probably installed SQL Server 100 times in the past ~7 years since first seeing SQL 2005.. and I think that I’ve only had 3-4 installs when things go wrong.

    It would be nice if someone would write a SQL SERVER ERASER that will go through and delete every sql install on a machine… I’m taking a look at doing something like that.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s