For a SQL Server 2000 default instance, the registry keys and values under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer are absolutely critical to its functioning. So if someone tells you that the whole registry subtree underneath there does not exist on his box, but yet the SQL Server 2000 default instance works just fine on that box, what would be your response?

One thing you may do is to execute the following T-SQL command to check if the keys are indeed missing:

EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion', 'CurrentVersion'

Now, you may just get the current version number returned from this statement. To further verify that the keys are there, you may launch Regedit.exe from your workstation and connect to the registry on the box. You then navigate to the MSSQLServer key, and not surprisingly, you see all the SQL Server related registry keys and values right where they are supposed to be.

Case closed, right? Well, the person still claims that he can't see the registry keys, and even send you a Regedit screenshot to prove it. So you follow the exact steps of the person to reproduce the behavior. And surprisingly, you don’t see the registry keys, even though you are a local administrator.

What's going on?

Well, this is the scenario of running a 32-bit SQL Server 2000 default instance on Windows Server x64 edition. If you use a 64-bit tool (such as a 64-bit regedit.exe on the 64-bit Windows), you won’t see the keys because they are not stored there. In fact, the 32-bit SQL Server registry keys and values are actually stored under HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer.

However, Windows provides a transparent registry ‘redirection’ for 32-bit tools. So, if you happen to use a 32-bit tool—such as regedit.exe from your 32-bit workstation and xp_regread from a 32-bit SQL Server 2000 Instance—to view the registry, you would see the keys underneath HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer just where you expect them to be.

[2008/08/25] This post has been modified significantly to correct some inaccurate statements because I mis-read Joe Chang's post.

Joe Chang posted some interesting results using the TPC-H queries with the scale factor set to 10. I happened to have done something similar, and naturally noticed a rather significant difference between his results and mine. [2008/08/25: Okay the difference is not as significant as I had thought.]

My results were obtained on SQL Server 2005 Enterprise x64 Edition running on Windows Server 2003 Enterprise x64 Edition. The test server, whose make and model will remain undisclosed,  had four 2.93GHz quad-core Intel Tigerton sockets (Xeon X7350) with 64GB of RAM with 20GB allocated to the SQL Server buffer pool. The TPC-H scale factor was set to 10, and the data were generated with DBGEN from tpc.org.

My results are as follows:

All the numbers in the above table are query elapsed times in milliseconds except the very last line highlighted in yellow, which are in seconds. The last line contains the total elapsed time for Query1 through Query22 for each MAXDOP setting. All the results were obtained with all the database pages cached in the buffer pool. The same TPC-H query stream (i.e. Query 1 through Query 22) was repeated for 10 times with all the results from the first run thrown out, and the max elapsed time and the min elapsed time for each query for each of the subsequent runs were also thrown out before the elapsed times were averaged.

So what's the difference between Joe's results and mine?

Although there are some differences in the elapsed times in terms of the absolute numbers, the differences are minor enough to be no cause for alarm, especially when you take into consideration the platform differences and probably some differences in the test setup.

However, the deline in the elapsed times in my test results is more significant than it is in Joe's results when MAXDOP is changed from 1 through 16. The difference is not as dramatic as I had throught, and initially commented on in the original version of this post (because I was reading the wrong line for whatever reason).

Given that TPC-H has been around for a long time and all DBMS vendors have tried very hard to optimize their products to performance well on TPC-H, I do not expect SQL Server to be an exception, and am rather pleased to see that it scales very well with MAXDOP on these queries.

I'm a bit embarrassed that I didn't read Joe's results correctly first time around, but felt that it's time well spent to be able to cross check independent tests results from realistic envrionments.

It’s well known that bookmark lookup (aka key lookup in case of a clustered index) is not cheap, especially when it comes to retrieving a lot of data. So I’m not going to rehash the pros and cons of bookmark lookup or why bookmark lookup is expensive. But I’ve noticed that when it comes to discussing bookmark lookup, all the literature seems to being focusing on their implications on storage I/Os. There is nothing wrong with that. In practice, it ultimately does come down to the fact that when reading a large amount of data, random I/Os are much more expensive than sequential I/Os, and bookmark lookup tends to incur random I/Os.

What I’d like to highlight in this post is that even if all or most of the pages are in memory already, bookmark lookup is still very expensive compared to scan.

To show this is the case, I piggybacked on the test setup described in my last post. Let me briefly recap the test setup. A single table was used, and its definition is as follws:

CREATE TABLE test (
      i           int NOT NULL,
      j           int NOT NULL,   
      dt          datetime,
      filler      char(5000) NOT NULL
)

Two indexes were created on the table:

CREATE CLUSTERED INDEX cix_test ON test(i);
CREATE INDEX cx_test ON test(j);

And the table was populated with 2,000,000 rows with the following INSERT statement in a loop (local variable @i going from 1 to 2,000,000):

INSERT test(i, j, dt, filler)
SELECT @i,
       CASE WEHN @i % 2 = 0 THEN @i ELSE 2000000 - @i END,
       getdate(),
       'abc'

Then, the following two queries were separately run multiple times on a server with 32GB of RAM (25GB of which was allocated to the SQL2005 instance buffer pool):

SELECT max(dt) FROM test;
SELECT max(dt) FROM test WITH (index(cx_test));

After the second run, all the pages were cached in memory (the table was about 16GB in size). Notice that with the first query SQL Server used clustered index scan to produce the result, whereas with the second query the index hint forced SQL Server to scan the nonclustered index and then use bookmark lookup to produce the result.

I’ll look at the storage I/O implication of these two access paths in a separate post, but for now what do you think might be the difference in the elapsed time of these two query-processing methods when all the pages were cached in memory?

The following chart shows the difference:

For this test, the bookmark lookup method took almost three times as long as did the clustered index scan method.

Why such a huge difference? When all the pages were cached in the buffer pool, we can’t explain the query elapsed time difference with the difference in storage I/O efficiency. We can, however, explain the difference with the difference in the number of pages SQL Server engine must visit in the buffer pool.

The output from SET STATISTICS IO ON for each method is as follows:

Clustered Index Scan:

Scan count 1, logical reads 2008931, physical reads 0, read-ahead reads 0 …

Bookmark Lookup:

Scan count 1, logical reads 8130363, physical reads 0, read-ahead reads 0 …

Clearly, the access method with bookmark lookup was not efficient, visiting four times as many pages as did the access method with clustered index scan.

Also note that compared with the size of the table, the size of the nonclustered index was insignificant. There were about 5,000 pages in the leaf pages of the nonclustered index, whereas there were 2,000,000 leaf pages in the clustered index. So the cost of processing the query with the nonclustered index was dominated by the bookmark or key lookups.

Of course, what I’ve discussed in this post is probably more academic than practical as you can’t expect all the pages being cached when processing large reporting queries in most real environments, and therefore the real dominate factor is not how many pages SQL Server needs to traverse in memory, but how many pages SQL Server has to bring into memory and how these pages are brought into memory.

It's not my intention to leave you an impression that I'm picking on the bookmark lookup operation because it's just a bad method to retrieve data. That's not the case at all because for some queries, it is an excellent method. But that's not the focus of this post, and there are plenty of discussions on the advantages of the bookmark lookup operation you can find elsewhere.

Test Environment

SQL Server 2005 Enterprise x64 Edition ran on Windows Server 2003 Enterprise x64 Edition with Service Pack 2. The SQL2005 build was 9.0.3239 (i.e. SQL Server 2005 CU7) with 25GB allocated to the buffer pool. The server was a HP BL680 G5 including four 2.4GHz quad-core Xeon E7340 processors (aka Tigerton) with 2x4MB L2 cache and 32GB of RAM.

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.