Partition Management....(Don't forget the Management)

You'd think by now I'd know better than to cut corners. But for some reason, I still somehow seem to decide once in a while for short term gains over long term benefits...

A while back I was overloaded with stuff so I set up a year's worth of new monthly partitions in one of our reporting databases. I did not, however, chose to create any nice job to automatically create new partitions or anything like that, as I did not have time (or so I thought).

Well, 'A while back' is now 18 months ago.... Hmm... 6 months of data in one partition. Ouch.

Might not be a big deal with Oracle, but it's SQLServer 2005. Sadly, just splitting 1 day of data into a new partition takes 10 minutes and 9 GB of transaction log space. Very depressing. (At least I tested and timed it before I tried to split one month's data into a new partition).

Kills me some times. Don't put off till tomorrow what you can do today, if tomorrow might become next year... ;)

Hopefully I'll come up with a slick way to split those partitions faster... we'll see.

And yes, I'm going to create a job to add partitions in advance.... :)

SQLServer 2005: Working with partitions

Now that SQLServer finally supports something the database world would really consider a decent first attempt at partitioning, I think it's high time we all start putting it to practice.

Partitioning in the Oracle world is almost taken for granted. It's one of the most straight forward ways to scale a database in terms of size and performance, without looking at additional hardware and/or heavy software changes in terms of data flows and use patterns.

Partitioning does not need to be restricted to data warehousing either. It has very practical applications to OLTP databases. When it comes to SQLServer, in my mind, it has even more impact on performance than it does with Oracle because of SQLServer's implementation of locking (although, technically, with SQLServer 2005, it is possible to implement a reader's don't block writer's transaction scheme).

This is because partitioning allows us to have more control over what rows/pages get locked during inserts and selects if we design it correctly. The next few posts with focus on ways to partition an OLTP application, and my workarounds for unimplemented schemes like hash-partitioning. Most of my initial on the job training I give credit to Frank Bommarito, an incredibly talented Oracle architect.

SQLServer: DataWarehouses and DATETIME

So we want to grab significant transactions from yesterday for our datawarehouse, but we're not quite sure exactly what time our DTS extraction jobs will run.

What is the easiest way to get all rows that have a datetime column entry from yesterday? With Oracle, we just use trunc sysdate - 1 and trunc sysdate, but I haven't been able to find a good trunc function in SQLServer.

Here's what we've come up with... (Ugly, but effective)

SELECT *
FROM audit
WHERE moddate >= CAST(CAST(DATEPART(YEAR,GETDATE()) AS VARCHAR)
+ '-' + CAST(CAST(DATEPART(MONTH,GETDATE()) AS VARCHAR)
+ '-' + CAST(CAST(DATEPART(DAY,GETDATE()) AS VARCHAR) AS DATETIME) - 1
AND moddate <= CAST(CAST(DATEPART(YEAR,GETDATE()) AS VARCHAR) + '-' + CAST(CAST(DATEPART(MONTH,GETDATE()) AS VARCHAR) + '-' + CAST(CAST(DATEPART(DAY,GETDATE()) AS VARCHAR) AS DATETIME)


[ audit table has various fields, with moddate as datetime datatype. ]

SQLServer: Partitions

Initial testing with SQLServer 2000 partitions appears to work simillarly with Oracle from a select perspective.

Explain plans show accessing only local partition indexes, and not all partitions (or tables) in the union views. Inserts / updates appear to first check each partition, then perform the transaction only on the necessary partition.

Some of the issues we've specifically found with partitions are all the nitpicky rules associated with them.

• Underlying tables must have a primary key and the partition constraint must be part of it.
• Doesn't seem to work with between all the time with the optimizer.
• Must be running Enterprise Edition of SQLServer

Hmm.... Now that's about the same cost as Oracle ;)

And so on, and so on, and so on...

It'll be nice when SQLServer catches up with Oracle from a partitioning perspective, but at least it's a start.