How large are blocks of data in SQL Server?

Question

I'm working in SQL Server 2008 R2 and have created a query that gathers and sums the total of data files and log files' sizes. However, I can't find how much actual disk space a single block of SQL data takes up on the disk so I can convert it into something more meaningful.

Here is the script:

DECLARE @DataSize INT
DECLARE @LogSize INT
SELECT @DataSize = SUM(size) from sys.database_files where type_desc = 'Rows'
SELECT @LogSize = SUM(size) from sys.database_files where type_desc = 'Log'
PRINT @DataSize
PRINT @LogSize

How large is one block of space? Would it be easy to convert those two integer variables into something more meaningful for a sysadmin?

Answer 1

From the column documentation of sys.database_files:

size: Current size of the file, in 8-KB pages.

So a more meaningful query might be something like this:

SELECT
 df.name AS LogicalFileName,
 df.physical_name AS PhysicalPath,
 CONVERT(int, (((df.size * 8192) / 1024.0) / 1024.0)) AS SizeInMB
 FROM sys.database_files df;

(You can expand on that further as necessary.)

• db2: It's worth adding that SQL Server does data file I/O in chunks of 8 pages, or 64 KB "extents", so optimize storage around that rather than individual 8 KB pages.
• Greenstone Walker: Additionally, if the disk is dedicated to SQL Server data files then format it using an 8192 byte allocation unit.
• TomTom: Actually no - a 8192yte allocation unit would be utterly stupid. As SQL Server does all IO in extends - you need to format it witha 64k allocation unit. Then NTFS allocation units and IO operations line up. SQL Server never does smaller IO than 64k.
• Mark Storey-Smith: @TomTom Actually no, SQL Server does not do all IO in extents. The tests demonstrated in Dissecting SQL Server physical reads with Extended Events and Process monitor and Detailed Windows I/O: Process Monitor are easily replicated (and interesting to observe).
• Mark Storey-Smith: That said, 64k allocation is still the recommendation although this typically has no effect on performance (IOPS, MB/sec or latency).