SQL Server Notes by AB | Note #31 | Database-wise CPU Cost | #ABSQLNotes

SQL Server Notes by AB | Note #31 | Database-wise CPU Cost | #ABSQLNotes

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 the CPU consumption by their databases. This means you need to find out how much CPU is being consumed by each database. Sounds strange, right? As a database professional, you know that CPU cycles are consumed by workloads and queries, not by the database itself. But those workloads belong to a database, right? So if we sum up the CPU consumption of all the workloads and group them by the database, we can try to achieve "database-wise CPU 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 CPU cycles. 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 two DMV - sys.dm_exec_query_stats (QS) and sys.dm_exec_plan_attributes (PA). The QS DMV gives you the execution statistics for each query and the PA DMV gives you the value for each plan attribute, one of them being the database id. That's it. Now all that we need is to join them and extract the computed output for our requirement. Check out a short demo on YouTube: https://www.youtube.com/watch?v=UZ8qN1Km9S0. Want to read more SQL notes like this one? Here: https://bit.ly/ABSQLNotes.