Linchi Shea : SQL Server 2008

Is the SELECT permission checked in an UPDATE statement?

Linchi Shea — Mon, 17 Oct 2011 19:44:00 GMT

I was at PASS Summit 2011 in Seattle and had a chat with Jack Richins of Microsoft on the SQL Server audit feature after his presentation. A question came up with regards to whether the SELECT permission should be checked when an UPDATE statement is being...(read more)

Performance impact: Try/Catch blocks in SQLCLR – a follow up

Linchi Shea — Fri, 08 Jul 2011 03:48:00 GMT

My previous post showed a simple test that appears to suggest that you may experience significant performance degradation if multiple users are calling the same SQLCLR function at the same time and they are all catching a lot of exceptions. However, it’s...(read more)

Performance impact: too many try/catch blocks may be harmful in SQLCLR

Linchi Shea — Wed, 06 Jul 2011 04:40:00 GMT

If you have many try/catch blocks in your .NET code and your code execution actually passes through them, you should expect a performance hit. That’s intuitive and no surprise. What is surprising is the extent to which you may experience severe performance...(read more)

SQL Server challenge: show me trace flag 1118 is significant

Linchi Shea — Thu, 01 Oct 2009 15:53:00 GMT

Here is a SQL Server challenge as I have failed so far: Describe a reproducible workload that would see significant throughput improvement when trace flag 1118 is enabled. I have seen a lot of chatters, anecdotes, and stories on the impact of trace flag...(read more)

T-SQL Exercise: Simplest data-loading script to produce worst query performance – June 7th Update

Linchi Shea — Mon, 08 Jun 2009 01:31:00 GMT

All the previously posted results (May 25^th and May 29^th) on this exercise were obtained with query parallelism disabled (i.e. the sp_configure ‘max degree of parallelism’ option was set to 1).

Since the following test query is sensitive to query parallelism, we need to see what impact query parallelism may have.

DBCC DROPCLEANBUFFERS

SELECT COUNT(*) FROM dbo.test;

It turns out that with parallelism the test query exhibited significantly different performance characteristics with the three data sets we have been using as test vehicles than without parallelism. And again, the nature of the storage system proved to be a significant confounding factor. The following table shows the results with query parallelism on both the internal drive (the C: drive) and the drive presented from a departmental level disk array (the E: drive), the same two drives used in the May 25^th update and the May 29^th update.

Drive	Test Run	Elapsed Time (second)
Internal (C:)	Adam’s script – test run 1	90
	Adam’s script – test run 2	88
	Adam’s script – test run 3	87
	Tibor’s script – test run 1	217
	Tibor’s script – test run 2	219
	Tibor’s script – test run 3	226
	Linchi’s script – test run 1	325
	Linchi’s script – test run 2	324
	Linchi’s script – test run 3	324
Disk array (E:)	Adam’s script – test run 1	74
	Adam’s script – test run 2	75
	Adam’s script – test run 3	72
	Tibor’s script – test run 1	93
	Tibor’s script – test run 2	97
	Tibor’s script – test run 3	89
	Linchi’s script – test run 1	51
	Linchi’s script – test run 2	51
	Linchi’s script – test run 3	51

Note the dramatic change in the relative impact of the data sets on the test query when the storage performance and query parallelism were added to the mix. In particular, note how the test query was able to perform much faster on the faster drive (drive E:) with some data sets.

I just want to present the data points in this post, and will dive in a bit in the next follow up.

Performance impact: thread mode vs. fiber mode

Linchi Shea — Mon, 04 May 2009 19:56:00 GMT

SQL Server can run in one of two modes: thread mode or fiber mode. By default, SQL Server runs in thread mode in which a SQL Server worker is associated with a Windows thread throughout all phases of its execution. This can be changed with the sp_configure option ‘Lightweight Pooling’. When Lightweight Pooling is turned on, SQL Server runs in fiber mode in which a SQL Server worker is associated with a user-mode Windows construct called fiber. Switching among fibers is handled in the user mode with the objective of reducing the cost of calling into the kernel for context switches.

Whenever fiber mode is discussed, Microsoft has always piled a huge load of ominous warnings, generally related to the potential stability issues that may come up with fiber mode. And because of these warnings, fiber mode is rarely, if ever, used in real world production environments. Staying away from fiber mode is of course the right thing to do because many of the ‘external SQL Server components’ that may not be fiber-mode friendly are usually essential in a real production environment.

