Management Studio Express Reports
If you are using MS-SQL server, then you should have SQL Server Management Studio installed. The part of management studio that I want to talk about today, is the standard reports that it has.
After opening management studio, and expanding the databases, right-click on one of your databases, and you will get a menu. Highlight "Reports", and then "Standard Reports", and you will get the list of included reports that you can run on your database.
There are reports for indexes, blocking transactions, and disk usage, among others. Some of these can be useful for isolating performance issues. Others for the overall health or for maintenance.
One of the reports in particular that I look at periodically, is the "Disk Usage by Top Tables". This report breaks out the number of records, and the disk space requirements, for each table in the database. The information is reported in descending order of disk usage. I check this report, looking for tables that I may be able to reduce the size of by purging information. It could be invoice history that can be purged. There may be a table that was added to tracking some specific activity, and that table is no longer needed, or it can be trimmed down. Sometimes it is just an eye opener to see just what tables are using all that disk space.
If you are not familiar with them, spend a few minutes going through those standard reports. If nothing else, they can give you an overall feel for the health of your SQL server.
Dave.