The following screenshot will give a DBA severe indigestion (although I have to imagine it is not entirely uncommon on unmanaged installations of SQL, especially those where SQL is just the black box that is installed as a prerequisite).

A SQL installation with the database files, logs, and backups on the same volume, all with the simple recovery model enabled.  Excessive memory paging. Unexpected shutdowns.  Database consistency check not current (Paul Randal is probably losing sleep at night).  Disk partition offset is wrong see here and here).

I can’t imagine why this server is not performing optimally.

image

The first step to fixing a problem is understanding and admitting that you have a problem.

Fortunately, the SQL Team and CSS Escalation Engineers have put their heads together to document common SQL misconfigurations, written a tool to scan your SQL Server for these misconfigurations, and give you information on how to fix them.  I strongly recommend you give it a go on your own SQL server :)  It may just save a call into support down the road. 

You’ll need to install the Microsoft Baseline Configuration Analyzer first, and then the SQL Best Practices Analyzer.  You can run the scan remotely, but will need to enable PowerShell remoting following the steps at the bottom of the post (if you are installing the BPA locally, the installer will do it for you).  Can anyone beat my screenshot for the ugliness of their install?

Microsoft Baseline Configuration Analyzer 2.0

Microsoft Baseline Configuration Analyzer 2.0 (MBCA 2.0) can help you maintain optimal system configuration by analyzing configurations of your computers against a predefined set of best practices, and reporting results of the analyses.

Microsoft® SQL Server® 2008 R2 Best Practices Analyzer

The Microsoft SQL Server 2008 R2 BPA is a diagnostic tool that performs the following functions:

  • Gathers information about a Server and a Microsoft SQL Server 2008 or 2008 R2 instance installed on that Server
  • Determines if the configurations are set according to the recommended best practices
  • Reports on all configurations, indicating settings that differ from recommendations
  • Indicates potential problems in the installed instance of SQL Server
  • Recommends solutions to potential problems
Additional Information

Important:
SQL Server 2008 R2 BPA is able to scan both the local computer and remote computers. Therefore, in both the local and remote cases, it is required that your PowerShell settings be modified. The modifications support PowerShell remoting, and increase maximum number of concurrent shells for a user.
The following commands will be executed on the machine where the BPA analysis is initiated. You MUST check the “Select to continue…” box to apply these modifications, or select “cancel” to exit Setup.
Enable Remoting using "Enable-PSRemoting"- Performs configuration actions to enable this machine for remote management.
Includes:

  • Runs the Set-WSManQuickConfig cmdlet, which performs the following tasks:
    • Starts the WinRM service
    • Sets the startup type on the WinRM service to Automatic
    • Creates a listener to accept requests on any IP address
    • Enables a firewall exception for WS-Management communications
    • Enables all registered Windows PowerShell session configurations to receive instructions from a remote computer
    • Registers the "Microsoft.PowerShell" session configuration, if it is not already registered
    • Registers the "Microsoft.PowerShell32" session configuration on 64-bit computers, if it is not already registered
    • Removes the "Deny Everyone" setting from the security descriptor for all the registered session configurations
    • Restarts the WinRM service to make the preceding changes effective
  • Configure MaxShellsPerUser using "winrm set winrm/config/winrs `@`{MaxShellsPerUser=`"10`"`}"
    • Specifies the maximum number of concurrent shells that any user can remotely open on the same computer. If this policy setting is enabled, the user will not be able to open new remote shells if the count exceeds the specified limit. If this policy setting is disabled or is not configured, the limit will be set to 5 remote shells per user by default.
    For more information about PowerShell remoting, please see : http://msdn.microsoft.com/en-us/library/aa384372(VS.85).asp