[ASE]crash update stat

[Oraman]

sur une version

Code :

11.9.2

à chaque fois qu'on lance

Code :

UPDATE UPDATE ALL statistics TABLE1

sachant que la table 1 est assez volumineuses quelques million de lignes on le message d'erreur

Code :

the connection has dropped

et puis dans le logerr de sybase on a

Code :

1
2
3
4
 
pc: 0x77e7b3bc KERNEL32.dll (0x0, 0x0, 0x0, 0x0)
end of stack trace, spid 11, kpid 2752554, suid 1
SQL Server system exception (0xc0000005) generated BY a storage access violation.

ce problème je l'ai sur quelques tables uniquement : les tables volumineuses.
et puis j'ai essyer un reorg rebuild

Code :

reorg rebuild TABLE1

j'ai le message d'erreur suivant :

Code :

You cannot run reorg ON a TABLE wich uses allpages locking

qui a une idée sur ce problème ?

[mpeppler]

La première question est: pourquoi le "update all statistics"?
C'est extrèmement consommateur de CPU/IO/place disque dans tempdb, et n'est que très rarement utile.

Il vaut nettement mieux faire

Code :

1
2
 
UPDATE INDEX statistics <nom table>

Michael

[Oraman]

le update index statistics donne le même message d'erreur que all stat

[mpeppler]

Je ferais un DBCC CHECKTABLE et DBCC TABLEALLOC sur les tables qui ont le problème.

Michael

[Roller]

