How to track Azure SQL resource usage in code?

Casper Rubæk 246 Reputation points
2022-08-08T15:32:54.743+00:00

I am building a multi-tenant SaaS (completely shared infrastructure compute and database) in Azure with Azure SQL database and I want to know specifically what each of the databases calls that occur for each tenant costs.

How can I track this in C# code?

With Azure Cosmos DB I know I can track by request unit charge which I get returned to my code. I want something similar for the SQL database so I can store this data in Azure Table storage.

I am using Entity Framework Core.

Azure SQL Database
Azure Cost Management
Azure Cost Management
A Microsoft offering that enables tracking of cloud usage and expenditures for Azure and other cloud providers.
1,930 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,481 questions
0 comments No comments
{count} votes

4 answers

Sort by: Most helpful
  1. Alberto Morillo 32,716 Reputation points MVP
    2022-08-08T16:48:31.313+00:00

    If the application is multi-tenant but separate databases per customer, you can use this API (in the example they re using "Legacy" tag) and make use of a tag on each database to keep track of charges.


  2. Bruce (SqlWork.com) 53,426 Reputation points
    2022-08-08T17:00:18.503+00:00

    sql server does not return this data nor doe it have great tools for this (never designed as a billable resource). you can get some connection statistics:

    https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/provider-statistics-for-sql-server

    In a standalone instance you can configure the Resource Governor and query its tables. but this only gives max limits not usage.

    https://learn.microsoft.com/en-us/sql/relational-databases/resource-governor/view-resource-governor-properties?view=sql-server-ver16

    I do not believe this is available for azure sql. you will need to extract the data from azure billing:

    https://learn.microsoft.com/en-us/azure/cost-management-billing/costs/tutorial-export-acm-data?WT.mc_id=costmanagementcontent_docsacmhorizontal_-inproduct-learn&tabs=azure-portal

    if you need to separate data by tenant, you will probably need a database per tenant, or come up with your own costing scheme (assign a cost to queries and result size).


  3. Erland Sommarskog 100.1K Reputation points MVP
    2022-08-09T06:38:51.6+00:00

    What you possibly could to is to issue SET STATISTICS TIME ON (and/or SET STATISTICS IO ON) and use the information returned by SQL Server for your debiting. To pick up this output in C# you would have to create an InfoMessage handler.

    I'm not certain how accurate this would be though. CPU time should be OK, but if you want to track I/O load?

    There is also the problem that one tenant may consume more CPU, because the query plan has been sniffed and optimized for a different tenant? Or simply has a usage pattern which is penalised because you write crappy code?

    We should also not forget the the general problem: monitoring adds overhead. So if SQL Database would offer something you could use, it would also make the system slower.


  4. Erland Sommarskog 100.1K Reputation points MVP
    2022-08-09T18:40:08.92+00:00

    You can submit feedback requests on https://feedback.azure.com/d365community/forum/04fe6ee0-3b25-ec11-b6e6-000d3a4f0da0. When you do this, make sure that you explain your business clearly, because that is something that Microsoft pays attention to.

    I think you absolutely have a valid use case. But I am not really holding my breath that Microsoft will address this, because I can see the challenge. As Bruce says, SQL Server was not designed for this case. Rather, SQL Server has been designed to be used within an Enterprise where applications share the load in solidarity. That is, one process can produce a lot of physical reads that drags a lot of data into the cache - which then other processes can benefit from.

    I brought this up with some MVP colleagues, and one mentioned "SQL insights", that I don't know what it is, but he reported he had a customer using it in way you are looking into. Other suggestions included using extended events to monitor calls and using SqlConnection.RetrieveStatistics. But there was also the obvious comment that it's a lot simpler with one database per tenant. And if you use stored procedures, you could add your own accounting to those.

    0 comments No comments