Sql-server – Precision for average_disk_seconds_per_read

extended-eventssql server

In SQL Server Extended Events, event = perfobject_logicaldisk, what is the actual unit of time for average_disk_seconds_per_read and average_disk_seconds_per_write?

All of the documentation I've found says "seconds", but my sample data includes values like these: 87708875, 1573358215, etc.

It's probably microseconds. I exported the target data to Excel and divided it by 1,000,000,000,000, which gives numbers that seem to be correct for microseconds, but I want confirmation before use such an xEvent query.

Best Answer

Extended Events are notorious for being poorly documented. Duration / units is especially so, see:

Extended event duration Millisecond or Microsecond?

Despite the fact that "seconds" is in the event field name, you're correct that the value being reported is nowhere near seconds. You mentioned that you have to divide the number by a trillion (a 1 with 12 zeroes after it). Rather than microseconds, that actually means the number is in picoseconds. Which sounds...crazy. But I think it's right.

To confirm this, I ran SELECT * from dbo.Posts; on the StackOverflow2010 database while running three different trace tools simultaneously and compared the results.

Extended Events: perfobject_logicaldisk

This event gets logged every 15 seconds.

For the "C:" instance_name at timestamp 09:44:43 (thus a span starting at 09:44:28), I got an average_disk_seconds_per_read of 3,839,009,107 during that 15 second interval.

For the "HarddiskVolume1" instance_name at that same timestamp, I got average_disk_seconds_per_read of 2,239,089.

There's also an entry for instance_name "HarddiskVolume3" with average_disk_seconds_per_read of 1,046,233.

I think those two volumes are system reservations and restore partitions respectively. For reference, here's the output from DiskPart on my machine

C:\WINDOWS\system32>diskpart

Microsoft DiskPart version 10.0.17134.1

Copyright (C) Microsoft Corporation.
On computer: *****************

DISKPART> list volume

  Volume ###  Ltr  Label        Fs     Type        Size     Status     Info
  ----------  ---  -----------  -----  ----------  -------  ---------  --------
  Volume 0     D                       DVD-ROM         0 B  No Media
  Volume 1         System Rese  NTFS   Partition    500 MB  Healthy    System
  Volume 2     C                NTFS   Partition    977 GB  Healthy    Boot

PerfMon.exe: LogicalDisk -> Avg. Disk sec/Read

This Windows performance counter object is documented as being reported in seconds. I only had it log the "C:" instance name. I see values around .002 seconds (2 milliseconds) on average during the period mentioned above.

PerfView: Windows Kernel/DiskIO/Read ETW event

PerfView samples much more aggressively than the other methods here (see screenshot with a snippet of the details below). Dumping that output (which is filtered to only include I/O done by the SQL Server process) into Excel indicates that the average seconds per read was 2.7 milliseconds over the entire sample.

What does it all mean?

ETW Tracing and Performance Counters, which are well known Windows components for measuring various performance metrics, indicate that reads were taking on average somewhere between .002 and .003 seconds (2-3 milliseconds).

Since the value we got from extended events was 3,839,009,107 I think we can assume it's trying to tell us .003 seconds (closer to .004). Backing in to that number, we'd have to divide by a trillion to get there.

I might be bad at math, but it looks like the number reported by this EE field is in picoseconds.

Terrible. Just...terrible.

Related Solutions

Sql-server – Importing from Excel

As you said

1) Import a sheet with only headers and sample data (to "seed" the data type) into a new table
2) Use Design view to correct data types as necessary
3) Import the sheet with all the headers and data

I would like to say that if you want to import excel sheet with header data then through TSQL is the best option to do that.

First you have to check in SSMS tools there is ODBC driver are there or not in Server Objects->Linked Servers->MICROSOFT.ACE.OLEDB.12.0 or other microsoft ACE OLEDB driver. it depends on your SQL Server version. In My case it is SQL Server 2012.

Suppose that if you are using SQL Server 2012 and there is MICROSOFT.ACE.OLEDB.12.0 then make sure 'Allow inprocess' is Enable.

How You shall do that

Right click over MICROSOFT.ACE.OLEDB.12.0 driver and in new window there will be 'Allow inprocess' option. check to that option like that

write down in your SSMS this TSQL statement like that, through this query you shall import your execl column header with your data.

Use DatabaseName;
        Go
        select * INTO [User]
        from openrowset('MICROSOFT.ACE.OLEDB.12.0',
        'Excel 12.0; Database=C:\TestFolder\User.xlsx; HDR=yes; IMEX=1',
        'select * from [User$]');
        Go

Here DatabaseName is the Name of Database.
User is the Table Name .
User.xlsx is the Excel sheet , which you will import.
Make sure that HDR='yes' which will contains your header column names.

