My Own Custom Table Partitioning

Question

My Own Custom Table Partitioning

Viewing 4 posts - 31 through 34 (of 34 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

There are a lot of very good comments already, so I'm not going to delve very deeply into custom partitioning. Instead of that - I'd offer you another thought, which is that it's very easy to scale this out based on multiple servers, and control the "scale out" during the sign-in process from the UI. Rolling out multiple "copies" of your site where the only difference is the web.config in each IIS instance. It essentially allows you to "cheaply" spread your traffic at will across any number of SQL instances on any number of servers. So - no fancy partitioning process - just one telling the app what server/instance to deal with.

As a matter of fact - you can get there by simply getting creative with ONE site, and an extensive web.config with a few custom sections. Log in, pull in the user's profile, which then tells you where to get the rest of the data, and go from there. Everything else could be identical.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

ammon SSC Enthusiast Points: 147 More actions · Answer 2

Thats not a bad idea! My approach would net the same results, but your way would keep it simpler. I think I might just plan on doing that. Then if we never do get to the point that I need to split stuff up I can easily. So I can go forward designing/planning a system that uses one DB w/o partitioning. Having said that, I am leaning towards doing standard SQL partitioning on the tables that I know are going to be huge....

Aaron West SSC Eights! Points: 808 More actions · Answer 3

I know this whole subject may have been beaten to death, but I don't buy the argument that 64-bit integers are a big cost. I would estimate less than 1% additional cost in CPU and space for most applications, unless your rows are already *VERY* small, in which case perhaps 5% or so. Each row is at least about 16 bytes I think before you even add data. (I forgot exactly what the row overhead is...it's a lot higher if you use row versioning though, which uses an additional 14 to 16 bytes per row.) If you think a table will ever have anything close to 2 billion rows, and your primary key is an INT IDENTITY(1,1), then it's probably worth making the primary key bigint up-front. (I did that on a table with 130 million rows, after a few years of collecting data, and I did it when the table had only 50 million or fewer, and I've never seen a performance impact due to expanding to 64 bits.)

And as for web services, conversion of an INT64 to a string would result in the same sized string as if it were an INT32, providing the number fits in 31 bits, so that argument doesn't make sense either.

If you were to argue against GUIDs, that's another story... My problem with GUIDs isn't the size, 128 bits, as much as the page fragmentation encountered when some DBAs choose to make the GUID the first (if only) key of a clustered index, as may happen if you make it the primary key and accept defaults. Sometimes this is actually desired behavior, for example for a table which is spread among many disks (I'd say at least 6 to 8) where the randomness of GUIDs results in spreading the IO out more evenly. But if I were to configure a table that way, I would probably implement a nightly ALTER INDEX..REORGANIZE with some slack space in the pages (eg. FILLFACTOR=80%), so that fragmentation of the pages is infrequent. Perhaps the best argument for partitioning is that operations such as this could act on a single partition.

ammon SSC Enthusiast Points: 147 More actions · Answer 4

Thanks for the input. Based on a lot of input here I did decided to use bigint for the tables that will get really big.

Aaron West (6/11/2008)
And as for web services, conversion of an INT64 to a string would result in the same sized string as if it were an INT32, providing the number fits in 31 bits, so that argument doesn't make sense either.

Well, I should clarify - I am actually going to use Windows Communication Foundation web services. They serialize data instead of using plain XML like standard Web Services do.