| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_spaceusage_object_init | | 31 Aug 14 | Defects Dependencies |
1 2 /* 3 ** SP_SPACEUSAGE_OBJECT_INIT 4 ** 5 ** The sub-procedure that initializes the temporary table #spaceusageinfo 6 ** with space usage information depending on the action. For "display" or 7 ** "display summary" or "archive" action, only the raw data extracted from 8 ** different system tables is loaded into #spaceusageinfo (all other data 9 ** will have to be computed in the next step to make the information 10 ** complete) whereas for the action "report" and "report summary", the 11 ** processed data in the archive table is loaded into the #spaceusageinfo 12 ** table. Called by sp_spaceusage_object. 13 ** 14 ** Parameters 15 ** @actionword - The action to be performed. 16 ** @nouname - Was no owner name provided by the user? 17 ** @uname - Owner/user name. 18 ** @tname - Table name. 19 ** @iname - Index name. 20 ** @entity_type - The type of entity. 21 ** @archivetabname - The 3-part archive table name. 22 ** @from_date - Date from when on to get archived data. 23 ** @to_date - Date till which to get archvied data. 24 ** 25 ** Returns 26 ** 0 - if all goes well 27 ** 6 - archive table not found 28 ** other - error while execution 29 { 30 */ 31 create procedure sp_spaceusage_object_init 32 ( 33 @actionword varchar(20) 34 , @nouname bit 35 , @uname varchar(255) 36 , @tname varchar(255) 37 , @iname varchar(255) 38 , @entity_type varchar(12) 39 , @archivetabname varchar(320) = NULL 40 , @from_date varchar(30) = NULL 41 , @to_date varchar(30) = NULL 42 ) 43 as 44 begin -- { 45 46 declare @entity_table varchar(6) 47 , @entity_index varchar(6) 48 49 , @action_display varchar(8) 50 , @action_display_summary varchar(16) 51 , @action_report varchar(7) 52 , @action_report_summary varchar(15) 53 , @action_archive varchar(8) 54 55 , @esdnumstr varchar(20) 56 , @ebfnumstr varchar(20) 57 , @esdnum int 58 , @ebfnum int 59 60 , @returnStatus int 61 , @sqlstmt varchar(4096) 62 , @whoami varchar(40) 63 , @msg varchar(256) 64 65 select @whoami = "sp_spaceusage_object_init" 66 67 , @entity_table = "table" 68 , @entity_index = "index" 69 70 , @action_display = "display" 71 , @action_display_summary = "display summary" 72 , @action_report = "report" 73 , @action_report_summary = "report summary" 74 , @action_archive = "archive" 75 76 /* Extract the ESD number and the EBF number from the version string.*/ 77 exec sp_versioncrack @@version, "ESD", @esdnumstr out, @esdnum out 78 exec sp_versioncrack @@version, "EBF", @ebfnumstr out, @ebfnum out 79 80 /* 81 ** NOTE1: When user does not supply owner name of a table, then current 82 ** user is implicitly assumed. As is the convention, in case the owner 83 ** name is omitted and there is no table owned by current user that 84 ** qualifies, but one owned by DBO qualifies, we will include that in 85 ** the result. 86 ** 87 ** For example, asumme there are tables t1 and t2 owned by user1, tables 88 ** t1 and t3 owned by dbo and table t1 owned user2 in a database. When 89 ** user1 supplies user1.t1, only user1's table t1 should be included. 90 ** When user1 supplies user2.t1, only user2's table t1 should be 91 ** included. When user1 omits the owner and supplies t1, again only 92 ** user1's table t1 should be included as it exists. But if user1 omits 93 ** the owner and supplies t3, dbo's table t3 should be included as no 94 ** table t3 owned by user1 exists while t3 owned by dbo does. 95 ** 96 ** Due to this reason the WHERE clause handling the owner name has a 97 ** sub-query embedded. 98 ** 99 ** NOTE2: In case of 'display', 'display summary' and 'archive' action 100 ** for the entity type index, we need to include the data layer 101 ** information for all the table whose index qualifies. This is required 102 ** as we need the estimates for data layer in order to make estimates 103 ** about the index layer. Hence, an additional condition is added to the 104 ** WHERE clause handling the index name to take care of this. 105 */ 106 107 if @actionword in (@action_display 108 , @action_display_summary 109 , @action_archive) 110 111 begin -- { -- Display/Archive mode 112 113 /* 114 ** Collect all the necessary raw data or meta data for 115 ** estimation. 116 ** 117 */ 118 insert into #spaceusageinfo 119 select 120 @@version_number as VersionNum 121 , @esdnum as ESDNum 122 , @ebfnum as EBFNum 123 , getdate() as ArchiveDateTime 124 , @@servername as ServerName 125 , @@maxpagesize as MaxPageSize 126 , db_name() as DBName 127 , user_name(o.uid) as OwnerName 128 , o.name as TableName 129 , i.id as Id 130 , p.indid as IndId 131 , i.name as IndexName 132 , p.partitionid as PtnId 133 , p.name as PtnName 134 , p.data_partitionid as DataPtnId 135 136 -- These are the values which needs to be accurate in 137 -- order for us to correctly report the estimated data 138 -- or reserved page counts. A recent 'update table 139 -- statistics'is necessary for these values to be 140 -- accurate. 141 -- 142 , RowSize = case 143 when p.indid > 1 144 then convert(decimal(10, 4), 145 t.leafrowsize) 146 else convert(decimal(10, 4), 147 t.datarowsize) 148 end 149 150 -- Compute the row count using the built-in. As the 151 -- number of rows is not applicable for the index layer, 152 -- it is set to NULL for indid > 0. For indid = 1, the 153 -- dummy data layer row that we add in the next step 154 -- would have the value set appropriately. 155 -- 156 , NumRows = case 157 when p.indid > 0 158 then NULL 159 else row_count(db_id(), i.id 160 , p.partitionid) 161 end 162 , t.rowcnt as RowCount_ts 163 164 , t.forwrowcnt as NumFwdRows 165 , t.delrowcnt as NumDelRows 166 , t.emptypgcnt as EmptyPageCount 167 168 -- The following built-ins return 0 in case of invalid 169 -- inputs. So, no need to check for NULL. Except for 170 -- used_pages(), the built-ins return only the index 171 -- layer pages for indid=1. For used_pages(), however, 172 -- they return both data and index layer used pages. The 173 -- data layer used pages can be got by passing indid=0 174 -- to the built-in and then subtracted to get the index 175 -- layer used pages. 176 -- 177 , data_pages(db_id(), i.id, p.indid, p.partitionid) 178 as DataPageCount 179 , used_pages(db_id(), i.id, p.indid, p.partitionid) 180 - case p.indid 181 when 1 then used_pages(db_id(), i.id, 0, 182 p.partitionid) 183 else 0 184 end 185 as UsedPageCount 186 , reserved_pages(db_id(), i.id, p.indid, p.partitionid) 187 as RsvdPageCount 188 189 -- Placeholder for space utilizaton and cluster ratio 190 -- and other statistics collected using the 191 -- derived_stat() built-in. 192 -- 193 , convert(decimal(9, 2), 0) as SpUtil 194 , convert(decimal(5, 4), 0) as DPCR 195 , convert(decimal(5, 4), 0) as DRCR 196 , convert(decimal(5, 4), 0) as IPCR 197 , convert(decimal(5, 4), 0) as LGIO 198 199 -- Extract the space management parameters. 200 , fill_factor as FF 201 , maxrowsperpage as MRPP 202 , exp_rowsize as ERS 203 , res_page_gap as RPG 204 205 -- Other columns from systabstats that may be 206 -- interesting. 207 -- 208 , t.leafcnt as LeafPageCount 209 , t.indexheight as IndexHeight 210 , t.oamapgcnt as OAMAPageCount 211 , t.extent0pgcnt as Extent0PageCount 212 , t.status as Status 213 , o.sysstat as Sysstat 214 , o.sysstat2 as Sysstat2 215 216 -- Some input and output columns that would be computed. 217 , convert(decimal(10, 4), 0) as NonLeafRowSize 218 , NULL as ExpIndexHeight 219 , 0 as ExpDataPageCount 220 , 0 as ExpUsedPageCount 221 , 0 as ExpRsvdPageCount 222 , 0 as ExpLeafPageCount 223 , convert(decimal(7, 2), 0) as PctBloatUsedPages 224 , convert(decimal(7, 2), 0) as PctBloatRsvdPages 225 , convert(decimal(7, 2), 0) as PctBloatLeafPages 226 , convert(decimal(5, 2), 0) as ExtentUtil 227 , convert(decimal(5, 2), 0) as PctEmptyPages 228 , convert(decimal(5, 2), 0) as PctFwdRows 229 230 -- Auxilliary columns to facilitate estimate computation 231 , convert(tinyint, ((o.sysstat2 & 57344) / 16384) 232 ) as LockScheme 233 , convert(smallint, - 1) as NumVarCols 234 , convert(bit, 0) as HasAPLCI 235 , convert(date, t.statmoddate) -- StatModDate 236 237 -- List of generated columns that will not be archived. 238 -- 239 , convert(float, 0.0) as SpacePerPage 240 , convert(float, 0.0) as CalcRowsPerPage 241 , convert(float, 0.0) as EmptyPages 242 , convert(float, 0.0) as DataPages 243 , convert(float, 0.0) as UsedPages 244 , convert(float, 0.0) as RsvdPages 245 , convert(float, 0.0) as LeafPages 246 , convert(float, 0.0) as ExpDataPages 247 , convert(float, 0.0) as ExpUsedPages 248 , convert(float, 0.0) as ExpRsvdPages 249 , convert(float, 0.0) as ExpLeafPages 250 251 from sysindexes i 252 , syspartitions p 253 , systabstats t 254 , sysobjects o 255 256 where o.name like @tname 257 258 -- Refer to NOTE1 at line# 140. 259 and (user_name(o.uid) like @uname 260 or (@nouname = 1 261 and not exists (select 1 262 from sysobjects o2 263 where o2.name = o.name 264 and user_name(o2.uid) = @uname) 265 and user_name(o.uid) = "dbo" 266 ) 267 ) 268 269 -- Only look for user/system tables. 270 and type in ('U', 'S') 271 272 -- Skip fake tables. 273 and ((o.sysstat & 1024) != 1024) 274 275 -- Skip proxy tables. 276 and ((o.sysstat2 & 1024) != 1024) 277 278 -- Skip tables with no real storage. 279 and (p.indoampage != 0 280 or p.datoampage != 0) 281 282 and o.id = i.id 283 and p.id = i.id 284 and p.indid = i.indid 285 286 -- Do a right join with systabstats so as to include a row 287 -- corresponding to indid = 255, although there is no row for 288 -- it present in systabstats. 289 -- 290 and t.id =* p.id 291 and t.indid =* p.indid 292 and t.partitionid =* p.partitionid 293 294 -- Refer to NOTE2 at line# 158. 295 and (i.name like @iname 296 or (i.indid in (0, 1) 297 and o.id in (select i2.id 298 from sysindexes i2 299 where name like @iname) 300 ) 301 ) 302 303 if (@@error != 0) 304 return (@@error) 305 306 /* 307 ** Add a dummy row with indid = 0, for each indid = 1 column. 308 ** This is to separate the index and data layer for indid = 1 309 ** case, which is otherwise difficult. 310 */ 311 insert into #spaceusageinfo 312 select VersionNum 313 , ESDNum 314 , EBFNum 315 , ArchiveDateTime 316 , ServerName 317 , MaxPageSize 318 , DBName 319 , OwnerName 320 , TableName 321 , Id 322 , IndId = 0 323 , IndexName = TableName 324 , PtnId 325 , PtnName 326 , DataPtnId 327 , RowSize 328 , NumRows = row_count(db_id(), Id, PtnId) 329 , RowCount_ts 330 , NumFwdRows 331 , NumDelRows 332 , EmptyPageCount 333 , DataPageCount = data_pages(db_id(), Id, 0, PtnId) 334 , UsedPageCount = used_pages(db_id(), Id, 0, PtnId) 335 , RsvdPageCount = reserved_pages(db_id(), Id, 0, PtnId) 336 , SpUtil 337 , DPCR 338 , DRCR 339 , IPCR 340 , LGIO 341 , FF 342 , MRPP 343 , ERS 344 , RPG 345 , LeafPageCount 346 , IndexHeight 347 , OAMAPageCount 348 , Extent0PageCount 349 , Status 350 , Sysstat 351 , Sysstat2 352 , NonLeafRowSize 353 , ExpIndexHeight 354 , ExpDataPageCount 355 , ExpUsedPageCount 356 , ExpRsvdPageCount 357 , ExpLeafPageCount 358 , PctBloatUsedPages 359 , PctBloatRsvdPages 360 , PctBloatLeafPages 361 , ExtentUtil 362 , PctEmptyPages 363 , PctFwdRows 364 , LockScheme 365 , NumVarCols 366 , HasAPLCI = 1 367 , StatModDate 368 , SpacePerPage 369 , CalcRowsPerPage 370 , EmptyPages 371 , DataPages 372 , UsedPages 373 , RsvdPages 374 , LeafPages 375 , ExpDataPages 376 , ExpUsedPages 377 , ExpRsvdPages 378 , ExpLeafPages 379 380 from #spaceusageinfo 381 where IndId = 1 382 383 if (@@error != 0) 384 return (@@error) 385 386 end -- } -- Display/Archive mode 387 388 else if @actionword in (@action_report 389 , @action_report_summary) 390 391 begin -- { -- Report action 392 393 -- Check for valid archive table name is already done by the 394 -- calling procedure sp_spaceusage_object. 395 396 select @sqlstmt = " INSERT INTO #spaceusageinfo " 397 + " SELECT * " 398 + ", SpacePerPage" + "=0" 399 + ", CalcRowsPerPage" + "=0" 400 + ", EmptyPages" + "=0" 401 + ", DataPages" + "=0" 402 + ", UsedPages" + "=0" 403 + ", RsvdPages" + "=0" 404 + ", LeafPages" + "=0" 405 + ", ExpDataPages" + "=0" 406 + ", ExpUsedPages" + "=0" 407 + ", ExpRsvdPages" + "=0" 408 + ", ExpLeafPages" + "=0" 409 + " FROM " + @archivetabname + " o " 410 + " WHERE DBName LIKE '" + db_name() + "'" 411 + " AND TableName LIKE '" + @tname + "'" 412 + " AND IndexName LIKE '" + @iname + "'" 413 414 -- Refer to NOTE1 at line#140 415 select @sqlstmt = @sqlstmt 416 + " AND " 417 + "(OwnerName LIKE '" + @uname + "'" 418 + " OR ( 1=" + convert(char(1), @nouname) 419 + " AND NOT EXISTS(" 420 + " SELECT 1" 421 + " FROM " 422 + @archivetabname 423 + " i1" 424 + " WHERE " 425 + "i1.TableName" 426 + "=o.TableName" 427 + " AND " 428 + "i1.OwnerName" 429 + "='" + @uname 430 + "')" 431 + " AND o.OwnerName = 'dbo'" 432 + ")" 433 + ")" 434 435 -- The ArchiveDateTime part of the query does the following. 436 -- When both @from_date and @to_date are NULL, it looks for the 437 -- latest ArchiveDateTime for each object that qualifies and 438 -- selects that entry into the #spaceusageinfo table. If only 439 -- @from_date is NULL, it selects all data till @to_date. And 440 -- when only @to_date is missing, it assumes @to_date to be 441 -- today. 442 -- 443 if @from_date is NULL 444 begin 445 if @to_date is NULL 446 begin -- both dates are null 447 select @sqlstmt = @sqlstmt 448 + " AND ArchiveDateTime " 449 + " = (SELECT " 450 + "max(ArchiveDateTime)" 451 + " FROM " + @archivetabname 452 + " i2 " 453 + " WHERE i2.DBName=o.DBName" 454 + " AND i2.OwnerName" 455 + "=o.OwnerName" 456 + " AND i2.TableName" 457 + "=o.TableName" 458 + " AND i2.IndexName" 459 + "=o.IndexName" 460 + " AND (i2.ServerName is NULL" 461 + " or i2.ServerName" 462 + "=o.ServerName)" 463 + ")" 464 end 465 else 466 begin -- only from_date is null 467 select @sqlstmt = @sqlstmt 468 + " AND ArchiveDateTime " 469 + " <= '" + @to_date + "'" 470 end 471 end 472 else 473 begin 474 if @to_date is NULL 475 begin -- only to_date is null 476 select @sqlstmt = @sqlstmt 477 + " AND ArchiveDateTime " 478 + " >= '" + @from_date + "'" 479 + " AND ArchiveDateTime " 480 + " <= getdate()" 481 end 482 else 483 begin -- both dates are non-null 484 select @sqlstmt = @sqlstmt 485 + " AND ArchiveDateTime " 486 + " >= '" + @from_date + "'" 487 + " AND ArchiveDateTime " 488 + " <= '" + @to_date + "'" 489 end 490 end 491 492 exec @returnStatus = sp_exec_SQL @sqlstmt, @whoami 493 494 if @returnStatus != 0 495 return (@returnStatus) 496 497 end -- } -- Report action 498 499 -- If no row in #spaceusageinfo, no object qualified and hence report an 500 -- error message indicating the same and return back. 501 -- 502 if not exists (select 1 from #spaceusageinfo) 503 begin 504 declare @tmpname varchar(290) 505 , @tmpdbname varchar(30) 506 507 select @tmpname = @uname + "." + @tname 508 , @tmpdbname = db_name() 509 510 if @entity_type = @entity_index 511 select @tmpname = @tmpname + "." + @iname 512 513 raiserror 19531, @entity_type, @tmpname, @tmpdbname 514 return (7) 515 end 516 return (0) 517 518 end -- } -- } 519
exec sp_procxmode 'sp_spaceusage_object_init', 'AnyMode' go Grant Execute on sp_spaceusage_object_init to public go
| DEPENDENCIES |
| PROCS AND TABLES USED reads table sybsystemprocs..sysobjects calls proc sybsystemprocs..sp_versioncrack calls proc sybsystemprocs..sp_split_string read_writes table tempdb..#spaceusageinfo (1) reads table sybsystemprocs..systabstats reads table sybsystemprocs..syspartitions calls proc sybsystemprocs..sp_exec_SQL reads table sybsystemprocs..sysindexes CALLERS called by proc sybsystemprocs..sp_spaceusage_object  called by proc sybsystemprocs..sp_spaceusage |
