Free Software, Source Code, and Best-Practice Documentation Index from your
Cleveland Software, Database, Web Design, Consulting, and SQL / Delphi Experts

Tuning Very Large SQL-Server Databases - Part 3
by Mike Eberhart (3/10/2009)

In Part 1 of SQL-Server Large Database Performance Optimization and Part 2 of Large SQL-Server Database Performance Optimization, I discussed the need to Reduce Physical Storage Requirements and use proper data-normalization as the first foundation-step in achieving optimal SQL-Server processing Speed. Part 2 also explored the reasons WHY a reduction in physical storage requirements was so important — whether the physical data-storage is hard drive storage, RAM device storage or SSD (Solid State Disks), RAID arrays, SAN / NAS networked storage... the need for speed implies a need to reduce storage requirements regardless.

Note: the database performance tuning concepts discussed in this series thus far, apply nearly universally to all mainstream RDBMS (Relational Database Management Systems) products — not just Microsoft SQL-Server 2008, 2005, or 2000 — but also to the likes of MySQL, PostgreSQL, Oracle, IBM DB/2 (DB2), and others. And some of the concepts discussed herein (clustered indexes and such) will also apply to many database solutions too, since this concept is not unique to SQL-Server.

Now I am going to discuss some specific database-tuning strategies with respect to indexes in SQL Server relational databases (SQL Server 2000, SQL Server 2005, SQL Server 2008, etc)..

Clustered vs. non-Clustered Indexes

One of the most important decisions to make when defining an index-structure throughout your SQL-Server databases is how to best make use of Clustered and/or non-Clustered indexes.

Knowing how, to best use, and when to use, a clustered index can make a serious difference in performance tuning large SQL-Server databases. Basically, if a key-column is ALWAYS increasing (in sort order) as data is added, and that column is relatively "narrow" (in bytes), make it CLUSTERED and you'll save a LOT space (since, the data for the Clustered version of an index is already PART of the same file). This is why applying clustered indexing to pre-ordered data is a great fit, and why applying clustered indexes to monotonically increasing values (like those in IDENTITY columns) makes a lot of sense - the data is physically ordered by the values on the table, and adding values to the end of a table can be done quite efficiently in most cases.

Certainly you can still use clustered indexed for data that is not pre-sorted or otherwise ordered already, but depending on the situation, this can lead to some significant inefficiencies as SQL-Server will need to physically re-order the table's data as values are added in order to arrange the data in the table by the clustered-index column(s) data values -- which can make disks thrash like crazy. Keep this in mind, especially in high-volume and large database scenarios.

Here are some further points to consider regarding Clustered vs. non-Clustered Indexes and performance in SQL-Server :

• As mentioned before, less data (actual bytes) to move, regardless of what speed your system can move this data (whether in RAM or to and from disk), it will still perform the same operation in less time — and, there is a good chance the elapsed processing time will decrease by a percentage greater than the percentage of storage-space you have saved...
• ...so, choose an indexing strategy that prevents unnecessary data-movement operations, and/or minimized data-movement. This will involve understanding what percentage of time your application spends performing INSERTS as opposed to SELECTS, UPDATES, and DELETES. Why does this matter? Well...
• UPDATES to Clustered-Index Columns can be painfully slow operations... (perhaps the reasons are obvious; if not, this article should be expanded upon in coming months)

Let me discuss some real-world time-savings and performance comparisons I have witnessed before and after optimizing database indexing strategies:

Before Tuning:

I was confronted with a rather substantial SQL-Server 2005 database at a client site that had an Order-Lines table with millions of rows of data residing in it. The client had a custom application that performed inserts, updates, and deletions on this table on a regular basis, and with rather high volume of transactions. And, though the custom application's GUI was still responding within the threshold of "acceptable response time" at a few seconds (as many as 10-15 seconds under heaviest loads), the delay was bothersome (and costly) as hourly workers had to basically slow down and wait for the application to "catch up to them".


Tuning: A quick examination of the database stored procedures that were used to perform Order-Line additions, updates, and deletes, revealed which database tables were involved in the order-processing maintenance business process (and related application GUI forms) and what tables and columns and indexes these procedures interacted with. Further digging then revealed that within the primary Order-Lines table, there was an already existing clustered index on the table that had been applied to a group of columns that uniquely identified each OrderLine row.

I also noticed that the table in question had another OrderLineID column (a unique value, IDENTITY value, monotonically increasing auto-increment value) on it already. Perhaps it was added at a later point in the software development and database design process - who knows - but, this jumped out as an easy, immediate, on-the-spot fix opportunity that would deliver serious performance improvements. I simply altered the database table index definitions to make the table use the IDENTITY value as the Clustered index, and the multi-column-indentifying-value column-group a non-clustered index (i.e., the new post-tuning index setup was using a Cluster on Identity values vs. a cluster on a combination of other columns' values.


After Tuning: the results were dramatic and GUI operation responsiveness became nearly instant (as compared to multi-second response times). I also had to verify (as you always should do) that my changes did not impact the performance of OTHER application functions - like reporting operations, ad-hoc queries, web-application access to this same data, and so on - was not negatively impacted. This testing proved out OK, as no regressions were encountered; though, further testing also led me to make a few other index-tuning changes that delivered additional speed gains and performance improvements to the various portions of the database applications. This is just one simple case of looking for some "low hanging fruit" to pick when performance tuning with indexing strategy considerations in focus.

More examples to come... in the mean time, I hope you have found this a useful series of database tuning and optimization information. Feel free to contact us with suggestions, errata, etc. Enjoy!