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 createproceduresp_monitor_list15 as16 begin17 declare@sqlstmt varchar(100)18 ,@retval int
19 20 -- For consistency with other commands, require both mon_role and21 -- 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@sqlstmt25 if(@retval!= 0)26 return@retval27 28 set nocount on29 30 -- Create a #temp table in which we will stash away any config31 -- options found to b set in the control table. The control table32 -- itself might not be found, so we have to play games by creating33 -- a temp table first, and then use it to join later on.34 --35 selectconfiginto #mon_config
36 frommaster.dbo.sysconfigures37 where 1 = 0
38 39 print " "
40 if(object_id('tempdb.dbo.mon_config')ISNULL)41 begin42 execsp_getmessage 19476,@sqlstmtoutput43 print@sqlstmt, 'sp_monitor'
44 print " "
45 end46 else47 begin48 -- First collect and display the config items needed49 -- that are needed to be ON for the monitoring entity50 -- types that are seen to be 'enable'd from the control51 -- 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 fromtempdb.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 for66 -- 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@retval76 77 createtable #mon_types(78 mon_type varchar(30)79 ,missing_cfg_option intdefault 0
80 ,enabled intdefault 0
81 ,numcfgs_enabled_via_spmon intdefault 0
82 ,config_name varchar(100)NULL83 ,type varchar(10)NULL84 ,cfg_value intdefault 0
85 ,run_value intdefault 0
86 )lock allpages
87 88 exec@retval=sp_monitor_list_montypes89 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 begin95 -- We found some monitoring config options were ON, but96 -- in some cases they might not be turning ON all the97 -- required config options. Do a match between the98 -- monitoring entties that are known to be enabled99 -- via sp_monitor v/s their missing config options,100 -- and list those here, so that the user can at least101 -- know what config options are missing that prevents102 -- them from successfully doing a particular kind of103 -- monitoring (that they think they have already104 -- enabled via sp_monitor).105 --106 execsp_getmessage 19638,@sqlstmtoutput107 print ""
108 print@sqlstmt109 print ""
110 111 execsp_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 end116 117 118 -- Then, report the mapping between monitoring types and119 -- associated config options enabled.120 --121 execsp_getmessage 19477,@sqlstmtoutput122 print " "
123 print@sqlstmt, 'sp_monitor'
124 print " "
125 126 execsp_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 end131 132 print " "
133 execsp_getmessage 19477,@sqlstmtoutput134 print@sqlstmt, 'sp_configure'
135 print " "
136 137 -- select * from #mon_config138 139 -- Report on any configurations options that might have already140 -- been set by the user via sp_configure, but are not found in the141 -- control table. This will only report those config options that142 -- 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.name146 , "run_value" =cu.value147 , 'cfg_value' =co.value148 ,cu.type149 into #configures
150 frommaster.dbo.sysconfigures co
151 ,master.dbo.syscurconfigs cu
152 whereco.config=cu.config153 andcu.value!= 0
154 andco.configNOTIN(select config from #mon_config)155 andco.parent=(selecti.config156 frommaster.dbo.sysconfigures i
157 wherename= "Monitoring")158 159 -- Report the monitoring type if certain config options are found160 -- to be ON. This lists all known config options that are required161 -- 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 END182 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@retval187 188 end-- }189
exec sp_procxmode 'sp_monitor_list', 'AnyMode'
go
Grant Execute on sp_monitor_list to public
go