Indexing Strategies for OLTP Databases.

March 24, 2012

Let me start by saying that I am not a DBA, I am a BI Developer.  I work with some really bright DBA’s and they can tell you much more about this topic then I can, and probably in great depth, with many terms like PERFMON, DMV and querying a bunch of those Sys tables.  To this point in my career I’ve haven’t had the exposure or need to think about many of the DBA tasks.  I mostly worry about building packages, cubes and repots.  This just isn’t going to cut it.  As I’ve progressed in my career as a consultant, I realized that clients are going to be expecting more and more from me, and rightfully so.  As a junior developer I could lean on the more senior guys to help carry me when things outside of my knowledge base came up, but now I need to become one of those senior guys, and that means <gulp> moving outside of my comfort zone and diving into topics that are in areas other than strictly Business Intelligence.  So for my first foray into the land of the DBA I decided to look into Indexes, and more specifically indexing strategies in OLTP systems, since my only real exposure to them is reading from them when loading data into a data warehouse, and not writing to them. 

Now some of this might seem pretty basic to you but this is how I think about them.  Online Transactional Processing (OLTP) systems are primarily used for data entry. Since the primary function of them is to input data, and usually high volumes of data over an extended period of time, the primary performance concern for an OLTP system is how fast can it input the data. Now that is over simplifying it a bit, because its not like every new insert is coming from a new customer ordering a new item.  Most likely it’s a returning customer ordering an item that has been ordered by other customers before.  So obviously we aren’t going to put duplicate data inside our OLTP system (3NF and all that goodness) so there will have to be some reads performed on the database. This is where the indexes come in.  You want to create indexes that will speed up the reads you are doing on your OLTP system to enable you to do faster inserts updates and deletes to your system.  The problem is that indexes can actually slow down the inserts updates and deletes.  The trick then is to find the “sweet spot” where the indexes speed up the  overall process, not just the reads on your queries. As you can imagine there is no single way to do it, no one “right” answer.  I did however find some tips out there to help fledgling DBAs and other wannabes like me, keep from making a fool of ourselves when the cool kids (DBAs) come behind and look at the indexes we’ve implemented.  Here are some of the Highlights and I’ve included links to them.

Well I hope this has helped someone other than me. One thing I have learned since I started blogging, is that if I can explain it in writing then I have a pretty good grasp of the concept, and I can always fall back on the blog that I wrote as a refresher (convenient!).


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: