Home > SQL Server, T-SQL > SQL SERVER – Performance Monitoring System Stored Procedures – T-SQL

SQL SERVER – Performance Monitoring System Stored Procedures – T-SQL


Performance  is the most critical part of any system to serve the request at its best. But, we need a well configured machine with lots of hard drive space for data storage, sufficient RAM and good processor with multiple core and etc. Even if we have everything in place our application and database design should be appropriate with best practices implemented for high performance.

Often, we face some performance issues with SQL Server as the data and user request grows. Now, we are in the situation to identify the performance problem and fix it with best possible solutions. To do so, we need performance monitoring tools. SQL Server has many performance monitor tools inbuilt, external utility software and system stored procedure.

In this blog post, I am going to introduce few performance monitoring system stored procedures that helps us to monitor the activities of current user activity, processes, blocking, locking and data store utilization.

Performance Monitoring System Stored Procedures.

sp_who (Transact-SQL)

Reports snapshot information about current SQL Server users and processes, including the currently executing statement and whether the statement is blocked.

sp_lock (Transact-SQL)

Reports snapshot information about locks, including the object ID, index ID, type of lock, and type or resource to which the lock applies.

sp_spaceused (Transact-SQL)

Displays an estimate of the current amount of disk space used by a table (or a whole database).

sp_monitor (Transact-SQL)

Displays statistics, including CPU usage, I/O usage, and the amount of time idle since sp_monitor was last executed.

We can use the output of these stored procedure in our application to perform some task such as alerts, remove blocks and lock by releasing the resource and look for unusual user activities and etc.

These procedures need appropriate permission for execution. I believe you have understood the use of these performance monitoring procedure in SQL Server.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: