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.

SQL SERVER - Find Busiest Database tlrw

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)

SQL SERVER - Find Busiest Database tlrw1

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
Previous Post
SQLAuthority News – SQL Server Migration QuickStart
Next Post
SQLAuthority News – Vote for SQL Server 2005 Service Pack 4 – Vote for SQL Server 2008 Service Pack 2

Related Posts

29 Comments. Leave new

  • 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

    Reply
  • Several of my databases don’t show up in this query. Is this because they have no current connections?

    Reply
  • 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.

    Reply
  • So, how I do the same in Sql 2000 ? i try but not works

    Reply
  • 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

    Reply
    • 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.

      Reply
  • DMV sys.dm_exec_query_stats resets when services are reset, where as DVF sys.dm_io_virtual_file_stats works with server restart.

    My needs are more related to service restart.

    Reply
    • The virtual file stats DMF will also reset with a service restart. The difference is, it actually reports the data you’re interested in seeing :-)

      Reply
  • good post,

    Started to use it today.

    Reply
  • Sundar Manmohan
    January 15, 2010 6:55 am

    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.

    Reply
  • Benjamin Nevarez
    January 15, 2010 10:08 am

    I totally agree with Adam, this DMV will not give you the information you are looking for. Plus not every plan is cached and some cached plans can be removed by memory pressure.

    Ben

    Reply
    • 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

      Reply
  • 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

    Reply
    • 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

      Reply
  • Hi Pinal,
    Thanks for the script, Nice discussion around this topic.

    Reply
  • 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?

    Reply
  • Hello Sir,
    Nice Script.
    is there any way we find the busiest Table in a database ?

    Reply
  • Hi,

    Visit the link https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/system-dynamic-management-views?view=sql-server-2017 to get the whole list of Dynamic Management Views, different categories and detailed explanation.

    Regards,
    Phani

    Reply
  • Please send this query for sql 2000

    I am not aware of any workaround for SQL Server 2000.

    Reply
  • There are so much to learn. With DMV’s we could do lot of analysis on production environments.

    Reply
  • Pinal,
    Here we can see the AdhocSQL databasename in the list .What does that mean?

    Reply
  • Hello Abhikit,

    A dynamic statment, can be executed in any database and the sql_handle is not associated with any db_id. So it can interpreted that all batch that are not associated with any db_id are adhoc statements.
    For more information see the page:
    https://docs.microsoft.com/en-us/collaborate/connect-redirect

    Regards,
    Pinal Dave

    Reply
  • 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.?

    Reply
  • CROSS APPLYing, not CROSS JOINing, eh?

    Reply

Leave a Reply

Menu