Tuesday, May 22, 2007

Tuning large SQL-Server databases - Technique Discussion

I have recently written a couple pieces on how to tune very large Microsoft SQL-Server databases (for optimal performance and speed, and minimized size - which, go hand in hand). I mainly work with SQL-Server 2000 or SQL-Server 2005 these days, but the techniques I focus on in my first couple articles will apply to most any version of SQL-Server (or, Oracle, DB2, etc. for that matter), since I focus on minimizing physical storage space as the first line of an offense in speed optimization and tuning.

Here are the links to my initial discussions:
I say "initial" discussion, because I plan to continue this series with discussions about all sorts of MS SQL-Server performance tuning and optimization strategies, including things like proper indexing strategies, stored-procedure (Transact SQL) techniques, and much more. But, all of the other SQL Server optimization techniques are dependent upon getting your physical database storage optimized. Period. (note: data normalization is a key part of optimizing physical storage too - so, get familiar with that concept if you are not already).

If you find these useful, and/or want more detail about any aspect of the discussions, just let me know. After helping all sorts of companies tune their massive SQL relational databases, I know one thing is a fact: most huge databases can benefit from a bit of common-sense tuning. So, start by reading these performance tips, and realize some performance benefits right away.

1 comment:

Mike Eberhart said...

Update (6/5/2007):

From a whitepaper that Microsoft put out about the upcoming features in SQL-Server 2008, comes the following:

"Data compression
Data compression enables you to store your data more effectively and reduce the storage requirements for your data. SQL Server 2008 provides significant performance improvements for large I/O bound workloads such as data warehousing. SQL Server 2008 also provides native support out-of-the box for backup compression."

I find this interesting, and timely. This MAY address part of what I address in my SQL-Tuning discussions, and will certainly be a nice improvement. You should still follow best-practices for data-storage optimization as I have outline, regardless of compression.