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


exec sp_procxmode 'sp_monitor_list', 'AnyMode'
go

Grant Execute on sp_monitor_list to public
go
DEFECTS
 PERR 6 Parsing Error Could not find definition for table tempdb..mon_config 59
 MEST 4 Empty String will be replaced by Single Space 107
 MEST 4 Empty String will be replaced by Single Space 109
 MEST 4 Empty String will be replaced by Single Space 144
 MEST 4 Empty String will be replaced by Single Space 180
 MINU 4 Unique Index with nullable columns master..sysconfigures master..sysconfigures
 MTYP 4 Assignment type mismatch @procname: varchar(30) = varchar(100) 24
 MULT 4 Using literal database 'tempdb' tempdb..mon_config 59
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 111
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 126
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 183
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {parent}
155
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(7) 61
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 62
 TNOI 4 Table with no index master..syscurconfigs master..syscurconfigs
 MGTP 3 Grant to public master..sysconfigures  
 MGTP 3 Grant to public master..syscurconfigs  
 MGTP 3 Grant to public sybsystemprocs..sp_monitor_list  
 MNER 3 No Error Check should check @@error after select into 35
 MNER 3 No Error Check should check return value of exec 42
 MNER 3 No Error Check should check @@error after select into 53
 MNER 3 No Error Check should check return value of exec 106
 MNER 3 No Error Check should check return value of exec 111
 MNER 3 No Error Check should check return value of exec 121
 MNER 3 No Error Check should check return value of exec 126
 MNER 3 No Error Check should check return value of exec 133
 MNER 3 No Error Check should check @@error after select into 144
 MNER 3 No Error Check should check @@error after update 164
 MNER 3 No Error Check should check return value of exec 183
 MUCO 3 Useless Code Useless Begin-End Pair 16
 MUCO 3 Useless Code Useless Brackets 25
 MUCO 3 Useless Code Useless Brackets 40
 MUCO 3 Useless Code Useless Brackets 74
 MUCO 3 Useless Code Useless Brackets 90
 MUIN 3 Column created using implicit nullability 77
 MZMB 3 Zombie: use of non-existent object tempdb..#mon_config_for_type 58
 MZMB 3 Zombie: use of non-existent object tempdb..mon_config 59
 QCTC 3 Conditional Table Creation 77
 QJWT 3 Join or Sarg Without Index on temp table 154
 QNAJ 3 Not using ANSI Inner Join 59
 QNAJ 3 Not using ANSI Inner Join 150
 QNUA 3 Should use Alias: Table #mon_config 154
 QPNC 3 No column in condition 37
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {name}
157
 MSUB 2 Subquery Marker 91
 MSUB 2 Subquery Marker 155
 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: 68 14

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