If you run a bare minimum SQL Server instance (e.g. no XML, no mail, no linked servers), can you expect performance gain from fiber mode? Generally speaking, you may not see any performance gain. But there are cases you can see performance gains.

I was curious whether I could see any performance gain at all on a low-end server such as a four-core HP ProLiant DL365 G1 with 4GB of RAM. Note that this is a rather old and obsolete server model.

It turns out that under certain workloads, fiber mode can give you a huge performance boost. For my tests, I populated a TPC-C like database with about 9GB of data, and ran read-only workloads against the database (with the two read-only transactions in TPC-C in a 50/50 transaction mix). I configured each client to wait for 20 milliseconds before submitting the next query. The following chart shows the difference in transaction throughput at various load levels (in terms of the number of simulated concurrent users running queries against the server) between thread mode and fiber mode. The data points were obtained on a SQL Server 2008 instance (Enterprise x64 Edition and the build level was 10.0.1600).

If we look at the cases for 400 and 600 users for example, the performance gain from thread mode to fiber mode was ~ 40% (with the thread-mode transaction throughput as the baseline). This was a huge gain. I ran the tests in various random orders and repeated the tests at various random times. The results were consistent and reproducible.

I was surprised to see such a difference myself for two reasons. One is that I didn’t see much of a performance difference between thread mode and fiber mode when I ran the same workload in the same test environment without the 20ms wait time. I observed only about 5% consistent but marginal gain, switching from thread mode to fiber mode when the next query was submitted immediately after the previous one was finished. I can’t explain why the wait time made the difference.

The other reason I was surprised is that I had never seen any performance gain from enabling lightweight pooling or fiber mode before, though I had never tried this specific workload and had never tried this on SQL Server 2008.

I’m not sure if there is any practical value in this post because stability concerns will and should always trump any potential performance gain, especially when that performance gain is rather elusive. But at least it’s good to report that I have seen some real performance gain with fiber mode instead of just hearing somebody else talking about it.

Performance impact: a large number of virtual log files – Part II

Linchi Shea — Thu, 12 Feb 2009 05:40:00 GMT

In my previous post on the performance impact of having a large number of virtual log files (VLFs) in a transaction log, I showed that a large number of VLFs could be very bad for SQL Server 2008 performance. The test workloads were large batch delete, update, and insert. In other words, they were single monolithic transactions that generated a large number of transaction records. Intuitively, these large transactions would cause SQL Server to cross many VLF boundaries and incur the penalty of crossing these boundaries.

The question then became: how about workloads that submit small transactions such as common OLTP workloads?

As it turns out, a typical OLTP workload may not experience any performance degradation at all, even when the number of VLFs is into the 20,000 range.

The supporting evidence lies in the test results of a TPC-C like benchmark that I ran against the same test databases dbVLF_small and dbVLF_large, whose setup was described in the previous post. These two test databases were configured identically except that one had 16 VLFs in its transaction log and the other had 20,000 VLFs in its transaction log. The test workload used the standard TPC-C read-write transaction mix, which means that the workload was relatively write heavy.

The test environment was the same as described in the previous post. The test database was sized for 100 TPC-C warehouses, which translates into ~9GB for all the data and indexes.

But I’ll skip any details regarding the test setup and test data, and go straight to present the test results, which are highlighted in the following chart:

For this OLTP workload, the number of VLFs in the transaction log did not make any significant difference. This, I guess, is good news. Did I hear a sigh of relief?

Performance impact: a large number of virtual log files – Part I

Linchi Shea — Mon, 09 Feb 2009 17:13:00 GMT

It is generally known that having a large number of virtual log files (VLFs) in a database transaction log is undesirable. A blog post by the Microsoft support team in Stockholm showed that a large number of virtual log files could seriously lengthen the database recovery time. Tony Rogerson also reported that lots of virtual log files were bad for triggers and log backups on SQL Server 2000.

This blog post explores two questions:

Is a large number of VLFs in a transaction log still a significant performance factor in SQL Server 2008?
Does a large number of VLFs have an adversely impact on application-related operations such as INSERT, UPDATE, and DELETE?

As I’ll show in the rest of this post, the answers to both questions are yes. Let me first describe the test, and then present the test results.

The test databases

I created two databases: dbVLF_small and dbVLF_large on the same SQL Server 2008 instance (Enterprise x64 Edition and the build number was 10.0.1600). The host was an HP DL585 with four single core 2.2GHz Opteron processors and 32GB of RAM (28GB of which was allocated to the buffer pool). The following table highlights the key properties of these two test databases:

Property	Property value
	dbVLF_small	dbVLF_large
Data file size	20GB	20GB
Data file location	E: drive	E: drive
Log file size	10GB	10GB
Log file location	F: drive	F: drive
Recovery mode	Full	Full
Number of VLFs	16	20,000

As you see, the only masterial difference between these two databases was the number of virtual log files. The dbVLF_small database had 16 VLFs (a small number of VLFs), and the dbVLF_large database had 20,000 VLFs (a large number of VLFs). It’s probably a bit extreme for a database to have 20,000 virtual log files. But it’s not completely unreasonable if you let your database log file to grow in small increments. Note that my intention is to highlight the impact in case if your database does end up with a large number of VLFs.

The test table

An identical table, called customer, was created in both databases. The DDL for the customer table is as follows:

CREATE TABLE customer

(

c_id int,

c_d_id tinyint,

c_w_id int,

c_first char(16),

c_middle char(2),

c_last char(16),

c_street_1 char(20),

c_street_2 char(20),

c_city char(20),

c_state char(2),

c_zip char(9),

c_phone char(16),

c_since datetime,

c_credit char(2),

c_credit_lim numeric(12,2),

c_discount numeric(4,4),

c_balance numeric(12,2),

c_ytd_payment numeric(12,2),

c_payment_cnt smallint,

c_delivery_cnt smallint,

c_data char(500)

)

CREATE UNIQUE CLUSTERED INDEX ci_customer on customer(c_w_id, c_d_id, c_id)

This was the customer table used in the TPC-C benchmark.

The test data

3,000,000 rows—with data as specified for the TPC-C customer table— were bulk copied into the customer table in both databases. The index was then rebuilt, and sp_spaceused showed the table size to be about 2GB.

The tests

Three simple tests were run against the customer table in each database. The common characteristics of these three tests were that they were a single large transaction.

The insert test

SELECT * INTO customer_tmp
FROM customer
WHERE 1=2;
go
INSERT customer_tmp
SELECT * FROM customer;

The update test

UPDATE customer
SET c_data = lower(c_data);

The delete test

DELETE FROM customer;

The test results

The following table summarizes the test results:

Test	Elapsed time (sec) in database: dbVLF_small	Elapsed time (sec) in database: dbVLF_large
The insert test	281	1069
The update test	174	1554
The delete test	193	936

The following chart gives a better visual representation of the same results:

In all the three cases, it was significantly (should I say dramatically) slower to perform these common SQL operations when the database had 20,000 VLFs than when the database had 16 VLFs. The insert was about four times slower. The update was about eight times slower, and the delete was about five times slower.

So the test results confirm that it is definitely a significant performance issue in SQL Server 2008 to have a large number of virtual log files in a transaction log. Moreover, the test results show that the problem can be felt by common SQL operations such as insert, update, and delete in large batch processes.

What does this all mean? First, pay attention to the often-repeated best practice advice: do not grow your database log files in small increments (because that is often how you introduce a large number of VLFs into your transaction log). For instance, if you know you’ll need 10GB for your log, it’s best to allocate 10GB in one fell swoop. Secondly, if you do find a large number of VLFs in your transaction log, it may be worth the maintenance effort to reduce the number. You can reduce the number of VLFs by first shrinking the log file to a minimum size, and then allocating the required space in one ALTER DATABASE statement. (SQL Server MVP Tibor Karaszi has a thorough discussion on shrinking log files at his site)

------

By the way, the 20,000 virtual log files were generated as follows. First, create the database in the full recovery mode with a 512KB initial log file size and 512KB file growth increment. Then, backup the database, and create a dummy table with a single char(8000) column. Finally, inserting data into this table in a loop until the log file size reaches 5000MB. This is essentially the same procedure used by the Microsoft support team in Stockholm in their post.

To prepare for the insert, update, and delete tests, backup the log, and expand the log file to 10GB.

SQL Server 2008 Data Compression: Impact of Data Distribution

Linchi Shea — Fri, 20 Jun 2008 04:59:00 GMT

In this post, I continue to explore the implications of SQL Server 2008 data compression. It is particularly worth highlighting the fact that SQL Server 2008 data compression is performed at the page level instead of the table level. In other words, when SQL Server 2008 goes about compressing data, it does it one page at a time, and its compression algorithm considers only the data on that page.

The page level approach to data compression has important implications, one of which is that what kind of compression ratio you get on a table depends on how the data in the table is distributed. That is, it is possible that with the exactly same set of data in the table, you can get radically different compression ratios if data is shuffled around in the table.

To drive this point home, let me show you an extreme example with the following table:

create table customer (

cid int identity,

filler char(100) NOT NULL,

)

With this table, I tested two scenarios:

Scenario 1: Clustered index was created on column cid:

create clustered index cus_cx on customer(cid);

Scenario 2: Clustered index was created on column filler

create clustered index cus_cx on customer(filler);

Again, with the exactly same set of data in the table, in each scenario I compressed the data, and compared the table size before the compression and after the compression. The data compression was done with ALTER TABLE statement as follows:

ALTER TABLE customer REBUILD

WITH (DATA_COMPRESSION = PAGE, MAXDOP=8);

The following chart shows the table size before and after compression for each test scenario:

Clearly, when the table was clustered on the column cid, the data compression ratio was very low (slightly greater than 1), whereas when the table was clustered on the column filler, the data compression ratio was very good at ~10 (the compressed table was about 10 times smaller).

Why?

First we need to know exactly what data was in the table, i.e. we need to know the characteristics of the data. Well, the data was populated with the following script:

declare @i int

set @i = 1

begin tran

while @i <= 8000000

begin

insert customer(filler)

select left(cast(@i % 80 as varchar(8)) + '00000000', 7) + replicate('a', 92)

if (@i % 10000) = 0

begin

commit tran

begin tran

end

select @i = @i + 1

end

commit tran

With this script, the first 80 rows would have the cid values going from 1 through 80, and the filler column would have 80 distinct values. With the cid values going from 81 through 160, the filler column would have another 80 distinct values, but as a set they were identical to the first 80 values. This pattern repeated every 80 rows because of the expression @i % 80 in the script.

Note that the table row was sized such that each page would contain ~80 rows. This means that if there was a clustered index on column cid, every page would have 80 distinct values in the filler column. When SQL Server tried to compress such a page, there wasn’t much to compress. Now, when the clustered index was created on the filler column instead, every page was filled with duplicate values in the filler column, making the page extremely conducive to data compression.

Again, this post is to help highlight the behavior of SQL Server 2008 data compression. The behavior presented here is neither good nor bad. It’s just a natural consequence of the page-level algorithm. Oracle’s data compression also adopts a page/block level algorithm, and therefore would see the same behavior.

Note that if all the values are distinct, no matter how you shuffle the data you won't improve its compression ratio.

SQL Server 2008 Page Compression: Performance impact on table scans

Linchi Shea — Sat, 17 May 2008 01:28:00 GMT

My previous post shows that data compression may not come for free, although hopefully by the RTM time the adverse performance impact on inserts will have been reduced significantly. In this post, I want to show you that data compression can enhance read performance. Intuitively, data compression may reduce the number of pages SQL Server must read into memory from disk, and the savings in terms of the number of pages can be significant, and therefore there may be a positive impact on reads.

To continue our tradition of letting data points do the talking, I ran the following simple script with the customer table when (1) it was page compressed and when (2) it was not page compressed. This was the same customer table I described in an earlier post on page compression and multiple processors.

dbcc dropcleanbuffers;
declare @dt datetime;
select @dt = getdate();

select avg(c_balance) from customer;

select datediff(second, @dt, getdate());
go

Note that there was no index on the c_balance column, and I verified that the actual execution plan was a table scan (well a clustered index scan). DBCC DROPCLEANBUFFER was included to ensure pages were read from disk, not already cached. The following chart summarizes the results.

Wow! The elapsed time difference between the compressed data and uncompressed data was huge. It took 52 seconds to scan the table when it's not compressed, but it took only 3 seconds when the table was compressed. Note that when uncompressed, the table had 2264 pages, and when compressed it had 534 pages.

Two more points to note: First, I did not try to explain why the improvement in table scan performance seemed to be disproportional to the reduction in the table size as the result of page compression. But I did run these tests many times to make sure I was not going to give you incorrect data. Second, I would caution you not to get too hung up on these specific numbers. As they often say, your mileage may vary. Instead, focus on the big picture impression that page compression may give your reporting queries a performance boost. In a real app, life is not so simple, and you would have to strike a balance between better reporting queries and potentially longer batch/data feed processing time when you consider page compression.

