Tuesday, March 13, 2007

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.

Monday, March 05, 2007

SQLServer 2000: Memory Grants Pending

I tell you -

Finding out anything about memory grants pending is virtually impossible. Yea, there are a few references to it out there in the google world, but most just refer to the fact that it's not much more than the title implies. SQLServer has memory grants for processes queued up.

At surface value, you would expect it would mean 'get more memory', which is what most of the links imply.

As it turns out - you need to dig a little further. We noticed that some applications we run were slowing down our whole server due to memory grants pending. So add memory, of course....

Nope - throw on a trace and look for deadlocks - sure enough, the real culprit was some out of control application code that was causing deadlocks in a cascading manner, which in turn caused the memory grants queue to jump way up and slow SQLServer to a crawl. Fix the deadlocks, fix the memory grants queue.

Anyway, haven't yet looked at 2005 for these issues and possible explanations, but maybe there will be more info....