DatabaseProcApplicationCreatedLinks
sybsystemprocssp_spaceusage_object_populate  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     **	SP_SPACEUSAGE_OBJECT_POPULATE
4     **
5     **	The sub-procedure that populates the temporary table #spaceusageinfo 
6     **	with computed space usage information based on the raw space usage 
7     **	information already in it. This is applicable only for the "display", 
8     **	"display summary" and "archive" action. Called by sp_spaceusage_object.
9     **
10    **	Parameters
11    **		None.	
12    **
13    **	Returns
14    **		0 - if all goes well
15    **	    other - error while execution 
16    {
17    */
18    create or replace procedure sp_spaceusage_object_populate
19    as
20        begin -- {	
21    
22            declare
23                @objid int
24                , @indid int
25                , @ptnid int
26                , @lockscheme tinyint
27    
28                , @syscur_ffactor float -- server-wide fill factor %
29                , @syscur_ers_pct float -- server-wide exp row size %
30    
31                , @datalayerpages int
32                , @datapartitionid int
33                , @numrowscovered float
34                , @indexrowsize float
35    
36                , @numpagesPerExtent int
37                , @numextent0 int
38                , @dummyindexlevel smallint
39                , @aplpagehdrsize tinyint
40                , @dolpagehdrsize tinyint
41    
42                , @returnStatus int
43                , @msg varchar(256)
44                , @has_roleperm int
45                , @nullarg char(1)
46                , @dummy int
47                , @status int
48                , @gp_enabled int
49                , @dbname varchar(255)
50    
51    
52    
53            select @dummyindexlevel = - 1
54                , @numpagesPerExtent = 8
55                , @numextent0 = 1
56                , @aplpagehdrsize = 32
57                , @dolpagehdrsize = 44
58                , @has_roleperm = 0
59    
60            /* 
61            ** Get the current server wide default value for the ERS and FF. 
62            **
63            ** NOTE: These values are not available in syscurconfigs, and hence,
64            ** need to get them from sysconfigures.
65            */
66            select @syscur_ers_pct = value / 100.0
67            from master.dbo.sysconfigures
68            where comment = 'default exp_row_size percent'
69    
70            select @syscur_ffactor = value / 100.0
71            from master.dbo.sysconfigures
72            where comment = 'default fill factor percent'
73    
74            /* 
75            ** If not set(NULL), or set to 0 or NULL, handle it apprpriately as this
76            ** will be used later during space calculations.
77            */
78            select @syscur_ffactor = case @syscur_ffactor
79                    when NULL then 1
80                    when 0 then 1
81                    else @syscur_ffactor
82                end
83    
84            select @syscur_ers_pct = case @syscur_ers_pct
85                    when NULL then 0
86                    when 1 then 0
87                    else @syscur_ers_pct
88                end
89    
90    
91            /*	
92            ** Pull out space utilizaton and cluster ratio using the derived_stat()
93            ** built-in. This may need to be converted into appropriate format 
94            ** before display to be in synch with other related output columns. 
95            ** If granular permissions is not enabled the derived_stat() built-in 
96            ** requres SA role (for objects not owned by the user running the 
97            ** built-in) and will be displayed only when this sproc is run with 
98            ** that role. Else, it will show 0.
99            ** If granular permissions is enabled then the derived_stat() built-in
100           ** requires 'manage database'  permission (for objects not owned by 
101           ** the user running the built-in) and will be displayed only when this
102           ** sproc is run with that permission. Else, it will show 0.
103           */
104           select @dbname = db_name()
105           select @nullarg = NULL
106           execute @status = sp_aux_checkroleperm "sa_role", "manage database",
107               @dbname, @gp_enabled output
108   
109           if @status = 0
110               select @has_roleperm = 1
111   
112           update #spaceusageinfo
113           set SpUtil = convert(decimal(9, 2),
114               100 * derived_stat(Id, IndId, PtnId,
115                   "space utilization"))
116               , DPCR = convert(decimal(5, 4),
117               derived_stat(Id, IndId, PtnId, "dpcr"))
118               , DRCR = convert(decimal(5, 4),
119               derived_stat(Id, IndId, PtnId, "drcr"))
120               , IPCR = convert(decimal(5, 4),
121               derived_stat(Id, IndId, PtnId, "ipcr"))
122               , LGIO = convert(decimal(5, 4),
123               derived_stat(Id, IndId, PtnId, "lgio"))
124           where OwnerName = user_name()
125               or @has_roleperm > 0
126   
127           if (@@error != 0)
128               return (@@error)
129   
130           /* 
131           ** Compute and update current value for some space related metrics
132           ** like extent utilization (ratio of the pages in use to the pages
133           ** reserved), PctEmptyPages (ratio of the empty	pages to the total 
134           ** pages in use), PctFwdRows (ratio of the number of forwarded rows to
135           ** the total rows respectively). 
136           */
137           update #spaceusageinfo
138           set ExtentUtil = convert(decimal(5, 2),
139               case RsvdPageCount
140                   when 0 then 0
141                   else UsedPageCount * 100.0 / RsvdPageCount
142               end)
143   
144               , PctEmptyPages = convert(decimal(5, 2),
145               case
146                   when DataPageCount = 0 then 0
147                   when (EmptyPageCount / DataPageCount) > 9.99
148                   then 999.99
149                   else EmptyPageCount * 100.0 / DataPageCount
150               end)
151               , PctFwdRows = convert(decimal(5, 2),
152               case NumRows
153                   when 0 then 0
154                   else NumFwdRows * 100.0 / NumRows
155               end)
156   
157           if (@@error != 0)
158               return (@@error)
159   
160           update #spaceusageinfo
161           set NumVarCols = (select count(*)
162                   from syscolumns i
163                   where i.id = #spaceusageinfo.Id
164                       and i.offset < 0)
165   
166           if (@@error != 0)
167               return (@@error)
168   
169           /*
170           ** For data layer pages (indid = 0), calculate the space per page 
171           ** available as follows.
172           **
173           ** Adjust for the page header, to get the actual row-storage space on
174           ** a page. 
175           **
176           ** Apply fill factor as follows:
177           **	APL table with CI (indid = 1), if value stored in sysindexes for
178           **	indid 1, use it. If not, use the server wide default value.
179           **	
180           **	DOL table (indid=0), if value stored in sysindexes for indid 0,
181           **	use it. If not, fully pack it.
182           ** 
183           ** Apply expected row size as follows:
184           **	Applicable only for DOL tables with variable length columns. 
185           **
186           **	If set to 0, use the server wide default expected row size
187           **	percent to leave space in each row for expanding rows.
188           **
189           **	If set to 1, we fully pack the data page.
190           **
191           **	If set to any other value, then it will be taken care of later
192           **	(w.r.t to data row size value)
193           */
194           update #spaceusageinfo
195           set SpacePerPage = (@@maxpagesize - @aplpagehdrsize)
196               *
197               (case FF
198                   when NULL then 1
199                   when 0 then @syscur_ffactor
200                   else FF / 100.0
201               end)
202           where IndId = 0
203               and LockScheme = 0 -- APL table
204   
205           if (@@error != 0)
206               return (@@error)
207   
208           update #spaceusageinfo
209           set SpacePerPage = (@@maxpagesize - @dolpagehdrsize)
210               *
211               (case FF
212                   when NULL then 1
213                   when 0
214                   then case
215                       when NumVarCols > 0
216                           and ERS = 0
217                       then (1 - @syscur_ers_pct)
218                       else 1
219                   end
220                   else FF / 100.0
221               end)
222           where IndId = 0
223               and LockScheme != 0 -- non-APL, i.e., DOL table
224   
225           if (@@error != 0)
226               return (@@error)
227   
228           /* 
229           ** Calculate the number of data rows that can fit a single page based on
230           ** the space available per page for rows and the row size.
231           **
232           ** For DOL tables, if ERS is set [to a value other than 0/1], inserts
233           ** reserve that much space for the row even if currently it is smaller
234           ** in size in case the table has variable length columns. 
235           **
236           ** Figure in a 2-byte offset table overhead.
237           */
238           update #spaceusageinfo
239           set CalcRowsPerPage = SpacePerPage / (case LockScheme
240                   when 0 then RowSize
241                   else case --DOL
242                           when ERS is not NULL
243                               and ERS != 0
244                               and ERS != 1
245                               and ERS > RowSize
246                               and NumVarCols > 0
247                           then ERS
248                           else RowSize
249                       end
250               end
251               + 2) -- Row offset entry size
252           where IndId = 0
253   
254           if (@@error != 0)
255               return (@@error)
256   
257           /*
258           ** For APL tables, if MRPP is set, it limits the no of rows that can fit
259           ** a page. Also, an APL table can not have more than 256 rows per page.
260           */
261           update #spaceusageinfo
262           set CalcRowsPerPage = case
263                   when MRPP != 0
264                       and MRPP < CalcRowsPerPage
265                   then MRPP
266                   else case
267                           when CalcRowsPerPage > 256.0
268                           then 256.0
269                           else CalcRowsPerPage
270                       end
271               end
272           where IndId = 0 and LockScheme = 0 -- APL data layer
273   
274           if (@@error != 0)
275               return (@@error)
276   
277           /*
278           ** Update the temp table to get the estimated data layer pages that
279           ** would be required if this were a fully packed table. 
280           **
281           ** If the number of rows in the table is 0, then the expected data pages
282           ** for the data layer is 1.
283           */
284           update #spaceusageinfo
285           set ExpDataPageCount = case NumRows
286                   when 0 then 1
287                   else ceiling(NumRows / CalcRowsPerPage)
288               end
289           where IndId = 0
290   
291           if (@@error != 0)
292               return (@@error)
293   
294           /* 
295           ** Estimate number of used pages based on the data page estimate
296           ** assuming the same number of pages will be used for internal 
297           ** structures as are being used currently.
298           */
299           update #spaceusageinfo
300           set ExpUsedPageCount = ExpDataPageCount + (UsedPageCount
301               - DataPageCount)
302           where IndId = 0
303   
304           if (@@error != 0)
305               return (@@error)
306   
307           /* 
308           ** For the text/image index [indid = 255] there isn't a way to estimate
309           ** the number of data/used pages. So, set it to	DataPageCount/
310           ** UsedPageCount.
311           */
312           update #spaceusageinfo
313           set ExpDataPageCount = DataPageCount
314               , ExpUsedPageCount = UsedPageCount
315           where IndId = 255
316   
317           if (@@error != 0)
318               return (@@error)
319   
320           /*
321           ** Set the non-leaf row size for indid > 0 except for the text/image
322           ** index[indid = 255].
323           **
324           ** For APL tables, the non-leaf row is same as leaf row for CI, and
325           ** has an additional 4 byte pointer [to an index page] in case of NCI.
326           **
327           ** Ignoring the suffix compression at non-leaf levels and the duplicate
328           ** key handling at the leaf level, in DOL tables, the non-leaf rows has
329           ** a 4 byte pointer in place of the 6 byte RID of the leaf rows.
330           **
331           ** NOTE: For indid = 1, leaf row size is not maintained and will be
332           ** computed later. This step will actually set it is as data row size.
333           **
334           */
335           update #spaceusageinfo
336           set NonLeafRowSize = case LockScheme
337                   when 0 then (case IndId
338                       when 1 then (RowSize)
339                       else (RowSize + 4)
340                   end)
341                   else (RowSize - 6 + 4)
342               end
343           where IndId > 0 and IndId != 255
344   
345           if (@@error != 0)
346               return (@@error)
347   
348           /*
349           ** Compute/Estimate the ExpDataPageCount for the index layer [indid>0]
350           **
351           ** Steps involved -
352           ** 	1. set the #indexData table [by selecting from #spaceusageinfo 
353           **	   into it]. at this point, the leaf and non-leaf row sizes are
354           **	   already computed and set. sp_index_space_est will compute the
355           **	   leaf and non-leaf rows per page values internally based on 
356           **	   them.
357           **
358           **	2. go thru each  threesome and pass it to the
359           ** 	   sp_index_space_est. this sproc would set the data pages and
360           **	   used pages for this  threesome in the
361           **	   #indexData table appropriately.
362           **
363           **	3. set the ExpDataPageCount and ExpUsedPageCount in 
364           **	   #spaceusageinfo table for the index accordingly.
365           **
366           */
367   
368           /*			STEP 1.	
369           ** Create and populate the #indexData table required by the sproc
370           ** sp_index_space_est which will be used to calculate the 
371           ** ExpDataPageCount, ExpUsedPageCount, ExpRsvdPageCount for the	index 
372           ** layer [indid >0]
373           */
374           select Id as id
375               , IndId as indid
376               , PtnId as ptnid
377               , IndexName as name
378               , "" as type
379               , - 1 as indexlevel
380               , convert(smallint NULL, FF) as ffactor
381               , convert(smallint NULL, MRPP) as maxrowsperpage
382               , convert(int NULL, 0) as datalayerpages
383               , convert(float NULL, NumRows) as numrows
384               , convert(float NULL, RowSize) as leafrowsize
385               , convert(float NULL, NonLeafRowSize) as nonleafrowsize
386               , convert(tinyint NULL, LockScheme) as lockscheme
387               , 0 as numofdatapages
388               , convert(float, 0) as datasizeinkb
389               , 0 as numofusedpages
390               , convert(float, 0) as usedsizeinkb
391           into #indexData
392           from #spaceusageinfo
393           where IndId > 0
394               and IndId != 255
395   
396           if (@@error != 0)
397               return (@@error)
398   
399           /* 
400           ** Create a unique index on #indexData in order to create an updatable
401           ** cursor on it.
402           */
403           create unique index uind on #indexData(id, indid, ptnid, indexlevel)
404   
405           if (@@error != 0)
406               return (@@error)
407   
408           /*			STEP 2.	
409           ** Compute the no. of index pages and used pages for the indices one at
410           ** a time using the sp_index_space_est sproc.
411           */
412           declare indexcursor cursor for
413           select id, indid, ptnid, lockscheme
414           from #indexData
415           where indexlevel = @dummyindexlevel
416           for update
417   
418           open indexcursor
419   
420           while (1 = 1)
421           begin -- {
422   
423               -- Go to the next  quad.
424               fetch indexcursor into @objid, @indid, @ptnid, @lockscheme
425   
426               if @@sqlstatus != 0
427                   break
428   
429               -- For indid = 1, the index row size is not maintained. So, we
430               -- will use the sproc sp_index_row_size_est to get a rough 
431               -- estimate for it.
432               --
433               if @indid = 1
434               begin -- {
435                   exec @returnStatus = sp_index_row_size_est
436                       @objid
437                       , @indid
438                       , @lockscheme
439                       , NULL
440                       , @indexrowsize out
441   
442                   if @returnStatus != 0
443                       return (@returnStatus)
444   
445                   -- Update the leaf row size and non leaf row size with
446                   -- the estimated index row size both for #indexData 
447                   -- [needed for index space estimate] and #spaceusageinfo
448                   -- [needed as it is the repository for all the collected
449                   -- data]
450                   --
451                   update #indexData
452                   set leafrowsize = @indexrowsize
453                       , nonleafrowsize = @indexrowsize
454                   where id = @objid
455                       and indid = @indid
456                       and ptnid = @ptnid
457                       and indexlevel = @dummyindexlevel
458   
459                   if (@@error != 0)
460                       return (@@error)
461   
462                   update #spaceusageinfo
463                   set RowSize = @indexrowsize
464                       , NonLeafRowSize = @indexrowsize
465                   where Id = @objid
466                       and IndId = @indid
467                       and PtnId = @ptnid
468   
469                   if (@@error != 0)
470                       return (@@error)
471   
472               end --} -- End of if 
473   
474               -- Fetch the data partition id corresponding to the index. 
475               select @datapartitionid = DataPtnId
476               from #spaceusageinfo
477               where Id = @objid
478                   and IndId = @indid
479                   and PtnId = @ptnid
480   
481               if (@datapartitionid = 0)
482               begin
483   
484                   -- Global, unpartitioned index covering all data
485                   -- partitions.
486                   --
487                   select @datalayerpages = sum(ExpDataPageCount)
488                       , @numrowscovered = sum(NumRows)
489                   from #spaceusageinfo
490                   where Id = @objid
491                       and IndId = 0
492               end
493               else
494               begin
495   
496                   -- Local, partitioned index covering a single 
497                   -- partition.
498                   --
499                   -- APL CI are on same partition as the data they cover
500                   -- For them the partition ID and the data partition ID
501                   -- would always match.
502                   --
503                   select @datalayerpages = ExpDataPageCount
504                       , @numrowscovered = NumRows
505                   from #spaceusageinfo
506                   where Id = @objid
507                       and IndId = 0
508                       and PtnId = @datapartitionid
509               end
510   
511               update #indexData
512               set datalayerpages = @datalayerpages
513                   , numrows = @numrowscovered
514               where id = @objid
515                   and indid = @indid
516                   and ptnid = @ptnid
517                   and indexlevel = @dummyindexlevel
518   
519               if (@@error != 0)
520                   return (@@error)
521   
522               -- Get the index space estimate for the 
523               -- threesome.
524               --
525               exec @returnStatus = sp_index_space_est @objid, @indid, @ptnid
526   
527               if @returnStatus != 0
528                   return (@returnStatus)
529   
530               -- Consolidate the result. 
531               -- 
532               -- sp_index_space_est sums up all the index layer data and used
533               -- pages for each level and sets the numofdatapages and 
534               -- numofusedpages columns for the indexlevel = -1 row with that
535               -- value. We set ExpDataPageCount and ExpUsedPageCount of 
536               -- #spaceusageinfo table to those values.
537               --
538               update #spaceusageinfo
539               set ExpDataPageCount = (select numofdatapages
540                       from #indexData
541                       where id = @objid
542                           and indid = @indid
543                           and ptnid = @ptnid
544                           and indexlevel =
545                           @dummyindexlevel)
546                   , ExpIndexHeight = (select max(indexlevel)
547                       from #indexData
548                       where id = @objid
549                           and indid = @indid
550                           and ptnid = @ptnid)
551   
552               where Id = @objid
553                   and IndId = @indid
554                   and PtnId = @ptnid
555   
556               if (@@error != 0)
557                   return (@@error)
558   
559               -- Instead of using the numofusedpages from #indexData as the
560               -- estimate for ExpUsedPageCount, use the current internal
561               -- structure overhead (UsedPageCount - DataPageCount) and add it
562               -- to ExpDataPageCount to get the ExpUsedPageCount.
563               --
564               update #spaceusageinfo
565               set ExpUsedPageCount = ExpDataPageCount +
566                   (UsedPageCount - DataPageCount)
567               where Id = @objid
568                   and IndId = @indid
569                   and PtnId = @ptnid
570   
571               if (@@error != 0)
572                   return (@@error)
573   
574               -- Set the ExpLeafPageCount of #spaceusageinfo table. For indid
575               -- 1, the leaf pages are same as the data pages.
576               --
577               update #spaceusageinfo
578               set ExpLeafPageCount = case IndId
579                       when 1 then 0
580                       else (select numofdatapages
581                               from #indexData
582                               where id = @objid
583                                   and indid = @indid
584                                   and ptnid = @ptnid
585                                   and indexlevel = 0)
586                   end
587               where Id = @objid
588                   and IndId = @indid
589                   and PtnId = @ptnid
590   
591               if (@@error != 0)
592                   return (@@error)
593   
594           end -- } 	-- End of while 
595   
596           close indexcursor
597           deallocate cursor indexcursor
598   
599           /*
600           ** Go back and update expected reserved pages rounding up the expected
601           ** data pages to a multiple of # of extents.
602           **
603           ** Incorporate the reserve page gap as follows. For CI, RPG applies
604           ** to both the data and index pages and for NCI it applies to only
605           ** data pages and leaf level pages.
606           */
607           update #spaceusageinfo
608           set ExpRsvdPageCount = ceiling((ExpUsedPageCount
609                   + case RPG
610                       when 0 then 0.0
611                       else
612                           ceiling(
613                               (1.0 / RPG) *
614                               case
615                                   when IndId <= 1
616                                   then ExpUsedPageCount
617                                   else ExpLeafPageCount
618                               end
619                           )
620                   end
621                   ) / @numpagesPerExtent
622               ) * @numpagesPerExtent
623   
624           if (@@error != 0)
625               return (@@error)
626   
627           /*
628           ** Now that we have got the expected data/reserved page counts,
629           ** compute the %age bloat values.
630           */
631           update #spaceusageinfo
632           set PctBloatRsvdPages = (convert(float, (RsvdPageCount
633               - ExpRsvdPageCount))
634               / ExpRsvdPageCount) * 100
635               , PctBloatUsedPages = (convert(float, (UsedPageCount
636               - ExpUsedPageCount))
637               / ExpUsedPageCount) * 100
638           where ExpUsedPageCount > 0
639   
640           update #spaceusageinfo
641           set PctBloatLeafPages = (convert(float, (LeafPageCount
642               - ExpLeafPageCount))
643               / ExpLeafPageCount) * 100
644           where ExpLeafPageCount > 0
645   
646           if (@@error != 0)
647               return (@@error)
648   
649           /*
650           ** Raise a warning message if the values were found suspicious.
651           **
652           ** This message might come up often when space parameters are set. This
653           ** is because the procedure takes them into account even though they
654           ** might have not been applied yet.
655           **
656           ** Also, the expected reserved pages may be off by the number of extent0
657           ** extents allocated to the object because the AP in extent0 is counted
658           ** by our estimates whereas in reality that is not the case. There's no
659           ** way to get this number for an object, we assume it to be 1 here. The
660           ** variable @numextent0 should be changed appropriately if this number
661           ** is tracked, or can be derived, in future.
662           */
663           if exists (select 1
664                   from #spaceusageinfo
665                   where (PctBloatRsvdPages < 0
666                           and (ExpRsvdPageCount - RsvdPageCount) >
667                           @numextent0
668                       )
669                       or PctBloatUsedPages < 0
670                       or PctBloatLeafPages < 0
671                       or PctFwdRows < 0
672                       or PctEmptyPages < 0
673                       or PctEmptyPages > 100
674                   )
675           begin
676               exec sp_getmessage 19532, @msg out
677               print @msg, "UPDATE [TABLE] STATISTICS"
678           end
679   
680       end -- } 	-- }
681   


exec sp_procxmode 'sp_spaceusage_object_populate', 'AnyMode'
go

Grant Execute on sp_spaceusage_object_populate to public
go
DEFECTS
 MEST 4 Empty String will be replaced by Single Space 378
 MINU 4 Unique Index with nullable columns master..sysconfigures master..sysconfigures
 MTYP 4 Assignment type mismatch @row_size: int = float 440
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: #indexData.uind unique
(id, indid, ptnid, indexlevel)
Intersection: {indexlevel}
415
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 164
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 415
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 457
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 517
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 544
 CUSU 3 Cursor updated through 'searched update': risk of halloween rows indexcursor 451
 CUSU 3 Cursor updated through 'searched update': risk of halloween rows indexcursor 511
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 163
 MAW1 3 Warning message on %name% tempdb..#indexData.id: Warning message on #indexData_crby_sybsystemprocs__sp_spaceusage_object_populate 413
 MAW1 3 Warning message on %name% tempdb..#indexData.id: Warning message on #indexData_crby_sybsystemprocs__sp_spaceusage_object_populate 454
 MAW1 3 Warning message on %name% tempdb..#indexData.id: Warning message on #indexData_crby_sybsystemprocs__sp_spaceusage_object_populate 514
 MAW1 3 Warning message on %name% tempdb..#indexData.id: Warning message on #indexData_crby_sybsystemprocs__sp_spaceusage_object_populate 541
 MAW1 3 Warning message on %name% tempdb..#indexData.id: Warning message on #indexData_crby_sybsystemprocs__sp_spaceusage_object_populate 548
 MAW1 3 Warning message on %name% tempdb..#indexData.id: Warning message on #indexData_crby_sybsystemprocs__sp_spaceusage_object_populate 582
 MGTP 3 Grant to public master..sysconfigures  
 MGTP 3 Grant to public sybsystemprocs..sp_spaceusage_object_populate  
 MGTP 3 Grant to public sybsystemprocs..syscolumns  
 MNER 3 No Error Check should check @@error after update 631
 MNER 3 No Error Check should check return value of exec 676
 MUCO 3 Useless Code Useless Begin-End Pair 20
 MUCO 3 Useless Code Useless Brackets 127
 MUCO 3 Useless Code Useless Brackets 128
 MUCO 3 Useless Code Useless Brackets 147
 MUCO 3 Useless Code Useless Brackets 157
 MUCO 3 Useless Code Useless Brackets 158
 MUCO 3 Useless Code Useless Brackets 166
 MUCO 3 Useless Code Useless Brackets 167
 MUCO 3 Useless Code Useless Brackets 205
 MUCO 3 Useless Code Useless Brackets 206
 MUCO 3 Useless Code Useless Brackets 225
 MUCO 3 Useless Code Useless Brackets 226
 MUCO 3 Useless Code Useless Brackets 254
 MUCO 3 Useless Code Useless Brackets 255
 MUCO 3 Useless Code Useless Brackets 274
 MUCO 3 Useless Code Useless Brackets 275
 MUCO 3 Useless Code Useless Brackets 291
 MUCO 3 Useless Code Useless Brackets 292
 MUCO 3 Useless Code Useless Brackets 304
 MUCO 3 Useless Code Useless Brackets 305
 MUCO 3 Useless Code Useless Brackets 317
 MUCO 3 Useless Code Useless Brackets 318
 MUCO 3 Useless Code Useless Brackets 337
 MUCO 3 Useless Code Useless Brackets 338
 MUCO 3 Useless Code Useless Brackets 345
 MUCO 3 Useless Code Useless Brackets 346
 MUCO 3 Useless Code Useless Brackets 396
 MUCO 3 Useless Code Useless Brackets 397
 MUCO 3 Useless Code Useless Brackets 405
 MUCO 3 Useless Code Useless Brackets 406
 MUCO 3 Useless Code Useless Brackets 420
 MUCO 3 Useless Code Useless Brackets 443
 MUCO 3 Useless Code Useless Brackets 459
 MUCO 3 Useless Code Useless Brackets 460
 MUCO 3 Useless Code Useless Brackets 469
 MUCO 3 Useless Code Useless Brackets 470
 MUCO 3 Useless Code Useless Brackets 481
 MUCO 3 Useless Code Useless Brackets 519
 MUCO 3 Useless Code Useless Brackets 520
 MUCO 3 Useless Code Useless Brackets 528
 MUCO 3 Useless Code Useless Brackets 556
 MUCO 3 Useless Code Useless Brackets 557
 MUCO 3 Useless Code Useless Brackets 571
 MUCO 3 Useless Code Useless Brackets 572
 MUCO 3 Useless Code Useless Brackets 591
 MUCO 3 Useless Code Useless Brackets 592
 MUCO 3 Useless Code Useless Brackets 624
 MUCO 3 Useless Code Useless Brackets 625
 MUCO 3 Useless Code Useless Brackets 646
 MUCO 3 Useless Code Useless Brackets 647
 QAFM 3 Var Assignment from potentially many rows 66
 QAFM 3 Var Assignment from potentially many rows 70
 QAFM 3 Var Assignment from potentially many rows 475
 QAFM 3 Var Assignment from potentially many rows 503
 QPNC 3 No column in condition 125
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
Uncovered: [number, colid]
163
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: #indexData.uind unique
(id, indid, ptnid, indexlevel)
Intersection: {ptnid, id, indid}
548
 QTLO 3 Top-Level OR 124
 QTLO 3 Top-Level OR 665
 VNRD 3 Variable is not read @nullarg 105
 VNRD 3 Variable is not read @gp_enabled 107
 VUNU 3 Variable is not used @dummy 46
 CUPD 2 Updatable Cursor Marker (has for update clause) 413
 MSUB 2 Subquery Marker 539
 MSUB 2 Subquery Marker 546
 MSUB 2 Subquery Marker 580
 MSUB 2 Subquery Marker 663
 MSUC 2 Correlated Subquery Marker 161
 MTR1 2 Metrics: Comments Ratio Comments: 43% 18
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 35 = 42dec - 9exi + 2 18
 MTR3 2 Metrics: Query Complexity Complexity: 242 18

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..sysconfigures (1)  
   reads table master..syscurconfigs (1)  
read_writes table tempdb..#indexData (1) 
reads table master..sysconfigures (1)  
calls proc sybsystemprocs..sp_index_row_size_est  
   reads table sybsystemprocs..syscolumns  
   reads table sybsystemprocs..sysobjects  
   reads table sybsystemprocs..sysindexes  
read_writes table tempdb..#spaceusageinfo (1) 
calls proc sybsystemprocs..sp_index_space_est  
   reads table sybsystemprocs..sysindexes  
   reads table master..syscurconfigs (1)  
   read_writes table tempdb..#indexData (1) 
reads table sybsystemprocs..syscolumns  
calls proc sybsystemprocs..sp_getmessage  
   reads table master..sysmessages (1)  
   reads table sybsystemprocs..sysusermessages  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..syslanguages (1)  

CALLERS
called by proc sybsystemprocs..sp_spaceusage_object  
   called by proc sybsystemprocs..sp_spaceusage