-1

I need someone to help me understand two columns of dm_io_virtual_file_stats.

Looking to my results of that procedure running and filtered against one database, I can see that the size_on_disk_bytes hasn't changed at all from early morning compared to the end of the day.

It started and ended the day with 3747 MB. However, if I subtract the value of num_of_bytes_written at the beginning of the day from the value of the end of the day, I find that there were more than 48 GB were written to the database data file.

Can anyone explain where and what those 48 GB are?

  • Is the database heavy on updates, and/or equal inserts & deletes? I'd double check your conversion to MB/GB too, as 48GB writes on a 3.7GB database does seems high. – Gareth Lyons Dec 18 '18 at 13:45
  • 4
    You should probably share the script you wrote, if you want help with it. – Erik Darling Dec 18 '18 at 13:52
2

The number of bytes written includes updates, so it might not increase the size of your actual data file. It could be that your workload includes lots of data replacement.

Additionally, whenever your data file grows, the size of the file will increase based on your database settings, and then data will be written into the empty space. So the "size on disk" should only increase periodically (as your file needs to grow), not as each row is inserted.

The number of bytes written even includes bytes written that were eventually rolled back. Check out this small demo from my copy of the Stack Overflow database:

DECLARE @firstRun AS TABLE 
(
    num_of_bytes_written BIGINT NOT NULL, 
    size_on_disk_bytes BIGINT NOT NULL
);

DECLARE @secondRun AS TABLE 
(
    num_of_bytes_written BIGINT NOT NULL, 
    size_on_disk_bytes BIGINT NOT NULL
);

INSERT INTO @firstRun
SELECT num_of_bytes_written, size_on_disk_bytes 
FROM sys.dm_io_virtual_file_stats(DB_ID('StackOverflow2010'), 1);

BEGIN TRANSACTION;

UPDATE dbo.Users
SET Reputation = 1;

INSERT INTO @secondRun
SELECT num_of_bytes_written, size_on_disk_bytes 
FROM sys.dm_io_virtual_file_stats(DB_ID('StackOverflow2010'), 1);

ROLLBACK TRANSACTION;

SELECT * FROM @firstRun;
SELECT * FROM @secondRun;

And the results?

screenshot of query results

Updating that whole table did ~26 MB of updates to the data file, but they were all eventually rolled back - none which increased the size of my data file on disk.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.