2005 Table Partition - Optimization Test

On the job we decided to implement table partitioning with SQL Server 2005. I wanted to get an understanding of the increases and decreases that we would see with a partitioned table. Below is documentation of my tests.

Tables Involved
Step 1:
Create Table called 'nonPartitioned'
No Primary Key
16 varchar(50) fields
1 bit field called 'activeFlag'

Step 2:
Populated table with random text
Created 250,000 rows

Step 3:
Mark a sizeable portion of the table as active.
I took 50,000 rows and marked them as active.
I took rows that cotained an 'a' in 'fieldG'

Step 4:
This table is partitioned on the activeFlag, 1 or 0
Create Partition Function
Create Partition Schema
Create Partition Table

Step 5:
Copy Data from 'nonPartitioned' to 'partitioned'

Step 6:
Create table called 'smallTable'
This holds only active records
Populate with active records only

Step 7:
Create table 'selected'
This will hold data that we select from either partition

Some important metrics to note at this point:

52,689 where ActiveFlag =1
197,311 where ActiveFlag =0

35.51 MB - Partitioned Table Size
36.17 MB - Non Partitioned Table Size
7.45 MB - Small Table

First Test - Selecting from Small Partition
The first test is to select data and see if we have a speed increase.

• Truncate the 'selected' table
• Perform the following 1,000 times
• Select 10 random records from the nonPartitioned Table where activeFlag = 1
  
• Insert it into the selected table

4:53 (4 min, 53 seconds) - NonPartitioned Table
3:39 (3 min, 39 seconds) - Partitioned Table
3:55 (3 min, 55 seconds) - Small Table

Second Test - Selecting from Large Partition
The first test is to select data and see if we have a speed increase.

• Truncate the 'selected' table
• Perform the following 1,000 times
• Select 10 random records from the nonPartitioned Table where activeFlag = 0
  
• Insert it into the selected table

13:23 - Partitioned Table
12:59 - NonPartitioned Table
00:00 - Small Table (no select available of large set)

The following code was used for those tests:

truncate table selected
declare @count int
set @count = 0
while @count < activeflag="0" count="@count+1">
Third Test - Updating 1,000 rows
I now want to test the update statements to these partitions. I used the following logic

• Truncate the 'selected' table
• Perform the following 1,000 times
• Select 1 random records from the nonPartitioned Table where activeFlag = 1
• Update one field (fieldB) for this record

5:03 - Partitioned Table
9:56 - NonPartitioned Table
4:57 - Small Table

Third Test - Updating 5,000 rows
I now want to test the update statements to these partitions. I used the following logic

• Truncate the 'selected' table
• Perform the following 5,000 times
• Select 1 random records from the nonPartitioned Table where activeFlag = 1
• Update one field (fieldB) for this record

33:17 - Partitioned Table
49:25 - NonPartitioned Table
24:27 - Small Table

Conclusion

If you must have all the data in one table, then partitioning the table will greatly increase your performance in all areas.

However if you can pull the data out of the table into a smaller table, this will give you the greatest performance during transactional processing.

Caveat

This is my own home grown test, as always perform your own test before making production relavant decisions. Your data may give you different results.