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
Several of my databases don’t show up in this query. Is this because they have no current connections?
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
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
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
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?