4

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?

14

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.)

  • 2
    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. – db2 Jul 9 '13 at 20:51
  • Additionally, if the disk is dedicated to SQL Server data files then format it using an 8192 byte allocation unit. – Greenstone Walker Jul 10 '13 at 4:02
  • 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. – TomTom Jul 10 '13 at 4:03
  • 9
    @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 Jul 10 '13 at 11:07
  • 7
    That said, 64k allocation is still the recommendation although this typically has no effect on performance (IOPS, MB/sec or latency). – Mark Storey-Smith Jul 10 '13 at 11:08

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.