DatabaseProcApplicationCreatedLinks
sybsystemprocssp_filter_metrics  31 Aug 14Defects Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     
4     /*
5     ** Messages for "sp_filter_metrics"
6     **
7     ** 19417, "predicate '%1!' is an invalid filter condition."
8     ** 17230, "You must be the System Administrator (SA) or the Database Owner (dbo) to execute this procedure."
9     ** 19345, "No qpmetrics group with group id = '%1!' exists in this database."
10    */
11    
12    /*
13    ** sp_filter_metrics delete qpmetrics records from SYSQUERYMETRICS
14    ** based on the inputed predicate and gid (sa only).
15    */
16    
17    create procedure sp_filter_metrics
18        @pgid varchar(30) = NULL,
19        @pred varchar(600) = NULL
20    as
21        declare @gid int
22        declare @filter_cmd varchar(1000)
23    
24        set nocount on
25    
26        /* Don't start a transaction for the user, he does not expect it. */
27        if @@trancount = 0
28        begin
29            set chained off
30        end
31    
32        /* Don't hold long READ locks, the user might not appreciate it. */
33        set transaction isolation level 1
34    
35        if user_id() != 1 and charindex("sa_role", show_role()) = 0
36        begin
37            /* 17230, "You must be the System Administrator (SA) or the Database Owner (dbo) to execute this procedure." */
38            raiserror 17230
39    
40            return 1
41        end
42        else
43        begin
44            select @gid = convert(integer, @pgid)
45    
46            if @gid <= 0 or not exists (select 1 from sysqueryplans where gid = - @gid)
47            begin
48                /* 19345, "No qpmetrics group with group id = '%1!' exists in this database." */
49                raiserror 19345, @gid
50    
51                return 2
52            end
53            else
54            begin
55                /* Now do the pruning based on the filtering condition */
56                select @filter_cmd =
57                    " delete sysqueryplans from sysqueryplans, sysquerymetrics where "
58                select @filter_cmd = @filter_cmd + "sysqueryplans.gid = - @gid and "
59                select @filter_cmd = @filter_cmd + "sysqueryplans.id = sysquerymetrics.id and "
60                select @filter_cmd = @filter_cmd + "( " + @pred + " )"
61                exec (@filter_cmd)
62    
63                /* 19417, "predicate '%1!' is an invalid filter condition" */
64                if (@@error != 0)
65                begin
66                    raiserror 19417, @pred
67    
68                    return 3
69                end
70            end
71        end
72    
73        return 0
74    


exec sp_procxmode 'sp_filter_metrics', 'AnyMode'
go

Grant Execute on sp_filter_metrics to public
go
DEFECTS
 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}		 46
 MDYN 3 Proc uses Dynamic SQL but is not flagged with Dynamic Ownership Chain 17
 MGTP 3 Grant to public sybsystemprocs..sp_filter_metrics  
 MGTP 3 Grant to public sybsystemprocs..sysqueryplans  
 MUCO 3 Useless Code Useless Brackets 64
 QISO 3 Set isolation level 33
 MDYS 2 Dynamic SQL Marker 61
 MSUB 2 Subquery Marker 46
 MTR1 2 Metrics: Comments Ratio Comments: 43% 17
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 4 = 6dec - 4exi + 2 17
 MTR3 2 Metrics: Query Complexity Complexity: 31 17

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..sysqueryplans  

CALLERS
called by proc sybsystemprocs..sp_metrics