Joe;

After your first comment, I re-read your post. I mis-read your original post, and some statements in my post above are not accurate. I'll have to modify my post.

I've modofied the post to include the corrections.

Joe,

I didn't see any material disk activity during the test. So I'm not sure whether tempdb performance matters that much. I'm double checking whether this has anything at all to do with a Perl wrapper I was using. The SQL2005 version is 9.00.3239.

Also, I didn't follow the strict TPC-H rule in randomizing the some of the query parameters. In other words, I didn't use QGEN. I used the validation queries with the 'parameters' fixed throughout my tests.

That's the validation parameter value as per the TPC-H specs. I think we are using the same queries. The queries I used are as follows:

/* TPC_H Query 1 - Pricing Summary Report */

SELECT L_RETURNFLAG,

L_LINESTATUS,

SUM(L_QUANTITY) AS SUM_QTY,

SUM(L_EXTENDEDPRICE) AS SUM_BASE_PRICE,

SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) AS SUM_DISC_PRICE,

SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)) AS SUM_CHARGE,

AVG(L_QUANTITY) AS AVG_QTY,

AVG(L_EXTENDEDPRICE) AS AVG_PRICE,

AVG(L_DISCOUNT) AS AVG_DISC,

COUNT(*) AS COUNT_ORDER

 FROM LINEITEM

WHERE L_SHIPDATE <= dateadd(dd, -90, '1998-12-01')

GROUP BY L_RETURNFLAG, L_LINESTATUS

ORDER BY L_RETURNFLAG, L_LINESTATUS

/* TPC_H Query 2 - Minimum Cost Supplier */

SELECT TOP 100 S_ACCTBAL,

S_NAME, N_NAME,

P_PARTKEY,

P_MFGR,

S_ADDRESS,

S_PHONE,

S_COMMENT

 FROM PART, SUPPLIER, PARTSUPP,NATION, REGION

WHERE P_PARTKEY = PS_PARTKEY

  AND S_SUPPKEY = PS_SUPPKEY

  AND P_SIZE = 15

  AND P_TYPE LIKE '%%BRASS'

  AND S_NATIONKEY = N_NATIONKEY

  AND N_REGIONKEY = R_REGIONKEY

  AND R_NAME = 'EUROPE'

  AND PS_SUPPLYCOST = (SELECT MIN(PS_SUPPLYCOST)

                         FROM PARTSUPP, SUPPLIER, NATION, REGION

                        WHERE P_PARTKEY = PS_PARTKEY AND S_SUPPKEY = PS_SUPPKEY

                          AND S_NATIONKEY= N_NATIONKEY

                          AND N_REGIONKEY = R_REGIONKEY

                          AND R_NAME = 'EUROPE')

ORDER BY S_ACCTBAL DESC, N_NAME, S_NAME, P_PARTKEY

/* TPC_H Query 3 - Shipping Priority */

SELECT TOP 10 L_ORDERKEY,

  SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT))AS REVENUE,

  O_ORDERDATE, O_SHIPPRIORITY

 FROM CUSTOMER, ORDERS, LINEITEM

WHERE C_MKTSEGMENT = 'BUILDING'

  AND C_CUSTKEY = O_CUSTKEY

  AND L_ORDERKEY = O_ORDERKEY

  AND O_ORDERDATE < '1995-03-15'

  AND L_SHIPDATE > '1995-03-15'

GROUP BY L_ORDERKEY, O_ORDERDATE, O_SHIPPRIORITY

ORDER BY REVENUE DESC, O_ORDERDATE

/* TPC_H Query 4 - Order Priority Checking */

SELECT O_ORDERPRIORITY,

COUNT(*) AS ORDER_COUNT

 FROM ORDERS

WHERE O_ORDERDATE >= '1993-07-01'

  AND O_ORDERDATE < dateadd (mm, 3, '1993-07-01')

  AND EXISTS (SELECT * FROM LINEITEM

               WHERE L_ORDERKEY = O_ORDERKEY

                 AND L_COMMITDATE < L_RECEIPTDATE)

GROUP BY O_ORDERPRIORITY

ORDER BY O_ORDERPRIORITY

/* TPC_H Query 5 - Local Supplier Volume */

