Linchi Shea

SQL Server 2008 Page Compression: Performance impact on table scans

2008-05-17T01:28:00Z

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

2008-05-13T02:49:00Z

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

2008-05-11T23:20:00Z

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

2008-05-05T13:59:00Z

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.

Performance impact of controller cache: SQL Server read only workloads

2008-04-01T03:45:00Z

In my previous post, I looked at how a typical OLTP workload may be affected by various controller cache configurations. And the conclusion was that giving too much cache (say all 512MB) to reads hurt the OLTP performance. The primary reason was that the writes from the OLTP workloads were starved of cache. Now, let's take a look at how the controller cache configurations may impact on the performance of read queries whose execution plans are dominated by index seeks (both clustered and nonclustered). The TPC-C read-only transactions happen to be queries dominated by index seeks.

As you'll see below, the index-seek dominated reads are not sensitive to controller cache configurations, at least for the test configurations described below.

The test configuration was exactly the same as what was described in the previous post except that the transaction mix was changed to the following to include only the two read-only transactions (i.e. Order Status and Stock Level).

New Order: 0%
Payment: 0%
Order Status: 50%
Delivery: 0%
Stock Level: 50%

To measure the transaction throughput, I had to use a different metric because the New Order transaction was no longer executed in the tests. Instead, the transaction throughput was measured as the number of the Stock Level transaction executed per second.

Again, the three test scenarios differed only in how the controller cache was configured:

0 MB was allocated to reads and 512MB was allocated to writes
128MB was allocated to reads and 384MB was allocated to writes
512MB was allocated to reads and 0 MB was allocated to writes

The test results are shown in the following chart:

The chart shows little difference in the transaction throughput among the three test scenarios. You could argue that the scenario where 128MB was allocated to read performed the best. But the transaction throughput difference was not significant enough to justify that conclusion, although the pattern was consistent throughout the repeated test runs,

How was the I/O subsystem exercised by the read-only workloads? Let me again focus on the tests with 100 users. From a disk I/O perspective, the read-only workloads during the steady state can be characterized as follows:

On the F drive with the data file, the key LoggicalDisk perfmon counter values were as follows:

* Avg. Disk Bytes/Read = ~8366 bytes
* Avg. Disk sec/Read = 0.347
* Current Disk Queue Length = 122
* Disk Reads/sec = 446
The key Buffer Manager counter values were as follows:

* Page life expectancy = ~60
* Page reads/sec = 478
* Readahead pages/sec = 236
* Target pages = 193176
* Total pages = 193176
The Wait Statistics were heavily concentrated on Page IO latch waits.

Performance impact of controller cache: SQL Server OLTP workloads

2008-03-30T00:39:00Z

Through empirical data presented in my two previous posts on the performance impact of controller cache configurations on sequential disk I/O workloads and large SQL workloads, we observed that allocating controller cache to writes would result in better performance, at least with the described workloads. Let me now turn attention to SQL Server OLTP workloads, and check how the controller cache configurations may impact the transaction throughput of an archetypal OLTP workload--TPC-C in its standard transaction mix.

As you'll see below, the same observation still holds.

The best way to understand TPC-C is to read its specifications at http://www.tpc.org/. But briefly, it models after the data processing of a business that sells items to customers in multiple districts. The customers may place orders for items, and the business maintains items in stock in multiple warehouses. The database keeps track of these business activities. So you'll find a customer table, an order table, an item table, a stock table, and so on. And like any real business, there are certain constraints among these tables so that you can't just scale one table without also scaling some other tables. In fact, the number of rows in most tables--thus the size of the database--is ultimately determined by the number of warehouses. So, if you want to test a larger database, you specify a larger number of warehouses.

For my tests, I sized the test to 100 warehouses that resulted in about 9GB of allocated storage space. And to ensure that the disk I/Os would be heavily exercised, I set the SQL Server buffer pool to 2GB.

The data file was placed on the F drive and the log file was placed on the G drive. F and G were two individual 72GB 10,000rmp SAS disk drives that shared the same HP Smart Array P400 controller with 512MB for cache.

The stored procedures executed against these tables model five typical business activities: (1) placing a new order, (2) making a payment, (3) checking the order status, (4) making a delivery (i.e. fulfilling an order), and (5) checking the stock level. Among these stored procedures, order status and stock level are read only; the others are read/write. The transaction throughput metric is the number of new orders processed.

The standard transaction mix is as follows:

New Order: 45%

Payment: 43%

Order Status: 4%

Delivery: 4%

Stock Level: 4%

This simply means that, for instance, 45% of the time the client program submits the new order transaction (i.e. calling the new order stored procedure). Strictly speaking, what I ran can't be called TPC-C because not all the stringent TPC-C rules were followed. For instance, to simplify the tests, I did not leave any think time or wait time between consecutive database calls. But that works fine for this post since I just wanted to apply an OLTP workload to the test system and I'm not here to publish any official TPC-C results.

The test results are shown in the following chart:

Note that what's important in this case is the relative throughputs among the three different test scenarios, and these three test scenarios were different only in how the controller cache was configured. The absolute throughput numbers do not mean anything.

So as suspected, giving all 512MB of the controller cache to reads led to a significantly lower throughput, compared to the scenarios where a large portion of the cache (384MB or 512MB) was given to writes.

How was the I/O subsystem exercised by these workloads? Let me focus on the tests with 100 users. From a disk I/O perspective, the OLTP workloads during the steady state can be characterized as follows:

On the F drive with the data file
I recorded the following perfmon counters:

* Avg. Disk Bytes/Read = ~8KB, and Avg. Disk Bytes/Write = ~8KB
* Avg. Disk sec/Read = ~275ms, and Avg. Disk sec/Write = ~320ms

So 8KB pages were being read from the data file into the buffer pool randomly, and the lazy writer was writing 8KB pages out to the data file randomly. And these I/O requests saturated the F drive. Note that during the 3 minute test duration, one checkpoint was issued manually.
On the G drive with the log file
I recorded the following perfmon counters:

* Avg. Disk Bytes/Write = ~4KB
* Avg. Disk sec/Write = ~28ms

So SQL Server was writing to the log in ~4KB blocks, and the G drive was pushed hard.

If we look at the waitstats obtained during the test run, not surprisingly the waits were heavily concentrated on Page IO latch wait.

Performance impact of controller cache: SQL Server large operations

2008-03-27T03:23:00Z

In my previous post on the performance impact of controller cache configurations, I presented some empirical results showing the performance impact of configuring the controller cache to various read/write settings on large sequential I/Os.

Why did I single out large sequential I/Os? That's because large sequential I/Os are heavily used by SQL Server in performing such critical operations as bulk loads, table scans, and checkpoints, to name a few. Since you are most likely a SQL Server professional, a natural question is: do the results in the previous post really translate into anything relevant to SQL Server performance? Conceptually, the answer is of course yes.

But nothing beats seeing it directly. So I ran the following four SQL Server tests:

Bulk Load
A C# program was written to load data into the customer table using the System.Data.SqlClient.SqlBulkCopy class. The batch size for the bulk load was set to 50,000. The data was generated in the program per the TPC-C specifications for the customer table.
Full table scan -- SELECT

After the clean buffer pages were dropped, the following query was run against the customer table:
SELECT avg(c_balance) FROM customer;
Note that there is no index on the column c_balance.
Full table scan -- UPDATE
After the clean buffer pages were dropped, the following update was run against the customer table:
UPDATE customer
SET c_data = 'a' + substring(c_data, 2, len(c_data))
There is no index on the c_data column.
Checkpoint
The 'recovery interval' sp_configure option was set to 32767 for the all tests mentioned in this post, effectively disabling automatic checkpoints. A manual checkpoint is issued after the UPDATE statement described above. This test measures the duration of that checkpoint.

All the tests were performed on the customer table in the TPC-C benchmark, whose DDL is included in the appendix. The customer table was populated with 3,000,000 rows, that took up about 2GB of storage space.

The tests were repeated multiple times for each of the following three test scenarios:

0MB/512MB
Out of the 512MB total cache in the disk controller, 0MB was allocated for reads and 512MB for writes.
128MB/384MB
Out of the 512MB total cache in the disk controller, 128MB was allocated for reads and 384MB for writes.
512MB/0MB
Out of the 512MB total cache in the disk controller, 512MB was allocated for reads and 0MB for writes.

The test results are shown in the following four charts.

Figure 1 shows that for the bulk load benefited dramatically from controller write cache. When 512MB or 384MB was allocated to the write cache, the bulk load took about 53% of the time it took to load the same data when no write cache was allocated. From the perfmon, the bulk load was observed to cause ~64KB writes on both the data file and the log file of the test database.

Figure 2 shows that the full table scan query ran faster without any read cache in the controller. Per the perfmon counters, the table scan was doing >300KB reads.

Figure 3 shows that the full table scan update ran faster with write cache. Per the perfmon counters, the update was doing ~64KB writes to the log file. Figure 4 again shows that large sequential writes performed better with write cache because checkpoints were observed to be doing >128KB writes.

Overall, the results from these SQL Server operations are consistent with the results from pure I/Os tests described in the previous post.

Appendix: DDL for the test table customer

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)
);
go
CREATE UNIQUE CLUSTERED INDEX ci_customer

on customer(c_w_id, c_d_id, c_id);
CREATE UNIQUE NONCLUSTERED INDEX nc1_customer

on customer(c_w_id, c_d_id, c_last, c_first, c_id);

Performance impact of controller read cache: large sequential I/Os

2008-03-24T16:14:00Z

In the next several blog posts, I’ll share with you some empirical results concerning the performance impact of configuring the read/write cache of a disk controller.

In the comments on Joe Chang’s blog at this site on Storage Performance for SQL Server, some statements were made concerning the performance impact of read cache in a disk controller. Joe and I agreed that read cache doesn’t really help for what matters. In fact, it may degrade the performance of large sequential reads. In this blog post, I’ll give you some supporting evidence.

All the data points reported in this blog post were obtained on an HP Smart Array P400 SAS controller with 512MB of cache that could be configured for either reads or writes, or a combination of reads and writes. There were three 72GB 10,000rpm SAS drives hanging off the controller, and they were configured as three separate disks (i.e. just a bunch of disks).

The results reported in this and the next few blog posts were from tests done on a 10GB test file on one of the three disks. The test tool was conducted primarily with sqlio.exe, and the I/O commands executed are listed at the end of this post.

The two charts below illustrate the performance impact of various cache settings on the throughput of 256KB sequential reads and that of 256KB sequential writes at various I/O load levels. The top chart is for 256KB sequential reads and the bottom chart is for 256KB sequential writes. Note that each data point in the charts represents the consistent results from many repeated test runs in the configured test environment.

As is evident in the top chart, the throughput of 256KB sequential reads can be degraded by the presence of a significant amount of read cache. In this case, 256KB sequential reads actually experienced a rather large drop in throughput (~20%) from ~73MB per second when no read cache was allocated to ~53MB per second when 512MB was allocated to reads.

The bottom chart suggests that increasing the amount of controller cache for writes did improve the performance of 256KB sequential writes. When the write cache was increased from 0MB to 512MB, the throughput of 256KB sequential writes went up from 57MB per second to 62MB per second, an increase of ~9%.

Now does this mean that the controller cache should be all allocate to writes? I’ll address that question in the next several posts in the context of other I/O workloads. But let me give you a hint before I get to the next post. Note that in the top chart there was no performance difference in 256KB sequential reads whether 0MB or 128MB was allocated to the read cache. Also note that in the bottom chart there was no performance difference in 256KB sequential writes whether 512MB or 384MB was allocated to the write cache. This is significant as other read I/O workloads may benefit from a small amount of read cache without harming the write I/O workloads.

Appendix: Batch file for large sequential I/Os

call sqlIO.exe -kW -s30 -o2   -fsequential -b256 -BH -LS -Fparam_F.txt
call sqlIO.exe -kW -s30 -o8   -fsequential -b256 -BH -LS -Fparam_F.txt
call sqlIO.exe -kW -s30 -o16 -fsequential -b256 -BH -LS -Fparam_F.txt
call sqlIO.exe -kW -s30 -o32 -fsequential -b256 -BH -LS -Fparam_F.txt
call sqlIO.exe -kW -s30 -o64 -fsequential -b256 -BH -LS -Fparam_F.txt
call sqlIO.exe -kW -s30 -o128 -fsequential -b256 -BH -LS -Fparam_F.txt
call sqlIO.exe -kW -s30 -o256 -fsequential -b256 -BH -LS -Fparam_F.txt

call sqlIO.exe -kR -s30 -o2   -fsequential -b256 -BH -LS -Fparam_F.txt
call sqlIO.exe -kR -s30 -o8   -fsequential -b256 -BH -LS -Fparam_F.txt
call sqlIO.exe -kR -s30 -o16 -fsequential -b256 -BH -LS -Fparam_F.txt
call sqlIO.exe -kR -s30 -o32 -fsequential -b256 -BH -LS -Fparam_F.txt
call sqlIO.exe -kR -s30 -o64 -fsequential -b256 -BH -LS -Fparam_F.txt
call sqlIO.exe -kR -s30 -o128 -fsequential -b256 -BH -LS -Fparam_F.txt
call sqlIO.exe -kR -s30 -o256 -fsequential -b256 -BH -LS -Fparam_F.txt

where the content of the parameter file param_F.txt is as follows:

F:\testfile.dat 1 0x0 10000

Is the 8KB page obsolete or aging?

2008-03-04T00:07:00Z

In one of their year 2000 papers—Rules of Thumb in Data Engineering, Jim Gray and Prashant Shenoy stated that, “Over the last decade, disk pages have grown from 2KB to 8KB and poised to grow again. In ten years, the typical small transfer unit will probably be 64KB, and large transfer units will be a megabyte or more.”

Should we expect the page size of SQL Server to grow? Or is the 8KB page size still optimal for SQL Server? I remember discussing this very question with someone in the know about a year ago, and was told that the suggestion of using a larger page size in SQL Server was more BS than substance. The argument was essentially that SQL Server rarely incurs 8KB I/Os, and it always tries to ‘batch up’ I/Os into large transfers when it does such operations as read-ahead reads, checkpoints, lazy writes, and so on.

I’m not here to argue for or against using a single fixed 8KB page size in SQL Server. I’m simply not in a position to do justice to the topic. I’m sure Microsoft has been experimenting with different page sizes. At least, I sure hope so.

I just want to highlight some observations related to this topic.

First, note that SQL Server’s choice of a single fixed 8KB page is different from Oracle and DB2, which allow multiple page/block sizes. A single fixed page size can obviously lead to simplified code, but at the same time has the potential to introduce this fixed page size assumption throughout the code, making it difficult to accommodate a different page size should that becomes necessary.

Although the fact that it uses 8KB pages doesn’t necessarily mean that SQL Server would perform a lot of 8KB I/Os, it is not that rare to see SQL Server does 8KB I/Os in real world applications. This is especially true when there is fragmentation within a database.

Conceptually, it does seem that the larger the page size, the more row slots you can have on a page, and therefore fewer levels you may have for your indexes. Potentially, this can lead to fewer page splits and fewer physical I/Os. On the flip side, a larger page size may have a higher chance for running into unwanted concurrency issues, though this shouldn’t really be a problem with row-level locking. The other argument against a large page size is that if your workload typically requests a small chunk of data, getting a 32KB or even 64KB page into the buffer pool only to use a few short rows could be wasteful.

The introduction of database compression in SQL Server 2008—its dictionary compression in particular—seems to give some legitimacy to the question of whether the 8KB page starts to show signs of aging. For instance, with dictionary compression, it may work better with larger pages. Intuitively, the larger the page size, the more likely you may see repeating data patterns and the less overhead space the compression information structure may take. Oracle uses a similar block-level compression scheme and research and experience on the Oracle compression has shown that generally larger blocks tend to produce better compression ratios.

Now if you check the Oracle literature, you would find some rather heated debate on whether there is any value in using multiple block sizes in a real world app. Most agree that using a larger block size in data warehousing applications tend to result in better performance. But some have argued that the performance gain may not be worth the added hassle in managing multiple block sizes, and people may be better off just staying with the default block size, which happens to be 8KB.

So this is not a settled topic. It remains interesting to see whether Microsoft will change the page size or introduce multiple page sizes post SQL Server 2008.

Drive letters should go

2008-02-29T21:27:00Z

Recently, I was involved in designing a SQL2005 cluster. Let’s just say that it was going to have many more nodes until we found that we had to scale down to a 3+1 cluster because of drive letter limitation. The annoying thing is that even with a 3+1 cluster and with mountpoints used to the limit, we may still run out of drive letters.

With each node being a four-socket quad-core server, this 3+1 cluster would have 64 cores in total. Not a huge deal these days, but still quite a bit computing power nevertheless!

