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 |
25/01/2007, 16h38
Tables MDA et maintenance
00
