Let’s say you are running SQL Server in a hosting environment where the instance is hosting hundreds of small databases, each belonging to a different client. Your hosting company is billing customers based on resource consumption, and IO is a critical resource. This means you need to find out how much IO is being consumed by each database. Sounds strange, right? As a database professional, you know that IOPs happen with workloads and queries, not by the database itself. But those workloads belong to a database, right? So if we sum up the IO consumption (reads & writes) of all the workloads and group them by the database, we can try to achieve “database-wise IO consumption”. Hmm… this does not sound as meaningless as it was sounding a while ago. I know my audience. It is unlikely that you are working for a hosting company. So let us change the business a bit. Let’s say your SQL Server instance is hosting multiple small databases for different applications running inside the IT infrastructure of your company serving different purposes. And you have the same requirement to find out which database is consuming the maximum reads & writes (overall IO consumption). So that you can focus your tuning efforts on workloads coming from those databases. Yeah, this sounds better. So in SQL Server, you can achieve this by using the DMV – sys.dm_io_virtual_file_stats. This DMV has important attributes like the total number of bytes read and written for each data file present in the database. Well, once you get to this, you can do the maths. Well, there is another DMV, sys.dm_exec_query_stats, which has IO numbers for each query but let’s talk about that in another note. For this one, let’s focus on sys.dm_io_virtual_file_stats. To know more watch the video below.
Subscribe to SQLMaestros YouTube channel. If you want more learning content in your inbox, subscribe to SQLMaestros Bulletin.
Thanks for reading & watching. If you liked the content, do share it. I am on Twitter if you wish to connect.
You can also subscribe to my exclusive newsletter ConnectWithAB.