ScottPletcher (7/23/2014)

---

Igor Micev (7/23/2014)

---

ScottPletcher (7/23/2014)

---

I suspect there's a better clustering key, rather than the "default" identity, that might clear up a lot of your performance issues. Getting the best clustering key(s) is the first thing you should do, either confirming the current one or determining a better one.

This is really a good think you could do, to move the varchar(max) columns out of the main table into another. I've been working on NoSQL systems and that approach is pretty much used. That will bring benefits.

Then, if the varchar(max) columns aren't heavily used, you could force them out of the main table space, the compress the main table (unfortunately, then the "overflow" would not be compressed).

But if you separate the table by columns, would you need to compress the tables? Maybe no big benefits as like the benefits from the separating.

However, it's good to see feedback from your work and experience.

Let me clarify: I was actually referring to forcing the data off the main pages into overflow pages for the same table, not to a different table.