Azure Sql Database Log I/O Seems High

13,115

Solution 1

The metrics represents the writes to the transaction log of the database. The transaction log tracks the changes to the data and hence this for the most part is a function of the amount of data you insert or update. As you said auditing has no impact on the log rate in SQL Database.

Solution 2

There is no impact of auditing based on your description of the workload. Insert / Update produces the log in the order of data you insert. A couple options you can try to improve the workload efficiency in SQL DB v12 is to disable SI / RCSI. If your records size is small this saves some data IO / log and temp db usage. More details @ http://www.sqlindepth.com/row-versioning-in-sql-database-version-v12/ You can also compress the tables so that your IO / log can be a little less than what it is, however your CPU consumption goes up a little.

Solution 3

Auditing is orthogonal to the transaction log IO and this is to maintain consistency and durability of your database. As Sirisha said you can disable SI / RCSI / compress (V12 database). You may also want to drop unwanted indexes on the tables so that the log IO can be small.

Solution 4

Try to change the retention days to the minimal that works with your case unless you need to keep the retention days to the max.

The logs are stored in your storage account.

Hope this helps.

Share:
13,115

Related videos on Youtube

Jacob Roberts
Author by

Jacob Roberts

Challenges of solving issues is what drives me. I spend most of my days at work solving complex problems and building software solutions. My nights are filled with fun and excitement enjoying the fruits of my labors with the realization that what I have was built on dedication alone.

Updated on September 15, 2022

Comments

  • Jacob Roberts
    Jacob Roberts about 1 year

    I've been optimizing our Azure Sql Database and started getting really good performance. The main concern now is the Logging that it does. When running a insert/update load test, everything is low except the CPU which is peaking around 15% and the logging which is peaking around 25%. Since the logging IO is hitting 25%, this causes the DTUs to be 25%. I turned off Auditing in the settings for the database but that did nothing. Is there a way to reduce the logging that is being done? I'm not even sure where the logs are being saved.

    Any insight on this would help as I've googled and couldn't locate anything worth mentioning about the logging that is happening.

    Here is a screen shot of the metrics...

    Azure SQL Database Metrics

    Workflow Details:

    I don't have byte sizes on me as I'm not in the office atm. Every Task is a SELECT and either INSERT or UPDATE, a typical add or update flow using Entity Framework. These tasks fire off and finish at a rate of 63 tasks per second to create those metrics.

  • Jacob Roberts
    Jacob Roberts over 8 years
    Thanks. I found the logs but the retention was set to 0 so there are too many logs to look at the container in Azure Storage Explorer. I did drop the retention down to 10 but as someone mentioned, the auditing doesn't effect DTUs.
  • Jacob Roberts
    Jacob Roberts over 8 years
    Now that you mention it, these Log IOs did get higher after I did a few "much needed" non clustered indexes. It is what it is I guess, considering if I do not have those indexes then the queries take forever.
  • Satya_MSFT
    Satya_MSFT over 8 years
    how frequently you load the data? Disabling / reenabling the index is an option for infrequent loads.
  • Jacob Roberts
    Jacob Roberts over 8 years
    I did a select SELECT is_read_committed_snapshot_on FROM sys.databases which returned true so I ran ALTER DATABASE [MyDatabaseName] SET READ_COMMITTED_SNAPSHOT OFF but it errors with message Incorrect syntax near 'READ_COMMITTED_SNAPSHOT'.. Your answer looks promising but I can't verify it until I get past this issue.
  • Sirisha Chamarthi
    Sirisha Chamarthi over 8 years
    You need to be on SQL DB V12. Check for version number (select @@version). This should be 12.X.X.X
  • Jacob Roberts
    Jacob Roberts over 8 years
    Well, crap. I'm on version 11.
  • Jacob Roberts
    Jacob Roberts over 8 years
    Requested updates from Azure to v12. I'll post when I find out more.
  • AMAAAAAR
    AMAAAAAR almost 5 years
    Be very careful with the ALTER DATABASE statement above. Running the statement on a 15 GB test database caused the database to reject all connection attempts. I waited appx 15-20 mins before I lost my patience and deleted the database in the portal and recreated it. I'm not sure if the database would recover if I waited longer, and I did not attempt admin connection.