DatabaseProcApplicationCreatedLinks
sybsystemprocssp_show_query_tuning  31 Aug 14Defects Dependencies

1     
2     /*
3     ** Generated by spgenmsgs.pl on Tue Dec 19 13:43:49 2006 
4     */
5     /*
6     ** raiserror Messages for show_query_tuning [Total 0]
7     */
8     /*
9     ** sp_getmessage Messages for show_query_tuning [Total 0]
10    */
11    /*
12    ** End spgenmsgs.pl output.
13    */
14    
15    /*
16    ** sp_show_query_tuning
17    **
18    ** Display information in query_tuning groups
19    ** Requires Galaxy or later
20    **
21    */
22    
23    create procedure sp_show_query_tuning
24        @showtype varchar(30) = NULL
25    as
26        declare @msg varchar(250)
27        declare @uid int
28    
29        set nocount on
30    
31        /* 
32        ** sa is allowed to query all query_tuning entries (including uid 0 entry). 
33        ** Regular user can only query the rows with the same uid.
34        */
35        if user_id() != 1 and charindex("sa_role", show_role()) = 0
36        begin
37            select @uid = user_id()
38        end
39        else
40        begin
41            select @uid = null
42        end
43    
44        if (@showtype = 'query_tuning_objects')
45        begin
46            select gid 'Group ID', count(*) 'Query Tuning Objects'
47            from sysqueryplans
48            where ((type > 100 and type < 1000) or type > 1000)
49                and uid = isnull(@uid, uid)
50            group by gid
51            order by gid
52    
53            return 0
54        end
55    
56        if (@showtype = 'ase125_better_plans')
57        begin
58            select id, sequence, text
59            from sysqueryplans sq1
60            where type < 100
61                and uid = isnull(@uid, uid)
62                and id in
63                    (select id from sysqueryplans sq2
64                    where sq2.id = sq1.id
65                        and sq2.type > 1000
66                        and (sq2.status & 2) != 0
67                        and not exists
68                            (select id from sysqueryplans sq3
69                            where sq3.id = sq2.id
70                                and sq3.type > 1000
71                                and (sq3.status is NULL or (sq3.status & 2) = 0)
72                                and convert(int, substring(sq3.text, charindex('t3', sq3.text) + 3, charindex('l1', sq3.text) - charindex('t3', sq3.text) - 4)) < convert(int, substring(sq2.text, charindex('t3', sq2.text) + 3, charindex('l1', sq2.text) - charindex('t3', sq2.text) - 4)))
73                        and exists
74                            (select id from sysqueryplans sq4
75                            where sq4.id = sq2.id
76                                and sq4.type > 1000
77                                and (sq4.status is NULL or (sq4.status & 2) = 0)
78                                and convert(int, substring(sq4.text, charindex('t3', sq4.text) + 3, charindex('l1', sq4.text) - charindex('t3', sq4.text) - 4)) > convert(int, substring(sq2.text, charindex('t3', sq2.text) + 3, charindex('l1', sq2.text) - charindex('t3', sq2.text) - 4))))
79    
80            return 0
81        end
82    
83        if (@showtype = 'ase15_better_plans')
84        begin
85            select id, sequence, text
86            from sysqueryplans sq1
87            where type < 100
88                and uid = isnull(@uid, uid)
89                and id in
90                    (select id from sysqueryplans sq2
91                    where sq2.id = sq1.id
92                        and sq2.type > 1000
93                        and (sq2.status is NULL or (sq2.status & 2) = 0)
94                        and not exists
95                            (select id from sysqueryplans sq3
96                            where sq3.id = sq2.id
97                                and sq3.type > 1000
98                                and (sq3.status & 2) != 0
99                                and convert(int, substring(sq3.text, charindex('t3', sq3.text) + 3, charindex('l1', sq3.text) - charindex('t3', sq3.text) - 4)) < convert(int, substring(sq2.text, charindex('t3', sq2.text) + 3, charindex('l1', sq2.text) - charindex('t3', sq2.text) - 4)))
100                       and exists
101                           (select id from sysqueryplans sq4
102                           where sq4.id = sq2.id
103                               and sq4.type > 1000
104                               and (sq4.status & 2) != 0
105                               and convert(int, substring(sq4.text, charindex('t3', sq4.text) + 3, charindex('l1', sq4.text) - charindex('t3', sq4.text) - 4)) > convert(int, substring(sq2.text, charindex('t3', sq2.text) + 3, charindex('l1', sq2.text) - charindex('t3', sq2.text) - 4))))
106   
107           return 0
108       end
109   
110       return 1
111   


exec sp_procxmode 'sp_show_query_tuning', 'AnyMode'
go

Grant Execute on sp_show_query_tuning to public
go
RESULT SETS
sp_show_query_tuning_rset_001
sp_show_query_tuning_rset_003
sp_show_query_tuning_rset_002

DEFECTS
 QJWI 5 Join or Sarg Without Index 62
 QJWI 5 Join or Sarg Without Index 89
 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}		 48
 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}		 60
 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]		 64
 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]		 64
 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 JOIN Candidate index: sysqueryplans.csysqueryplans unique clustered
(uid, gid, hashkey, id, type, sequence)
Intersection: {id}
Uncovered: [uid, gid, hashkey, sequence]		 75
 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]		 75
 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}		 87
 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]		 91
 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]		 91
 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]		 96
 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]		 96
 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]		 102
 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]		 102
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 48
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 60
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 65
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 70
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 76
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 87
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 92
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 97
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 103
 MGTP 3 Grant to public sybsystemprocs..sp_show_query_tuning  
 MGTP 3 Grant to public sybsystemprocs..sysqueryplans  
 MUCO 3 Useless Code Useless Brackets 44
 MUCO 3 Useless Code Useless Brackets 56
 MUCO 3 Useless Code Useless Brackets 83
 QCRS 3 Conditional Result Set 46
 QCRS 3 Conditional Result Set 58
 QCRS 3 Conditional Result Set 85
 VUNU 3 Variable is not used @msg 26
 MRST 2 Result Set Marker 46
 MRST 2 Result Set Marker 58
 MRST 2 Result Set Marker 85
 MSUC 2 Correlated Subquery Marker 63
 MSUC 2 Correlated Subquery Marker 68
 MSUC 2 Correlated Subquery Marker 74
 MSUC 2 Correlated Subquery Marker 90
 MSUC 2 Correlated Subquery Marker 95
 MSUC 2 Correlated Subquery Marker 101
 MTR1 2 Metrics: Comments Ratio Comments: 13% 23
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 3 = 5dec - 4exi + 2 23
 MTR3 2 Metrics: Query Complexity Complexity: 70 23
 PRED_QUERY_COLLECTION 2 {q=sybsystemprocs..sysqueryplans, q2=sybsystemprocs..sysqueryplans} 0 63
 PRED_QUERY_COLLECTION 2 {q=sybsystemprocs..sysqueryplans, q2=sybsystemprocs..sysqueryplans} 0 68
 PRED_QUERY_COLLECTION 2 {q=sybsystemprocs..sysqueryplans, q2=sybsystemprocs..sysqueryplans} 0 74
 PRED_QUERY_COLLECTION 2 {q=sybsystemprocs..sysqueryplans, q2=sybsystemprocs..sysqueryplans} 0 90
 PRED_QUERY_COLLECTION 2 {q=sybsystemprocs..sysqueryplans, q2=sybsystemprocs..sysqueryplans} 0 95
 PRED_QUERY_COLLECTION 2 {q=sybsystemprocs..sysqueryplans, q2=sybsystemprocs..sysqueryplans} 0 101

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..sysqueryplans  

CALLERS
called by proc sybsystemprocs..sp_query_tuning