DatabaseProcApplicationCreatedLinks
sybsystemprocssp_help_qpgroup  31 Aug 14Defects Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     
4     /*
5     ** Messages for "sp_help_qpgroup"
6     **
7     ** 18641, "Query plan groups in database '%1!'"
8     ** 18639, "The is no query plans group named '%1!' in this database."
9     ** 18642, "Query plans group '%1!', GID %2!"
10    ** 18643, "sysqueryplans rows consumption, number of query plans per row count"
11    ** 18644, "Query plans that use the most sysqueryplans rows"
12    ** 18645, "There is no hash key collision in this group."
13    ** 18646, "Number of collisions per hash key"
14    ** 18647, "Colliding query plan IDs per hash key"
15    ** 18648, "Query plans in this group"
16    ** 18640, "Unknown %1! option : '%2!'. Valid options are : %3!."
17    ** 18649, "The query plans group is empty."
18    */
19    
20    /*
21    ** sp_help_qpgroup displays information about an AP group. The legal 
22    ** modes are : "full", "stats", "hash", "list", "queries", "plans" 
23    ** or "counts".
24    */
25    
26    create procedure sp_help_qpgroup
27        @name varchar(255) = null,
28        @mode varchar(20) = "full"
29    as
30        declare
31            @msg varchar(1024),
32            @uid int,
33            @gid int,
34            @class int,
35            @attribute int,
36            @object_type char(2)
37    
38        set nocount on
39    
40        /* Don't start a transaction for the user, he does not expect it. */
41        if @@trancount = 0
42        begin
43            set chained off
44        end
45    
46        /* Don't hold long READ locks, the user might not appreciate it. */
47        set transaction isolation level 1
48    
49    
50        select @uid = nullif (user_id(), 1)
51    
52        exec sp_aux_sargs_qpgroup @class out, @attribute out, @object_type out
53    
54        if @name is null
55        begin
56            select @name = db_name()
57    
58            /* 18641, "Query plan groups in database '%1!'" */
59            exec sp_getmessage 18641, @msg out
60            print @msg, @name
61    
62            select object_cinfo,
63                GID = object,
64                Plans = count(distinct id)
65            into #sphelpqpgroup1rs
66            from sysattributes, sysqueryplans
67            where class = @class
68                and attribute = @attribute
69                and object_type = @object_type
70                and uid = isnull(@uid, uid)
71                and object *= gid
72            group by object_cinfo, gid
73            exec sp_autoformat @fulltabname = #sphelpqpgroup1rs,
74                @selectlist = "'Group' = object_cinfo, GID, Plans",
75                @orderby = "order by 1"
76            drop table #sphelpqpgroup1rs
77    
78            return 0
79        end
80    
81        exec sp_aux_get_qpgroup @name, @gid out
82    
83        if @gid is null
84        begin
85            /* 18639, "The is no query plans group named '%1!' in this database." */
86            raiserror 18639, @name
87    
88            return 1
89        end
90    
91        /* 18642, "Query plans group '%1!', GID %2!" */
92        exec sp_getmessage 18642, @msg out
93        print ""
94        print @msg, @name, @gid
95        print ""
96    
97        select count(*) as "Total Rows", count(distinct id) as "Total QueryPlans"
98        from sysqueryplans
99        where
100           uid = isnull(@uid, uid)
101           and gid = @gid
102   
103       -- group summary modes
104   
105       if (@mode = "stats" or @mode = "full")
106       begin
107           select id, count(*) as cnt
108           into #rows
109           from sysqueryplans
110           where uid = isnull(@uid, uid)
111               and gid = @gid
112           group by id
113   
114           /* 18643, "sysqueryplans rows consumption, number of query plans per row count" */
115           exec sp_getmessage 18643, @msg out
116           print @msg
117   
118           select cnt as "Rows", count(*) as "Plans"
119           from #rows
120           group by cnt
121           order by cnt desc
122   
123           if @@rowcount > 1
124           begin
125               /* 18644, "Query plans that use the most sysqueryplans rows" */
126               exec sp_getmessage 18644, @msg out
127               print @msg
128   
129               select cnt as "Rows", id as "Plan"
130               from #rows
131               where cnt > (select avg(cnt) + 1
132                       from #rows)
133               order by cnt desc, id
134           end
135   
136           if @mode != "full"
137               return 0
138       end
139   
140       if (@mode = "hash" or @mode = "full")
141       begin
142           select count(distinct hashkey) as "Hashkeys"
143           from sysqueryplans
144           where uid = isnull(@uid, uid)
145               and gid = @gid
146               and type = 10
147               and sequence = 0
148   
149           select hashkey, count(*) as collisions
150           into #colls
151           from sysqueryplans o
152           where uid = isnull(@uid, uid)
153               and gid = @gid
154               and type = 10
155               and sequence = 0
156               and exists (select *
157                   from sysqueryplans
158                   where uid = o.uid
159                       and gid = o.gid
160                       and hashkey = o.hashkey
161                       and id != o.id)
162           group by hashkey
163   
164           if @@rowcount = 0
165           begin
166               /* 18645, "There is no hash key collision in this group." */
167               exec sp_getmessage 18645, @msg out
168               print @msg
169           end
170           else
171           begin
172               /* 18646, "Number of collisions per hash key" */
173               exec sp_getmessage 18646, @msg out
174               print @msg
175   
176               select collisions, hashkey from #colls
177               order by collisions desc, hashkey
178   
179               /* 18647, "Colliding query plan IDs per hash key" */
180               exec sp_getmessage 18647, @msg out
181               print @msg
182   
183               select c.collisions, c.hashkey, q.id
184               from #colls c, sysqueryplans q
185               where gid = @gid
186                   and c.hashkey = q.hashkey
187               group by c.collisions, c.hashkey, q.id
188               order by c.collisions, c.hashkey, q.id
189           end
190   
191           if @mode != "full"
192               return 0
193       end
194   
195       if @mode = "full"
196           return 0
197   
198       -- per plan modes
199   
200       /* 18648, "Query plans in this group" */
201       exec sp_getmessage 18648, @msg out
202   
203       print ""
204       print @msg
205   
206       if (@mode = "list")
207       begin
208           select hashkey, id,
209                   (select substring(text, 1, 20) + "..."
210                   from sysqueryplans i
211                   where o.id = i.id
212                       and type = 10
213                       and sequence = 0) as "query",
214                   (select substring(text, 1, 20) + "..."
215                   from sysqueryplans i
216                   where o.id = i.id
217                       and type = 100
218                       and sequence = 0) as "plan"
219           from sysqueryplans o
220           where uid = isnull(@uid, uid)
221               and gid = @gid
222           group by hashkey, id
223           order by hashkey, id
224   
225           goto finish
226       end
227   
228       if (@mode = "queries")
229       begin
230           select hashkey, id,
231                   (select substring(text, 1, 50) + "..."
232                   from sysqueryplans i
233                   where o.id = i.id
234                       and type = 10
235                       and sequence = 0) as "query"
236           from sysqueryplans o
237           where uid = isnull(@uid, uid)
238               and gid = @gid
239           group by hashkey, id
240           order by hashkey, id
241   
242           goto finish
243       end
244   
245       if (@mode = "plans")
246       begin
247           select hashkey, id,
248                   (select substring(text, 1, 50) + "..."
249                   from sysqueryplans i
250                   where o.id = i.id
251                       and type = 100
252                       and sequence = 0) as "plan"
253           from sysqueryplans o
254           where uid = isnull(@uid, uid)
255               and gid = @gid
256           group by hashkey, id
257           order by hashkey, id
258   
259           goto finish
260       end
261   
262       if (@mode = "counts")
263       begin
264           select count(*) as "Rows",
265               sum(char_length(text)) as "Chars",
266               hashkey, id,
267                   (select substring(text, 1, 25) + "..."
268                   from sysqueryplans i
269                   where o.id = i.id
270                       and type = 10
271                       and sequence = 0) as "query"
272           from sysqueryplans o
273           where uid = isnull(@uid, uid)
274               and gid = @gid
275           group by hashkey, id
276           order by 1 desc, 2 desc, hashkey, id
277   
278           goto finish
279       end
280   
281       /* 18640, "Unknown %1! option : '%2!'. Valid options are : %3!." */
282       raiserror 18640, "sp_help_qpgroup", @mode, "'full', 'stats', 'hash', 'list', 'queries', 'plans', 'counts'"
283   
284       return 1
285   
286   finish:
287       if @@rowcount = 0
288       begin
289           /* 18649, "The query plans group is empty." */
290           exec sp_getmessage 18649, @msg out
291           print @msg
292       end
293   
294       return 0
295   


exec sp_procxmode 'sp_help_qpgroup', 'AnyMode'
go

Grant Execute on sp_help_qpgroup to public
go
RESULT SETS
sp_help_qpgroup_rset_010
sp_help_qpgroup_rset_009
sp_help_qpgroup_rset_008
sp_help_qpgroup_rset_007
sp_help_qpgroup_rset_006
sp_help_qpgroup_rset_005
sp_help_qpgroup_rset_004
sp_help_qpgroup_rset_003
sp_help_qpgroup_rset_002
sp_help_qpgroup_rset_001

DEFECTS
 QBGB 6 Bad group by object 63
 QJWI 5 Join or Sarg Without Index 71
 MEST 4 Empty String will be replaced by Single Space 93
 MEST 4 Empty String will be replaced by Single Space 95
 MEST 4 Empty String will be replaced by Single Space 203
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysattributes sybsystemprocs..sysattributes
 MTYP 4 Assignment type mismatch @name: varchar(30) = varchar(255) 81
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 73
 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}		 101
 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}		 111
 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, type, sequence}		 145
 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, type, sequence}		 153
 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}		 185
 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]		 211
 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]		 211
 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]		 216
 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]		 216
 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}		 221
 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]		 233
 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]		 233
 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}		 238
 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]		 250
 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]		 250
 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}		 255
 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]		 269
 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]		 269
 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}		 274
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 67
 QTYP 4 Comparison type mismatch smallint = int 67
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 68
 QTYP 4 Comparison type mismatch smallint = int 68
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 146
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 147
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 154
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 155
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 212
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 213
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 217
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 218
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 234
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 235
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 251
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 252
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 270
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 271
 MGTP 3 Grant to public sybsystemprocs..sp_help_qpgroup  
 MGTP 3 Grant to public sybsystemprocs..sysattributes  
 MGTP 3 Grant to public sybsystemprocs..sysqueryplans  
 MNEJ 3 'Not Equal' join 161
 MNER 3 No Error Check should check return value of exec 52
 MNER 3 No Error Check should check return value of exec 59
 MNER 3 No Error Check should check @@error after select into 62
 MNER 3 No Error Check should check return value of exec 73
 MNER 3 No Error Check should check return value of exec 81
 MNER 3 No Error Check should check return value of exec 92
 MNER 3 No Error Check should check @@error after select into 107
 MNER 3 No Error Check should check return value of exec 115
 MNER 3 No Error Check should check return value of exec 126
 MNER 3 No Error Check should check @@error after select into 149
 MNER 3 No Error Check should check return value of exec 167
 MNER 3 No Error Check should check return value of exec 173
 MNER 3 No Error Check should check return value of exec 180
 MNER 3 No Error Check should check return value of exec 201
 MNER 3 No Error Check should check return value of exec 290
 MUCO 3 Useless Code Useless Brackets 105
 MUCO 3 Useless Code Useless Brackets 140
 MUCO 3 Useless Code Useless Brackets 206
 MUCO 3 Useless Code Useless Brackets 228
 MUCO 3 Useless Code Useless Brackets 245
 MUCO 3 Useless Code Useless Brackets 262
 QCRS 3 Conditional Result Set 118
 QCRS 3 Conditional Result Set 129
 QCRS 3 Conditional Result Set 142
 QCRS 3 Conditional Result Set 176
 QCRS 3 Conditional Result Set 183
 QCRS 3 Conditional Result Set 208
 QCRS 3 Conditional Result Set 230
 QCRS 3 Conditional Result Set 247
 QCRS 3 Conditional Result Set 264
 QCTC 3 Conditional Table Creation 62
 QCTC 3 Conditional Table Creation 107
 QCTC 3 Conditional Table Creation 149
 QISO 3 Set isolation level 47
 QJWT 3 Join or Sarg Without Index on temp table 186
 QNAJ 3 Not using ANSI Inner Join 184
 QNAO 3 Not using ANSI Outer Join 66
 QNUA 3 Should use Alias: Column object_cinfo should use alias sysattributes 62
 QNUA 3 Should use Alias: Column object should use alias sysattributes 63
 QNUA 3 Should use Alias: Column id should use alias sysqueryplans 64
 QNUA 3 Should use Alias: Table sybsystemprocs..sysattributes 66
 QNUA 3 Should use Alias: Table sybsystemprocs..sysqueryplans 66
 QNUA 3 Should use Alias: Column class should use alias sysattributes 67
 QNUA 3 Should use Alias: Column attribute should use alias sysattributes 68
 QNUA 3 Should use Alias: Column object_type should use alias sysattributes 69
 QNUA 3 Should use Alias: Column uid should use alias sysqueryplans 70
 QNUA 3 Should use Alias: Column gid should use alias sysqueryplans 71
 QNUA 3 Should use Alias: Column object should use alias sysattributes 71
 QNUA 3 Should use Alias: Column gid should use alias sysqueryplans 72
 QNUA 3 Should use Alias: Column object_cinfo should use alias sysattributes 72
 QNUA 3 Should use Alias: Column gid should use alias q 185
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class, object_type, attribute}		 67
 MRST 2 Result Set Marker 97
 MRST 2 Result Set Marker 118
 MRST 2 Result Set Marker 129
 MRST 2 Result Set Marker 142
 MRST 2 Result Set Marker 176
 MRST 2 Result Set Marker 183
 MRST 2 Result Set Marker 208
 MRST 2 Result Set Marker 230
 MRST 2 Result Set Marker 247
 MRST 2 Result Set Marker 264
 MSUC 2 Correlated Subquery Marker 156
 MSUC 2 Correlated Subquery Marker 209
 MSUC 2 Correlated Subquery Marker 214
 MSUC 2 Correlated Subquery Marker 231
 MSUC 2 Correlated Subquery Marker 248
 MSUC 2 Correlated Subquery Marker 267
 MTR1 2 Metrics: Comments Ratio Comments: 23% 26
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 26 = 29dec - 5exi + 2 26
 MTR3 2 Metrics: Query Complexity Complexity: 164 26
 PRED_QUERY_COLLECTION 2 {q=sybsystemprocs..sysqueryplans, q2=sybsystemprocs..sysqueryplans} 0 156
 PRED_QUERY_COLLECTION 2 {q=sybsystemprocs..sysqueryplans, q2=sybsystemprocs..sysqueryplans} 0 209
 PRED_QUERY_COLLECTION 2 {q=sybsystemprocs..sysqueryplans, q2=sybsystemprocs..sysqueryplans} 0 214
 PRED_QUERY_COLLECTION 2 {q=sybsystemprocs..sysqueryplans, q2=sybsystemprocs..sysqueryplans} 0 231
 PRED_QUERY_COLLECTION 2 {q=sybsystemprocs..sysqueryplans, q2=sybsystemprocs..sysqueryplans} 0 248
 PRED_QUERY_COLLECTION 2 {q=sybsystemprocs..sysqueryplans, q2=sybsystemprocs..sysqueryplans} 0 267

DEPENDENCIES
PROCS AND TABLES USED
read_writes table tempdb..#rows (1) 
calls proc sybsystemprocs..sp_autoformat  
   read_writes table tempdb..#colinfo_af (1) 
   calls proc sybsystemprocs..sp_namecrack  
   reads table tempdb..systypes (1)  
   calls proc sybsystemprocs..sp_autoformat  
   reads table master..systypes (1)  
   reads table master..syscolumns (1)  
   reads table tempdb..syscolumns (1)  
writes table tempdb..#sphelpqpgroup1rs (1) 
calls proc sybsystemprocs..sp_getmessage  
   reads table master..sysmessages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
   reads table master..syslanguages (1)  
read_writes table tempdb..#colls (1) 
calls proc sybsystemprocs..sp_aux_sargs_qpgroup  
calls proc sybsystemprocs..sp_aux_get_qpgroup  
   calls proc sybsystemprocs..sp_aux_sargs_qpgroup  
   reads table sybsystemprocs..sysattributes  
reads table sybsystemprocs..sysattributes  
reads table sybsystemprocs..sysqueryplans