question

CasperRubaek avatar image
0 Votes"
CasperRubaek asked ErlandSommarskog answered

How to track Azure SQL resource usage in code?

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-databaseazure-cost-managementdotnet-sqlclient
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AlbertoMorillo avatar image
0 Votes"
AlbertoMorillo answered CasperRubaek commented

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.



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The issue is that it is shared infrastructure for all tenants. Full multi tenancy.

0 Votes 0 ·
Bruce-SqlWork avatar image
0 Votes"
Bruce-SqlWork answered CasperRubaek commented

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://docs.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://docs.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://docs.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).

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

This unfortunately does not work because I use 1 database for all tenants. I can assign cost tag to each customers database call but I need to know how much resources that each customer consumes. Quite a rudimentary requirement. But it seems it is not possible in Azure

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered CasperRubaek commented

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.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

statistics io only gives the logical vs physical read count. this is some measure of the I/o load.

the real issue is that sqlserver was not designed to support chargeback billing (like say Cosmos Db or Oracle). most of its monitoring is for detecting extremes, and self configuration. most stats are kept in memory and updated in real time, as opposed to logged or tied back to a connection.

you can query system tables for query stats, but this adds load, and can miss a query that happens between query passes.

0 Votes 0 ·

Yeah you are unfortunately right.

0 Votes 0 ·

Yeah, perhaps I just need to accept that I can't monitor each customers database usage with a single database. I hope Microsoft builds this into SQL server in the future.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.