Since this a database consolidation environment, we want to be able to accommodate as many databases in a SQL instance as we practically and sanely can. That means fewer instances, and possibly better resource utilization. On the other hand, we want to be able to contain and fence the resource utilization of each application whose databases are hosted in this cluster. That means isolating their databases into their own SQL2005 instances and fencing the corresponding Windows processes with processor affinity and/or some kind of Windows resource management utility.

It’s common that many applications don’t need the computing power of all 16 cores. In fact, many applications may not even need two cores. But let’s assume that for capacity planning purposes, we’ll offer two cores as the minimum unit for a SQL2005 instance. So we can support up to 16 / 2 = 8 instances on each node. In this cluster with three node active, we can accommodate 3 x 8 = 24 instances. But wait! Each SQL2005 instance needs at least one drive letter, and the letter C is already taken. So we can’t really support 24 SQL2005 instances. In many environments, two or three drive letters are often reserved on each server, therefore, the practical number of available drive letters is probably 20 or less.

Now, in reality, to be serious, how often do you really want that many SQL2005 instances in a cluster? Probably not very often at all. But as computing power increases, it’ll increasingly become more likely. In fact, we were considering a 6+2 cluster before we realized that we probably didn’t want to go there because of the drive letter limit.

Is it insane to have this many instances in a cluster? If that’s the case, why does Microsoft continues to bump up the number nodes in a cluster? Even if the number of your instances in a cluster is well below the upper limit, you don’t want to later run into a situation where you have to migrate your instances somewhere else just to avoid running out of drive letters.

From a strictly system design perspective, drive letters are completely archaic and not cool. The restriction is artificial and a product of a primitive era. Okay, I don’t want to over do it here as what is cool to me may not be cool to you!

The strongest argument, however, is competition. When they can easily do 6+2 failover clusters, for SQL Server to be limited by drive letters, it is just an unnecessary distraction we can do without.

Hexa-core and query parallelism

2008-02-28T17:03:00Z

I was struck by two things yesterday. One was that internet—the blogsphere in particular—was abuzz over the leaked information on Intel Dunnington chip, which is a six-core processor that is socket-compatible with Intel Tigerton (i.e. Xeon 7300). Do a Google search on the keywords: Intel Dunnington, and you’ll find plenty of information.

The other thing was that I got involved with a client in yet another round of discussions concerning query parallelism, which was prompted by a Microsoft support’s recommendation to turn off query parallelism for some batch processing because of certain problems with parallelism.

Intel as well as AMD is clearly on the march along their roadmap towards multi-core and many-core chips. And that is putting a lot of pressure on the software development community to ensure that their software can take full advantage of this rapid increase in computing power. The pace of hardware progress is outstripping that of software development. Among all the software applications, DBMS including SQL Server are relatively better positioned to keep pace with this multi-core/many-core march because they are inherently multi-threaded (or multi-tasked).

That said, programming parallelism is not trivial, and programming scalable parallelism is hard. If you have worked with SQL Server query parallelism in its current state, you know it doesn’t always do exactly what you want. With multi cores/many cores becoming a norm, you won’t afford to just turn it off when there is a problem here or there, as it is currently a ‘generally accepted’ practice. Query parallelism needs to become a lot more robust. And since it’s just not realistic to be completely problem free, query parallelism also needs to become a lot more granular so that the problem can be localized and if it comes to it, query parallelism can be turned on or off in isolated area.

The six-core Dunnington processor will be released second half of this year. That corresponds more or less with the release of SQL Server 2008. Note that four-socket servers are common. With Dunnington, that gives us 24 cores on a commodity server. And there are also some eight-socket x64 servers, though less common. And that would give us 48 cores on a commodity server. It’ll be interesting to see how SQL Server 2008 scales on these 24-core or 48-core x64 commodity servers.

You Gotta Stop Writing Tautologies

2008-02-21T22:44:00Z

Windows performance monitor is an excellent tool. But the online explanations for many perfmon counters often drive me nuts.

Take the counter "SQLServer:Databases\Log Growths" as an example. Its explain text is "Total number of log growths for this database." Well, that doesn't really add much to the information already conveyed by the counter name, does it? If the writer could just add one or two more sentences, it would make the explain text much more useful. Say, add a sentence to explicitly identify whether this includes both manual growths and autogrowths.

As another example, the explain text for the counter "SQLServer:General Statistics\Logical Connections" says "Number of logical connections to the system." Great! That's really helpful! How about adding a quick sentence to explain what is a logical connnection?

I understand there is very limited space for the explain text. But it does have enough space for a few more short sentences.

It's a total waste of time to provide this type of useless repetitions.

The End of an Architectural Era?

2008-02-20T15:49:00Z

Alan Cranfield on the SSWUG list alerted me to a paper by Mike Stonebraker and folks, proclaiming the end of the current relational database architecture that is generally embraced by all the major commercial relational DBMS and annoucing that a far better paradigm would be based on specialized engines. You can find the paper here:

http://www.vldb.org/conf/2007/papers/industrial/p1150-stonebraker.pdf

If anybody can make this type of bold prediction, Stonebraker certainly can, given that he is a pioneer and authority of the relational DBMS and such a fixture in the relational DBMS filed. That of course doesn't necessarily give him exclusive ownership to predicting the future. But whether or not you believe Mike Stonebraker has the crystal ball into the DBMS furture, the paper is an interesting read from a general DBMS architectural perspective.

To some extent, one can argue that DB2 v9 is moving in the direction of specialized engines when it has two separate engines, one relational and the other XML, in one system, albeit as a commercial system DB2 is nowhere near the radical proposal Stonebraker is advocating.

Now when it comes to this blog site, the Stonebraker paper is relevant to the discussions regarding 'beyond relational'. To some, this would be adding salt to the wound :-) For those who would like to continue that thread of discussions, take a look at http://www.dbms2.com/2008/02/15/non-relational-database-management/.

Microsoft 2008 Winter Scripting Games

2008-02-19T16:40:00Z

Microsoft is running its 3rd annual Winter Scripting Games. Three languages are allowed: VBScript, PowerShell, and Perl. The event sounds interesting. Check it out at:

http://www.microsoft.com/technet/scriptcenter/funzone/games/default.mspx

I didn't know about this event until somebody emailed me this morning. You bet I'll be looking into it, if not for anything else but to find whether there are compelling reasons for PowerShell :-)

Amdahl’s Law

2008-02-12T04:43:00Z

For the past few weeks, I’ve been working on a diverse array of issues ranging from studying SQL Server performance on various multi-core processors, pondering the implications of many-core processors, troubleshooting SQL Server performance problems, looking at the scalability of Oracle RAC and Sybase shared-disk clusters, and so on.

Everywhere I turned, I bumped into the signs of Amdahl’s law, in particular Amdahl’s parallelism law. If you studied the computer architecture in school, you almost certainly have come across Amdahl’s various laws including his parallelism law below:

If a computation has a serial component S, a parallel component P, and there are N processors, then the maximum speedup is as follows

Speedup =	S + P
	S + P / N

This law is often stated in various alternative but equivalent forms. The following is an alternative expression of Amdahl’s law in terms of the fraction of the computation that must be done serially (F):

Speedup =	1
	F + (1 – F) / N

One of many important implications of this law is that even if the number of processors goes to infinity, the maximum speedup you may get is:

Max Speedup =	S + P
	S

Therefore, if the serial component is a large fraction of the whole computation, you won’t get much speedup no matter how many processors you may have. On the other hand, if the parallel component is large, the speedup can be significant.

So, if your system has a shared global state, for consistency it must be accessed (updated) in a serialized fashion. This law suggests that if your workload is seriously bottlenecked on this global state, you won’t get much performance improvement by focusing your efforts on getting better processors, more processors, or improving any other hardware or software parallel components.

In the case of using the Intel multi-core processors, if your workload happens to bottleneck on its front-side bus, it most likely would not help to switch to faster processors. For an interesting discussion of the implications of Amdahl's law on multicore computing, I highly recommend reading this article: "Amdahl's Law in the Multicore Era".

And if you are designing a database application, but the design relies heavily on the shared tempdb, thus causing significant serialization on the tempdb system data structures, the scalability of your app will suffer.

In the case of RAC and Sybase shared-disk clusters, if your workload is such that it cause a huge amount of inter-node traffic to maintain cache coherency, your app will not scale well with more nodes in the cluster.

These observations are intuitive, and are easy to understand without you ever being exposed to Amdahl’s law. However, Amdahl’s law elegantly highlights the common thread to you, and is worth revisiting from time to time.