Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 14 down vote accepted

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

share|improve this answer
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
8  
@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
6  
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.