| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_helpcache | | 31 Aug 14 | Defects 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
| 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) |
