Analytics around data entires being made in SQL tables?

Vipul Sharma 21 Reputation points
2022-08-24T20:24:29.677+00:00

We need to analyze how much data is being inserted every month or every day to understand the usage of the db tables in order to scale the DB accordingly.

Azure Monitor
Azure Monitor
An Azure service that is used to collect, analyze, and act on telemetry data from Azure and on-premises environments.
2,778 questions
Azure SQL Database
Azure Analysis Services
Azure Analysis Services
An Azure service that provides an enterprise-grade analytics engine.
435 questions
0 comments No comments
{count} votes

Accepted answer
  1. Oury Ba-MSFT 16,076 Reputation points Microsoft Employee
    2022-09-12T18:17:26.803+00:00

    Hi @Vipul Sharma Thank you for posting your question on Microsoft Q&A and for using Azure services.

    My understanding is that you want to know how much is being inserted every month or every day to understand the usage of DB tables.
    In addition to @Alberto Morillo 's answer. The Data ingestion per solution chart on the Usage and Estimated Costs page for each workspace shows the total volume of data sent and how much is being sent by each solution over the previous 31 days. This helps you determine trends such as whether any increase is from overall data usage or usage by a particular solution.

    If you can find information on data usage in log analytics workspace.

    Regards,
    Oury

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Alberto Morillo 32,791 Reputation points MVP
    2022-08-24T21:18:44.097+00:00

    On a daily basis, you can create the following temporary table at the beginning of the day.

       SELECT o.name as table_name, SUM(p.rows) sum_rows  
       INTO tmpBegOfDate  
       FROM   sys.objects o  
       JOIN   sys.partitions p ON o.object_id = p.object_id  
       WHERE  p.index_id IN (0, 1)  
       GROUP  BY o.name order by sum_rows desc  
    

    At the end of the day create this second temporary table.

       SELECT o.name as table_name, SUM(p.rows) sum_rows  
       INTO tmpEndOfDate  
       FROM   sys.objects o  
       JOIN   sys.partitions p ON o.object_id = p.object_id  
       WHERE  p.index_id IN (0, 1)  
       GROUP  BY o.name order by sum_rows desc  
    

    Now you can run below query to get your statistics for a day.

       SELECT b.table_Name, (e.sum_rows - b.sum_rows) AS RowsInsertedToday  
       FROM tmpBegOfDate b INNER JOIN tmpEndOfDate e  
       ON b.table_name = e.table_name  
       WHERE e.sum_rows is not null AND b.sum_rows is not null  
    
    1 person found this answer helpful.
    0 comments No comments