SELECT N_NAME,

SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) AS REVENUE

 FROM CUSTOMER, ORDERS, LINEITEM, SUPPLIER, NATION, REGION

WHERE C_CUSTKEY = O_CUSTKEY

  AND L_ORDERKEY = O_ORDERKEY

  AND L_SUPPKEY = S_SUPPKEY

  AND C_NATIONKEY = S_NATIONKEY

  AND S_NATIONKEY = N_NATIONKEY

  AND N_REGIONKEY = R_REGIONKEY

  AND R_NAME = 'ASIA'

  AND O_ORDERDATE >= '1994-01-01'

  AND O_ORDERDATE < DATEADD(YY, 1, '1994-01-01')

GROUP BY N_NAME

ORDER BY REVENUE DESC

/* TPC_H Query 6 - Forecasting Revenue Change */

SELECT SUM(L_EXTENDEDPRICE*L_DISCOUNT) AS REVENUE

 FROM LINEITEM

WHERE L_SHIPDATE >= '1994-01-01'

  AND L_SHIPDATE < dateadd (yy, 1, '1994-01-01')

  AND L_DISCOUNT BETWEEN .06 - 0.01

  AND .06 + 0.01

  AND L_QUANTITY < 24

/* TPC_H Query 7 - Volume Shipping */

SELECT SUPP_NATION,

CUST_NATION,

L_YEAR, SUM(VOLUME) AS REVENUE

 FROM ( SELECT N1.N_NAME AS SUPP_NATION,

               N2.N_NAME AS CUST_NATION,

               datepart(yy, L_SHIPDATE) AS L_YEAR,

               L_EXTENDEDPRICE*(1-L_DISCOUNT) AS VOLUME

          FROM SUPPLIER, LINEITEM, ORDERS, CUSTOMER, NATION N1, NATION N2

         WHERE S_SUPPKEY = L_SUPPKEY

           AND O_ORDERKEY = L_ORDERKEY  

           AND C_CUSTKEY = O_CUSTKEY

           AND S_NATIONKEY = N1.N_NATIONKEY

           AND C_NATIONKEY = N2.N_NATIONKEY

           AND ((N1.N_NAME = 'FRANCE' AND N2.N_NAME = 'GERMANY') OR (N1.N_NAME = 'GERMANY' AND N2.N_NAME = 'FRANCE'))

           AND L_SHIPDATE BETWEEN '1995-01-01' AND '1996-12-31' ) AS SHIPPING

GROUP BY SUPP_NATION, CUST_NATION, L_YEAR

ORDER BY SUPP_NATION, CUST_NATION, L_YEAR

/* TPC_H Query 8 - National Market Share */

SELECT O_YEAR,

SUM(CASE WHEN NATION = 'BRAZIL' THEN VOLUME ELSE 0 END) / SUM(VOLUME) AS MKT_SHARE

 FROM (SELECT datepart(yy, O_ORDERDATE) AS O_YEAR,

              L_EXTENDEDPRICE * (1-L_DISCOUNT) AS VOLUME,

              N2.N_NAME AS NATION

         FROM PART, SUPPLIER, LINEITEM, ORDERS, CUSTOMER, NATION N1, NATION N2, REGION

        WHERE P_PARTKEY = L_PARTKEY

          AND S_SUPPKEY = L_SUPPKEY

          AND L_ORDERKEY = O_ORDERKEY

          AND O_CUSTKEY = C_CUSTKEY

          AND C_NATIONKEY = N1.N_NATIONKEY

          AND N1.N_REGIONKEY = R_REGIONKEY

          AND R_NAME = 'AMERICA'

          AND S_NATIONKEY = N2.N_NATIONKEY

          AND O_ORDERDATE BETWEEN '1995-01-01'

          AND '1996-12-31'

          AND P_TYPE= 'ECONOMY ANODIZED STEEL') AS ALL_NATIONS

GROUP BY O_YEAR

ORDER BY O_YEAR

/* TPC_H Query 9 - Product Type Profit Measure */

SELECT NATION,

O_YEAR,

