DatabaseProcApplicationCreatedLinks
sybsystemprocssp_helpcache  31 Aug 14Defects Dependencies

1     
2     /* This stored procedure is for displaying cache overhead and bindings */
3     /* 17260, "Can't run %1! from within a transaction." */
4     /* 18076, "Could not set curwrite to object level. Set your maxwrite label correctly." */
5     /* 18174, "The database '%1!' is offline. To obtain cache-bindings for objects in this database, please online the database and rerun sp_helpcache. */
6     
7     create procedure sp_helpcache
8         @parm1 varchar(255) = NULL,
9         @parm2 varchar(255) = NULL
10    as
11    
12        declare @cachename varchar(255) /* Name of the cache */
13        declare @stat int /* cache status from sysconfigures */
14        declare @cstat int /* status return from config_admin */
15        declare @unit_loc int /* location of unit specifier in str */
16        declare @size int /* cache size from sysconfigures */
17        declare @row_count int /* # rows in #syscacheconfig */
18        declare @config_size int /* cache's configured size 
19        ** (syscurconfigs
20        */
21        declare @run_size int /* cache's current run size from 
22        ** syscurconfigs
23        */
24        declare @overhead int /* cache's memory overhead */
25        declare @total_config int /* total configured cache memory */
26        declare @total_run int /* total running cache memory */
27        declare @total_overhead int /* total amount of overhead used */
28        declare @status2 int /* Sysdatabase[DAT_STATUS2] */
29        declare @first_char char(1) /* first char of parm1 */
30        declare @unit varchar(5) /* unit of size for configuration */
31        declare @name varchar(255) /* cache name from sysconfigures */
32        declare @config_sz_str varchar(30) /* str value for run_size */
33        declare @run_sz_str varchar(30) /* str value for config_size */
34        declare @overhead_sz_str varchar(30) /* str value for overhead size */
35        declare @print_str varchar(255) /* general string to print info */
36        declare @instancename varchar(255)
37        declare @instanceid int
38        declare @curr_avail_cache_mem float /* current amount of memory available
39        ** for named cache configuration.
40        */
41        declare @tempdb_mask int /* all tempdb status bits */
42    
43        /* Dont allow sp_helpcache to run with in a transaction. */
44        if @@trancount > 0
45        begin
46            /*
47            ** 17260, "Can't run %1! from within a transaction."
48            */
49            raiserror 17260, "sp_helpcache"
50            return (1)
51        end
52        else
53        begin
54            set transaction isolation level 1
55            set chained off
56        end
57    
58        select @first_char = substring(@parm1, 1, 1)
59        select @instancename = NULL
60        select @instanceid = NULL
61    
62        if (@parm1 is NULL or (patindex("%[a-z,A-Z]%", @first_char) != 0))
63        begin
64    
65            begin
66                select @cachename = @parm1
67            end
68    
69    
70            select distinct co.config, co.name, co.value, co.value3,
71                co.status,
72    
73                cu.value run_size, cu.memory_used memory_used
74            into #syscacheconfig
75            from master.dbo.sysconfigures co, master.dbo.syscurconfigs cu
76            where 1 = 2
77    
78    
79            begin
80                insert #syscacheconfig
81                select distinct co.config, co.name, co.value, co.value3,
82                    co.status, cu.value run_size,
83                    cu.memory_used memory_used
84                from master.dbo.sysconfigures co, master.dbo.syscurconfigs cu
85                where parent = 19
86                    and co.config = cu.config
87                    and co.config = 19
88                    and co.name = cu.comment
89                    and name like "%" + @cachename + "%"
90                order by name, config
91            end
92    
93    
94            /*
95            **  Find out the number of rows we want to look at.
96            */
97            select @row_count = count(*) from #syscacheconfig
98    
99            /*
100           **  If no rows qualify then this cache doesn't exist.
101           */
102           if @row_count = 0
103           begin
104               raiserror 18135, @parm1
105               return 1
106           end
107   
108           /*
109           ** populate the result into temp table. 
110           */
111           if ((select object_id("#cachesize_info")) IS NOT NULL)
112           begin
113               drop table #cachesize_info
114           end
115   
116           create table #cachesize_info(
117               cachename varchar(255),
118               configsize varchar(30),
119               runtimesize varchar(30),
120               overhead varchar(30))
121   
122           declare sysc_cursor cursor
123   
124           for select name, value, status, run_size, memory_used
125           from #syscacheconfig
126           order by name
127   
128   
129           open sysc_cursor
130   
131           select @total_config = 0
132           select @total_run = 0
133           select @total_overhead = 0
134   
135           while (@row_count > 0)
136           begin
137   
138               fetch sysc_cursor into @name, @size, @stat, @run_size, @overhead
139   
140   
141   
142   
143               if @stat & 16384 = 16384
144               begin
145                   /* Cache is deleted */
146                   select @row_count = @row_count - 1
147                   continue
148               end
149   
150               if (@stat & 32 = 32)
151               begin
152                   select @config_size = @size
153                   /*
154                   **  The actual run size is in syscurconfigs
155                   */
156   
157                   if (@@instanceid != NULL) and
158                       exists (select * from master.dbo.sysconfigures
159                           where parent = 19
160   
161                               and name = @name) and
162                       @instanceid = NULL
163                   begin
164                       select @run_size = 0
165                       select @overhead = 0
166                   end
167                   else
168                   begin
169                       select @total_config = @total_config + @config_size
170                   end
171   
172                   select @total_run = @total_run + @run_size
173               end
174               if (@stat & 64 = 64)
175               begin
176                   select @run_size = 0
177                   select @overhead = 0
178                   select @config_size = @size
179                   select @total_config = @total_config + @config_size
180               end
181               if (@stat & 128 = 128)
182               begin
183                   select @config_size = 0
184   
185                   if exists (select * from master.dbo.sysconfigures
186                           where parent = 19
187   
188                               and name = @name) and
189                       @instanceid = NULL
190                   begin
191                       select @run_size = 0
192                       select @overhead = 0
193                   end
194   
195                   select @total_run = @total_run + @run_size
196               end
197   
198               select @total_overhead = @total_overhead + @overhead
199               /*
200               **  Convert run_size ,config_size and overhead to megabyte 
201               **  values stored as strings
202               */
203               select @run_sz_str = rtrim(str(convert(float, @run_size) / 1024, 7, 2)) + " Mb"
204               select @config_sz_str = rtrim(str(convert(float, @config_size) / 1024, 7, 2)) + " Mb"
205               select @overhead_sz_str = rtrim(str(convert(float, @overhead) / 1024, 7, 2)) + " Mb"
206   
207               if @instanceid != NULL
208               begin
209                   select @instancename = instance_name(@instanceid)
210                   select @name = @name + ":" + @instancename
211               end
212               insert into #cachesize_info values (@name, @config_sz_str, @run_sz_str, @overhead_sz_str)
213   
214               select @row_count = @row_count - 1
215           end
216   
217           close sysc_cursor
218   
219           /* Display #cachesize_info */
220           exec sp_autoformat @fulltabname = #cachesize_info,
221               @selectlist = "'Cache Name' = cachename,'Config Size' = configsize,'Run Size' = runtimesize, 'Overhead' = overhead",
222               @orderby = "order by cachename"
223   
224           drop table #cachesize_info
225   
226           /*
227           **  If we're doing a helpcache on a specific cache then don't print
228           **  out info on global memory availability.
229           */
230           if patindex("%[a-z]%", @first_char) = 0
231           begin
232               select @curr_avail_cache_mem =
233                   config_admin(13, 0, 0, 0, NULL, NULL)
234   
235               /*
236               **  Subtract all cache overhead from available cache
237               **  memory.
238               */
239               select @curr_avail_cache_mem = @curr_avail_cache_mem -
240                   @total_overhead
241   
242               print " "
243               print " "
244               print "Memory Available For      Memory Configured"
245               print "Named Caches              To Named Caches"
246               print "--------------------       ----------------"
247               select @print_str = convert(char(28), str(convert(float, @curr_avail_cache_mem) / 1024, 7, 2) + " Mb") + str(convert(float, @total_config) / 1024, 7, 2) + " Mb"
248               print @print_str
249               print " "
250           end
251   
252   
253           print " "
254           print "------------------ Cache Binding Information: ------------------ "
255           print " "
256   
257           /*
258           **  Find out the number of rows we want to look at.
259           */
260           select @row_count = count(*) from #syscacheconfig
261   
262           declare bindings_cursor cursor
263           for select name from #syscacheconfig
264   
265           open bindings_cursor
266   
267           print "Cache Name           Entity Name                Type               Index Name                    Status"
268           print "----------           -----------                ----               ----------                    ------"
269           while (@row_count > 0)
270           begin
271               fetch bindings_cursor into @name
272   
273               select @cstat = config_admin(9, 2, 0, 0, NULL, @name)
274   
275               select @row_count = @row_count - 1
276           end
277   
278           close bindings_cursor
279   
280           /* Now print error message for offline databases. The built-in
281           ** function config_admin() ignores offline databases and we handle
282           ** them here after all online databases have been handled.  
283           */
284           select @tempdb_mask = number
285           from master.dbo.spt_values
286           where type = "D3" and name = "TEMPDB STATUS MASK"
287   
288           select name, status2
289           into #sysdb
290           from master.dbo.sysdatabases
291           where (status3 & @tempdb_mask) = 0
292   
293           /*
294           **  Find out the number of rows we want to look at.
295           */
296           select @row_count = count(*) from #sysdb
297   
298           declare offlinedb_cursor cursor
299           for select name, status2 from #sysdb
300   
301           open offlinedb_cursor
302   
303           while (@row_count > 0)
304           begin
305               fetch offlinedb_cursor into @name, @status2
306   
307               if (@status2 & 16 != 0)
308               begin
309                   /* 18174, "The database '%1!' is offline. To obtain 
310                   ** cache-bindings for objects in this database, 
311                   ** please online the database and rerun sp_helpcache. 
312                   */
313                   raiserror 18174, @name
314               end
315               select @row_count = @row_count - 1
316           end
317   
318           close offlinedb_cursor
319   
320           /* Print free cache device list for in-memory storage cache. */
321           select @row_count = count(*) from #syscacheconfig
322           where (status & 65536 = 65536)
323   
324           if @row_count > 0
325           begin
326               print " "
327               print "------------------ In-memory Storage Cache Space Information ------------------"
328               print " "
329   
330               select Name, DeviceName, Status, StartPage, NumPages, SizeKB
331               into #cachedevice
332               from master.dbo.monInmemoryStorage
333               where Name like "%" + @cachename + "%"
334   
335               insert into #cachedevice
336               select phyname, name, "active", low, (high - low + 1), ((high - low + 1) * 2)
337               from master.dbo.sysdevices
338               where status2 & 8 = 8
339                   and phyname like "%" + @cachename + "%"
340   
341               /* Display free cache device information. */
342               exec sp_autoformat @fulltabname = #cachedevice,
343                   @selectlist = "'Cache Name' = Name, 'Device Name'=DeviceName, 'Status' = Status, 'Start Page' = StartPage, 'Number of Pages' = NumPages, 'Size(KB)' = SizeKB",
344                   @orderby = "order by Name, Status, SizeKB DESC"
345               drop table #cachedevice
346           end
347   
348           return 0
349       end
350       else
351       begin
352   
353           /*
354           **  If we get here parm1 must be of the form %d[M P K G].
355           */
356           exec @stat = sp_aux_getsize @parm1, @size output
357           if @stat = 0
358           begin
359               return 1
360           end
361   
362           select @overhead = config_admin(12, 0, @size, 0, NULL, NULL)
363           select @overhead_sz_str = str(convert(float, @overhead) / 1024, 7, 2) + "Mb"
364   
365           select @print_str = convert(varchar(15), @overhead_sz_str) + " of overhead memory will be needed to manage a cache of size " + @parm1
366   
367           print @print_str
368       end
369   
370       return 0
371   


exec sp_procxmode 'sp_helpcache', 'AnyMode'
go

Grant Execute on sp_helpcache to public
go
DEFECTS
 QCAR 6 Cartesian product between tables master..sysconfigures co and [master..syscurconfigs cu] 75
 MINU 4 Unique Index with nullable columns master..sysconfigures master..sysconfigures
 MTYP 4 Assignment type mismatch Name: varchar(30) = varchar(127) 336
 MTYP 4 Assignment type mismatch @size_str: varchar(30) = varchar(255) 356
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 220
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 342
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
286
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 85
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 87
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 159
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 186
 TNOI 4 Table with no index master..monInmemoryStorage master..monInmemoryStorage
 TNOI 4 Table with no index master..syscurconfigs master..syscurconfigs
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause sysc_cursor 124
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause bindings_cursor 263
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause offlinedb_cursor 299
 MGTP 3 Grant to public master..monInmemoryStorage  
 MGTP 3 Grant to public master..spt_values  
 MGTP 3 Grant to public master..sysconfigures  
 MGTP 3 Grant to public master..syscurconfigs  
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public master..sysdevices  
 MGTP 3 Grant to public sybsystemprocs..sp_helpcache  
 MNAC 3 Not using ANSI 'is null' 157
 MNAC 3 Not using ANSI 'is null' 162
 MNAC 3 Not using ANSI 'is null' 189
 MNAC 3 Not using ANSI 'is null' 207
 MNER 3 No Error Check should check @@error after select into 70
 MNER 3 No Error Check should check @@error after insert 80
 MNER 3 No Error Check should check @@error after insert 212
 MNER 3 No Error Check should check return value of exec 220
 MNER 3 No Error Check should check @@error after select into 288
 MNER 3 No Error Check should check @@error after select into 330
 MNER 3 No Error Check should check @@error after insert 335
 MNER 3 No Error Check should check return value of exec 342
 MUCO 3 Useless Code Useless Brackets 50
 MUCO 3 Useless Code Useless Brackets 62
 MUCO 3 Useless Code Useless Brackets 111
 MUCO 3 Useless Code Useless Brackets 135
 MUCO 3 Useless Code Useless Brackets 150
 MUCO 3 Useless Code Useless Brackets 174
 MUCO 3 Useless Code Useless Brackets 181
 MUCO 3 Useless Code Useless Brackets 269
 MUCO 3 Useless Code Useless Brackets 303
 MUCO 3 Useless Code Useless Brackets 307
 MUCO 3 Useless Code Useless Brackets 322
 MUCO 3 Useless Code Useless Brackets 336
 MUIN 3 Column created using implicit nullability 116
 QAFM 3 Var Assignment from potentially many rows 284
 QAPT 3 Access to Proxy Table master..monInmemoryStorage 332
 QCTC 3 Conditional Table Creation 70
 QCTC 3 Conditional Table Creation 116
 QCTC 3 Conditional Table Creation 288
 QCTC 3 Conditional Table Creation 330
 QDIS 3 Check correct use of 'select distinct' 70
 QDIS 3 Check correct use of 'select distinct' 81
 QGWO 3 Group by/Distinct/Union without order by 70
 QISO 3 Set isolation level 54
 QNAJ 3 Not using ANSI Inner Join 75
 QNAJ 3 Not using ANSI Inner Join 84
 QNUA 3 Should use Alias: Column parent should use alias co 85
 QNUA 3 Should use Alias: Column name should use alias co 89
 QPNC 3 No column in condition 76
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {parent, name}
159
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {parent, name}
186
 VUNU 3 Variable is not used @parm2 9
 VUNU 3 Variable is not used @unit_loc 15
 VUNU 3 Variable is not used @unit 30
 CRDO 2 Read Only Cursor Marker (has an 'order by' clause) 124
 CUPD 2 Updatable Cursor Marker (updatable by default) 263
 CUPD 2 Updatable Cursor Marker (updatable by default) 299
 MSUB 2 Subquery Marker 158
 MSUB 2 Subquery Marker 185
 MTR1 2 Metrics: Comments Ratio Comments: 23% 7
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 22 = 25dec - 5exi + 2 7
 MTR3 2 Metrics: Query Complexity Complexity: 200 7
 PRED_QUERY_COLLECTION 2 {c=master..sysconfigures, c2=master..syscurconfigs} 0 81

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_aux_getsize  
reads table master..sysdatabases (1)  
read_writes table tempdb..#syscacheconfig (1) 
reads table master..sysconfigures (1)  
read_writes table tempdb..#sysdb (1) 
reads table master..monInmemoryStorage (1)  
calls proc sybsystemprocs..sp_autoformat  
   reads table tempdb..syscolumns (1)  
   calls proc sybsystemprocs..sp_autoformat  
   reads table master..syscolumns (1)  
   calls proc sybsystemprocs..sp_namecrack  
   reads table master..systypes (1)  
   read_writes table tempdb..#colinfo_af (1) 
   reads table tempdb..systypes (1)  
writes table tempdb..#cachesize_info (1) 
reads table master..syscurconfigs (1)  
writes table tempdb..#cachedevice (1) 
reads table master..spt_values (1)  
reads table master..sysdevices (1)