Tables MDA et maintenance

**arona** · 25/01/2007, 16h38

Bonjour a vous. J'ai installé les tables MDA comme indiqué par mickael et fadace les installations fonctionnent, mais je me pose les questions suivantes. Comment intéroger les dites tables, vous allez me dire select sur master..montable, mais éxiste t'il des proc stocks pouvant interprèter les infos? Et surtout comment être sur que les tables MDA qui sont dans master ne vont pas saturé la taille de mon device master ?? donc comment est-ce gèré faut 'il vider ces tables et si oui y'a t'il des méthodes (proc stock) pouvant faire cela.
Merci d'avance a vous.

**arona** · 25/01/2007, 16h51

Dans la foulée si vous avez des requète sql perméttant de tracer l'activité des procs stock sur un serveur pendant une période de temps donné. Si vous avez aussi tout un tas de requète se servant des tables mda je suis preneur (et/ou url avec ce type d'infos)
A+

**mpeppler** · 26/01/2007, 08h02

Premièrement, les tables MDA sont des tables "virtuelles" - elles consomment un peu de place en mémoire, mais ne consomment donc pas de place dans master ou ailleur sur disque.

Il y a deux types de tables:

1. Les tables cummulatives - ces tables donnent l'états de divers compteurs depuis le démarrage de l'instance. Par example, monDeviceIO donne le nombres d'IOs physiques par dévice, monOpenObjectActivity donne l'activité par objet (tables/indexes mais *pas* les procs stockées/triggers/etc).

2. Les tables "queues" - ces tables retournent les derniers X évenements qui ont été enregistrés, et le X est configurable via sp_configure (voir les options de type "pipe" - p.ex. "errorlog pipe active" et "errorlog pipe max messages"). Une requête sur une de ces tables la vide, donc pour travailler l'information il faut faire qqch comme:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
select * into #sqltext from monSysSqlText
-- maintenant utiliser l'info dans #sqltext pour faire la vraie requête...

Un exemple de proc utilisant les tables MDA est sp__allusage qui donne un résumé de l'activité sur les objets d'une base, tels que nombres d'accès, nombre de table scan, nombre de modifications, etc.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
 
-- Original code from Mich Talebzadeh, with input from Michael Peppler
use sybsystemprocs
go
IF EXISTS(SELECT 1 FROM sysobjects WHERE type = 'P' AND name = 'sp__allusage')
BEGIN
  DROP PROCEDURE sp__allusage
END
go
create procedure sp__allusage
as
begin
set nocount on
/* This code originated with Mich Talebzadeh */
set nocount on
 
-- Use the db you want to analyse, and then run this script.
--
-- First of all work out the table rows and sizes (reserved)
--
select  
        "Owner" = user_name(o.uid),
	TableName = o.name,
        IndexName = i.name,
        low = d.low,
        rowtotal = rowcnt(i.doampg),
        reserved = (reserved_pgs(i.id, i.doampg) + reserved_pgs(i.id, i.ioampg)) * (low / 1024)
into #t1
from sysobjects o, sysindexes i, master.dbo.spt_values d
where
      o.type = 'U'
      and i.id = o.id
      and d.number = 1
      and d.type = "E"
 
select distinct
       Owner,
       TableName,
       RowTotal = convert(char(11), sum(rowtotal)),
       Reserved =  sum(reserved)
into #table_size
from
     #t1
group by Owner,
         TableName
--
-- Identify dormant tables
--
  SELECT
  "Owner" = user_name(o.uid),
  "TableName" = o.name,
  "LogicalReads" = m.LogicalReads,
  "LockRequests" = m.LockRequests,
  "Operations" = m.Operations,
  "Selected" = m.OptSelectCount,
  "WhenLastSelected" = m.LastOptSelectDate,
  "Used" = m.UsedCount,
  "WhenLastUsed" = m.LastUsedDate,
  "Inserted" = m.RowsInserted,
  "Updated" = m.RowsUpdated,
  "Deleted" = m.RowsDeleted
  INTO
      #dormant
  from
       sysobjects o,
       master..monOpenObjectActivity m
  where
       object_name(m.ObjectID, m.DBID) = o.name
       and o.type = 'U'
       and o.id = m.ObjectID
       and m.IndexID = 0
       and m.DBID = db_id()
       and object_name(m.ObjectID, m.DBID) not like 'sa_%'
       and object_name(m.ObjectID, m.DBID) not like '%__sa%'
       and object_name(m.ObjectID, m.DBID) not like 'rs_%'
       and m.RowsInserted = 0
       and m.RowsUpdated = 0
       and m.RowsDeleted = 0
  PRINT ""
  PRINT "Displaying dormant tables with no DML activity"
  PRINT ""
  SELECT
       "TableName" = substring(t.Owner+"."+t.TableName, 1, 35),
       "Rows" = convert(numeric(10,0),s.RowTotal),
       "Size/KB" = convert(numeric(10,0),s.Reserved),
       "LogicalReads" = t.LogicalReads,
       "LockRequests" = t.LockRequests
  FROM
       #dormant t,
       #table_size s
  WHERE
       t.Owner = s.Owner
       and t.TableName = s.TableName
       and t.WhenLastUsed is NULL
       and not exists (select 1 from master..monOpenObjectActivity m
                  where object_name(m.ObjectID, m.DBID) = t.TableName
                    and object_name(m.ObjectID, m.DBID) = s.TableName
                    and m.DBID = db_id()
                    and m.IndexID > 0
                    and m.LastUsedDate is not NULL)
  ORDER BY
        t.Owner,
        t.TableName
--
-- Identify high activity tables
--
  SELECT
  "Owner" = user_name(o.uid),
  "TableName" = o.name,
  "LogicalReads" = m.LogicalReads,
  "LockRequests" = m.LockRequests,
  "Operations" = m.Operations,
  "Selected" = m.OptSelectCount,
  "WhenLastSelected" = m.LastOptSelectDate,
  "Used" = m.UsedCount,
  "WhenLastUsed" = m.LastUsedDate,
  "Inserted" = m.RowsInserted,
  "Updated" = m.RowsUpdated,
  "Deleted" = m.RowsDeleted
  INTO
      #temp
  from
       sysobjects o,
       master..monOpenObjectActivity m
  where
       object_name(m.ObjectID, m.DBID) = o.name
       and o.type = 'U'
       and o.id = m.ObjectID
       and m.IndexID = 0
       and m.DBID = db_id()
       and object_name(m.ObjectID, m.DBID) not like 'sa_%'
       and object_name(m.ObjectID, m.DBID) not like '%__sa%'
       and object_name(m.ObjectID, m.DBID) not like 'rs_%'
       and (m.RowsInserted > 0 or m.RowsUpdated > 0 or  m.RowsDeleted > 0)
    --   and m.LastOptSelectDate is NOT NULL
    --   and m.LastUsedDate is NOT NULL
 
  PRINT ""
  PRINT "Displaying high activity tables"
  PRINT ""
  SELECT
       "TableName" = substring(t.Owner+"."+t.TableName, 1, 20),
       "Rows" = convert(numeric(10,0),s.RowTotal),
       "Size/KB" = convert(numeric(10,0),s.Reserved),
        t.Inserted,
        t.Updated,
        t.Deleted,
        t.LockRequests
  FROM
       #temp t,
       #table_size s
  WHERE
       t.Owner = s.Owner
       and t.TableName = s.TableName
  --     and t.WhenLastSelected is NOT NULL
  --     and t.WhenLastUsed is NOT NULL
  ORDER BY
        t.Owner,
        t.TableName
--
SELECT
"TableName" = object_name(m.ObjectID, m.DBID),
"IndexName" = i.name,
"Selected" = m.OptSelectCount,
"WhenLastSelected" = m.LastOptSelectDate,
"Used" = m.UsedCount,
"WhenLastUsed" = m.LastUsedDate
into #used
from master..monOpenObjectActivity m,
     sysindexes i
where
    m.IndexID > 0