SUM(AMOUNT) AS SUM_PROFIT

 FROM (SELECT N_NAME AS NATION,

              datepart(yy, O_ORDERDATE) AS O_YEAR,

              L_EXTENDEDPRICE*(1-L_DISCOUNT)-PS_SUPPLYCOST*L_QUANTITY AS AMOUNT

         FROM PART, SUPPLIER, LINEITEM, PARTSUPP, ORDERS, NATION

        WHERE S_SUPPKEY = L_SUPPKEY

          AND PS_SUPPKEY= L_SUPPKEY

          AND PS_PARTKEY = L_PARTKEY

          AND P_PARTKEY= L_PARTKEY

          AND O_ORDERKEY = L_ORDERKEY

          AND S_NATIONKEY = N_NATIONKEY

          AND P_NAME LIKE '%%green%%') AS PROFIT

GROUP BY NATION, O_YEAR

ORDER BY NATION, O_YEAR DESC

/* TPC_H Query 10 - Returned Item Reporting */

SELECT TOP 20 C_CUSTKEY,

C_NAME,

SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) AS REVENUE,

C_ACCTBAL,

N_NAME,

C_ADDRESS,

C_PHONE,

C_COMMENT

 FROM CUSTOMER, ORDERS, LINEITEM, NATION

WHERE C_CUSTKEY = O_CUSTKEY

  AND L_ORDERKEY = O_ORDERKEY

  AND O_ORDERDATE>= '1993-10-01'

  AND O_ORDERDATE < dateadd(mm, 3, '1993-10-01')

  AND L_RETURNFLAG = 'R'

  AND C_NATIONKEY= N_NATIONKEY

GROUP BY C_CUSTKEY, C_NAME, C_ACCTBAL, C_PHONE, N_NAME, C_ADDRESS, C_COMMENT

ORDER BY REVENUE DESC

/* TPC_H Query 11 - Important Stock Indentification */

SELECT PS_PARTKEY,

      SUM(PS_SUPPLYCOST*PS_AVAILQTY) AS VALUE

 FROM PARTSUPP, SUPPLIER, NATION

WHERE PS_SUPPKEY = S_SUPPKEY

  AND S_NATIONKEY = N_NATIONKEY

  AND N_NAME = 'GERMANY'

GROUP BY PS_PARTKEY

HAVING SUM(PS_SUPPLYCOST*PS_AVAILQTY) > (SELECT SUM(PS_SUPPLYCOST*PS_AVAILQTY) * 0.0001000000

                                          FROM PARTSUPP, SUPPLIER, NATION

                                         WHERE PS_SUPPKEY = S_SUPPKEY

                                           AND S_NATIONKEY = N_NATIONKEY

                                           AND N_NAME = 'GERMANY')

ORDER BY VALUE DESC

/* TPC_H Query 12 - Shipping Modes and Order Priority */

SELECT L_SHIPMODE,

SUM(CASE WHEN O_ORDERPRIORITY = '1-URGENT' OR O_ORDERPRIORITY = '2-HIGH' THEN 1 ELSE 0 END) AS HIGH_LINE_COUNT,

SUM(CASE WHEN O_ORDERPRIORITY <> '1-URGENT' AND O_ORDERPRIORITY <> '2-HIGH' THEN 1 ELSE 0 END) AS LOW_LINE_COUNT

 FROM ORDERS, LINEITEM

WHERE O_ORDERKEY = L_ORDERKEY

  AND L_SHIPMODE IN ('MAIL','SHIP')

  AND L_COMMITDATE < L_RECEIPTDATE

  AND L_SHIPDATE < L_COMMITDATE AND L_RECEIPTDATE >= '1994-01-01'

  AND L_RECEIPTDATE < dateadd(yy, 1, '1994-01-01')

GROUP BY L_SHIPMODE

ORDER BY L_SHIPMODE

/* TPC_H Query 13 - Customer Distribution */

SELECT C_COUNT, COUNT(*) AS CUSTDIST

 FROM (SELECT C_CUSTKEY,

              COUNT(O_ORDERKEY)

         FROM CUSTOMER left outer join ORDERS

                 on C_CUSTKEY = O_CUSTKEY AND O_COMMENT not like '%%special%%requests%%'

       GROUP BY C_CUSTKEY) AS C_ORDERS (C_CUSTKEY, C_COUNT)

GROUP BY C_COUNT