SQL Server 2008 Page Compression: Performance impact on inserts

Linchi Shea — Tue, 13 May 2008 02:49:00 GMT

Before I begin, let me be very clear that the results I report in this post were obtained with SQL Server 2008 CTP6. Microsoft is still working on improving the performance of data compression. And in particular, the performance of inserting into a compressed table is expected to be significantly enhanced in SQL Server 2008 RTM.

In my previous post, I have shown that data compression can result in significant savings in storage space. In this post, I want to highlight the fact that not everything comes for free. Let's take a look at inserting into a B-tree table, i.e. a table with a clustered index. What is the performance impact of page compression on inserts?

Note that you must use TABLOCK hint for pages to receive page-level compression when the pages of a heap table are being populated with the INSERT statement. So, heap tables are much less interesting, and I'll hence focus only on B-tree tables.

In my tests, I ran the following script:

truncate table customer2;
-- enable page compression
alter table customer2 rebuild with (data_compression=page);
go
declare @dt datetime;
select @dt = getdate();
insert customer2 select * from customer;
select 'insert into customer2 with compression', datediff(second, @dt, getdate());
go
truncate table customer2;
-- disable compression
alter table customer2 rebuild with (data_compression=none);
go
declare @dt datetime;
select @dt = getdate();
insert customer2 select * from customer;
select 'insert into customer2 without compression', datediff(second, @dt, getdate());
go

The script measures the difference in the elapsed time of inserting 3,000,000 rows into (1) an empty table when page compression is enabled, and (2) an empty table when no data compression is enabled. The script was run multiple times to ensure that the elapsed times recorded were consistent. The following chart shows the results:

Clearly, the impact of page compression on inserts was rather significant in the tests. Without data compression, inserting 3,000,000 rows took ~80 seconds, whereas it took 217 seconds when page compression was enabled on the target B-tree table. No free lunch!

Again, these numbers were obtained with SQL Server 2008 CTP6, a work in progress build. I'll re-run the test once SQL Server 2008 RTM is released.

Now that I have shown that there can be performance penalty in using data compression, next time I'll show that data compression can also help improve your performance.

SQL Server 2008 Page Compression: Compression ratios with real-world databases

Linchi Shea — Sun, 11 May 2008 23:20:00 GMT

In my previous post on data compression, I looked at how rebuilding a table with page compression works with multiple processors via the MAXDOP option. In this post, I'll focus on what compression ratios I have seen in the real-world databases. Now, if you understand how SQL Server 2008 data compression works, you know that what compression ratio you may get really depends on to what extent SQL Server can find duplicate values on the data/index pages. Consequently, one real-world database can have a very high compression ratio while another may have a very low compression ratio: there is no typical compression ratio to expect.

That said, it is still interesting to report what compression ratios I have seen with real customer databases instead of 'cooked' data. Before I give the results, let me define what I mean by compression ratio:

Compression Ratio = Uncompressed Size / Compressed Size

To see what kind of compression ratios I may run into with real-world databases, I randomly selected 14 customer databases. IN this blog post, these databases have been renamed to DB1, DB2, ..., DB13, and DB14 to shield their identities. For each database, the following steps were performed:

Record the data size of sp_spaceused for the database. This is the uncompressed data size.
For each user table in the database, rebuild it with page compression. This was done by generating and executing a script that contains the following ALTER TABLE statement for each table:
alter table <the_table> rebuild with (data_compression=page, maxdop=8);
Record the data size of sp_spaceused for the database. This is the compressed data size.
For each user table in the database, rebuild the table with no compression, which effectively decompresses the table. This was done by generating and executing a script that contains the following ALTER TABLE statement for each table:
alter table <the_table> rebuild with (data_compression=none, maxdop=8);
Record the data size of sp_spaceused for the database. This is the decompressed data size.

The data size obtained in Step 3 was used as database compressed size, and the data size obtained in Step 5 was used as the database uncompressed size. The data size obtained in Step 1 was not used because it may be polluted by fragmentation. If we used the data size from Step 1, we could potentially be using a size that was inflated by fragmentation, resulting in an incorrectly higher compression ratio.

The following two charts summarize the results:

