Who is active on SQL Server?

SQL, what are you doing?

Most database developers and administrators will have this question some point in time, especially when your program takes “forever” to return results.

Thanks to Adam Machanic’s wonderful Stored Procedure (SP) “sp_whoisactive“ found here, you will know exactly what is happening on your database.

What does the SP provide?

It gives you:

  • Database name
  • User who sent the command
  • CPU cycles spent
  • TempDB activity
  • Blocking
  • # of reads & writes
  • SPID
  • Execution time
  • # Transactions open
  • Memory used

My favourite parameter to use with the SP is “@get_plans”. When you set this value equal 1 you get the query plan returned that SQL selected for the operation.

Example: sp_whoisactive @get_plans = 1

Note this SP must be run on the master database. This will require elevated security permissions.

Advertisements
This entry was posted in SQL Server 2014 and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s