How to Analyze Activity of One Database in SQL Server

Using one SQL Server instance for many projects is not unusual. However, defining the most active project may be quite difficult. Today, I would like to share several ways to analyze the activity of each particular database.

In this article, we are going to consider the following points:

  1. The number of database connections
  2. Disk space
  3. RAM capacity
  4. The activity of the database files for a particular period of time

The number of database connections

To define the number of connections, use master.dbo.sysprocesses

Note that the number of connections does not display the database activity and load. Connections can be either idle or active.

Disk space

The query returns two rows for each database. The first row is data size and the second one is a transaction log.

The sum of data files and logs returns only one row for each database.

RAM capacity

The Buffer Pool Percent column reflects the percentage of memory consumption of the total capacity.

The activity of the database files for a particular period of time

 

The script will collect information per minute by default. If you need a report for a longer period of time, change WAITFOR DELAY ’00:01:00′. 

The report returns information for each database file.

Conclusion

Even if you have many projects on one SQL Server instance, you can still get enough information about each of them. Of course, if the project is important and requires special conditions to access, I strongly recommend putting it into a separate instance, as we can not see and implement everything within one instance and many projects.

Dmitry Zaytsev

Dmitry Zaytsev

Dmitry started working with SQL Server back in 2010 and how has large experience in various aspects of working with SQL Server, including administration, development, migration, monitoring, performance, etc. Dmitry is the founder of sqlcom.ru
Dmitry Zaytsev

Dmitry Zaytsev

Dmitry started working with SQL Server back in 2010 and how has large experience in various aspects of working with SQL Server, including administration, development, migration, monitoring, performance, etc. Dmitry is the founder of sqlcom.ru