SQL SERVER – Find Busiest Database

In my recent training I was asked to how to find which is the busiest database in any SQL Server Instance. What he really meant by this is which database was doing lots of read and write operation.

To find the answer to this question I decided to look into the DMV which contains all the details of the executed query. From the DMV sys.dm_exec_query_stats I found three most important columns to determine busiest database.

DMV sys.dm_exec_query_stats contained columns total_logical_reads, total_logical_writes, sql_handle. Column sql_handle can help to to determine the original query by CROSS JOINing DMF sys.dm_exec_sql_text. From DMF sys.dm_exec_sql_text Database ID and from Database ID can be figured out very quickly.

SELECT SUM(deqs.total_logical_reads) TotalPageReads,
SUM(deqs.total_logical_writes) TotalPageWrites,
CASE
WHEN DB_NAME(dest.dbid) IS NULL THEN 'AdhocSQL'
ELSE DB_NAME(dest.dbid) END Databasename
FROM sys.dm_exec_query_stats deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
GROUP BY DB_NAME(dest.dbid)

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

30 thoughts on “SQL SERVER – Find Busiest Database

  1. Hi Pinal,

    Thanks for sharing this information. I think the total_logical_reads and total_logical_writes values are given at statement level. In order to get the specific statement of the code we need to make use of the columns statement_start_offset, statement_end_offset. To get the average logical reads specific to this statement, divide the logical reads with execution_count(number of executions).

    Regards,
    Phani

    Like

  2. You might want to rethink this approach.

    Your query will not be accurate if a stored procedure references multiple databases–quite common in most instances I see–and the fact that all ad hoc SQL is lumped together means that you’re losing a huge amount of data. In your example alone you’ve only successfully classified 20% of the activity. At best not very useful, and at worst misleading and counter-productive.

    A more appropriate way to collect this kind of information is by using the sys.dm_io_virtual_file_stats DMF. Google for articles and presentations on the topic by Andy Kelly. He has published full scripts that collect this data accurately and easily from the right places.

    Like

  3. Nice!

    I absolutely see it working on my computer.

    Jeff,

    It may be you are running only dymanic query and that could be the reason. In our infra, we use SP and it is giving me very accurate details.

    Margie

    Like

    • Ah, that would be the case, thanks. That would explain why my AdHocSQL is one of the biggest.

      I have several databases used by very small internal applications that are only using ad hoc queries.

      Like

  4. dev,

    You should also mention that this feature is also very useful to figure out how much Ad-Hoc queries are creating read and write in your system.

    In fact, this was never known gem.

    Like

    • Thanks Ben,

      I think your comment is very valuable to this post. This way anybody who will read this post will realize the limitation of this DMV and will be appropriately use this script.

      Kind Regards,
      Pinal

      Like

  5. Thought to share another couple of DMVs which I guess would be of help to production DBAs. There is one DMV on connections existing on the server “sys.dm_exec_connections” and one on the sessions “sys.dm_exec_sessions”. It provides lots of details along with resource consumption as well. HTH

    Like

    • Thanks Phani,

      I appreciate your suggestions here. This will help every body who will ready this post.

      It really helps community when a single topic is discussed by experts and added so much valuable information.

      Kind Regards,
      Pinal

      Like

  6. Hi Pinal,

    Great tip, thanks. We have some information about databases now, can we get the same information about tables (most read and write tables) additionally?

    Like

  7. Thanks a Lot.

    Sir, with this query can we make assumption that a DB is read or write intensive? And furthermore can we make suggetion for raid levels i.e. 5 or 10.?

    Like

  8. Pingback: SQL SERVER – Weekly Series – Memory Lane – #012 « SQL Server Journey with SQL Authority

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