I hope so that it will helpful to you . To import your Excel sheet data in SQL Server through SSMS tools & TSQL Script.

Sql-server – Find uncompressed size of all tables in a database

I need the actual size the uncompressed data will have.
...
I would prefer to have size as correct as possible.

While the desire for this information is certainly understandable, getting this information, especially in the context of "correct as possible" is trickier than everyone is expecting due to faulty assumptions. Whether doing the uncompressed shadow table idea mentioned in the question, or @sp_BlitzErik's suggestion in a comment about restoring the DB and uncompressing there to check, it should not be assumed that the size of the uncompressed table == the size of said data in memory on the app server:

Are all rows in the table being cached? Or just within a range? The assumption here is that it is all, and that might be correct, but I figured it should at least be mentioned that this might not be the case (unless documentation states otherwise, but this is a minor point anyway, just didn't want it to not be mentioned).

Question was updated to state: yes, all rows are being cached.
Structure overhead
1. On the DB side:
  Page and row-overhead on the DB side: How many rows fit on a page is determined by many factors that could throw off estimates. Even with a FILLFACTOR of 100 (or 0), there is still likely to be some unused space left over on the page due to it not being enough for an entire row. And that is in addition to the page header. Also, if any Snapshot Isolation functionality is enabled, there will be, I believe, an extra 13 bytes per row taken up by the version number, and that will throw off estimates. There is other minutia related to the actual size of the row (NULL bitmap, variable length columns, etc) but the items mentioned thus far should alone make the point.
2. On the app server side:
  What type of collection is being used to store the cached results? I assume this is a .NET app, so is it a DataTable? A generic list? A SortedDictionary? Each type of collection has a different amount of overheard. I would not expect any of the options to necessarily mirror the Page and Row overheads on the DB side, especially at scale (I'm sure a small amount of row might not have enough various to matter, but you aren't looking for differences in hundreds of bytes or just a few kB).
Datatypes
1. On the DB side:
  CHAR / VARCHAR data is stored at 1 byte per character (ignoring double-byte characters for the moment). XML is optimized to not take up nearly as much space as the text representation would imply. This datatype creates a dictionary of element and attribute names and replaces the actual references to them in the document with their respective IDs (kinda nice, actually). Otherwise, the string values are all UTF-16 (2 or 4 bytes per "character"), just like NCHAR / NVARCHAR. DATETIME2 is between 6 and 8 bytes. DECIMAL is between 5 and 17 bytes (depending on the precision).
2. On the app server side:
  Strings (again, assuming .NET) are always UTF-16. There is no optimization for 8-bit strings such as what VARCHAR holds. BUT, strings can also be "interned" which is a shared copy that can be referenced many times (but I don't know if this works for strings in collections, or if so, if it works for all types of collections). XML may or may not be stored the same way in memory (I will have to look that up). DateTime is always 8 bytes (like T-SQL DATETIME, but not like DATE, TIME, or DATETIME2). Decimal is always 16 bytes.

All of that to say: there is pretty much nothing you can do on the DB side to gain even fairly accurate memory footprint size on the app server side. You need to find a way to interrogate the app server itself, after being loaded with a particular table, so know how big it is. And I am not sure if a debugger would let you see the runtime size of a filled collection. If not, then the only way to get close would be to go through all rows of a table, multiplying each column by the appropriate .NET size (e.g. INT = * 4, VARCHAR = DATALENGTH() * 2, NVARCHAR = DATALENGTH(), XML = ?, etc), but that still leaves the question of the overhead of the collection plus each element of the collection.

Given some new definition in the question, one could probably do the following query to get rather close. And it doesn't matter whether the table is compressed or not, though it's up to each person to determine if scanning all rows is appropriate on Production (maybe do from a restore or during off-peak hours):

SELECT
   SUM( DATALENGTH([NVarcharColumn_1]) + DATALENGTH([NVarcharColumn_N]) ) + 
   SUM( (DATALENGTH([VarcharColumn_1]) + DATALENGTH([VarcharColumn_N])) * 2 ) + 
   SUM(4 * [number_of_INT_columns]) +
   SUM(8 * [number_of_BIGINT_and_DATETIME_columns]) +
   SUM(16 * [number_of_DECIMAL/NUMERIC_and_UNIQUEIDENTIFIER_columns]) +
   etc..
FROM [SchemaName].[TableName] WITH (NOLOCK) -- assuming no Snapshot Isolation

But remember, this doesn't account for collection or collection element overhead. And not sure if we can get that value without a debugger (or possibly something like ILSpy, but I am not recommending that as it might violate the EULA depending on local laws).