DatabaseProcApplicationCreatedLinks
sybsystemprocssp_drop_query_tuning  31 Aug 14Defects 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