Saturday, 21 June 2008

What impact can a poorly designed database have?

I was asked why do ask if you have a risk assessment for each database?

This is one of the standard questions I ask business is do they know what database they have on their systems and are they well maintained?
Because a database can have impact on SQL but just how much?

To answer that question I first have to give you some real world example, Microsoft talks about a performance hit when the database is not designed well or indexed, to translate this in to technical English as Microsoft are very cagey about this subject.

What it really means is the a Itanium 2 server with 4 physical CPU’s 64GB of RAM, for those of you that know you hardware might see where this is going, running SQL 2005 SP2 when loaded with a badly indexed database can go from average CPU load of 20% to 90% on a 3GB database.

Yes you read it right 90% CPU load on a 3GB database, do not underestimate the effect of a database can have on your server. This is why you should have a risk assessment for each database.

So it’s important to look at each database and where it came from as well as having some staging area to test the load before moving it to a production environment.

Surprisingly few companies have a database administrator on their team, most have just the support team and someone that knows how to install SQL.

So when something happened like SQL starts to put a heavy load on the CPU they don’t know where to look. Most if not all of what you need to know is just regular maintained tasks in SQL such as updating statistics, re-index or rebuild broken indexes, shrink the databases, regular transaction log backup
However this can’t be done on all database, some database do need special indexing and maintenance to keep them running.

So make a list of databases that you have and how best to maintain them, and remember to run this jobs at quiet times as re-indexing as an example will impact the performance of the database and depending of the physical resources of the server may impact the server as well and depending on the size of the database can take hours to run.

Don’t forget to have some form of alert if you maintenance job fails, no point in having the job if it doesn't run.

No comments: