2

I know in case of Cluster index bigger page size improve peformance for range and multipoint query.

Does bigger page size can effect to degrade performance when we have non cluster index .Because normally rows are not existed near each other. In this situation, big page size read more rows which it does not need to read .

Same case for Write opeartion (insert and update) .Normally we need to update and insert sigle row .

Does one IO take same time for 4K and 16 KB ?

1 Answer 1

3

For bulk operations in index order a larger page size can give a noticeable, sometimes significant, performance increase. For very random access, particularly many small writes, there is similar potential for significant performance reduction.

A single read of a 16K block will take four times longer than the read for a 4K block, assuming the drive heads are in the right place at the time, but reading four sequential 4K blocks will be almost the same as reading a single 16Kb one.

Unfortunately it isn't something we can give you more precise advice on, especially without a lot of information about your application's I/O patterns - it is something that you probably need to benchmark with your specific application in your test environments to be sure about.

4Kb is usually picked for the default (at least on x86 or a64 based CPUs) as it matches pages sizes in the underlying OS and sometimes hardware so in the general case is more efficient because it is the "natural size". DB specific considerations override this though, which is presumably why MS SQL Server uses 8Kb page sizes.

2
  • .Would please give me link of any papers (benchmark) where i see difference and which can help me to prove your hypothesis.
    – user35662
    Jan 19, 2015 at 16:59
  • 1
    purestorage.com/blog/… is the only one that comes up from a quick search (their results show very little difference between sizes), but you should be able to find more reports if you spend longer searching than I did. The results will vary considerably depending on the application and load patterns though, so you are better off trying it yourself by simulating high load on instances of your application with different sizes than basing decisions on someone else's more arbitrary benchmark. Jan 20, 2015 at 9:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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