SQL on Alpha or Intel?

SQL on Alpha or Intel?

Post by Carl Ray » Tue, 28 Nov 1995 04:00:00



Here's an interesting problem. We're evaluating SQL 6.0 on two
machines, one a Pentium 590 with 64 MB of Memory, the other an Alpha
2100 with 190 MB of memory, that runs at 250 MHz.

Both are running NT 3.51 and SQL 6.0. We created a database on the
Compaq, and pointed a WfW 3.11 PC (ODBC) client at it to run a query
from Excel 5. This query takes about 30 minutes to complete. Then we
copied the database to the Alpha (using the Transfer Facility). Then
reran the ODBC stuff, changing the data-source to the Alpha.

The expectation was that the Alpha would completely out-perform the
Compaq.  HOWEVER ... quite the reverse: the Compaq is consistently
faster (more than double).  Can anyone suggest why, or whether the
assumptions made in the test are erroneous?

After the first batch of tests, we tried to optimise the Alpha: we
increased the base priority of SQLserver threads, moved TempDB to RAM,
and increased the memory of the SQLserver to 50 MB (the default is 16
MB). The compaq received none of these benefits, but nor did we see a
corresponding increase in performance.

Indeed, when we ran Performance Monitor, there was no disk i/o going
on, and the processor was essentially quiet (less than 4%).

Can anyone suggest a cause for all this?

regards

Carl

 
 
 

SQL on Alpha or Intel?

Post by Leonard Lind » Wed, 29 Nov 1995 04:00:00



> Here's an interesting problem. We're evaluating SQL 6.0 on two
> machines, one a Pentium 590 with 64 MB of Memory, the other an Alpha
> 2100 with 190 MB of memory, that runs at 250 MHz.

> Both are running NT 3.51 and SQL 6.0. We created a database on the
> Compaq, and pointed a WfW 3.11 PC (ODBC) client at it to run a query
> from Excel 5. This query takes about 30 minutes to complete. Then we
> copied the database to the Alpha (using the Transfer Facility). Then
> reran the ODBC stuff, changing the data-source to the Alpha.

> The expectation was that the Alpha would completely out-perform the
> Compaq.  HOWEVER ... quite the reverse: the Compaq is consistently
> faster (more than double).  Can anyone suggest why, or whether the
> assumptions made in the test are erroneous?

..text deleted...
> Indeed, when we ran Performance Monitor, there was no disk i/o going
> on, and the processor was essentially quiet (less than 4%).

We use Access and ODBC as a front end to SQL 6.0.  I'm assuming that the
ODBC tool you're using has the same limitation as Access, namely, that
there are a number of queries which it will process locally (i.e., on
the client PC). If the query is processed locally, the CPU on the
client is probably the limiting factor. (This would explain why the
client is working but the server is doing essentially nothing.)

Quoting from Microsoft ("Accessing External Data from MS Access"
on Technet):

"Query operations performed locally include:

WHERE clause restrictions on top of a query with a DISTINCT.
WHERE clauses containing operations that cant be processed
remotely, such as user-defined functions that involve remote columns.
(Note that, in this case, only the parts of the WHERE clause that
cannot be processed remotely will be processed locally.)
Joins between tables from different data sources.
(Its important to note, however, that simply having joins between
tables from different data sources doesnt mean that all of the
processing occurs locally. if restrictions are sent to the server,
only relevant rows will be processed locally.)
Joins over aggregation or the DISTINCT clause.
Outer joins containing syntax not supported by the ODBC driver.
DISTINCT clauses containing operations that cant be processed remotely.
ORDER BY expressions (if the remote data source doesnt support them).
ORDER BY clauses containing operations that cant be processed remotely.
Multiple-level GROUP BY clauses, such as those used in reports with multiple
grouping levels.
GROUP BY clauses on top of a query with a DISTINCT option.
GROUP BY clauses containing operations that cant be processed remotely.
Crosstab queries that have more than one aggregate or that have an
ORDER BY clause that matches the GROUP BY clause.
TOP N or TOP N PERCENT."

Hope this helps..

Leonard Linde


 
 
 

SQL on Alpha or Intel?

Post by Simon Walke » Wed, 29 Nov 1995 04:00:00


Quote:> Here's an interesting problem. We're evaluating SQL 6.0 on two
> machines, one a Pentium 590 with 64 MB of Memory, the other an Alpha
> 2100 with 190 MB of memory, that runs at 250 MHz.

> Both are running NT 3.51 and SQL 6.0. We created a database on the
> Compaq, and pointed a WfW 3.11 PC (ODBC) client at it to run a query
> from Excel 5. This query takes about 30 minutes to complete. Then we
> copied the database to the Alpha (using the Transfer Facility). Then
> reran the ODBC stuff, changing the data-source to the Alpha.

This query takes 30 mins think time in SQL Server before it returns to
the client?  Or it takes 30 mins to drag a large result set back to the
PC?  Did you run the query locally on the servers to isolate any network
issues?  

Quote:> The expectation was that the Alpha would completely out-perform the
> Compaq.  HOWEVER ... quite the reverse: the Compaq is consistently
> faster (more than double).  Can anyone suggest why, or whether the
> assumptions made in the test are erroneous?

This is interesting, as my client runs Compaq Proliant gear but is
historically a big DEC (sorry, Digital) customer.  DEC salesman persuaded
them (ie. me) to benchmark a 2100.  As the live Compaq server was a dual
P66 with 200Mb RAM and the Alpha was  quad 250Mhz with 1.5Gb RAM we saw
an improvement, but not nearly the gain we expected.  In-depth
examination of this was precluded by the Alpha's power supply
self-destructing (for the second time) and we just sent it back.
I do know that in many tests we were able to factor out the IO subsystem
as queries ran entirely from SQL Server data cache.  

Quote:> After the first batch of tests, we tried to optimise the Alpha: we
> increased the base priority of SQLserver threads, moved TempDB to RAM,
> and increased the memory of the SQLserver to 50 MB (the default is 16
> MB). The compaq received none of these benefits, but nor did we see a
> corresponding increase in performance.

> Indeed, when we ran Performance Monitor, there was no disk i/o going
> on, and the processor was essentially quiet (less than 4%).

> Can anyone suggest a cause for all this?

> regards

> Carl

Is it possible that the Alpha version of NT and SQL are just not taking
advantage of the hardware as much as they might?  

Sorry I can't shed any light on your query, but I can at least
corroborate it to a certain extent!  

Cheers,
simon walker
The SQL Workshop
Milton Keynes, UK

 
 
 

SQL on Alpha or Intel?

Post by Joshua Guttma » Wed, 29 Nov 1995 04:00:00


The only thing I can suggest is to update statistics on all the indexes.
Also, the transfer utility rarely works without error. Do a dbcc on the
databases and look for currupt tables.
--
        _     ___
/-|  | |_ |\ | | |  | |\ | |/  Joshua Guttman - Senior Software Engineer

                               (404)663-8963 x136 Office
 
 
 

SQL on Alpha or Intel?

Post by Brian Jay Gou » Fri, 01 Dec 1995 04:00:00



... stuff deleted...

Quote:>Both are running NT 3.51 and SQL 6.0. We created a database on the
>Compaq, and pointed a WfW 3.11 PC (ODBC) client at it to run a query
>from Excel 5. This query takes about 30 minutes to complete. Then we
>copied the database to the Alpha (using the Transfer Facility). Then
>reran the ODBC stuff, changing the data-source to the Alpha.

This will tend to test the network, the ODBC implementation, and PC
performance and overall negate the performance measurement from the
Server.

Quote:>The expectation was that the Alpha would completely out-perform the
>Compaq.  HOWEVER ... quite the reverse: the Compaq is consistently
>faster (more than double).  Can anyone suggest why, or whether the
>assumptions made in the test are erroneous?

See above.    A valid comparison would be an ISQL/W query on the same
system with both systems generating the same query plan (validated
with showplan on).

Quote:>After the first batch of tests, we tried to optimise the Alpha: we
>increased the base priority of SQLserver threads, moved TempDB to RAM,
>and increased the memory of the SQLserver to 50 MB (the default is 16
>MB). The compaq received none of these benefits, but nor did we see a
>corresponding increase in performance.

None of these things should have had any effect on performance unless
you were cache bound or doing tempdb I/O which doesn't seem to be the
case.  The thread priority could theoretically have be preempting the
network tasks - thus slowing total performance by your measurement.

Quote:>Indeed, when we ran Performance Monitor, there was no disk i/o going
>on, and the processor was essentially quiet (less than 4%).

What is most likely is tha the PM is not showing real disk I/O.  Does
the controller light flash?  Can you hear disk activity?

Quote:>Can anyone suggest a cause for all this?

See above.

---------------------------------------

On the net since 1985:  "I've been flamed by better than you!"
==============================================================

 
 
 

SQL on Alpha or Intel?

Post by C. Ye » Sat, 02 Dec 1995 04:00:00


..clip...
Quote:>The expectation was that the Alpha would completely out-perform the
>Compaq.  HOWEVER ... quite the reverse: the Compaq is consistently
>faster (more than double).  Can anyone suggest why, or whether the
>assumptions made in the test are erroneous?
..clip...
>Can anyone suggest a cause for all this?

This is more of a confirmation than a definitive answer, but I had
a similar experience ... not with SQL 6.0 but 4.2.  The test I ran was
against a simple table of (US) zip codes of 180,000 occurrances.

I did not have the orders of magnitude differences that you did, but
a DEC P60 returned the same results as the Alpha 235, same indexes on
both tables.

The explanation that I got from MS PSS was that, because the Alpha is
a RISC processor, the SQL code set is larger than CISC (Intel.)

We ended up going with the Alpha anyway.  Now I'm beginning to wonder.

 
 
 

SQL on Alpha or Intel?

Post by Scott Zimmerma » Sun, 10 Dec 1995 04:00:00


Quote:> The expectation was that the Alpha would completely out-perform the
> Compaq.  HOWEVER ... quite the reverse: the Compaq is consistently
> faster (more than double).  Can anyone suggest why, or whether the
> assumptions made in the test are erroneous?

What about network configuration differences between the two? Named
pipes vs. TCP, NIC performance, etc.
Transaction log turned on both times?