Database | Proc | Application | Created | Links |
sybsystemprocs | sp_drop_query_tuning | 31 Aug 14 | Defects Dependencies |
1 2 /* 3 ** Generated by spgenmsgs.pl on Tue Dec 19 13:42:46 2006 4 */ 5 /* 6 ** raiserror Messages for drop_query_tuning [Total 3] 7 ** 8 ** 17230, "You must be the System Administrator (SA) or the Database Owner (dbo) to execute this procedure." 9 ** 19627, "No query_tuning object with id = '%1!' exists in this database." 10 ** 19628, "No query_tuning group with group id = '%1!' exists in this database." 11 */ 12 /* 13 ** sp_getmessage Messages for drop_query_tuning [Total 0] 14 */ 15 /* 16 ** End spgenmsgs.pl output. 17 */ 18 19 /* 20 ** sp_drop_query_tuning delete records from SYSQUERYPLANS associated with 21 ** a particular query_tuning object or a query_tuning group (sa only). 22 */ 23 24 create procedure sp_drop_query_tuning 25 @gid int = NULL, 26 @id int = NULL 27 as 28 declare @uid int 29 30 set nocount on 31 32 /* Don't start a transaction for the user, he does not expect it. */ 33 if @@trancount = 0 34 begin 35 set chained off 36 end 37 38 /* Don't hold long READ locks, the user might not appreciate it. */ 39 set transaction isolation level 1 40 41 select @uid = nullif (user_id(), 1) 42 43 if @id is not null 44 begin 45 delete from sysqueryplans 46 where 47 uid = isnull(@uid, uid) 48 and id = @id 49 50 if @@rowcount = 0 51 begin 52 /* 19627, "No learning record with id = '%1!' exists in this database." */ 53 raiserror 19627, @id 54 55 return 1 56 end 57 end 58 else 59 begin 60 if user_id() != 1 and charindex("sa_role", show_role()) = 0 61 begin 62 /* 17230, "You must be the System Administrator (SA) or the Database Owner (dbo) to execute this procedure." */ 63 raiserror 17230 64 65 return 2 66 end 67 else 68 begin 69 if @gid <= 0 or not exists (select 1 from sysqueryplans sq1 where gid = @gid and exists (select 1 from sysqueryplans sq2 where sq2.id = sq1.id and sq2.type > 100)) 70 begin 71 /* 19628, "No learning group with group id = '%1!' exists in this database." */ 72 raiserror 19628, @gid 73 74 return 3 75 end 76 else 77 begin 78 delete from sysqueryplans 79 where 80 gid = @gid and 81 id in (select id from sysqueryplans where type > 100 group by id) 82 end 83 end 84 end 85 86 return 0 87
exec sp_procxmode 'sp_drop_query_tuning', 'AnyMode' go Grant Execute on sp_drop_query_tuning to public go
DEFECTS | |
QJWI 5 Join or Sarg Without Index | 81 |
QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysqueryplans.csysqueryplans unique clustered (uid, gid, hashkey, id, type, sequence) Intersection: {id} | 48 |
QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysqueryplans.csysqueryplans unique clustered (uid, gid, hashkey, id, type, sequence) Intersection: {id} Uncovered: [uid, gid, hashkey, sequence] | 69 |
QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysqueryplans.csysqueryplans unique clustered (uid, gid, hashkey, id, type, sequence) Intersection: {id} Uncovered: [uid, gid, hashkey, type, sequence] | 69 |
QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysqueryplans.csysqueryplans unique clustered (uid, gid, hashkey, id, type, sequence) Intersection: {gid} | 69 |
QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysqueryplans.csysqueryplans unique clustered (uid, gid, hashkey, id, type, sequence) Intersection: {gid} | 80 |
QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysqueryplans.csysqueryplans unique clustered (uid, gid, hashkey, id, type, sequence) Intersection: {type} | 81 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 69 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 81 |
MGTP 3 Grant to public sybsystemprocs..sp_drop_query_tuning | |
MGTP 3 Grant to public sybsystemprocs..sysqueryplans | |
MNER 3 No Error Check should check @@error after delete | 45 |
MNER 3 No Error Check should check @@error after delete | 78 |
MUOT 3 Updates outside transaction | 78 |
QISO 3 Set isolation level | 39 |
MSUB 2 Subquery Marker | 69 |
MSUB 2 Subquery Marker | 81 |
MSUC 2 Correlated Subquery Marker | 69 |
MTR1 2 Metrics: Comments Ratio Comments: 49% | 24 |
MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 7 = 9dec - 4exi + 2 | 24 |
MTR3 2 Metrics: Query Complexity Complexity: 41 | 24 |
PRED_QUERY_COLLECTION 2 {q=sybsystemprocs..sysqueryplans, q2=sybsystemprocs..sysqueryplans} 0 | 69 |
DEPENDENCIES |
PROCS AND TABLES USED read_writes table sybsystemprocs..sysqueryplans CALLERS called by proc sybsystemprocs..sp_query_tuning |