Database | Proc | Application | Created | Links |
sybsystemprocs | sp_sysmon_collect_mda ![]() | ![]() | 31 Aug 14 | Defects 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
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 ![]() |