Setting up the hardware--disk question.

[jsteph]

Hi all,
This is somewhat academic, but I've a curiosity about this. When formatting the disk or disks for a sql-server platform, does it make a difference what cluster size one uses? For example, if the machine will be used, say as a datamart where it's high-volume reads that are important, it seems that you'd want the largest clusters.

However, I recall reading that one of the major dbms's--I thought it was sql-server--is talking to the disk at an even lower level than Windows api--is this true? Ie, does sql server have it's own interface to the disk so when one creates a db, the .mdf file is not so much a file in the OS sense but more like it's own partition? It sounds odd but it also sounds like it might have merit.
Thanks,
--Jim

 

[mrdenny]

Use 64k blocks. Data and log files are all done in 64k extents. SQL Server uses the Windows API to do all disk writes and reads.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[jsteph]

mrdenny,
Thank you. Does that mean that if a server's disks were formatted with the default (I believe 8K) that there could be some efficiency loss?

I had set up our datamart with 64K blocks but this was only because I was there for the 'birth' of this machine, but our other production app server was set up by our hardware guy so it definitely not 64K. I'm just wondering if the difference is one of those things thats so slight I should just let it go and never think about it, or if on our next disk upgrade we should back it up and reformat to 64K blocks.
--Jim

 

[mrdenny]

It all depends on your load. If you have a high IO load you'll notice the difference more. If you have a low load then you won't.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog