THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Joe Chang

Brains still matter - silly SSD notions

Everytime a good product comes out, people seem to contrive bad ideas for what can be replaced, usually brains, or people with brains, or that people lacking brains can accomplish something on their own.

Let me put it simply. Suppose one had a query that uses a nonclustered index, and required a key lookup, and the key lookup required a disk access.

Key lookup, disk access, 10 HDD:   2000-3000 lookups per sec (4K at high Q depth)

Key lookup, in memory:   200,000 lookups per second per core (2.66GHz Core2)

Key lookup, disk access, SSD array:   40,000 lookups per sec per core (same CPU)

Covered/Included index, in memory: 1-2M rows per sec.

It takes brains to build SQL and indexes to achieve the smallest practical memory footprint, and one that allows the most efficient CPU operations, meaning: 1) minimal avoidable key lookups, 2) enabling merge or hash joins when appropriate over loop joins.

The SQL Server internal cost based optimizer also makes the assumption that disk IO costs are 0.000740741 (seconds) per incremental page in a table scan (1350 pages/sec) and 0.003215 per key lookup (pseudo random 8K) for 320 per sec, about a 4.2 to 1 ratio in scan versus lookup. These numbers already do not reflect current generation disk drives (90-120MB/sec, 200-300 random IOPS). Suppose the new Intel SSD can do 160MB/sec irrespective of whether the access is sequential or small block random. Will MS change the CBO to a 1:1 ratio? And will it do so correctly depending on the whether the data file is on HDD or SSD? If not, then even if the SSD is very fast, SQL is using the wrong plan!

Note: In a poorly designed database/index set, hash and merge joins occur with a scan on one or more sources, and if the scan is big, the query is will be very expensive. So brain deficient people  associate hash and merge joins with expensive. If one had brains, one would realize what was expensive was the big scan. The reason for the hash or merge operation is that they are more efficient join mechanisms on a per row basis than a loop join, except for low row counts.

Published Monday, September 08, 2008 4:32 PM by jchang

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Rafa said:

Wow! I think I'm brainless...

I thought I would came here to get some fresh information from pros, so maybe someday I would have a brain, but problably it's not case, today I lost my time reading your cursing words...

Take it easy man, you're not talking with your parents!

September 8, 2008 10:25 PM

jchang said:

learning direct from the pros, without ever having to waste time wading through what seems to be crap, what a wonderful world, Dare to dream! The audacity of hope! Yes we can!

September 9, 2008 12:49 AM

Lazy Writer said:

2) enabling merge or hash joins when appropriate over loop joins.

Do you mean hash join more effective than nested loops join?

Seems you are a little bit wrong.

September 9, 2008 9:53 AM

Adam Machanic said:

Lazy Writer: In certain scenarios, absolutely! Why do you think that the SQL Server team would have put the operator into the query engine if it was worse than what was already there?

I have yet to post the solutions for the following challenge, but here's a hint: The top performers were able to get the query engine to use a hash match rather than nested loops.

September 9, 2008 10:02 AM

Daniels Rain said:

Hi, Joe Chang!

I am Daniels and currently I got a few questions about SQL 2008.

I am working at Shanghai, China.

And would you please send E-mail to me by the following address: and we could make a conversation about the issues.

Thx a lot and Best Regards!

September 10, 2008 5:47 AM

jchang said:

Lets assume that the cost of a simple SELECT query has a one-time setup cost, and an incremental cost per row. So the full cost is the setup cost plus rows * incremental cost.

Suppose that the setup cost for a loop join is 10 and for a hash join is 100. Suppose the incremental cost is 4 per row for loop join, and 2 for a hash join.

Do people still remember how to plot a line given an offset and slope? If not, ask the nearest high school student.

Which is more efficient, loop or hash? at 30 rows, 60 rows?

The SQL optimizer formulas is slightly complicated but in short, excluding the source table index seek costs. For a loop join, 0.00000418 is assessed per row on the join itself, 0.0001581 CPU is assessed for each additional index seek to the innersource, an additional 0.003125 is assessed for each IO estimated for repeated inner source accesses, with a provision for accounting pages already accessed. There are other effects. In a high row count loop join, the optimizer may elect to sort the outer source, reducing the estimate IO to access the inner source.

For not large hash joins, the startup cost is 0.01776, the cost of each row depends on the number of bytes from the inner and outer sources, so its relatively cheap if hash a few columns, but more expensive if you want to hash fat columns. For a 4 byte column, it 0.000015266 per row. For larger hash joins, an IO cost may be assessed for spooling intermediate hash results to temp, this depends on memory, 32/64-bit.

I am not mistaken on loop, hash and merge join performance.

There is a lot of garbage written by less than qualified people out there, thinking the loop is good, hash is bad, based on observations, but not thinking it through. Adam: thats impressive thinking, on why a less efficient operator would be substited

September 10, 2008 10:53 AM

Mike Good said:

I understand and agree with you on potential goodness of hash/merge joins.  My experience with SQL 2000 optimizer made it clear that that optimizer often chose hash/merge join when it was a poor choice.  I have not had enough experience with, or else just plain haven't seen this problem with SQL 2005, and I've no experience with 2008 yet.  

So question is this:  with 2005 or 2008 are you finding that you sometimes have to induce the optimizer to choose hash/merge join?  Was that what led to your initial stmts?  Or were you trying to discourage people from inducing the optimizer to use loop joins when the optimizer chose otherwise?

October 14, 2008 10:51 PM

jchang said:

to get the right hash or merge join, the table needs to have the right cluster key (or covering indexes). This usually involved propagating down grand parent keys. Consider: SELECT x FROM Orders o JOIN LineItem l ON l.OrderID = o.OrderID WHERE o.CustomerID = 1.

If CustomerID had been pushed down to LineItem, then the WHERE clause would have had a SARG for each table.

If the tables are not designed with an understanding of SQL cost structure, then no amount of inducing will get the best solution. The problem with SQL (any version) going to a hash/merge with scan is due to the Loop join cost structure. If all data is in memory, it might be better to force a loop join depending on the row count.

October 16, 2008 2:59 AM

Leave a Comment


About jchang

Reverse engineering the SQL Server Cost Based Optimizer (Query Optimizer), NUMA System Architecture, performance tools developer - SQL ExecStats, mucking with the data distribution statistics histogram - decoding STATS_STREAM, Parallel Execution plans, microprocessors, SSD, HDD, SAN, storage performance, performance modeling and prediction, database architecture, SQL Server engine

This Blog


Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement