articles >> performance tuning >> Save Space To Boost SQL Server Performance ...

Save Space To Boost SQL Server Performance

By : Neil Boyle Oct 31, 2002

It’s easy to become complacent about saving disk space when hard disk sizes keep growing and disk prices keep on dropping, but saving a few bytes here and there can help improve SQL Server's performance considerably.

If you have ever looked at an Execution Plan for a SQL Server query (and if you haven’t, you should!) you will see that SQL Server produces an estimated "cost" for executing queries. This cost is not in money terms, obviously, but in terms of computer resources required to run the query. The primary component of this costing is disk I/O, so it stands to reason that if we can reduce disk I/O, then we reduce the cost of executing a query, and therefore increase performance. In this article we will look at a few ways of doing this.

 

The Basics

Here is an fairly extreme example. I created two tables in SQL Server 7 and loaded each with 10,000 4-byte strings.

CREATE TABLE t1 (c1 CHAR(255) NOT NULL)

CREATE TABLE t2 (c1 VARCHAR(255) NULL)

One table was created using the VARCHAR(255) column type, and the other using the CHAR(255) type. Now, the CHAR(255) type uses a fixed length to store data, so if the string you store is less than 255 characters long, the remaining space is wasted. This is not true with the VARCHAR data type.

Running DBCC SHOWCONTIG against the tables showed that the table with fixed length columns took up 334 pages (a page is 8 Kilobytes in SQL 7 or SQL 2000) of storage space to store 10,000 rows. The version using VARCHAR took up only 23 pages to store the same data. The reduced disk space means that any retrieval operation, and particularly simple table-scanning operations such as SELECT COUNT(1) FROM, run much quicker against the smaller table.

Although this is an extreme example, even small savings can make a big difference. In the next example I re-created the two tables as follows, and loaded 10,000 rows into each. (To keep the examples simple, I am using one column in each table, but the same applies to tables with any number of columns, it’s the total row length that matters)

CREATE TABLE t1 (c1 CHAR(4000))

CREATE TABLE t2 (c1 CHAR(4040))

Table t1, when loaded with 10,000 rows, took up 5,000 pages of disk space. The row length for table t2 is precisely one percent longer that t1, so you might expect table t2 to take up only one percent more space, but it actually takes up double the number of pages that t1 does. The reason for this is that SQL Server 7 can store up to 8060 bytes of data on one page, so there is plenty of room for two rows from table t1 on each page. However, when we extend the row length to 4040, then only one row will fit on a page, hence we end up using twice as many pages. SQL Server insists on fitting a whole row on a single page, and will never try to save space by splitting a row across two pages.

Again, that was an extreme example, but as a general rule:

• The shorter the row length, the more rows you will fit on a page, and the smaller your table will be.
• The effect is even more noticeable in SQL Server 6.5, where the maximum row length is slightly over 2000 bytes.
• The example above also applies equally to SQL Server 2000.