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.
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:
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:
It seems to me that the 'Engine - Database consistency check not current' check is bugged. In our environment, when running the this BPA against several servers, this check warned about all the databases in the server not being current from a consistency check standpoint, when in fact I can find the eventlog entries showing that they are being checked successfully.