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 (https://blog.sqlauthority.com)
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
That is good point Phani.
I will check into it and will get back to you by email or over here.
Kind Regards,
Pinal
Several of my databases don’t show up in this query. Is this because they have no current connections?
Jeff,
As this is DMV it will only show the data from last services restart. Is that the case, with you?
Kind Regards,
Pinal
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.
So, how I do the same in Sql 2000 ? i try but not works
I am not aware of any workaround for SQL Server 2000.
The workaround for SQL Server 2000 is to use the correct approach to begin with and use the fn_virtualfilestats function–the precursor to the virtual file stats DMF.
Thanks Adam for your reply for SQL Server 2000.
This helps.
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
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.
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.
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 :-)
good post,
Started to use it today.
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.
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
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
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
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
Hi Pinal,
Thanks for the script, Nice discussion around this topic.
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?
Hello Sir,
Nice Script.
is there any way we find the busiest Table in a database ?
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
Please send this query for sql 2000
I am not aware of any workaround for SQL Server 2000.
There are so much to learn. With DMV’s we could do lot of analysis on production environments.
Pinal,
Here we can see the AdhocSQL databasename in the list .What does that mean?
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
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.?
CROSS APPLYing, not CROSS JOINing, eh?