|
|
Posted by Taw on 12/05/07 14:03
Hi,
The following query which used to run regularly is crashing the
server. The query is run on two tables which has aroung 60 million
records each.
Of course the query can be broken up and optimized I am just
interested to know if any one has any clue why this is happening?
DB: SQL Server 2000 enterprise edition SP4
OS: Windows 2003 enterprise edition
Error Log Message:
----------------------------
SqlDumpExceptionHandler: Process 5396 generated fatal exception
c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this
process.
*
*******************************************************************************
*
* BEGIN STACK DUMP:
* 12/05/07 19:35:09 spid 53
*
* Exception Address = 004245C9
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000000
* Input Buffer 1890 bytes -
* select a.XXX_Name,F_Seq_No,S_Seq_No, case wh
* en (cast(S_Seq_No as bigint)-cast(F_Seq_No as bigint))=1 then 'OK'
else
* 'Prob' end as CCCC from ( select case when
right(left(filename,5),
* 2)>10 then left(filename,9) else left(filename,5)end as XXX_Name,
max(
* left(right(filename,17),4)) as F_Seq_No from (SELECT distinct
filename
* FROM dbo.XXX20071201)t group by case when right(left(filename,5),
2)>10
* then left(filename,9) else left(filename,5)end ) as a left join
( s
* elect case when right(left(filename,5),2)>10 then left(filename,
9) els
* e left(filename,5)end as XXX_Name, min(left(right(filename,17),
4)) as
* S_Seq_No from (SELECT distinct filename FROM
dbo.XXX20071202_online)s
* group by case when right(left(filename,5),2)>10 then left(filename,
9) e
* lse left(filename,5)end )as b on a.XXX_Name=b.XXX_Name order by
a.XXX
* _Name
*
*
* MODULE BASE END SIZE
* sqlservr 00400000 00CBAFFF 008bb000
* ntdll 7C800000 7C8BFFFF 000c0000
* kernel32 77E40000 77F41FFF 00102000
* ADVAPI32 77F50000 77FEAFFF 0009b000
* RPCRT4 77C50000 77CEEFFF 0009f000
* Secur32 76F50000 76F62FFF 00013000
* MSVCP71 7C3A0000 7C41AFFF 0007b000
* MSVCR71 7C340000 7C395FFF 00056000
* opends60 41060000 41065FFF 00006000
* SHELL32 7C8D0000 7D0CDFFF 007fe000
* msvcrt 77BA0000 77BF9FFF 0005a000
* GDI32 77C00000 77C47FFF 00048000
* USER32 77380000 77410FFF 00091000
* SHLWAPI 77DA0000 77DF1FFF 00052000
* sqlsort 42AE0000 42B6FFFF 00090000
* ums 41070000 4107DFFF 0000e000
* comctl32 77420000 77522FFF 00103000
* sqlevn70 41080000 4108AFFF 0000b000
* NETAPI32 02CA0000 02CF6FFF 00057000
* AUTHZ 02D00000 02D13FFF 00014000
* COMRES 031B0000 03275FFF 000c6000
* ole32 03380000 034B8FFF 00139000
* XOLEHLP 03540000 03545FFF 00006000
* MSDTCPRX 03550000 035C8FFF 00079000
* OLEAUT32 035D0000 0365AFFF 0008b000
* msvcp60 03660000 036C4FFF 00065000
* MTXCLU 036D0000 036E8FFF 00019000
* VERSION 036F0000 036F7FFF 00008000
* WSOCK32 03700000 03708FFF 00009000
* WS2_32 03710000 03726FFF 00017000
* WS2HELP 03730000 03737FFF 00008000
* CLUSAPI 03780000 03791FFF 00012000
* RESUTILS 037A0000 037B2FFF 00013000
* USERENV 037C0000 03881FFF 000c2000
* mswsock 038A0000 038E0FFF 00041000
* DNSAPI 038F0000 03919FFF 0002a000
* winrnr 03960000 03966FFF 00007000
* WLDAP32 03970000 0399DFFF 0002e000
* rasadhlp 039C0000 039C4FFF 00005000
* SSNETLIB 00E50000 00E65FFF 00016000
* NTMARTA 00E80000 00EA0FFF 00021000
* SAMLIB 00EB0000 00EBEFFF 0000f000
* security 044B0000 044B3FFF 00004000
* hnetcfg 044C0000 04519FFF 0005a000
* wshtcpip 04A60000 04A67FFF 00008000
* SSmsLPCn 04B70000 04B77FFF 00008000
* SSnmPN70 04BA0000 04BA6FFF 00007000
* ntdsapi 04C30000 04C43FFF 00014000
* kerberos 04C80000 04CD7FFF 00058000
* cryptdll 04D70000 04D7BFFF 0000c000
* MSASN1 04D80000 04D91FFF 00012000
* rsaenh 048E0000 04914FFF 00035000
* PSAPI 00F30000 00F3AFFF 0000b000
* msv1_0 04930000 04956FFF 00027000
* iphlpapi 051C0000 051D9FFF 0001a000
* xpsp2res 051E0000 054A4FFF 002c5000
* CLBCatQ 054B0000 05532FFF 00083000
* SQLOLEDB 05550000 055D0FFF 00081000
* MSDART 055E0000 055F9FFF 0001a000
* MSDATL3 05600000 05614FFF 00015000
* oledb32 05BA0000 05C18FFF 00079000
* OLEDB32R 05C20000 05C30FFF 00011000
* xpstar 05C40000 05C8CFFF 0004d000
* SQLRESLD 05C90000 05C9BFFF 0000c000
* SQLSVC 05CA0000 05CBAFFF 0001b000
* ODBC32 05CC0000 05CFCFFF 0003d000
* COMCTL32 05D00000 05D96FFF 00097000
* comdlg32 05DA0000 05DE8FFF 00049000
* odbcbcp 05DF0000 05DF5FFF 00006000
* W95SCM 05E00000 05E0CFFF 0000d000
* SQLUNIRL 05E10000 05E3CFFF 0002d000
* WINSPOOL 05E40000 05E66FFF 00027000
* SHFOLDER 05E70000 05E78FFF 00009000
* odbcint 06150000 06166FFF 00017000
* NDDEAPI 06280000 06287FFF 00008000
* SQLSVC 06290000 06295FFF 00006000
* xpstar 062A0000 062A8FFF 00009000
* ACTIVEDS 063B0000 063E3FFF 00034000
* adsldpc 063F0000 06417FFF 00028000
* credui 06420000 0644DFFF 0002e000
* ATL 06450000 06467FFF 00018000
* adsldp 064D0000 064FDFFF 0002e000
* SXS 06680000 0673CFFF 000bd000
* xplog70 06740000 0674EFFF 0000f000
* xplog70 06750000 06754FFF 00005000
* xpsqlbot 041C0000 041C5FFF 00006000
* dbghelp 06D70000 06E6FFFF 00100000
*
* Edi: 2D8CB018: 00000000 00000000 00000000 00001F00
07000000 8B008300
* Esi: 2D8CA098: 2D8CA020 629222C8 00000000 00000014
2D8CB8B8 2D8CA9C0
* Eax: 00000000:
* Ebx: 00000000:
* Ecx: 2D8C80C0: 620A1220 2D8C8618 2D8C8618 2D8C80CC
2D8C80CC 2D8C8038
* Edx: 0696FED8: 00000000 06000000 005F87B3 00A99F4C
4107311B 000DBBA0
* Eip: 004245C9: FF50088B 45C60851 646705FC 002C168B
488B028B A8558B08
* Ebp: 0696EBD4: 0696EBE4 0087B831 0696FEB4 2D8CA688
0696FEC0 0087E635
* SegCs: 0000001B:
* EFlags: 00010286: 00690057 0064006E 0077006F 005F0073
0054004E 00500000
* Esp: 0696EB70: 2D8CA098 2D8CA688 0087E5B5 0696FED8
7C000000 005F87B3
* SegSs: 00000023:
*
*******************************************************************************
*
-------------------------------------------------------------------------------
* Short Stack Dump
* 004245C9 Module(sqlservr+000245C9)
* 0087B831 Module(sqlservr+0047B831) (GetIMallocForMsxml+00086CB1)
* 0087E635 Module(sqlservr+0047E635) (GetIMallocForMsxml+00089AB5)
* 0055C692 Module(sqlservr+0015C692)
* 41075309 Module(ums+00005309) (ProcessWorkRequests+000002D9 Line
456+00000000)
* 41074978 Module(ums+00004978) (ThreadStartRoutine+00000098 Line
263+00000007)
* 7C34940F Module(MSVCR71+0000940F) (endthread+000000AA)
* 77E64829 Module(kernel32+00024829) (GetModuleHandleA+000000DF)
*
-------------------------------------------------------------------------------
*Dump thread - spid = 53, PSS = 0x620a1220, EC = 0x620a1550
*Stack Dump being sent to d:\datafiles\MSSQL\log\SQLDump0044.txt
*
*******************************************************************************
*
* BEGIN STACK DUMP:
* 12/05/07 19:35:09 spid 53
*
* StackDump ()
*
* Input Buffer 1890 bytes -
* select a.XXX_Name,F_Seq_No,S_Seq_No, case wh
* en (cast(S_Seq_No as bigint)-cast(F_Seq_No as bigint))=1 then 'OK'
else
* 'Prob' end as CCCC from ( select case when
right(left(filename,5),
* 2)>10 then left(filename,9) else left(filename,5)end as XXX_Name,
max(
* left(right(filename,17),4)) as F_Seq_No from (SELECT distinct
filename
* FROM dbo.XXX20071201)t group by case when right(left(filename,5),
2)>10
* then left(filename,9) else left(filename,5)end ) as a left join
( s
* elect case when right(left(filename,5),2)>10 then left(filename,
9) els
* e left(filename,5)end as XXX_Name, min(left(right(filename,17),
4)) as
* S_Seq_No from (SELECT distinct filename FROM
dbo.XXX20071202_online)s
* group by case when right(left(filename,5),2)>10 then left(filename,
9) e
* lse left(filename,5)end )as b on a.XXX_Name=b.XXX_Name order by
a.XXX
* _Name
*
*
* Edi: 00000000:
* Esi: 000004BC:
* Eax: 00000000:
* Ebx: 002A76A0: 41076770 00000005 00000002 00000000
00F7FBB4 00000004
* Ecx: 00000000:
* Edx: 00000000:
* Eip: 7C8285EC: 24A48DC3 00000000 0024648D 0824548D
55C32ECD A48DEC8B
* Ebp: 048DE088: 002A76F8 41074AEE 000004BC FFFFFFFF
00000001 002AE540
* SegCs: 0000001B:
* EFlags: 00000246:
* Esp: 048DE018: 7C827D0B 77E61D1E 000004BC 00000001
00000000 77E61C96
* SegSs: 00000023:
*
*******************************************************************************
*
-------------------------------------------------------------------------------
* Short Stack Dump
* 7C8285EC Module(ntdll+000285EC) (KiFastSystemCallRet+00000000)
* 41074AEE Module(ums+00004AEE) (UmsThreadScheduler::Switch+0000004E
Line 490+0000000A)
* 410729F9 Module(ums+000029F9) (UmsScheduler::IdleLoop+00000119 Line
1373+0000000D)
* 41072B26 Module(ums+00002B26) (UmsScheduler::Suspend+00000066 Line
1182+00000000)
* 4107311B Module(ums+0000311B) (UmsEvent::Wait+0000007B Line
320+00000000)
* 00401261 Module(sqlservr+00001261)
* 0042341D Module(sqlservr+0002341D)
* 00854827 Module(sqlservr+00454827) (GetIMallocForMsxml+0005FCA7)
* 008554FB Module(sqlservr+004554FB) (GetIMallocForMsxml+0006097B)
* 005FA6E2 Module(sqlservr+001FA6E2) (SQLExit+00092BEB)
* 00429EAA Module(sqlservr+00029EAA)
* 00415D04 Module(sqlservr+00015D04)
* 00416214 Module(sqlservr+00016214)
* 00415F28 Module(sqlservr+00015F28)
* 0049C32E Module(sqlservr+0009C32E)
* 0049C46A Module(sqlservr+0009C46A)
* 41075309 Module(ums+00005309) (ProcessWorkRequests+000002D9 Line
456+00000000)
* 41074978 Module(ums+00004978) (ThreadStartRoutine+00000098 Line
263+00000007)
* 7C34940F Module(MSVCR71+0000940F) (endthread+000000AA)
* 77E64829 Module(kernel32+00024829) (GetModuleHandleA+000000DF)
*
-------------------------------------------------------------------------------
***Unable to get thread context for spid 53
***Unable to get thread context for spid 53
***Unable to get thread context for spid 53
***Unable to get thread context for spid 53
***Unable to get thread context for spid 53
***Unable to get thread context for spid 53
*Dump thread - spid = 53, PSS = 0x620a1220, EC = 0x2d8c80c0
*Stack Dump being sent to d:\datafiles\MSSQL\log\SQLDump0044.txt
*
*******************************************************************************
*
* BEGIN STACK DUMP:
* 12/05/07 19:35:10 spid 53
*
* StackDump ()
*
* Input Buffer 1890 bytes -
* select a.XXX_Name,F_Seq_No,S_Seq_No, case wh
* en (cast(S_Seq_No as bigint)-cast(F_Seq_No as bigint))=1 then 'OK'
else
* 'Prob' end as CCCC from ( select case when
right(left(filename,5),
* 2)>10 then left(filename,9) else left(filename,5)end as XXX_Name,
max(
* left(right(filename,17),4)) as F_Seq_No from (SELECT distinct
filename
* FROM dbo.XXX20071201)t group by case when right(left(filename,5),
2)>10
* then left(filename,9) else left(filename,5)end ) as a left join
( s
* elect case when right(left(filename,5),2)>10 then left(filename,
9) els
* e left(filename,5)end as XXX_Name, min(left(right(filename,17),
4)) as
* S_Seq_No from (SELECT distinct filename FROM
dbo.XXX20071202_online)s
* group by case when right(left(filename,5),2)>10 then left(filename,
9) e
* lse left(filename,5)end )as b on a.XXX_Name=b.XXX_Name order by
a.XXX
* _Name
*
*
* Edi: 2D8C80C0: 620A1220 2D8C8618 2D8C8618 2D8C80CC
2D8C80CC 2D8C8038
* Esi: 620A1220: 00040035 00000000 01417BB7 000099FB
00000000 00000000
* Eax: 00000000:
* Ebx: 0696E080: 00130D60 24203D20 20706265 20305424
2D203032 3D205E20
* Ecx: 0696D178: 00000000 0001003F 00000000 00000000
00000000 00000000
* Edx: 0011E878: 00000000 620A1220 2D8C80C0 00000002
0003002B 000E01DD
* Eip: 009BA08C: 26EB0088 90909090 EC458B90 83E47D8B
B3B904C7 8B000000
* Ebp: 0696D0FC: 0696D93C 009BA9B5 620A1220 00BCD818
2D8C80C0 0000020B
* SegCs: 0000001B:
* EFlags: 00010246: 00530055 007E0054 00000031 0055004E
0042004D 00520045
* Esp: 0696D0D4: 2D8C80C0 620A1220 0696E080 0696D178
0696D0D4 0696CD00
* SegSs: 00000023:
*
*******************************************************************************
*
-------------------------------------------------------------------------------
* Short Stack Dump
* 009BA08C Module(sqlservr+005BA08C) (GetOSErrString+00004F68)
* 009BA9B5 Module(sqlservr+005BA9B5) (GetOSErrString+00005891)
* 00A59EA2 Module(sqlservr+00659EA2) (CDStream::PrintDumpHeader
+0009ADC6)
* 009B9F56 Module(sqlservr+005B9F56) (GetOSErrString+00004E32)
* 009BA259 Module(sqlservr+005BA259) (GetOSErrString+00005135)
* 00A9A049 Module(sqlservr+0069A049) (CDStream::PrintDumpHeader
+000DAF6D)
* 7C359C97 Module(MSVCR71+00019C97) (_CxxLongjmpUnwind+000000EA)
* 7C359629 Module(MSVCR71+00019629) (_CxxExceptionFilter+000001C4)
* 7C3598AD Module(MSVCR71+000198AD) (_CxxExceptionFilter+00000448)
* 7C35994A Module(MSVCR71+0001994A) (_CxxExceptionFilter+000004E5)
* 7C359B9D Module(MSVCR71+00019B9D) (_CxxFrameHandler+00000026)
* 7C828752 Module(ntdll+00028752) (RtlRaiseStatus+000000E0)
* 7C828723 Module(ntdll+00028723) (RtlRaiseStatus+000000B1)
* 7C82855E Module(ntdll+0002855E) (KiUserExceptionDispatcher+0000000E)
* 0087B831 Module(sqlservr+0047B831) (GetIMallocForMsxml+00086CB1)
* 0087E635 Module(sqlservr+0047E635) (GetIMallocForMsxml+00089AB5)
* 0055C692 Module(sqlservr+0015C692)
* 41075309 Module(ums+00005309) (ProcessWorkRequests+000002D9 Line
456+00000000)
* 41074978 Module(ums+00004978) (ThreadStartRoutine+00000098 Line
263+00000007)
* 7C34940F Module(MSVCR71+0000940F) (endthread+000000AA)
* 77E64829 Module(kernel32+00024829) (GetModuleHandleA+000000DF)
*
-------------------------------------------------------------------------------
***Unable to get thread context for spid 53
***Unable to get thread context for spid 53
***Unable to get thread context for spid 53
***Unable to get thread context for spid 53
***Unable to get thread context for spid 53
***Unable to get thread context for spid 53
***Unable to get thread context for spid 53
***Unable to get thread context for spid 53
***Unable to get thread context for spid 53
***Unable to get thread context for spid 53
***Unable to get thread context for spid 53
***Unable to get thread context for spid 53
***Unable to get thread context for spid 53
***Unable to get thread context for spid 53
***Unable to get thread context for spid 53
*Dump thread - spid = 53, PSS = 0x620a1220, EC = 0x2adf20c0
*Stack Dump being sent to d:\datafiles\MSSQL\log\SQLDump0044.txt
*
*******************************************************************************
*
* BEGIN STACK DUMP:
* 12/05/07 19:35:11 spid 53
*
* StackDump ()
*
* Input Buffer 1890 bytes -
* select a.XXX_Name,F_Seq_No,S_Seq_No, case wh
* en (cast(S_Seq_No as bigint)-cast(F_Seq_No as bigint))=1 then 'OK'
else
* 'Prob' end as CCCC from ( select case when
right(left(filename,5),
* 2)>10 then left(filename,9) else left(filename,5)end as XXX_Name,
max(
* left(right(filename,17),4)) as F_Seq_No from (SELECT distinct
filename
* FROM dbo.XXX20071201)t group by case when right(left(filename,5),
2)>10
* then left(filename,9) else left(filename,5)end ) as a left join
( s
* elect case when right(left(filename,5),2)>10 then left(filename,
9) els
* e left(filename,5)end as XXX_Name, min(left(right(filename,17),
4)) as
* S_Seq_No from (SELECT distinct filename FROM
dbo.XXX20071202_online)s
* group by case when right(left(filename,5),2)>10 then left(filename,
9) e
* lse left(filename,5)end )as b on a.XXX_Name=b.XXX_Name order by
a.XXX
* _Name
*
*
* Edi: 00000000:
* Esi: 000005DC:
* Eax: 00000006:
* Ebx: 002A70D0: 41076770 00000002 00000005 00F8BB2C
00F8B8CC 00000000
* Ecx: 00000001:
* Edx: 00000000:
* Eip: 7C8285EC: 24A48DC3 00000000 0024648D 0824548D
55C32ECD A48DEC8B
* Ebp: 04A5F9B8: 002A7128 41074AEE 000005DC FFFFFFFF
00000001 002AD2C8
* SegCs: 0000001B:
* EFlags: 00000246:
* Esp: 04A5F948: 7C827D0B 77E61D1E 000005DC 00000001
00000000 77E61C96
* SegSs: 00000023:
*
*******************************************************************************
*
-------------------------------------------------------------------------------
* Short Stack Dump
* 7C8285EC Module(ntdll+000285EC) (KiFastSystemCallRet+00000000)
* 41074AEE Module(ums+00004AEE) (UmsThreadScheduler::Switch+0000004E
Line 490+0000000A)
* 410729F9 Module(ums+000029F9) (UmsScheduler::IdleLoop+00000119 Line
1373+0000000D)
* 41072B26 Module(ums+00002B26) (UmsScheduler::Suspend+00000066 Line
1182+00000000)
* 4107311B Module(ums+0000311B) (UmsEvent::Wait+0000007B Line
320+00000000)
* 00401261 Module(sqlservr+00001261)
* 0042341D Module(sqlservr+0002341D)
* 0087F0C2 Module(sqlservr+0047F0C2) (GetIMallocForMsxml+0008A542)
* 0087F5D3 Module(sqlservr+0047F5D3) (GetIMallocForMsxml+0008AA53)
* 008814E2 Module(sqlservr+004814E2) (GetIMallocForMsxml+0008C962)
* 0087B18B Module(sqlservr+0047B18B) (GetIMallocForMsxml+0008660B)
* 0043288B Module(sqlservr+0003288B)
* 00432542 Module(sqlservr+00032542)
* 00434980 Module(sqlservr+00034980)
* 00432542 Module(sqlservr+00032542)
* 005B810A Module(sqlservr+001B810A) (SQLExit+00050613)
* 005B8266 Module(sqlservr+001B8266) (SQLExit+0005076F)
* 0041D396 Module(sqlservr+0001D396)
* 0087B9BB Module(sqlservr+0047B9BB) (GetIMallocForMsxml+00086E3B)
* 0087E509 Module(sqlservr+0047E509) (GetIMallocForMsxml+00089989)
* 0055C692 Module(sqlservr+0015C692)
* 41075309 Module(ums+00005309) (ProcessWorkRequests+000002D9 Line
456+00000000)
* 41074978 Module(ums+00004978) (ThreadStartRoutine+00000098 Line
263+00000007)
* 7C34940F Module(MSVCR71+0000940F) (endthread+000000AA)
* 77E64829 Module(kernel32+00024829) (GetModuleHandleA+000000DF)
*
-------------------------------------------------------------------------------
***Unable to get thread context for spid 53
The query
-------------------------------------------------------------
select a.XXX_Name,F_Seq_No,S_Seq_No,
case when (cast(S_Seq_No as bigint)-cast(F_Seq_No as bigint))=1 then
'OK' else 'Prob' end as CCCC
from
(
select
case when right(left(filename,5),2)>10 then left(filename,9) else
left(filename,5)end as XXX_Name,
max(left(right(filename,17),4)) as F_Seq_No
from (SELECT distinct filename FROM dbo.XXX20071201)t
group by case when right(left(filename,5),2)>10 then left(filename,9)
else left(filename,5)end
) as a
left join
(
select
case when right(left(filename,5),2)>10 then left(filename,9) else
left(filename,5)end as XXX_Name,
min(left(right(filename,17),4)) as S_Seq_No
from (SELECT distinct filename FROM dbo.XXX20071202_online)s
group by case when right(left(filename,5),2)>10 then left(filename,9)
else left(filename,5)end
)as b
on a.XXX_Name=b.XXX_Name
order by a.XXX_Name
Navigation:
[Reply to this message]
|