6

I have read a document (Designing Highly Scalable OLTP Systems) that describe a trick how to get more performance:

Because rows are small (many fit a page) multiple locks may compete for one PAGELATCH. We can ”waste” a bit of space to get more performance. Solution: Pad rows with CHAR column to make each row take a full page.

Does anyone use this approach? What results have you got?

Thank you.

ADDED:

Warning. The linked article is not a guide to action. Thomas Kejser describes tests on terabyte-sized databases, no more.

|improve this question
6

I've only briefly scanned though the document but my first thought was "oh, a SQLCAT publication". These guys push out some fantastic material but much of their guidance is derived from the very extreme end of pushing the boundaries.

You can learn a great deal from reading about SQL Server behaviour in multi-million $$$ implementations but very little of it applies to typical installations. This particular document is the result of testing a 10k/tps, 256 core, 500 spindle system.

In the real world where you aren't pushing SQL Server internals beyond their anticipated limits, these are interesting diversions, not recommendations.

Your question is perhaps somewhat dangerous. A casual passer by may read your question, briefly scan the linked document and conclude that padding rows to fill a page is a good idea.

If I have 256 cores and 500 disks delivering 18GB/sec but I'm seeing PAGELATCH_EX waits for my highly specialised workload xyz, will I increase throughput by padding rows to fill a page?

Yes. As Thomas Kejser has demonstrated.

Should we all be doing the same?

Absolutely not!

|improve this answer
  • so I decided to ask. – garik Aug 31 '11 at 22:42
  • 2
    And there's absolutely nothing wrong with that Garik, no offence intended. – Mark Storey-Smith Aug 31 '11 at 22:44
1

Sounds pretty scary to me. You want one row per page? If the table is large and you do anything - ever - other than single-row seeks, I think you are asking for trouble. Sure you might have less page contention on average, but your I/O is going to go through the roof. Now, the impact will be relative - e.g. how many rows fit on a page normally? Are we talking about 3 kb rows or 300 byte rows?

|improve this answer
  • That example shows: (effective row size + char(5000) with some default value in it) < 8K, so if I understood, every row can be allocated only for one page. So it will work for rows with size > 4K (more than half of page) and < 8K, right? – garik Aug 31 '11 at 22:31

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.