The top chart includes the three database space measures mentioned above. As you can see, fragmentation was not a significant factor for these databases since the uncompressed size and decompressed size were quite close for each database. As it turned out, these databases were regularly maintained, including rebuilding clustered indexes, it's no surprise that there wasn't any serious fragmentation.

To highlight the compressed ratios for these databases, the bottom chart plots the compression ratios. So for these databases, the compression ratios--obtained using SQL Server 2008 CTP6 page compression--vary between 1.7 and 5.6.

The space savings were substantial with these databases. When decompressed, the total space usage was ~261GB. Compared that to the total space usage of ~88GB when compressed. In other words, SQL Server 2008 page compression would save us 173GB in storage space.

SQL Server 2008 Page Compression: Using multiple processors

Linchi Shea — Mon, 05 May 2008 13:59:00 GMT

SQL Server 2008 has introduced a long sought after feature -- Data Compression. This is a great feature, and I have no doubt it'll be widely used. The key compression method is called page compression, which uses the following three techniques to reduce the space taken up by duplicates on a page:

Row compression. This technique changes the storage formats of many fixed-length datatypes (e.g. char, int, money, binary, datetime, and so on) so that they occupy only the required number of bytes plus a small overhead.
Prefix compression. This technique finds duplicate prefixes on a page for each column , and replaces each duplicate with a small reference number.
Dictionary compression. This technique finds duplicate values on a page, collects them into a dictionary stored after the page header but before the data rows, and replaces the duplicate values with their corresponding offsets in the dictionary.

You can read more about SQL Server 2008 data compression in SQL2008 CTP6 Books Online.

In this post, I'll focus on a very specific question: How does the number of processors impact rebuilding a table with page compression? Note that one way to enable page compression on a table is to rebuild it with the option data_compression set to page. The following is an example:

ALTER TABLE customer REBUILD WITH (DATA_COMPRESSION=PAGE);

Since compressing a table is a CPU intensive operation, one naturally would expect that using multiple processors should help reduce the elapsed time of the above command. How do you instruct SQL Server 2008 to take advantage of multiple processors? There exists another rebuild option called MAXDOP, which you can use to inform SQL Server of the max number of processors you want the rebuild to use. The following is an example of setting the MAXDOP option to 8:

ALTER TABLE customer REBUILD WITH (DATA_COMPRESSION=PAGE, MAXDOP=8);

To find out the impact of multiple processors (or the impact of setting MAXDOP to different values) on the elapsed time of the above command, I ran a series of tests with the command by setting MAXDOP to 0, 1, 2, 3, 4, 5, 6, 7, and 8 in turn. Before I report the test results, I should give you some information about the test setup:

The TPC-C customer table was used to produce all the test results reported here. However, the original customer table in the TPC-C benchmark was not very compressible because its data didn't include many duplicates. For test purposes, I 'cooked' the table to make it more compressible. After I cooked it, the data size of the table was ~2,181,864KB without data compression, and was ~439,672KB with page compression.
The test server had 8 processors (with four dual-core sockets), and 8GB physical memory.
The data point for each MAXDOP setting was collected with the following T-SQL script. While only MAXDOP = 2 is shown below, in the test, this script was repeated for each of the MAXDOP values 0 through 8.

exec sp_spaceused 'customer';
go
DBCC DROPCLEANBUFFERS
GO
declare @dt datetime;
select @dt = getdate();
alter table customer rebuild with (data_compression=page, maxdop=2);
select 'maxdop = 2', datediff(second, @dt, getdate());
go
exec sp_spaceused 'customer';
go

The following chart summarizes how the MAXDOP setting affects the elapsed time of rebuilding the customer table with page compression.

A number of observations should be noted:

SQL Server 2008 page compression can effectively take advantage of multiple processors to speed up rebuilding a table. Using more processors generally leads to shorter elapsed time.
The diminishing return on the number of processors is clear from the chart. In this particular test environment, after 5 or 6 processors, adding another processor to rebuilding the table with page compression only reduced the elapsed time only marginally. The largest improvement was obtained when the number of processors was increased from one to two.
When the MAXDOP option was set to 0, its effect was the same as when it's set to 8, which happened to be the total number of processors on the server.
For this test setup, changing the buffer pool size from 6GB to 2GB did not materially alter the test results.
With MAXDOP=8, all eight processors were seen to be used 100%--as reported by the perfmon counter Processor\% Processor Time--during the table rebuild.