Derek Wilson delivers tactical and strategic Business Intelligence and Enterprise Architecture solutions. His primary focus in on Microsoft SQL Server technologies and aligning business problems to technology solutions. He architects BI solutions leveraging SQL Server, SharePoint and any other technologies that help his clients achieve better data driven decisions. By leveraging the information learned while collecting requirements for BI projects, he helps align business processes to technology helping further organizations Enterprise Architecture. He is an author, trainer, blogger and has been using SQL Server since version 6.5.

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.

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.

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.

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.