ORDER BY CUSTDIST DESC, C_COUNT DESC

/* TPC_H Query 14 - Promotion Effect */

SELECT 100.00 * SUM (CASE WHEN P_TYPE LIKE 'PROMO%%' THEN L_EXTENDEDPRICE*(1-L_DISCOUNT)

                         ELSE 0

                     END) / SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) AS PROMO_REVENUE

 FROM LINEITEM, PART

WHERE L_PARTKEY = P_PARTKEY AND L_SHIPDATE >= '1995-09-01' AND L_SHIPDATE < dateadd(mm, 1, '1995-09-01')

/* TPC_H Query 15 - Top Supplier */

SELECT S_SUPPKEY,

S_NAME,

S_ADDRESS,

S_PHONE,

TOTAL_REVENUE

 FROM SUPPLIER, REVENUE0

WHERE S_SUPPKEY = SUPPLIER_NO AND TOTAL_REVENUE = ( SELECT MAX(TOTAL_REVENUE) FROM REVENUE0)

ORDER BY S_SUPPKEY

/* TPC_H Query 16 - Parts/Supplier Relationship */

SELECT P_BRAND,

P_TYPE,

P_SIZE,

COUNT(DISTINCT PS_SUPPKEY) AS SUPPLIER_CNT

 FROM PARTSUPP, PART

WHERE P_PARTKEY = PS_PARTKEY

  AND P_BRAND <> 'Brand#45'

  AND P_TYPE NOT LIKE 'MEDIUM POLISHED%%'

  AND P_SIZE IN (49, 14, 23, 45, 19, 3, 36, 9)

  AND PS_SUPPKEY NOT IN (SELECT S_SUPPKEY FROM SUPPLIER

                          WHERE S_COMMENT LIKE '%%Customer%%Complaints%%')

GROUP BY P_BRAND, P_TYPE, P_SIZE

ORDER BY SUPPLIER_CNT DESC, P_BRAND, P_TYPE, P_SIZE

/* TPC_H Query 17 - Small-Quantity-Order Revenue */

SELECT SUM(L_EXTENDEDPRICE)/7.0 AS AVG_YEARLY

 FROM LINEITEM, PART

WHERE P_PARTKEY = L_PARTKEY

  AND P_BRAND = 'Brand#23'

  AND P_CONTAINER = 'MED BOX'

  AND L_QUANTITY<(SELECT 0.2 * AVG(L_QUANTITY) FROM LINEITEM WHERE L_PARTKEY = P_PARTKEY)

/* TPC_H Query 18 - Large Volume Customer */

SELECT TOP 100 C_NAME,

C_CUSTKEY,

O_ORDERKEY,

O_ORDERDATE,

O_TOTALPRICE,

SUM(L_QUANTITY)

 FROM CUSTOMER, ORDERS, LINEITEM

WHERE O_ORDERKEY IN (SELECT L_ORDERKEY

                       FROM LINEITEM

                     GROUP BY L_ORDERKEY

                     HAVING SUM(L_QUANTITY) > 300)

  AND C_CUSTKEY = O_CUSTKEY

  AND O_ORDERKEY = L_ORDERKEY

GROUP BY C_NAME, C_CUSTKEY, O_ORDERKEY, O_ORDERDATE, O_TOTALPRICE

ORDER BY O_TOTALPRICE DESC, O_ORDERDATE

/* TPC_H Query 19 - Discounted Revenue */

SELECT SUM(L_EXTENDEDPRICE* (1 - L_DISCOUNT)) AS REVENUE

 FROM LINEITEM, PART

