Here are two stored procedures written and maintained by the SQL community that I’ve found very useful.
The first is sp_whoisactive, written by Adam Machanic. It is a great proc to monitor activity on a SQL Server instance to gather data to diagnose many kinds of performance issues.

The procedure can be downloaded from:
SQLBlog.com

The other is from Brent Ozar, the sp_blitz procedure. This is very useful to return information for the administration of a SQL Server instance. Running the procedure will (among other things) let you know if there are databases that haven’t been recently backed up, a database with untrusted foreign keys, potential security issues, and the list goes on.

The procedure can be downloaded at:
Brent Ozar

A cool feature is that when an issue is discovered, the results will return a URL to Brent’s site to give more detail on the issue and steps to resolve it.