How to Use allocation_units system view for I/O Tests?

If want to use the allocation_units system view to calculate the I/O statistics while doing performance tests, you need to take a copy of the current counters from the allocation_units view before running a test, run the test, then compare counters to their copied values.

Here is an example SQL script, fyi_page_insert.sql, that runs a performance test of writing data using INSERT statements.

-- fyi_page_insert.sql
-- Copyright (c) FYIcenter.com 

DECLARE @Count INT
SET @Count = 100
DECLARE @Size INT
SET @Size = 256

DECLARE @STime DATETIME
DECLARE @ETime DATETIME
DECLARE @Duration INT

DECLARE @I INT
DECLARE @Message VARCHAR(5760)
SET @Message = 'The quick brown fox jumps over the lazy dog. '
SET @I = 7
WHILE @I > 0 BEGIN
   SET @Message = @Message+@Message
   SET @I = @I - 1
END
SET @Message = SUBSTRING(@Message,1,@Size/8-2)

CREATE TABLE FYI_DATA (
   MSG_1 VARCHAR(4096),
   MSG_2 VARCHAR(4096), 
   MSG_3 VARCHAR(4096), 
   MSG_4 VARCHAR(4096), 
   MSG_5 VARCHAR(4096), 
   MSG_6 VARCHAR(4096), 
   MSG_7 VARCHAR(4096), 
   MSG_8 VARCHAR(4096)
) 

select t.name, p.partition_id, p.rows, a.type_desc, 
   a.data_space_id, a.data_pages, f.file_id
   into #FYI_STATS
   from sys.objects t, sys.partitions p, sys.allocation_units a, 
      sys.database_files f
   where t.name = 'FYI_DATA'
      and t.object_id = p.object_id 
      and p.hobt_id = a.container_id
      and a.data_space_id = f.data_space_id

SET @STime = GETDATE()
SET @I = 0
WHILE @I < @Count BEGIN
   INSERT INTO FYI_DATA VALUES (@Message,@Message,@Message,@Message,
      @Message,@Message,@Message,@Message)
   SET @I = @I+1
END
SET @ETime = GETDATE()

SET @Duration = DATEDIFF(millisecond,@STime,@ETime)
SELECT @Size AS Size, @Count AS Count, @Duration AS Duration, 
   ''''+CONVERT(VARCHAR,p.partition_id) AS Partition, p.rows-l.rows AS '+Rows', 
   a.type_desc AS Allocation, a.data_pages-l.data_pages AS "+Pages", f.file_id
   from sys.objects t, sys.partitions p, sys.allocation_units a, 
      sys.database_files f, #FYI_STATS l 
   where t.name = 'FYI_DATA'
      and t.object_id = p.object_id 
      and p.hobt_id = a.container_id
      and a.data_space_id = f.data_space_id
      and l.file_id = f.file_id
      and l.partition_id = p.partition_id
      and l.type_desc = a.type_desc

DROP TABLE #FYI_STATS
DROP TABLE FYI_DATA

This script uses a temporary table to keep a copy of different counters of about the target table before the test.

New values of those same counters after the test are compared with old values to calculate the changes.

The partition_id value is a BIGINT. A prefix of "'" is added to keep it as text when copying it to shreadsheet tools.

Run the script with the default @Size and @Count values on SQL Server. The output may give you 2 records:

SIZE  COUNT  DURATION  PARTITION          +ROWS  ALLOCATION         +PAGES  FILE_ID
 256    100       424  72057594131054592  100    IN_ROW_DATA        4       1      
 256    100       424  72057594131054592  100    ROW_OVERFLOW_DATA  0       1  

With the above output, we can derive more information:

• The script reported that 100 rows were added to the partition of the target table. The row size is 256 bytes. So total of 256 * 100 = 25,600 bytes of data were stored in the target table.
• The script also reported that the execution time is 424 ms. So the throughput at the INSERT statement level is 100*1000/424 = 236 statement/sec. The latency is 4 ms/statement.
• The sys.allocation_units view reported that 2 storage units were allocated, one for IN_ROW_DATA and the other for ROW_OVERFLOW_DATA, to the same physical file.
• The sys.allocation_units view also reported that IN_ROW_DATA storage unit increased by 4 pages. So the page size is 25,600 / 4 = 6,400 bytes/page, assuming that the entire page is used to store data.

⇒ Page Size Varies on Azure SQL Database

⇐ I/O Tests on INSERT Statements

⇑ I/O Tests on INSERT Statements

⇑⇑ SQL Server Storage Tutorials