On a pas tout le stack trace, peut on avoir plus?
Pour refaire ta table, tu peux toujours faire un 'alter table <table> lock datarows'
puis un 'alter table <table> lock allpages' (attention, il te faut 1.25 fois la taille de la table d'espace dispo dans ta base pour faire cette opération).
Toujours pour la refaire, tu peux aussi dropper/recréer un index clustered existant, ou créer un index clustered contenant toutes les colonnes de la table puis le dropper (il te faudra aussi pas mal d'espace dispo dans ta base, une fois la table).

[Oraman]

aprés modification du lock a datrows sur les tables les reorg rebuild passe sans problème mais j'ai toujours un soucis avec les update stat qui ne passe pas

[mpeppler]

Comme l'a demandé Roller il faudrait voir la totalité du "stack trace" dans la log Sybase.

Michael

[Oraman]

ça peux pas être un problème d'éspace ?
les données de stats ou sont elles stockées ? dans le cas ou il faut rajouter de l'espace c'est sur la base elle même ou sur le master ?

[Oraman]

est ce que là c'est plus complet ?

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
 
SQL Server system exception (0xc0000005) generated BY a storage access violation.
pc: 0x466bb3 collocate+ 0x63 (0x1b3e220, 0xfffffffb, 0x221d, 0x2)
pc: 0x466bb3 collocate+ 0x63 (0x1b3ea00, 0x1b3f220, 0x1b3fa2c, 0x7)
pc: 0x51f84f sortcmp+ 0x2af (0x1b3f9f0, 0x201b9f10, 0x1b3f6f4, 0xffffffff)
pc: 0x91eb3e ups_build+ 0x46e (0x201b9f10, 0x400000, 0x0, 0x8)
pc: 0x91d83f update_statistics+ 0x16f (0x221d3c18, 0xf1, 0x200106c0, 0x204096bc)
[Handler pc: 0x5c0c40 ut_handle  installed BY the following FUNCTION:-]
pc: 0x91cc5f ups_main+ 0x2ef (0x21, 0x204096bc, 0x204096bc, 0x203ad338)
pc: 0x653725 s_execute+ 0x11e5 (0x221db800, 0x21, 0x204108f9, 0x204096bc)
[Handler pc: 0x6398a0 s_handle  installed BY the following FUNCTION:-]
pc: 0x63678f sequencer+ 0x15f (0x0, 0x200186f8, 0x260026, 0x20016d60)
pc: 0x436d31 tdsrecv_language+ 0xc1 (0x5, 0x4000, 0x200186f8, 0x260026)
[Handler pc: 0x452660 hdl_backout  installed BY the following FUNCTION:-]
[Handler pc: 0x5c0c40 ut_handle  installed BY the following FUNCTION:-]
[Handler pc: 0x5c0c40 ut_handle  installed BY the following FUNCTION:-]
pc: 0x40dc1c conn_hdlr+ 0xb5c (0x200186f8, 0x20016d60, 0x260026, 0x200186f8)
pc: 0x89c1c6 kpntwrapper+ 0x96 (0x89c130, 0x200186f8, 0x0, 0x0)
pc: 0x77e7b3bc KERNEL32.dll (0x1, 0x204096bc, 0x221d3cb0, 0x8)
end of stack trace, spid 12, kpid 2555943, suid 1
************************************
SQL causing error : UPDATE ALL statistics MATABLE
************************************
SQL Text: UPDATE ALL statistics MATABLE
curdb = 5 pstat = 0x10000 lasterror = 0 
preverror = 0 transtate = 1
curcmd = 241 program = SQL_Advantage                 
pc: 0x8a2977 os_get_cur_stk_desc+ 0x57 (0x1b3d44c, 0x270027, 0x1b3d44c, 0x0)
pc: 0x8a2977 os_get_cur_stk_desc+ 0x57 (0x1b3d44c, 0x1b3d9a0, 0x270f, 0x2)
pc: 0x883514 pcstkwalk+ 0x224 (0x270027, 0x2, 0x270f, 0x0)
pc: 0x8830ae ucstkgentrace+ 0x1ce (0x270027, 0x1, 0xc0000005, 0x204096bc)
pc: 0x862864 ucbacktrace+ 0x84 (0x270027, 0xffffffff, 0x1998, 0x0)
pc: 0x405fe6 terminate_process+ 0x566 (0xc0000005, 0x7800bdb5, 0x1b3dd94, 0x0)
pc: 0x85b56e kiexception+ 0x1ee (0x200186f8, 0x20016d60, 0x260026, 0x200186f8)
pc: 0x89c23d kpntwrapper+ 0x10d (0x89c130, 0x200186f8, 0x0, 0x0)
pc: 0x77e7b3bc KERNEL32.dll (0x1, 0x204096bc, 0x221d3cb0, 0x8)
end of stack trace, spid 12, kpid 2555943, suid 1
SQL Server system exception (0xc0000005) generated BY a storage access violation.
pc: 0x466bb3 collocate+ 0x63 (0x26be220, 0xfffffffb, 0x20f0, 0x2)
pc: 0x466bb3 collocate+ 0x63 (0x26bea00, 0x26bf220, 0x26bfa2c, 0x7)
pc: 0x51f84f sortcmp+ 0x2af (0x26bf9f0, 0x201b9f10, 0x26bf6f4, 0xffffffff)
pc: 0x91eb3e ups_build+ 0x46e (0x201b9f10, 0x400000, 0x0, 0x8)
pc: 0x91d83f update_statistics+ 0x16f (0x221eac18, 0xf1, 0x200106c0, 0x204096bc)
[Handler pc: 0x5c0c40 ut_handle  installed BY the following FUNCTION:-]
pc: 0x91cc5f ups_main+ 0x2ef (0x21, 0x204096bc, 0x204096bc, 0x203ad338)
pc: 0x653725 s_execute+ 0x11e5 (0x20f01800, 0x21, 0x204108f9, 0x204096bc)
[Handler pc: 0x6398a0 s_handle  installed BY the following FUNCTION:-]
pc: 0x63678f sequencer+ 0x15f (0x0, 0x200187a0, 0x270027, 0x20016d60)
pc: 0x436d31 tdsrecv_language+ 0xc1 (0x5, 0x4000, 0x200187a0, 0x270027)
[Handler pc: 0x452660 hdl_backout  installed BY the following FUNCTION:-]
[Handler pc: 0x5c0c40 ut_handle  installed BY the following FUNCTION:-]
[Handler pc: 0x5c0c40 ut_handle  installed BY the following FUNCTION:-]
pc: 0x40dc1c conn_hdlr+ 0xb5c (0x200187a0, 0x20016d60, 0x270027, 0x200187a0)
pc: 0x89c1c6 kpntwrapper+ 0x96 (0x0, 0x0, 0x0, 0x0)
pc: 0x77e7b3bc KERNEL32.dll (0x0, 0x0, 0x0, 0x0)
end of stack trace, spid 12, kpid 2621480, suid 1
************************************
SQL causing error : UPDATE ALL statistics MATABLE
************************************
SQL Text: UPDATE ALL statistics MATABLE
curdb = 5 pstat = 0x10000 lasterror = 0 
preverror = 0 transtate = 1
curcmd = 241 program = SQL_Advantage                 
pc: 0x8a2977 os_get_cur_stk_desc+ 0x57 (0x26bd44c, 0x280028, 0x26bd44c, 0x0)
pc: 0x8a2977 os_get_cur_stk_desc+ 0x57 (0x26bd44c, 0x26bd9a0, 0x270f, 0x2)
pc: 0x883514 pcstkwalk+ 0x224 (0x280028, 0x2, 0x270f, 0x0)
pc: 0x8830ae ucstkgentrace+ 0x1ce (0x280028, 0x1, 0xc0000005, 0x204096bc)
pc: 0x862864 ucbacktrace+ 0x84 (0x280028, 0xffffffff, 0x1a40, 0x0)
pc: 0x405fe6 terminate_process+ 0x566 (0xc0000005, 0x7800bdb5, 0x26bdd94, 0x0)
pc: 0x85b56e kiexception+ 0x1ee (0x200187a0, 0x20016d60, 0x270027, 0x200187a0)
pc: 0x89c23d kpntwrapper+ 0x10d (0x0, 0x0, 0x0, 0x0)
pc: 0x77e7b3bc KERNEL32.dll (0x0, 0x0, 0x0, 0x0)
end of stack trace, spid 12, kpid 2621480, suid 1
SQL Server system exception (0xc0000005) generated BY a storage access violation.
pc: 0x466bb3 collocate+ 0x63 (0x271e220, 0xfffffffb, 0x20e6, 0x2)
pc: 0x466bb3 collocate+ 0x63 (0x271ea00, 0x271f220, 0x271fa2c, 0x7)
pc: 0x51f84f sortcmp+ 0x2af (0x271f9f0, 0x201b3140, 0x271f6f4, 0xffffffff)
pc: 0x91eb3e ups_build+ 0x46e (0x201b3140, 0x400000, 0x0, 0x8)
pc: 0x91d83f update_statistics+ 0x16f (0x2103bc18, 0xf1, 0x20010720, 0x20402388)
[Handler pc: 0x5c0c40 ut_handle  installed BY the following FUNCTION:-]
pc: 0x91cc5f ups_main+ 0x2ef (0x21, 0x20402388, 0x20402388, 0x203ad300)
pc: 0x653725 s_execute+ 0x11e5 (0x20e6f000, 0x21, 0x204095c5, 0x20402388)
[Handler pc: 0x6398a0 s_handle  installed BY the following FUNCTION:-]
pc: 0x63678f sequencer+ 0x15f (0x0, 0x200188f0, 0x290029, 0x20016d60)
pc: 0x436d31 tdsrecv_language+ 0xc1 (0x5, 0x4000, 0x200188f0, 0x290029)
[Handler pc: 0x452660 hdl_backout  installed BY the following FUNCTION:-]
[Handler pc: 0x5c0c40 ut_handle  installed BY the following FUNCTION:-]
[Handler pc: 0x5c0c40 ut_handle  installed BY the following FUNCTION:-]
pc: 0x40dc1c conn_hdlr+ 0xb5c (0x200188f0, 0x20016d60, 0x290029, 0x200188f0)
pc: 0x89c1c6 kpntwrapper+ 0x96 (0x89c130, 0x200188f0, 0x0, 0x0)
pc: 0x77e7b3bc KERNEL32.dll (0x0, 0x0, 0x0, 0x0)
end of stack trace, spid 11, kpid 2752554, suid 1
************************************
SQL causing error : UPDATE ALL statistics MATABLE
************************************
SQL Text: UPDATE ALL statistics MATABLE
curdb = 5 pstat = 0x10000 lasterror = 0 
preverror = 0 transtate = 1
curcmd = 241 program = SQL_Advantage                 
pc: 0x8a2977 os_get_cur_stk_desc+ 0x57 (0x271d44c, 0x2a002a, 0x271d44c, 0x0)
pc: 0x8a2977 os_get_cur_stk_desc+ 0x57 (0x271d44c, 0x271d9a0, 0x270f, 0x2)
pc: 0x883514 pcstkwalk+ 0x224 (0x2a002a, 0x2, 0x270f, 0x0)
pc: 0x8830ae ucstkgentrace+ 0x1ce (0x2a002a, 0x1, 0xc0000005, 0x20402388)
pc: 0x862864 ucbacktrace+ 0x84 (0x2a002a, 0xffffffff, 0x1b90, 0x0)
pc: 0x405fe6 terminate_process+ 0x566 (0xc0000005, 0x7800bdb5, 0x271dd94, 0x0)
pc: 0x85b56e kiexception+ 0x1ee (0x200188f0, 0x20016d60, 0x290029, 0x200188f0)
pc: 0x89c23d kpntwrapper+ 0x10d (0x89c130, 0x200188f0, 0x0, 0x0)
pc: 0x77e7b3bc KERNEL32.dll (0x0, 0x0, 0x0, 0x0)
end of stack trace, spid 11, kpid 2752554, suid 1
SQL Server system exception (0xc0000005) generated BY a storage access violation.
pc: 0x466bb3 collocate+ 0x63 (0x275e220, 0xfffffffb, 0x20a5, 0x2)
pc: 0x466bb3 collocate+ 0x63 (0x275ea00, 0x275f220, 0x275fa2c, 0x7)
pc: 0x51f84f sortcmp+ 0x2af (0x275f9f0, 0x201b3140, 0x275f6f4, 0xffffffff)
pc: 0x91eb3e ups_build+ 0x46e (0x201b3140, 0x400000, 0x0, 0x8)
pc: 0x91d83f update_statistics+ 0x16f (0x2217cc18, 0xf1, 0x20010720, 0x20402388)
[Handler pc: 0x5c0c40 ut_handle  installed BY the following FUNCTION:-]
pc: 0x91cc5f ups_main+ 0x2ef (0x21, 0x20402388, 0x20402388, 0x203ad300)
pc: 0x653725 s_execute+ 0x11e5 (0x210a2800, 0x21, 0x204095c5, 0x20402388)
[Handler pc: 0x6398a0 s_handle  installed BY the following FUNCTION:-]
pc: 0x63678f sequencer+ 0x15f (0x0, 0x20018998, 0x2a002a, 0x20016d60)
pc: 0x436d31 tdsrecv_language+ 0xc1 (0x5, 0x4000, 0x20018998, 0x2a002a)
[Handler pc: 0x452660 hdl_backout  installed BY the following FUNCTION:-]
[Handler pc: 0x5c0c40 ut_handle  installed BY the following FUNCTION:-]
[Handler pc: 0x5c0c40 ut_handle  installed BY the following FUNCTION:-]
pc: 0x40dc1c conn_hdlr+ 0xb5c (0x20018998, 0x20016d60, 0x2a002a, 0x20018998)
pc: 0x89c1c6 kpntwrapper+ 0x96 (0x89c130, 0x20018998, 0x0, 0x0)
pc: 0x77e7b3bc KERNEL32.dll (0x0, 0x0, 0x0, 0x0)
end of stack trace, spid 11, kpid 2818091, suid 1
************************************
SQL causing error : UPDATE ALL statistics MATABLE
************************************
SQL Text: UPDATE ALL statistics MATABLE
curdb = 5 pstat = 0x10000 lasterror = 0 
preverror = 0 transtate = 1
curcmd = 241 program = SQL_Advantage                 
pc: 0x8a2977 os_get_cur_stk_desc+ 0x57 (0x275d44c, 0x2b002b, 0x275d44c, 0x0)
pc: 0x8a2977 os_get_cur_stk_desc+ 0x57 (0x275d44c, 0x275d9a0, 0x270f, 0x2)
pc: 0x883514 pcstkwalk+ 0x224 (0x2b002b, 0x2, 0x270f, 0x0)
pc: 0x8830ae ucstkgentrace+ 0x1ce (0x2b002b, 0x1, 0xc0000005, 0x20402388)
pc: 0x862864 ucbacktrace+ 0x84 (0x2b002b, 0xffffffff, 0x1c38, 0x0)
pc: 0x405fe6 terminate_process+ 0x566 (0xc0000005, 0x7800bdb5, 0x275dd94, 0x0)
pc: 0x85b56e kiexception+ 0x1ee (0x20018998, 0x20016d60, 0x2a002a, 0x20018998)
pc: 0x89c23d kpntwrapper+ 0x10d (0x89c130, 0x20018998, 0x0, 0x0)
pc: 0x77e7b3bc KERNEL32.dll (0x0, 0x0, 0x0, 0x0)
end of stack trace, spid 11, kpid 2818091, suid 1
SQL Server system exception (0xc0000005) generated BY a storage access violation.
pc: 0x466bb3 collocate+ 0x63 (0x279e220, 0xfffffffb, 0x20a5, 0x2)
pc: 0x466bb3 collocate+ 0x63 (0x279ea00, 0x279f220, 0x279fa2c, 0x7)
pc: 0x51f84f sortcmp+ 0x2af (0x279f9f0, 0x201b3140, 0x279f6f4, 0xffffffff)
pc: 0x91eb3e ups_build+ 0x46e (0x201b3140, 0x400000, 0x0, 0x8)
pc: 0x91d83f update_statistics+ 0x16f (0x20f4dc18, 0xf1, 0x20010720, 0x20402388)
[Handler pc: 0x5c0c40 ut_handle  installed BY the following FUNCTION:-]
pc: 0x91cc5f ups_main+ 0x2ef (0x21, 0x20402388, 0x20402388, 0x203ad300)
pc: 0x653725 s_execute+ 0x11e5 (0x20f02000, 0x21, 0x204095c5, 0x20402388)
[Handler pc: 0x6398a0 s_handle  installed BY the following FUNCTION:-]
pc: 0x63678f sequencer+ 0x15f (0x0, 0x20018ae8, 0x2c002c, 0x20016d60)
pc: 0x436d31 tdsrecv_language+ 0xc1 (0x5, 0x4000, 0x20018ae8, 0x2c002c)
[Handler pc: 0x452660 hdl_backout  installed BY the following FUNCTION:-]
[Handler pc: 0x5c0c40 ut_handle  installed BY the following FUNCTION:-]
[Handler pc: 0x5c0c40 ut_handle  installed BY the following FUNCTION:-]
pc: 0x40dc1c conn_hdlr+ 0xb5c (0x20018ae8, 0x20016d60, 0x2c002c, 0x20018ae8)
pc: 0x89c1c6 kpntwrapper+ 0x96 (0x89c130, 0x20018ae8, 0x0, 0x0)
pc: 0x77e7b3bc KERNEL32.dll (0x1, 0x20402388, 0x20f4dcb0, 0x8)
end of stack trace, spid 11, kpid 2949165, suid 1

[Roller]

Un update index statistics ou un update statistics créé des tables de travail en tempdb et utilise des sort buffers dans le default data cache pour trier le contenu des colonnes non indexées.
Je sais que le procédure cache est aussi utilisé, mais je ne suis pas certain que ce soit le cas en ASE 11.
Il serait donc probablement utile d'augmenter la taille du default data cache, du procedure cache et de diminuer le nombre de sort buffers (sp_configure 'number of sort buffers') utilisés pour le tri.
Je ne pense pas que ce soit un problème de procédure cache, dans ces cas la il y a une erreur 701 (peut-être pas sur la 11...).

[Oraman]

bon.
quels sont les commandes pour faire tou ça (je ne suis pas expert sybase)

[Roller]

Peux-tu essayer un 'update statistics <table>'.
Si le message d'erreur se reproduit, inutile de toucher aux sort buffers, le problème vient d'ailleurs.

[Oraman]

update statistics <table> donne exactement le même message d'erreur