Conference amcucs::ms-sqlsvr

353.0. "Alpha and Intel-Different Optimizer Behavior?" by GUIDUK::HEALY (Alan Healy @ZSO) Fri Mar 21 1997 15:20

    I am talking to a customer who claims that the behavior of SQL Server
    on an Alpha is different than on an Intel for the same SQL statement in
    the same database.  In addition, the behavior of the Alpha version
    seems very strange.

    He is running SQL V6.0 (no service packs) on NT 3.51 (no service
    packs).  He is using a series of "Update" statements to update a field
    that is indexed.  The statements are:

	UPDATE CD_CUSTOMER SET OCCUP_CD = '1' WHERE OCCUP_CD = 'SALES'
	GO
	UPDATE CD_CUSTOMER SET OCCUP_CD = '2' WHERE OCCUP_CD = 'RETIR'
	GO
	UPDATE CD_CUSTOMER SET OCCUP_CD = '3' WHERE OCCUP_CD = 'STUD'
	GO
	UPDATE CD_CUSTOMER SET OCCUP_CD = '4' WHERE OCCUP_CD = 'MLTRY'
	GO

    OCCUP_CD is indexed with apparently lots of duplicates. It is a large
    table (lots of rows).

    He claims that on an Intel server it uses the index on OCCUP_CD every
    time (according to SHOWPLAN) and that each update is fairly quick.

    On the Alpha, however, the first statement results in a sequential scan
    (which of course takes a long time), but the subsequent statements use
    the index.

    I was under the impression that SQL on Alpha is the exact same code as
    SQL on Intel so the results should be the same.  Is this not true? 
    Also, I was not aware that the SQL optimizer would pick a different
    plan on subsequent queries unless the index statistics were updated in
    between.

    Has anyone else seen similar behavior?

    By the way, the reason he is using 6.0 is that he got ODBC errors on
    the client when running against the Alpha database under 6.5 (and
    naturally, didn't get the errors with the Intel).  I've asked for more
    detail on this problem, but he is reluctant to do the work required to
    recreate the conditions.   (I already got him to downgrade to 6.0 on
    the Intel to be sure that the same optimizer behavior wasn't manifested
    on the Intel).
    
    I have suggested that they try to get some sort of official support
    from someone who knows both Alpha and Intel SQL (preferably Digital),
    but I thought I would check to see if there are any known problems of
    this sort.  I did a couple of searches with Comet but didn't find
    anything close.

	Al
    
    

    
    I am not aware of anything specific that could be causing the problems. 
    
    However, I would recommend he get to the latest service packs on 
    both SQL 6.0 and NT 3.51 to make sure there aren't any outstanding
    issues that those service packs may be able to address. 
    
    If he does an sp_configure on both machines, how do they compare? 
    
    Remember that Alpha systems do require more memory than Intel.  And 
    if he has the same amount of memory allocated to the Intel and the 
    Alpha machines, we could see some differences in performance.  Although
    I would be surprised if the optimizer would behave differently. 
    
    Make sure the db size (device size and the db size itself) are the 
    same on both machines.  And compare the numbers of records.  
    
    Mary Jo 
    

>>    On the Alpha, however, the first statement results in a sequential scan
>>    (which of course takes a long time), but the subsequent statements use
>>    the index.

	This was a known problem on the Alpha SQL Server for V4.2.  It
	happened if a db was built without indices, and the indices were
	dynamically added afterwards.  I don't know if the fix made it
	into the first release of V6.0.  I agree with Mary Jo that the
	latest service packs should be applied.  Or, if your customer wants
	to see a load take a real long time, he can define the db with the
	indices and then load the data.  Then the indices will be used.

tgc