October 19, 2002 at 11:05 pm
SQL Server 2000 & W2K Advanced Server.
These questions assume physical reads and physical writes. (disk to cache, cache to disk)
- How much data does SQL Server 2000 get in one single physical read I/O?
- What is the amount of data that it transfers for a read? write?
October 21, 2002 at 3:53 am
IO is in terms of pages which in SQL 2000 are 8k in size.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
October 21, 2002 at 3:55 am
Usin SET STATISTICS IO ON or selecting this option in the options of Query analyser. Shows what how many reads are made for each table, from cache and disk
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
October 21, 2002 at 4:57 am
I think that disk level IO is done in 64k chunks, not necessarily contiguous either. They do 'scatter gather' reads. Basically they can read 8 pages for the price of 1.
Andy
October 21, 2002 at 5:15 am
For the complete story look up "I/O Architecture" in BOL and the associated pages.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
October 21, 2002 at 7:17 am
I've looked in BOL. Not there what I am looking for.
So for each physical read, SQL Server 2000 brings from disk to cache a 64KB chuck? (8 pages). Even if it only needs 1 row it will bring in 64KB? How many I/O trips does it have to make? Does this depend on the disk cluster size?
What about writes? It will write out one page if it is scheduled correct?
So if the cluster size is 512kb, it will have to make 16 trips to complete the 8kb I/O correct? (512bytes x 16 = 8kb)
October 21, 2002 at 2:44 pm
My understanding of what it states is that it will read and write 8K pages "an io" if more than this is required it can read on extent into non-contigous memory using scatter gather. The server will also use read-ahead when it determines that it would be beneficial, i.e. read in 64kb blocks. This allows the sql server to request in one call the 64kb data, NT then manages the request to the disk, which does the neccesary reads from the disk.
Are you trying to determine the allocation unit size that you should use?
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
October 21, 2002 at 3:12 pm
exactly.
We are going on an EMC SAN and we want the optimal cluster size/allocation unit size for SQL Server 2000 on Windows 2000 Advanced Server.
I am really concerned about the cluster size choice and the relationship between SQL Server 2000 physical reads and physical writes with that cluster size.
October 21, 2002 at 4:07 pm
Because SQL Server files are created in large blocks and SQL Server handles the pages within the file, I believe that their is little difference but you are probably best going for a 4k size. Do a search in google on "allocation unit size" "SQL Server" there are some good articles
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
October 21, 2002 at 4:53 pm
You also want to look at the stripe size - we use 64k. Works well enough. Intel has a free app called IOmeter that is very very handy for testing this sort of stuff. With a good disk set its hard to generate the IO's needed to stress it.
Andy
October 21, 2002 at 5:06 pm
Andy,
What is the difference between the stripe set and cluster size?
October 22, 2002 at 2:47 am
cluster size and allocation unit are the same
stripe set is the set of disks used in an strip array.
stripe size
Here are some good articles
http://searchwin2000.techtarget.com/tip/1,289483,sid1_gci804291,00.html
http://www.storageadmin.com/Articles/Index.cfm?ArticleID=2797
The later suggests that a stripe size of 1024 sectors (1024*512 = 512Kbytes) is the best. The test was reading in 64Kb blocks and so 51Kb is probably too high.
This is better
http://www.storageadmin.com/Articles/Index.cfm?ArticleID=5398
and says that 64Kb is better for reading and smaller is better for writing, So how is your app usage distributed, %reads or %writes
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply