SQL Server Performance

Help:EXCEPTION_ACCESS_VIOLATION

Discussion in 'Performance Tuning for DBAs' started by Jelly0228, Mar 25, 2004.

  1. Jelly0228 New Member

    Hello,
    I got a serious error when I used a UPDATE statement to update a table this morning.please help me.
    I also got a error message from the HP Server's screen:
    ----------------------------
    Single-bit
    Memory error in
    slot 1
    03/14/04 18:00
    ----------------------------

    This is the update statement :
    -------------------------------
    update pagodrm_miss
    set yymm=b.yymm,last_no=b.last_no,demo_no=b.demo_no,guest=b.guest,
    etd=b.etd,artic_no=b.artic_no,zx_qty=b.zx_qty,hzout_mk=b.hzout_mk,
    nos_mk=b.nos_mk,seq_ship=b.seq_ship
    from pagodrm_miss a,dispatch_miss b
    where a.podr_no=b.podr_no and b.oa_mk='Y' and
    b.podr_no in (
    select distinct podr_no from cdpag_miss
    union
    select distinct podr_no from djpag_miss
    );
    ----------------------------------
    But when i tried to udpate the 'poaodrm_miss' table use this statement,there is no error occurred:UPDATE pagodrm_miss set yymm='200403' where podr_no='AAAA';

    Following is the error log:
    =====================================================================
    BugCheck Dump
    =====================================================================

    This file is generated by Microsoft SQL Server 8.00.760
    upon detection of fatal unexpected error. Please return this file,
    the query or program that produced the bugcheck, the database and
    the error log, and any other pertinent information with a Service Request.

    Computer type is AT/AT COMPATIBLE.
    Current time is 08:15:50 03/26/04.
    4 Intel x86 level 6, 500 Mhz processor(s).
    Windows NT 5.0 Build 2195 CSD Service Pack 4.


    Memory
    MemoryLoad = 90%
    Total Physical = 2047 MB
    Available Physical = 184 MB
    Total Page File = 3941 MB
    Available Page File = 2119 MB
    Total Virtual = 2047 MB
    Available Virtual = 243 MB

    *Stack Dump being sent to D:program FilesMicrosoft SQL ServerMSSQLlogSQLDu
    mp0001.txt
    * *****************************************************************************
    **
    *
    * BEGIN STACK DUMP:
    * 03/26/04 08:15:50 spid 86
    *
    * Exception Address = 00526FB5 (all_expand + 0000005B Line 0+00000000)
    * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
    * Access Violation occurred writing address 0000002A
    * Input Buffer 4088 bytes -
    *
    *
    * MODULE BASE END SIZE
    * sqlservr 00400000 00B2CFFF 0072d000
    * ntdll 77F80000 77FFAFFF 0007b000
    * KERNEL32 77E60000 77F38FFF 000d9000
    * ADVAPI32 796D0000 79731FFF 00062000
    * RPCRT4 786F0000 7875DFFF 0006e000

    Thanks for any help!

    Jelly.
  2. derrickleggett New Member

    You need to run a hardware diagnostic on your server. Use the Insight Manager tools to run a hardware diagnostic on it. They take quite a while to run, so just run the high-level memory tests first. If that doesn't show anything, you will want to run a full.

    If the computer is still under warranty, call HP customer service immediately.

    Couple questions. Have you changed anything about the server or added any new programs lately?

    Derrick Leggett

  3. Jelly0228 New Member

    Derrick,
    Many thanks for your help.I didn't change anything about the server .
    I guess maybe the memory error made the excption. what's the mean of these record?
    ----------------------------------
    Memory
    MemoryLoad = 90%
    Total Physical = 2047 MB
    Available Physical = 184 MB
    Total Page File = 3941 MB
    Available Page File = 2119 MB
    Total Virtual = 2047 MB
    Available Virtual = 243 MB
    ------------------------------------
    It seems to show the total Physical memory of HP server is 2047 MB,right??But in fuct,It has 2 GB memory .



    Jelly.
  4. satya Moderator

  5. Jelly0228 New Member

    Satya,
    The SQL 's service pack is sp3,the OS's service pack is sp4.
    Thanks!

    Jelly.
  6. satya Moderator

    Can you just explain a bit more about h/w of SQL Server?

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  7. derrickleggett New Member

    Have you gotten the error since it happened last time? Have you ran a hardware diagnostic yet?

    Derrick Leggett

  8. Jelly0228 New Member

    Derrick,
    I called the HP service suport yesterday,The man asked me that try to dust the memories;the server didn't show any error message after i did it,and everything is ok.But this morning ,the server stoped work and made no response !Hence I restarted the server.Unfortunately one of database can't work.I had to reconvey the db.I also got an other error message in the HP Server's screen:
    ------------------
    Processor 3
    Internal error
    2004/03/27 10:08:00
    ------------------
    I think I will have a test for hardware,I have a CD that about HP Netserver Navigator,but I can't run it in Windows.would you commend me some nice tools for test?
    Thanks!

    Satya,
    Sorry I didn't understand about 'h/w',what mean?
    Thanks!



    Jelly.
  9. Jelly0228 New Member

    Oh,I can test hardware with HP Netserver Navigator CD.Now I am testing.....


    Jelly.
  10. Jelly0228 New Member

    Hi,
    I think maybe that isn't any hardware made this exception.To try to run following code,you will get the same error message:

    -------------Create table-----
    create table aa (podr_no char(10),style_no char(10))
    go
    create table bb (podr_no char(10),demo_no char(10))
    go
    create table cc (podr_no char(10),style_no char(10))
    go
    create table dd (podr_no char(10),qty int)
    go

    --------------Insert into rows to the tables---------
    insert into AA values('JELLY','TEST')
    insert into AA values('JELLY01','TEST01')
    insert into AA values('JELLY02','TEST02')

    insert into BB values('JELLY','PCN-17')
    insert into BB values('JELLY01','PCN-17')

    insert into CC values('JELLY','A-JELLY')
    insert into CC values('JELLY02','B-JELLY')

    insert into DD values('JELLY',110)
    insert into DD values('JELLY01',101)
    insert into DD values('JELLY02',102)

    ---------------Run this UPDATE statement---
    update AA set style_no=b.style_no
    from AA a,CC b
    where a.podr_no=b.podr_no and b.podr_no in
    ( select distinct podr_no from bb
    union
    select distinct podr_no from dd )

    Result: you will receive the same error message!(Please to view SQL Server error log. )
    I know this statement isn't nice for perfomance reason.but as you know,we can't limit the developers of applictions that how to write their SQL statement.

    Jelly.
  11. satya Moderator

  12. derrickleggett New Member

    we can't limit the developers of applictions that how to write their SQL statement.

    Why??? Someone needs to. The development managers wouldn't let them write horribly inneficient application code would they?

    Derrick Leggett

  13. satya Moderator

    If your developers cannot follow what has been observed, then its duty of a DBA to rectify it by referring the concerned material.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  14. Chappy New Member

    Nice in theory, but in my experience theres often one dba/developer to many developers. Not enough hours in the day to rework every query unless its causing specific issues
  15. satya Moderator

    I agree, its not an easy task to achieve but atleast you can make them understand the situation.
    In my exp. a gaint leap will be started with a small step, always.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  16. derrickleggett New Member

    Most developers will write better code if you show them the performance and usability increase from bad code. They will then train others. Our developers write much better code than when I got there. They outnumber me and the other DBA 25-1.

    If you can't get them to write better code, all the tuning in the world is just delaying the inevitable. You might as well just go home.

    Derrick Leggett

  17. Jelly0228 New Member

    Thanks all the pepole.I will try to do it!

    Jelly.

Share This Page