Collections:
Use fn_virtualfilestats() for I/O Tests
How to Use fn_virtualfilestats() for I/O Tests?
✍: FYIcenter.com
If want to use the fn_virtualfilestats() function table to calculate
the I/O statistics while doing performance tests,
you need to take a copy of the current counters from the fn_virtualfilestats()
function before running a test, run the test, then compare counters
to their copied values.
Here is an example SQL script, fyi_insert_write.sql, that runs a performance test of writing data using INSERT statements.
-- fyi_insert_write.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, s.NumberReads, s.BytesRead, s.NumberWrites, s.BytesWritten into #FYI_STATS from sys.objects t, sys.partitions p, sys.allocation_units a, sys.database_files f, fn_virtualfilestats(DB_ID(),NULL) s 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 f.file_id = s.fileid 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() WAITFOR DELAY '00:00:05' SET @Duration = DATEDIFF(millisecond,@STime,@ETime) SELECT @Size AS Size, @Count AS Count, @Duration AS Duration, 1000*@Count/@Duration AS 'Throughput', @Duration/@Count AS Latency, p.rows-l.rows as '+Rows', a.type_desc, a.data_pages-l.data_pages as "+Pages", f.file_id, s.NumberReads-l.NumberReads as "Reads", s.BytesRead-l.BytesRead as "Bytes Read", s.NumberWrites-l.NumberWrites as "Writes", s.BytesWritten-l.BytesWritten as "Bytes Written", l.* from sys.objects t, sys.partitions p, sys.allocation_units a, sys.database_files f, fn_virtualfilestats(DB_ID(),NULL) s, #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 f.file_id = s.fileid 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 are compared with old values to calculate the changes.
The "WAITFOR DELAY '00:00:05'" function is called to wait for 5 seconds to give enough time for fn_virtualfilestats() to be refreshed after the test is terminated.
Run the script with the default @Size and @Count values on SQL Server. The output may give you 2 records with many fields. But you can divide the record into 2 chunks:
SIZE COUNT DURATION THROUGHPUT LATENCY +ROWS 256 100 407 245 4 100 TYPE_DESC +PAGES FILE_ID READS BYTES READ WRITES BYTES WRITTEN IN_ROW_DATA 4 1 0 0 1 8192 ROW_OVERFLOW_DATA 0 1 0 0 1 8192
As you can see:
If we put all reports together, we see some inconsistencies:
⇒ fn_virtualfilestats() Not Useful on Azure SQL
⇐ Page Size Varies on SQL Server 2016 Database
2019-05-12, 1315🔥, 0💬
Popular Posts:
How to generate test phone numbers for US and Canada? Test phone numbers are frequently needed in te...
How to generate currency test values? Currency test values are frequently needed in testing date and...
What are JMeter command line options? You can get see JMeter command line options by running this co...
How to validate and decode MAC (Media Access Control) addresses? In order to help your programming o...
How to valid IP addresses? In order to help your programming or testing tasks, FYIcenter.com has des...