and m.IndexID <> 255 -- ignore text, image data chain
and m.IndexID = i.indid
and m.ObjectID = i.id
and m.DBID = db_id()
print ""
if exists(select 1 from #used where Selected = 0 and Used = 0)
begin
  print ""
  print 'Indexes never selected or used by the optimizer'
  print ""
  select  "TableName" = substring(u.TableName, 1, 20),
          u.IndexName,
          "IndexSize/KB" = i.reserved,
          u.Selected,
          u.Used
  from  #used u,
        #t1 i
  where u.TableName = i.TableName
        and u.IndexName = i.IndexName
        and u.Selected = 0 and u.Used = 0
  order by u.TableName
end
if exists(select 1 from #used where Selected > 0 and Used = 0)
begin
  print ""
  print 'Indexes selected by the optimizer but never used in query'
  print ""
  select  "TableName" = substring(u.TableName, 1, 20),
        u.IndexName,
        "IndexSize/KB" = i.reserved,
        u.Selected,
        "When Last selected" = u.WhenLastSelected
  from  #used u,
        #t1 i
  where u.TableName = i.TableName
        and u.IndexName = i.IndexName
        and u.Selected > 0 and u.Used = 0
  order by u.TableName
end
if exists(select 1 from #used where Selected = 0 and Used > 0)
begin
  print ""
  print 'Indexes Used by the optimizer but never selected'
  print ""
  select  "TableName" = substring(u.TableName, 1, 20),
        u.IndexName,
        "IndexSize/KB" = i.reserved,
        u.Selected
  from  #used u,
        #t1 i
  where u.TableName = i.TableName
        and u.IndexName = i.IndexName
        and u.Selected = 0 and u.Used > 0
  order by u.TableName
end
select  TableName,
        IndexName,
        Selected,
        Used
into    #clean
from    #used
where   Used > 0
and	Selected > 0
--
select  TableName,
        IndexName,
        Selected,
        Used,
        "Selected_over_sum_selected" = convert(numeric(10,4),Selected*1.0/sum(Selected)*1.0),
        "Used_over_sum_used" = convert(numeric(10,4),Used*1.0/sum(Used)*1.0),
        "Used_over_selected" = convert(numeric(10,4),Used*1.0/Selected*1.0)
into #results
from #clean
group by TableName
if exists (select 1 from #results)
begin
  print ""
  print 'Index usage analysis'
  print ""
  print "R1 = Selected/SUM(Selected)"
  print "R2 = Used/SUM(Used)"
  print "R3 = Used/Selected"
  print ""
  select "TableName" = substring(r.TableName, 1, 15),
        "IndexName" = substring(r.IndexName, 1, 20),
        "Size/KB" = i.reserved,
        r.Selected,
        r.Used,
        "R1" = str(r.Selected_over_sum_selected, 9, 3),
        "R2" = str(r.Used_over_sum_used, 9, 3),
        "R3" = str(r.Used_over_selected, 9, 3)
  from  #results r,
        #t1 i
  where 
        r.TableName = i.TableName
        and  r.IndexName = i.IndexName
        and r.Used_over_sum_used < 1.0
  order by r.TableName, r.Used_over_sum_used desc
end
 
 
-- Identify tables accessed via a table scan
  SELECT
  "Owner" = user_name(o.uid),
  "TableName" = o.name,
  "LogicalReads" = m.LogicalReads,
  "PagesRead" = m.PagesRead,
  "WhenLastSelected" = m.LastOptSelectDate,
  "Used" = m.UsedCount
  INTO
      #tabscan
  from
       sysobjects o,
       master..monOpenObjectActivity m
  where
       o.type = 'U'
       and o.id = m.ObjectID
       and m.IndexID = 0
       and m.DBID = db_id()
       and object_name(m.ObjectID, m.DBID) not like 'sa_%'
       and object_name(m.ObjectID, m.DBID) not like '%__sa%'
       and object_name(m.ObjectID, m.DBID) not like 'rs_%'
       and m.LastUsedDate is not NULL
       and m.UsedCount > 0
print ""
print 'Tables accessed via Table Scans'
print ""
  SELECT
       "TableName" = substring(t.Owner+"."+t.TableName, 1, 25),
       "Rows" = convert(numeric(8,0),s.RowTotal),
       "Size/KB" = convert(numeric(8,0),s.Reserved),
       "LogicalReads" = t.LogicalReads,
       "PagesRead" = t.PagesRead,
       "Used" = str(t.Used, 5, 0),
       t.WhenLastSelected
  FROM
       #tabscan t,
       #table_size s
  WHERE
       t.Owner = s.Owner
       and t.TableName = s.TableName
  ORDER BY
        t.Owner,
        t.TableName
 
 
end
go
grant exec on sp__allusage to public
go

**arona** · 26/01/2007, 12h06

Merci des ces infos, tu m'avais conseillé d'installer ces tables, c'est fait mais la récupération des infos récolté est ... pas tres facil.
L'interprétation de ces données c'est autre chose. J'ai récupéré des exemple de scripts sur le site de sypron.
Merci pour ta proc stock, je vais tenter de trouver des infos "utilisable".
Merci encore.
A+

Tables MDA et maintenance

Sybase

Discussions similaires

Partager

Partager