Xeon e5330: Intel Xeon E5330 4-Core 2.13GHz 1066MHz FSB 8MB L2 Cache Socket LGA771 Processor

/shop/e5330-intel-xeon-e5330-2-133ghz-4c-4t-105-watt-12541

Intel Xeon [email protected], 4-Cores 4-Threads @105 Watt, Core Name: Clovertown, Core Size: 0.065nm, Socket Type: Socket 771, L2 Cache: 8192KB, L3 Cache: noneKB, L4 Cache: none

• Details & Specs
• Reviews
• Recently Viewed Products

Technical Specs:

Core Name	Clovertown
Core Size (mm)	0. 065
Socket Types	Socket 771
Cores	4
Threads	4
Base Freq (GHz)	2.133
Turbo Freq (GHz)	No Turbo
L2 Cache (KB)	8192
L3 Cache (KB)	none
L4 Cache (KB)	none
Watt	105

Large Query Performance from SQL Server 2000 to 2008, 32 & 64-bit

Home, Parent TPC-H Interim

SQL Blog: Large
Query Performance from SQL Server 2000 to 2008, 32 & 64-bit

This is a update of my original discussion on this topic in 2008.

I had been meaning to do a somewhat comprehensive review of
SQL Server performance from versions 2000 to 2008 for both 32 and 64-bit
on Data Warehouse type queries, with in depth examination of
scaling in parallel execution plans.
For now, I can provide a short summary.

Test Platform

The test platform is a Dell PowerEdge 2900 with 2 quad-core Xeon E5330
2.66GHz processors, and 24GB memory. The operating system is Windows Server
2008 64-bit for both 32 and 64-bit SQL Server versions. Technically SQL Server
2000 is not supported, but this is just a performance comparison, not a
production environment. The database is generated using the TPC-H dbgen kit
for scale factor 10, meaning the Lineitem table is approximately 10GB, and the entire
database is approximately 17GB, which fits entirely in memory. There was some
tempdb activity, which is spread across 10 15K drives.

All (original) tests are run twice to load data into memory
and pre-compile the execution plan for the second run.
All results shown are for the second run.
For SQL Server 2008, the tables use the new Date data type in place of Date Time,
and queries are modified to avoid conversion anomalies as noted below.
The
ALTER DATABASE tpch
SET DATE_CORRELATION_OPTIMIZATION ON
optimization was not employed here. This will discussed separately.

The newer results are based on ten test runs, ignoring the first sequence,
and averaging the results of the last nine,
to load data into memory and eliminate the query compile time.

Overall Results

Below is the total (sum) CPU time in milli-seconds to execute the 22 queries in sequence
for max degree of parallelism: 1, 2, 4, and 8.
Note official TPC-H scores are a geometric mean, scaled to the size of the database.

Duration in milli-seconds to run 22 queries by max DOP.

SQL Server 2000

On SQL Server 2000 build 2187,
notice that CPU increases from 514. 8 to 589.2 seconds going from degree of parallelism (DOP) 1 to 2 and so on to Max DOP 8.
This is expected because there is overhead to employing a parallel execution plan,
and the overhead increases with the number of threads involved.
Between SQL Server 2000 RTM and build 2187,
there was a sharp jump in the CPU required at DOP 8.
I will disregard this as there were significant changes and code fixes between
the two builds concerning correctness of parallel execution plan results.
Still, there is an overall performance gain from DOP 4 to 8.

Several years ago, I mentioned that SQL Server 2000 performance is very problematic beyond DOP 4.
That was before multi-core processors, and there were at most 4 cores per NUMA node.
So the more correct interpretation is that SQL Server 2000 is very problematic on NUMA systems.
An earlier look at SQL Server 2005 RTM showed no such problems on NUMA.

SQL Server 2005 32-bit

In SQL Server 2005 (and SQL Server 2008, there is actually a decrease in CPU going from DOP 1 to 2.
This is mostly attributed to the bitmap filter in hash operations.
Some queries show a significant drop in CPU from DOP 1 to 2, others no change, and some an increase.
From DOP 2 to 4 there is a slight increase in CPU and a more significant increase in going from DOP 4 to 8.
This might indicate that DOP 2 and 4 are very good for overall efficiency,
benefitting from bitmap filters in hash join operations, yet without incurring excessive parallelism overhead.
(This is unrelated to the recommendation of Max DOP 4 on Itanium systems based on cores per NUMA node).
Unrestricted parallelism on the 8 core system yields the best single stream completion times,
although this should really be tested on 16 or more cores before setting any rules.

In the transition from SQL Server 2000 to 2005 RTM, both 32-bit,
there is a modest 15% reduction in the duration to run the 22 TPC-H queries using non-parallel execution plans.
The improvement is similar at DOP 2, but then improves to 29% at DOP 4.
At DOP 8 using all 8 processor cores, the reduction is a very substantial 49%, almost twice as fast.

Service Pack 2 does not change results at DOP1,
but yield 10-14% improvement in parallel plans at DOP 2, 4 and 8.

SQL Server 2005 64-bit

From SQL Server 2005 32-bit to 64-bit, both RTM builds,
the performance gain in terms of reduced duration was a solid 20% across all DOP from 1 to 8.
The CPU efficiency improvement was a little less, so the tempdb configuration affects
the results.
Even though the entire data and indexes fit in memory,
a query with large intermediate results is less likely to spool to tempdb at 64-bit than 32-bit.
From SQL Server 2005 64-bit RTM to Service Pack 2,
an additional 10% was realized at DOP 2 and higher.

SQL Server 2008 64-bit

SQL Server 2008 RTM is marginally better than SQL 2005 SP2.
There is significant variation from query to query,
so improvements should be expected over time hopefully to correct the query plans
that are slower while maintaining the performance advantage of plans that are better.
One of the big disasters in the SQL Server 2008 parallel execution plans occurs on Query 5, Local Supplier Volume.
The query is:

/* 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 < CONVERT(DATE,DATEADD(YY,1,
‘1994-01-01’))

GROUP BY N_NAME

ORDER BY REVENUE DESC

Compare the CPU-ms and duration between SQL Server 2005 sp2 and 2008 RTM, both 64-bit, by DOP.

The non-parallel plan is shown below (plan cost 1412. 87).

The non-parallel plan starts with the Nation and Region tables to identify which customers are of interest,
then joining to Orders and Lineitem, and finally joining as the inner source to Supplier.

The parallel plan is below (plan cost 1142.17).

The parallel plan starts with the date range on the Orders table,
joins Lineitem, then joining to successively as the inner source to Customers, Nation, Region and Supplier.
next joins to before to all and Region tables to identify which customers are of interest.

In both cases, the estimate number of row involved is 1.378M,
but because in the parallel plan the join to Region occurs late,
the estimated 9M rows in Lineitem that meet the Orders date range are carried for three hash joins before being eliminated.

The non-parallel and parallel plan summary details are shown below.
The parallel plan does indeed have a lower plan cost.

The main elements of the non-parallel plan

The main elements of the parallel plan

The IO costs for explicit tables and index are the same for non-parallel and parallel plans.
The CPU cost in a parallel plan is reduced by a factor of two in a DOP 2 plan.
The DOP 4 plan has the same CPU cost as DOP 2.
Each successive doubling of DOP reduces CPU cost by a factor of 2 at least up to 16.
Anyone who can provide a system with a much larger number of cores can observe CPU cost pattern.

The MaxDOP 1 plan is essentially:

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

FROM SUPPLIER

INNER JOIN (

&nbspSELECT N_NATIONKEY, N_NAME, L_EXTENDEDPRICE, L_DISCOUNT, L_SUPPKEY

&nbspFROM NATION

&nbspINNER JOIN REGION ON N_REGIONKEY = R_REGIONKEY

&nbspINNER JOIN CUSTOMER ON C_NATIONKEY = N_NATIONKEY

&nbspINNER JOIN ORDERS ON C_CUSTKEY = O_CUSTKEY

&nbspINNER JOIN LINEITEM ON L_ORDERKEY = O_ORDERKEY

&nbspWHERE R_NAME = ‘ASIA’

&nbspAND O_ORDERDATE >= ‘1994-01-01’
&nbspAND O_ORDERDATE <
CONVERT(DATE,&nbspDATEADD(YY,1, ‘1994-01-01’))

) x ON L_SUPPKEY = S_SUPPKEY AND S_NATIONKEY = N_NATIONKEY

GROUP BY N_NAME

ORDER BY REVENUE DESC

OPTION (FORCE ORDER)

At MaxDOP 1, the actual CPU is 21,965 ms for the original query,
the MaxDOP 2 CPU is 28,721ms for the original.
The CPU for the forced query is 13,323.

So this one query added 15.4
CPU-sec to the total 22 query 324.3 CPU-sec, close to 5%, and about 8.0sec duration.

Query 8 was also bad news on the parallel plans,
with about 5 CPU-sec lost on the MaxDOP 2 parallel plan compared with a forced parallel plan modeled on the
non-parallel plan. One might think that MS should have caught these anomalies.
I think the reason they do not is that MS does not look at SF1-30 TPC-H
results. The minimum for publication is 100GB, and that will probably increase
to 300GB soon, because 30GB is not a real data warehouse. I do think MS should
look very carefully at SF1-30. The queries are at the onset of eligibility for
parallelism. The really big queries in SF100 and higher are
less likely to encounter plan problems. While not strictly a data
warehouse, most transactional databases I have seen do not remotely resemble
TPC-C or E. I would say most have TPC-H SF1-10 sized queries mixed in with
smaller transactions. So a bad execution plan can be really bad news.

I am sufficiently satisfied that
SQL Server 2008 has a very powerful engine, and a decent optimizer. However, I
have complained in the past about the rigid assumptions that all query
costs factor in IO time, the use of a fixed random to sequential IO performance
model (320 IOPS to 10.5MB/sec) and an out of balance IO-CPU ratios. If a proper
calibration of the true cost formulas were to be done, there would probably be
fewer silly mistakes resulting in goofy execution plans. Given that many
people do not know how to diagnose this type of problem, a simple test of 2000
or 2005 and 2008 can encounter this matter, leading to a decision to stay with
2000/2005, when a few simple adjustments would have corrected the 2008 results.

SQL Server Settings

Generally I follow the HP TPC-H
publications on optimization settings, particularly -E and -T834. Neither
changed results by more than 1% either way. I had also looked at -T2301 in the
past finding no apparent differences. I really would like MS to provide more
details on T2301. Are there set points below which it has no effect?

SQL Server 2008 new Date data type changes

The 3 datatime columns in the LineItem table from 2005 become
Date columns, for an apparent savings of 12 bytes. The 2005 tpch
SF10 database was 13.77GB (rather million KB) data and 3.68G indexes for a
total of 17.46G. In 2008, using the Date data type in place of datetime, the size is 12.77 data and 2.96G index for a
total of 15.74G. The average bytes per row of LineItem drops from 169 to 153, because one of
the DateTime/Date columns was the cluster key.

Nornally a simple reduction in size on column width, not row count, does not improve
performance unless it impacts fit in memory. I always try to exclude this
factor because one can generate any difference in performance by adjusting the
amount of disk IO.

The original TPC-H queries may have SARG of the form

AND O_ORDERDATE >= ‘1994-01-01’

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

Even before SQL 2008, the date
functions would return a datetime or smalldatetime result as appropriate. In SQL 2008, the
nature extension is to return a date type when the comparison is a date column.
I made this request in connect and was told to bugger off. So
SQL 2008 will convert the column to date time to equate with the function,
losing the benefit of a proper SARG. Anyone upgrading to SQL 2008
with the date type and not changing code as below may get a nasty suprise.

AND O_ORDERDATE >= ‘1994-01-01’

AND O_ORDERDATE <
CONVERT(DATE,DATEADD(YY,1,
‘1994-01-01’ ))

&nbsp

[Update 2009-11-27]

The TPC-H reports from 2009 on use the following:

AND O_ORDERDATE <
DATEADD(YY,1,
CAST(‘1994-01-01’ AS DATE))

which also has the desired effect.

Little things like this can cause people to refuse to budge from SQL 2000, which really needs to be retired.

Duration for SQL 2008 64-bit

Both are RTM

SQL Server 2000 sp4 + hf 2187 32-bit

my apologies, Linchi post SQL 2005 64-bit results, so my
duration results for SQL 2005 64-bit, SP2 (no cu) below

Huawei e5330 — 3G WiFi router up to 21 Mbps

• Description

• Properties

• Reviews (0)

Description

Huawei e5330 — 3G WiFi router up to 21 Mbps

3G WiFi router Huawei E5330 (also known as MTS 424d) is a universal device for organizing wireless Internet access for all mobile operators in Russia, Crimea, Sevastopol. Also, when traveling abroad, the E5330 mobile hotspot can be used in 3G UMTS 900/2100 networks worldwide.

Main advantages of E5330 3G WiFi router

E5330 3G WiFi router is quite easy to use. By pressing the power button, after 5 seconds, the router is ready to work. The ability to simultaneously access the Internet for up to 10 devices connected to the e5330 3G WiFi router makes the device an indispensable assistant at home, in the country or on a trip. Minimal bells and whistles: Several LED indicators on the front panel will tell you about the status of the mobile hotspot. More detailed information about the operation of the device is available to users of Android or Apple devices using Huawei’s proprietary HiLink utility.

Technical features of the 3G WiFi router E5330

The 3G WiFi router is configured to automatically work in 2G (GSM) 850/900/1800/1900 MHz and 3G (WCDMA/UMTS) 900/2100 MHz networks. To work in the networks of some operators, when connecting to the network for the first time, users need to go to the WEB user interface and create a profile with the operator’s settings.