Quick troubleshooting with the standard reports

Without typing a query, you can quickly get a lot of useful information from the standard reports in Management Studio with a few clicks, mainly the instance and database specific ones but there are also standard reports in more places. You can find them by right-clicking on your SQL instance, Reports, Standard Reports and if you right-click on a database, Reports, Standard Reports. I will go through some of the main reports and give tips on what you should check.

Instance level

Server Dashboard
Expand "Non Default Configuration Options" and see all settings that do not have default values. Under "Activity Details" on the right side check if more than one trace is running (default trace is ok i.e. 1 trace)

Configuration Changes / Schema Changes History
All configuration changes and schema changes that the default trace has captured are shown here. Check for example who removed the CFO login.

Memory Consumption
Check that Memory Grants Outstanding/Pending is at or near 0, Page Life Expectancy should be above 300. If they do not, this may indicate a lack of memory. Check the charts for memory components as well as buffer memory. Investigate further if you have a large amount of "Stolen memory", however this does not have to be a problem. Expand "Memory Usage by Components", if you use a lot of memory for ad hoc query plans you should turn on the instance setting "Optimize for ad hoc workloads" (2008 and later). Check if any memory component uses a lot of memory (except the buffer pool)

Activity / Performance
View current activity and performance statistics, check if any specific question/query type stands out from the crowd.

Database level

Disk usage by top tables
Overview of tables and indexes - Look for tables without indexes or where indexes take up more space than the table, also look for tables with a lot of unused space which may indicate that the table needs to be rebuilt (ALTER TABLE REBUILD). The latter may result in degraded disk I/O performance.

Backup and Restore Events
Check that backups are working and any restores made

Database Consistency History
Results of CHECKDB

Index Usage / Physical Statistics
Check how well indexes are used, unused indexes should be removed, and if indexes need to be defragmented with e.g. rebuild.

Schema Changes History
Check all structure changes, e.g. if someone dropped a table it will be shown here.

/Fredrik