DatabaseProcApplicationCreatedLinks
sybsystemprocssp_sysmon_collect_mda  31 Aug 14Defects Dependencies

1     
2     /*
3     ** This sproc is installed as a part of installmontables
4     ** The caller is responsible for checking if this stored procedure
5     ** exists or not.
6     */
7     
8     create procedure sp_sysmon_collect_mda
9         @interval_or_option char(12), /* Time interval string, option
10        ** "begin_sample" or "end_sample" */
11        @mda_status int output /* used in remembering mda enable
12    				** status */
13    as
14    
15        /* ----------- declare local variables ------------ */
16        declare @msg varchar(250) /* error msg output */
17        declare @snapshot_time datetime /* time recorded in row */
18    
19        select @snapshot_time = getdate()
20    
21        if (@interval_or_option != "end_sample")
22        begin
23            if exists (select 1 from tempdb.dbo.sysobjects
24                    where name like 'tempcachestats' and uid = 1)
25            begin
26                drop table tempdb.dbo.tempcachestats
27            end
28    
29            if exists (select 1 from tempdb.dbo.sysobjects
30                    where name like 'tempbufpoolstats' and uid = 1)
31            begin
32                drop table tempdb.dbo.tempbufpoolstats
33            end
34    
35            if exists (select 1 from tempdb.dbo.sysobjects
36                    where name like 'tempobjstats' and uid = 1)
37            begin
38                drop table tempdb.dbo.tempobjstats
39            end
40    
41            if exists (select 1 from tempdb.dbo.sysobjects
42                    where name like 'tempcachedobjstats' and uid = 1)
43            begin
44                drop table tempdb.dbo.tempcachedobjstats
45            end
46    
47            /* Create the schema */
48            select @snapshot_time as "InsertTime",
49                @mda_status as "MdaStatus",
50                CacheID, RelaxedReplacement,
51                PhysicalReads,
52                LogicalReads, CachePartitions, CacheName
53            into tempdb.dbo.tempcachestats
54            from master.dbo.monDataCache
55    
56            select @snapshot_time as "InsertTime",
57                CacheID, IOBufferSize,
58                AllocatedKB, PagesTouched, PhysicalReads,
59                CacheName
60            into tempdb.dbo.tempbufpoolstats
61            from master.dbo.monCachePool
62    
63            select @snapshot_time as "InsertTime",
64                LogicalReads, PhysicalReads,
65                DBID, ObjectID, IndexID
66            into tempdb.dbo.tempobjstats
67            from master.dbo.monOpenObjectActivity
68    
69            select @snapshot_time as "InsertTime",
70                CachedKB, CacheName, DBID,
71                ObjectID, IndexID
72            into tempdb.dbo.tempcachedobjstats
73            from master.dbo.monCachedObject
74        end
75        else
76        begin
77            /* raise an error if table does not exist */
78            if not exists (select 1 from tempdb.dbo.sysobjects where
79                        name = 'tempcachestats')
80            begin
81                /* If we only wanted to know if config values were changed
82                ** just return 0.
83                */
84                if (@mda_status = 99)
85                begin
86                    return (0)
87                end
88                raiserror 17733, 'tempdb..tempcachestats'
89                return (1)
90            end
91    
92            /* 
93            ** Get the Value of MDA status stored at begin sample 
94            ** If @mda_status was passed as 99 we should return from here.
95            */
96            if (@mda_status = 99)
97            begin
98                select @mda_status = MdaStatus from tempdb.dbo.tempcachestats
99                return (0)
100           end
101           select @mda_status = MdaStatus from tempdb.dbo.tempcachestats
102   
103           if not exists (select 1 from tempdb.dbo.sysobjects where
104                       name = 'tempbufpoolstats')
105           begin
106               raiserror 17733, 'tempdb..tempbufpoolstats'
107               return (1)
108           end
109   
110           if not exists (select 1 from tempdb.dbo.sysobjects where
111                       name = 'tempobjstats')
112           begin
113               raiserror 17733, 'tempdb..tempobjstats'
114               return (1)
115           end
116   
117           if not exists (select 1 from tempdb.dbo.sysobjects where
118                       name = 'tempcachedobjstats')
119           begin
120               raiserror 17733, 'tempdb..tempcachedobjstats'
121               return (1)
122           end
123   
124           /* Store the snapshot with time stamp for MDA tables */
125           insert into tempdb.dbo.tempcachestats
126           select @snapshot_time as "InsertTime",
127               @mda_status as "MdaStatus",
128               CacheID, RelaxedReplacement,
129               PhysicalReads, LogicalReads, CachePartitions, CacheName
130           from master.dbo.monDataCache
131   
132           insert into tempdb.dbo.tempbufpoolstats
133           select @snapshot_time as "InsertTime",
134               CacheID, IOBufferSize,
135               AllocatedKB, PagesTouched, PhysicalReads,
136               CacheName
137           from master.dbo.monCachePool
138   
139           insert into tempdb.dbo.tempobjstats
140           select @snapshot_time as "InsertTime",
141               LogicalReads, PhysicalReads,
142               DBID, ObjectID, IndexID
143           from master.dbo.monOpenObjectActivity
144   
145           insert into tempdb.dbo.tempcachedobjstats
146           select @snapshot_time as "InsertTime",
147               CachedKB, CacheName, DBID,
148               ObjectID, IndexID
149           from master.dbo.monCachedObject
150       end
151       return 0
152   


exec sp_procxmode 'sp_sysmon_collect_mda', 'AnyMode'
go

Grant Execute on sp_sysmon_collect_mda to public
go
DEFECTS
 PERR 6 Parsing Error Could not find definition for table tempdb..tempcachestats 98
 PERR 6 Parsing Error Could not find definition for table tempdb..tempcachestats 101
 PERR 6 Parsing Error Could not find definition for table tempdb..tempcachestats 125
 PERR 6 Parsing Error Could not find definition for table tempdb..tempbufpoolstats 132
 PERR 6 Parsing Error Could not find definition for table tempdb..tempobjstats 139
 PERR 6 Parsing Error Could not find definition for table tempdb..tempcachedobjstats 145
 MULT 4 Using literal database 'tempdb' tempdb..sysobjects 23
 MULT 4 Using literal database 'tempdb' tempdb..sysobjects 29
 MULT 4 Using literal database 'tempdb' tempdb..sysobjects 35
 MULT 4 Using literal database 'tempdb' tempdb..sysobjects 41
 MULT 4 Using literal database 'tempdb' tempdb..tempcachestats 53
 MULT 4 Using literal database 'tempdb' tempdb..tempbufpoolstats 60
 MULT 4 Using literal database 'tempdb' tempdb..tempobjstats 66
 MULT 4 Using literal database 'tempdb' tempdb..tempcachedobjstats 72
 MULT 4 Using literal database 'tempdb' tempdb..sysobjects 78
 MULT 4 Using literal database 'tempdb' tempdb..tempcachestats 98
 MULT 4 Using literal database 'tempdb' tempdb..tempcachestats 101
 MULT 4 Using literal database 'tempdb' tempdb..sysobjects 103
 MULT 4 Using literal database 'tempdb' tempdb..sysobjects 110
 MULT 4 Using literal database 'tempdb' tempdb..sysobjects 117
 MULT 4 Using literal database 'tempdb' tempdb..tempcachestats 125
 MULT 4 Using literal database 'tempdb' tempdb..tempbufpoolstats 132
 MULT 4 Using literal database 'tempdb' tempdb..tempobjstats 139
 MULT 4 Using literal database 'tempdb' tempdb..tempcachedobjstats 145
 TNOI 4 Table with no index master..monCachePool master..monCachePool
 TNOI 4 Table with no index master..monCachedObject master..monCachedObject
 TNOI 4 Table with no index master..monDataCache master..monDataCache
 TNOI 4 Table with no index master..monOpenObjectActivity master..monOpenObjectActivity
 MGTP 3 Grant to public sybsystemprocs..sp_sysmon_collect_mda  
 MGTP 3 Grant to public tempdb..sysobjects  
 MNER 3 No Error Check should check @@error after select into 48
 MNER 3 No Error Check should check @@error after select into 56
 MNER 3 No Error Check should check @@error after select into 63
 MNER 3 No Error Check should check @@error after select into 69
 MNER 3 No Error Check should check @@error after insert 125
 MNER 3 No Error Check should check @@error after insert 132
 MNER 3 No Error Check should check @@error after insert 139
 MNER 3 No Error Check should check @@error after insert 145
 MUCO 3 Useless Code Useless Brackets 21
 MUCO 3 Useless Code Useless Brackets 84
 MUCO 3 Useless Code Useless Brackets 86
 MUCO 3 Useless Code Useless Brackets 89
 MUCO 3 Useless Code Useless Brackets 96
 MUCO 3 Useless Code Useless Brackets 99
 MUCO 3 Useless Code Useless Brackets 107
 MUCO 3 Useless Code Useless Brackets 114
 MUCO 3 Useless Code Useless Brackets 121
 MZMB 3 Zombie: use of non-existent object tempdb..tempcachestats 53
 MZMB 3 Zombie: use of non-existent object tempdb..tempbufpoolstats 60
 MZMB 3 Zombie: use of non-existent object tempdb..tempobjstats 66
 MZMB 3 Zombie: use of non-existent object tempdb..tempcachedobjstats 72
 MZMB 3 Zombie: use of non-existent object tempdb..tempcachestats 98
 MZMB 3 Zombie: use of non-existent object tempdb..tempcachestats 101
 MZMB 3 Zombie: use of non-existent object tempdb..tempcachestats 125
 MZMB 3 Zombie: use of non-existent object tempdb..tempbufpoolstats 132
 MZMB 3 Zombie: use of non-existent object tempdb..tempobjstats 139
 MZMB 3 Zombie: use of non-existent object tempdb..tempcachedobjstats 145
 QAPT 3 Access to Proxy Table master..monDataCache 54
 QAPT 3 Access to Proxy Table master..monCachePool 61
 QAPT 3 Access to Proxy Table master..monOpenObjectActivity 67
 QAPT 3 Access to Proxy Table master..monCachedObject 73
 QAPT 3 Access to Proxy Table master..monDataCache 130
 QAPT 3 Access to Proxy Table master..monCachePool 137
 QAPT 3 Access to Proxy Table master..monOpenObjectActivity 143
 QAPT 3 Access to Proxy Table master..monCachedObject 149
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {name}
79
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {name}
104
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {name}
111
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {name}
118
 VUNU 3 Variable is not used @msg 16
 MSUB 2 Subquery Marker 23
 MSUB 2 Subquery Marker 29
 MSUB 2 Subquery Marker 35
 MSUB 2 Subquery Marker 41
 MSUB 2 Subquery Marker 78
 MSUB 2 Subquery Marker 103
 MSUB 2 Subquery Marker 110
 MSUB 2 Subquery Marker 117
 MTR1 2 Metrics: Comments Ratio Comments: 16% 8
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 10 = 15dec - 7exi + 2 8
 MTR3 2 Metrics: Query Complexity Complexity: 89 8

DEPENDENCIES
PROCS AND TABLES USED
reads table master..monCachePool (1)  
reads table master..monOpenObjectActivity (1)  
reads table master..monDataCache (1)  
reads table tempdb..sysobjects (1)  
reads table master..monCachedObject (1)  

CALLERS
called by proc sybsystemprocs..sp_sysmon_collect  
   called by proc sybsystemprocs..sp_sysmon