How do i find out the most active database?

I mean to say I have 10 DBs and some transactions keep on happening on these DBs.

I want to know which is less used or which DB has less no of transactions or less active through out the day


Capture this:

Select  instance_name As Database_Name,
        cntr_value As TranSec
From    sys.dm_os_performance_counters where counter_name = 'Transactions/sec'                  
And     instance_name <> '_Total'
Order By instance_name

Then run it again at the end of the day. Subtract one set of values from the other. That will give you the most transactions.

You can look for a similar counter for logins/sec but it may be misleading depending on how the app connects to the database and if it keeps the connection open or not.

Another good way is by the disk IO but this can be misleading too as a lot can show up and be caused by backups/maintenance.

Select  Db_Name(mf.database_id) As Database_Name,
        Sum(a.num_of_reads) As Reads,
        Sum(a.num_of_bytes_read) As Bytes_Read,
        Sum(a.num_of_writes) As Writes,
        Sum(a.num_of_bytes_written) As Bytes_Written
From    sys.master_files mf
Cross Apply sys.dm_io_virtual_file_stats(mf.database_id, mf.file_id) a
Group By Db_Name(mf.database_id)
Order By 1

Again you'd run it in the morning and again in the afternoon and subtract to compare.

| improve this answer | |

Unless you have Systems Centre Operations Manager with the SQL Server Management Pack, a good way is to set up some sort of data collector that queries the appropriate DMV's.

Assuming you are running SQL Server 2008 + you can set up a Management Data Warehouse on the instance using SQL Server Management Studio. The result is a graphical user interface in the style of a performance dashboard.

Link: Generic How To Guide on MSsqltips

Set up out of the box is easy and informative, and when you get bored of the default measures, it can be easily extended.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.