DatabaseProcApplicationCreatedLinks
sybsystemprocssp_monitor_list  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     ** sp_monitor_list
4     **
5     **	Generate a summary listing of the various monitoring types that
6     **	are enabled, and associated config options that are ON (or not).
7     **	This summary report will give the user a quick snapshot of the
8     **	current state of monitoring in the server.
9     **
10    ** Returns:
11    **	0 - Success; Non-zero for failure.
12    {
13    */
14    create or replace procedure sp_monitor_list
15    as
16        begin
17            declare @sqlstmt varchar(100)
18                , @retval int
19    
20            -- For consistency with other commands, require both mon_role and
21            -- sa_role, even though only sa_role is sufficient for this command.
22            --
23            select @sqlstmt = object_name(@@procid, db_id('sybsystemprocs'))
24    
25            set nocount on
26    
27            -- Create a #temp table  in which we will stash away any config
28            -- options found to b set in the control table. The control table
29            -- itself might not be found, so we have to play games by creating
30            -- a temp table first, and then use it to join later on.
31            --
32            select config into #mon_config
33            from master.dbo.sysconfigures
34            where 1 = 0
35    
36            print " "
37            if (object_id('tempdb.dbo.mon_config') IS NULL)
38            begin
39                exec sp_getmessage 19476, @sqlstmt output
40                print @sqlstmt, 'sp_monitor'
41                print " "
42            end
43            else
44            begin
45                -- First collect and display the config items needed
46                -- that are needed to be ON for the monitoring entity
47                -- types that are seen to be 'enable'd from the control
48                -- table.
49                --
50                select mc.monitor
51                    , mc.configname
52                    , mc.enabled
53                    , run_value = cu.value
54                    , type = cu.type
55                into #mon_config_for_type
56                from tempdb.dbo.mon_config mc
57                , master.dbo.syscurconfigs cu
58                where monitor != 'tracing'
59                    and confignum > 0 -- get real config item rows.
60                    and cu.config = mc.confignum
61    
62                -- Identify the unique config options that were needed for
63                -- all monitoring types currently found enabled.
64                --
65                select @sqlstmt = "INSERT #mon_config "
66                    + "SELECT DISTINCT confignum "
67                    + "FROM tempdb.dbo.mon_config"
68                    + " WHERE monitor != 'tracing'"
69    
70                exec @retval = sp_exec_SQL @sqlstmt, "sp_monitor_list"
71                if (@retval != 0)
72                    return @retval
73    
74                create table #mon_types(
75                    mon_type varchar(30)
76                    , missing_cfg_option int default 0
77                    , enabled int default 0
78                    , numcfgs_enabled_via_spmon int default 0
79                    , config_name varchar(100) NULL
80                    , type varchar(10) NULL
81                    , cfg_value int default 0
82                    , run_value int default 0
83                ) lock allpages
84    
85                exec @retval = sp_monitor_list_montypes
86    
87                if ((@retval = 0)
88                        and (EXISTS (select 1 from #mon_types
89                                where numcfgs_enabled_via_spmon > 0
90                                    and missing_cfg_option > 0)))
91                begin
92                    -- We found some monitoring config options were ON, but
93                    -- in some cases they might not be turning ON all the
94                    -- required config options. Do a match between the
95                    -- monitoring entties that are known to be enabled
96                    -- via sp_monitor v/s their missing config options,
97                    -- and list those here, so that the user can at least
98                    -- know what config options are missing that prevents
99                    -- them from successfully doing a particular kind of
100                   -- monitoring (that they think they have already
101                   -- enabled via sp_monitor).
102                   --
103                   exec sp_getmessage 19638, @sqlstmt output
104                   print ""
105                   print @sqlstmt
106                   print ""
107   
108                   exec sp_autoformat @fulltabname = #mon_types
109                       , @selectlist = "mon_type, config_name, run_value, cfg_value, type"
110                       , @whereclause = "where (numcfgs_enabled_via_spmon > 0) AND (missing_cfg_option > 0)"
111                       , @orderby = "order by mon_type"
112               end
113   
114   
115               -- Then, report the mapping between monitoring types and
116               -- associated config options enabled.
117               --
118               exec sp_getmessage 19477, @sqlstmt output
119               print " "
120               print @sqlstmt, 'sp_monitor'
121               print " "
122   
123               exec sp_autoformat @fulltabname = #mon_config_for_type
124                   , @selectlist = "'mon_type'=monitor, 'config_name'=configname, run_value, enabled, type"
125                   , @orderby = "order by monitor"
126   
127           end
128   
129           print " "
130           exec sp_getmessage 19477, @sqlstmt output
131           print @sqlstmt, 'sp_configure'
132           print " "
133   
134           -- select * from #mon_config
135   
136           -- Report on any configurations options that might have already
137           -- been set by the user via sp_configure, but are not found in the
138           -- control table. This will only report those config options that
139           -- were set not by sp_monitor, but only by sp_configure/cfgfile.
140           --
141           select mon_type = convert(varchar(100) NULL, "")
142               , config_name = co.name
143               , "run_value" = cu.value
144               , 'cfg_value' = co.value
145               , cu.type
146           into #configures
147           from master.dbo.sysconfigures co
148           , master.dbo.syscurconfigs cu
149           where co.config = cu.config
150               and cu.value != 0
151               and co.config NOT IN (select config from #mon_config)
152               and co.parent = (select i.config
153                   from master.dbo.sysconfigures i
154                   where name = "Monitoring")
155   
156           -- Report the monitoring type if certain config options are found
157           -- to be ON. This lists all known config options that are required
158           -- for various monitoring entities. See the NOTE in the prolog of 
159           -- sp_monitor_verify_cfgval.
160           --
161           update #configures
162           set mon_type = CASE config_name
163   
164                   -- Keep list and entries in THEN clause in alphabetical order.
165                   --
166                   WHEN "SQL batch capture" THEN "connection,statement"
167                   WHEN "deadlock pipe active" THEN "deadlock"
168                   WHEN "deadlock pipe max messages" THEN "deadlock"
169                   WHEN "max SQL text monitored" THEN "connection, statement"
170                   WHEN "per object statistics active" THEN "connection,procedure,statement"
171                   WHEN "process wait events" THEN "event"
172                   WHEN "statement pipe active" THEN "procedure"
173                   WHEN "statement pipe max messages" THEN "procedure,statement"
174                   WHEN "statement statistics active" THEN "procedure,statement"
175                   WHEN "wait event timing" THEN "connection,deadlock,event,statement"
176                   -- WHEN "xx"			THEN "yy" dummy row.
177                   ELSE ""
178               END
179   
180           exec @retval = sp_autoformat @fulltabname = #configures
181               , @selectlist = "config_name, run_value, cfg_value, type, mon_type"
182               , @orderby = "order by mon_type"
183           return @retval
184   
185       end -- }
186   

DEFECTS
 PERR 6 Parsing Error Could not find definition for table tempdb..mon_config 56
 MEST 4 Empty String will be replaced by Single Space 104
 MEST 4 Empty String will be replaced by Single Space 106
 MEST 4 Empty String will be replaced by Single Space 141
 MEST 4 Empty String will be replaced by Single Space 177
 MINU 4 Unique Index with nullable columns master..sysconfigures master..sysconfigures
 MULT 4 Using literal database 'tempdb' tempdb..mon_config 56
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 108
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 123
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 180
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {parent}
152
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(7) 58
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 59
 TNOI 4 Table with no index master..syscurconfigs master..syscurconfigs
 MGTP 3 Grant to public master..sysconfigures  
 MGTP 3 Grant to public master..syscurconfigs  
 MNER 3 No Error Check should check @@error after select into 32
 MNER 3 No Error Check should check return value of exec 39
 MNER 3 No Error Check should check @@error after select into 50
 MNER 3 No Error Check should check return value of exec 103
 MNER 3 No Error Check should check return value of exec 108
 MNER 3 No Error Check should check return value of exec 118
 MNER 3 No Error Check should check return value of exec 123
 MNER 3 No Error Check should check return value of exec 130
 MNER 3 No Error Check should check @@error after select into 141
 MNER 3 No Error Check should check @@error after update 161
 MNER 3 No Error Check should check return value of exec 180
 MUCO 3 Useless Code Useless Begin-End Pair 16
 MUCO 3 Useless Code Useless Brackets 37
 MUCO 3 Useless Code Useless Brackets 71
 MUCO 3 Useless Code Useless Brackets 87
 MUIN 3 Column created using implicit nullability 74
 MZMB 3 Zombie: use of non-existent object tempdb..#mon_config_for_type 55
 MZMB 3 Zombie: use of non-existent object tempdb..mon_config 56
 QCTC 3 Conditional Table Creation 74
 QJWT 3 Join or Sarg Without Index on temp table 151
 QNAJ 3 Not using ANSI Inner Join 56
 QNAJ 3 Not using ANSI Inner Join 147
 QNUA 3 Should use Alias: Table #mon_config 151
 QPNC 3 No column in condition 34
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {name}
154
 MSUB 2 Subquery Marker 88
 MSUB 2 Subquery Marker 152
 MTR1 2 Metrics: Comments Ratio Comments: 36% 14
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 11 = 11dec - 2exi + 2 14
 MTR3 2 Metrics: Query Complexity Complexity: 64 14

DEPENDENCIES
PROCS AND TABLES USED
reads table master..sysconfigures (1)  
calls proc sybsystemprocs..sp_exec_SQL  
   writes table sybsystemprocs..sp_exec_SQL_rset_001 
read_writes table tempdb..#mon_types (1) 
calls proc sybsystemprocs..sp_monitor_list_montypes  
   calls proc sybsystemprocs..sp_autoformat  
      reads table tempdb..systypes (1)  
      writes table sybsystemprocs..sp_autoformat_rset_001 
      writes table sybsystemprocs..sp_autoformat_rset_003 
      writes table sybsystemprocs..sp_autoformat_rset_005 
      read_writes table tempdb..#colinfo_af (1) 
      reads table master..systypes (1)  
      writes table sybsystemprocs..sp_autoformat_rset_004 
      calls proc sybsystemprocs..sp_autoformat  
      calls proc sybsystemprocs..sp_namecrack  
      writes table sybsystemprocs..sp_autoformat_rset_002 
      reads table master..syscolumns (1)  
      reads table tempdb..syscolumns (1)  
   calls proc sybsystemprocs..sp_monitor_verify_setup  
      reads table master..sysobjects (1)  
      calls proc sybsystemprocs..sp_monitor_getcfgval  
         reads table master..sysconfigures (1)  
         reads table master..syscurconfigs (1)  
      calls proc sybsystemprocs..sp_monitor_getcfgnum  
         reads table master..sysconfigures (1)  
   read_writes table tempdb..#mon_types (1) 
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  
   calls proc sybsystemprocs..sp_getmessage  
      reads table master..sysmessages (1)  
      reads table master..syslanguages (1)  
      reads table sybsystemprocs..sysusermessages  
      calls proc sybsystemprocs..sp_validlang  
         reads table master..syslanguages (1)  
   calls proc sybsystemprocs..sp_monitor_verify_cfgval  
      calls proc sybsystemprocs..sp_monitor_getcfgval  
writes table tempdb..#configures (1) 
read_writes table tempdb..#mon_config (1) 
calls proc sybsystemprocs..sp_autoformat  
reads table master..syscurconfigs (1)  
calls proc sybsystemprocs..sp_getmessage