WHERE (    P_PARTKEY = L_PARTKEY

       AND P_BRAND = 'Brand#12'

       AND P_CONTAINER IN ( 'SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')

       AND L_QUANTITY >= 1

       AND L_QUANTITY <= 1 + 10

       AND P_SIZE BETWEEN 1 AND 5

       AND L_SHIPMODE IN ('AIR', 'AIR REG')

       AND L_SHIPINSTRUCT = 'DELIVER IN PERSON')

   OR (    P_PARTKEY = L_PARTKEY

       AND P_BRAND = 'Brand#23'

       AND P_CONTAINER IN ( 'MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')

       AND L_QUANTITY >= 10 AND L_QUANTITY <= 10 + 10 AND P_SIZE BETWEEN 1 AND 10

       AND L_SHIPMODE IN ('AIR', 'AIR REG') AND L_SHIPINSTRUCT = 'DELIVER IN PERSON')

   OR (    P_PARTKEY = L_PARTKEY

       AND P_BRAND = 'Brand#34'

       AND P_CONTAINER IN ( 'LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')

       AND L_QUANTITY >= 20

       AND L_QUANTITY <= 20 + 10

       AND P_SIZE BETWEEN 1 AND 15

       AND L_SHIPMODE IN ('AIR', 'AIR REG')

       AND L_SHIPINSTRUCT = 'DELIVER IN PERSON')

/* TPC_H Query 20 - Potential Part Promotion */

SELECT S_NAME,

S_ADDRESS

 FROM SUPPLIER, NATION

WHERE S_SUPPKEY IN ( SELECT PS_SUPPKEY

                       FROM PARTSUPP

                      WHERE PS_PARTKEY in (SELECT P_PARTKEY FROM PART WHERE P_NAME like 'forest%%')

                        AND PS_AVAILQTY > (SELECT 0.5 * sum(L_QUANTITY)

                                             FROM LINEITEM

                                            WHERE L_PARTKEY = PS_PARTKEY

                                              AND L_SUPPKEY = PS_SUPPKEY

                                              AND L_SHIPDATE >= '1994-01-01'

                                              AND L_SHIPDATE < dateadd(yy,1,'1994-01-01'))

                   )

  AND S_NATIONKEY = N_NATIONKEY

  AND N_NAME = 'CANADA'

ORDER BY S_NAME

/* TPC_H Query 21 - Suppliers Who Kept Orders Waiting */

SELECT TOP 100 S_NAME,

COUNT(*) AS NUMWAIT

 FROM SUPPLIER, LINEITEM L1, ORDERS, NATION

WHERE S_SUPPKEY = L1.L_SUPPKEY

  AND O_ORDERKEY = L1.L_ORDERKEY

  AND O_ORDERSTATUS = 'F'

  AND L1.L_RECEIPTDATE> L1.L_COMMITDATE

  AND EXISTS (SELECT * FROM LINEITEM L2

               WHERE L2.L_ORDERKEY = L1.L_ORDERKEY

                 AND L2.L_SUPPKEY <> L1.L_SUPPKEY)

  AND NOT EXISTS (SELECT * FROM LINEITEM L3

                   WHERE L3.L_ORDERKEY = L1.L_ORDERKEY

                     AND L3.L_SUPPKEY <> L1.L_SUPPKEY

                     AND L3.L_RECEIPTDATE > L3.L_COMMITDATE)

  AND S_NATIONKEY = N_NATIONKEY

  AND N_NAME = 'SAUDI ARABIA'

GROUP BY S_NAME

ORDER BY NUMWAIT DESC, S_NAME

/* TPC_H Query 22 - Global Sales Opportunity */

SELECT CNTRYCODE,

COUNT(*) AS NUMCUST,

SUM(C_ACCTBAL) AS TOTACCTBAL

 FROM (SELECT SUBSTRING(C_PHONE,1,2) AS CNTRYCODE,

              C_ACCTBAL

         FROM CUSTOMER

        WHERE SUBSTRING(C_PHONE,1,2) IN ('13', '31', '23', '29', '30', '18', '17')

          AND C_ACCTBAL > (SELECT AVG(C_ACCTBAL)

                             FROM CUSTOMER WHERE C_ACCTBAL > 0.00

                              AND SUBSTRING(C_PHONE,1,2) IN ('13', '31', '23', '29', '30', '18', '17')

                          )

          AND NOT EXISTS ( SELECT * FROM ORDERS WHERE O_CUSTKEY = C_CUSTKEY)

                          ) AS CUSTSALE

GROUP BY CNTRYCODE

ORDER BY CNTRYCODE

I'm modifying a C# wrapper to run these queries in multiple streams, kind of similar to the TPC-H throughput test. A single stream even with MAXDOP set toa igher may be able to drive a server hard enough. I was using a Perl script, but Perl is lousy in controlling multiple threads/processes exactly.