Regardless of the type of development (application or reporting) that you are performing on SQL Server. There are 5 items within SQL Server that I consistently use to help me design and troubleshoot solutions.
SP_Who
SP_Who is a system stored procedure that returns information on what is currently running on your SQL box. It shows you the following information:
- SPID
- ECID
- Status
- Login name
- Host Name
- Block
- DB Name
- Command
- Request ID
I use the sp often to see what processes are actively running and if there are any blocks on the system.
Complete information can be found here: SP_Who
DBCC ShowContig
DBCC ShowContig returns information on your data and indexes within a specific database on your server. Using DBCC ShowContig allows you to see the fragmentation and scan density of your indexes. These 2 metrics can help you determine any issues with your indexes, a critical step for business intelligence applications. In SQL 2014, this command is being replaced with sys.dm_db_index_physical_stats.
Information_Schema.Routines
This is a system view that returns one record for every Stored Procedure and function in your database. There are 2 columns in this view that I leverage often the ROUTINE_NAME and ROUTINE_DEFINITION. The ROUTINE_NAME returns the name of the sp or function. The ROUTINE_DEFINITION is what I use to perform lookups if I need to determine where a specific column is being used in the database to make modifications or troubleshoot issues.
Complete information can be found here: Information_Schema.Routines
Information_Schema.Columns
The Information_Schema.Columns returns system information on the tables and columns in the database. I use this to find what columns and datatypes when researching databases versus looking them up in the table designer on each table.
Complete information can be found here:
http://msdn.microsoft.com/en-us/library/ms188348.aspx
sp_helpserver
I generally use a lot of linked servers from a central reporting database to other systems that I need to pull data from. The stored procedure sp_helpserver will return all information on linked servers on your SQL Server system.
