DatabaseProcApplicationCreatedLinks
sybsystemprocssp_cachestrategy  31 Aug 14Defects Dependencies

1     
2     /*
3     ** 17756, "The execution of the stored procedure '%1!' in database
4     **         '%2!' was aborted because there was an error in writing the
5     **         replication log record."
6     ** 17763, "The execution of the stored procedure '%1!' in database '%2!' was aborted because there
7     **         was an error in updating the schemacnt column in sysobjects."
8     ** 18173, "Only the System Administrator (SA) or the Object Owner
9     **	   may execute this stored Procedure."
10    ** 18176, "Usage: sp_cachestrategy dbname [, [ownername.]tablename
11    **	   [, indexname | `text only' | `table only'
12    **	   [, prefetch | mru, `on' | `off']]]"
13    ** 18077, "Set curwrite to the level of index."
14    ** 18079, "Set curwrite to the level of the object in sysobjects."
15    ** 18076, "Could not set curwrite to object level. Set your maxwrite label correctly."
16    ** 18333, "Cache configuration failed.  Your curread must dominate the sensitivity level of the object."
17    ** 19979, "Cannot run '%1!' for in-memory database '%2!'."
18    */
19    
20    /* 
21    ** This procedure sets a default prefetch strategy for a given object.
22    ** For non-clustered index it only affects its index pages.
23    ** For clustered indexes it affects the data pages as well.
24    **
25    ** All updates to sysindexes are done by forcing the index access via
26    ** 'csysindexes' so that it's clear that only one row is being updated.
27    ** (Multi-row updates to sysindexes are prohibited.)
28    */
29    CREATE PROCEDURE sp_cachestrategy
30        @dbname varchar(255),
31        @tablename varchar(255),
32        @indexname varchar(255) = NULL,
33        @strategy varchar(30) = NULL,
34        @onoff varchar(4) = NULL
35    AS
36    
37        DECLARE @stat2 int
38        DECLARE @dbid int
39        DECLARE @ownerid int
40        DECLARE @tabid int
41        DECLARE @indid smallint
42        DECLARE @entitytype char(1)
43        DECLARE @status int
44        DECLARE @updated int
45        DECLARE @indname varchar(255)
46        DECLARE @largeio varchar(8)
47        DECLARE @bufstr varchar(8)
48    
49    
50    
51    
52        IF @@trancount = 0
53        BEGIN
54            SET chained off
55        END
56    
57        SET transaction isolation level 1
58    
59        SELECT @stat2 = 0
60    
61        IF @dbname IS NULL
62        BEGIN
63            /*
64            ** The database name is required for this stored procedure
65            ** 18176, "Usage: sp_cachestrategy dbname [, [ownername.]tablename
66            **	   [, indexname | `text only' | `table only'
67            **	   [, prefetch | mru, `on' | `off']]]"
68            */
69            raiserror 18176
70            RETURN (1)
71        END
72    
73        /*
74        ** Retrieve the id's required to identify the object affected
75        */
76        SELECT @status = 0
77        SELECT @dbid = NULL
78        SELECT @ownerid = NULL
79        SELECT @tabid = NULL
80        SELECT @indid = NULL
81        SELECT @entitytype = NULL
82        EXEC @status = sp_aux_parse_entity @dbname, @tablename, @indexname,
83            @dbid OUTPUT, @ownerid OUTPUT,
84            @tabid OUTPUT, @indid OUTPUT,
85            @entitytype OUTPUT
86    
87    
88        IF @status = 2
89        BEGIN
90            /*
91            ** A syntax error was encountered.  Raise a usage message.
92            ** 18176, "Usage: sp_cachestrategy dbname [, [ownername.]tablename
93            **	   [, indexname | `text only' | `table only'
94            **	   [, prefetch | mru, `on' | `off']]]"
95            */
96            raiserror 18176
97            RETURN (1)
98        END
99        ELSE IF @status != 0
100       BEGIN
101           RETURN (@status)
102       END
103   
104       /* Do not allow sp_cachestrategy to be run on in-memory database. */
105       if exists (select * from master.dbo.sysdatabases
106               where dbid = @dbid and (status4 & 4096 = 4096))
107       begin
108           raiserror 19979, "sp_cachestrategy", @dbname
109           return (1)
110       end
111   
112       /*
113       **  Perform permission checks.  Basically, only the System Administrator
114       **  can change the cache strategy for objects he/she doesn't own.
115       */
116       IF @ownerid != user_id()
117       BEGIN
118           /*
119           ** Check if the user has the SA role.
120           */
121           IF proc_role("sa_role") = 0
122           BEGIN
123               /*
124               ** proc_role already raised an error message but we want
125               ** to supply an addendum saying that you need to be the
126               ** owner of the object in order to perform this operation.
127               */
128               raiserror 18173
129               RETURN (1)
130           END
131   
132       END
133   
134   
135       /*
136       **  Fix the index id if the entity is a table.
137       */
138       IF (@entitytype = 'T')
139       BEGIN
140           /*
141           ** Leaf level of clustered index, if it exists,
142           ** contain the data pages
143           */
144           SELECT @indid = 1
145   
146           IF NOT EXISTS (SELECT *
147                   FROM sysindexes
148                   WHERE id = @tabid AND indid = 1)
149           BEGIN
150               SELECT @indid = 0
151           END
152       END
153   
154       /*
155       ** If @strategy is entered, but it is neither "prefetch" nor "mru"
156       ** we should report an error. If @strategy is correct entered,
157       ** and @onoff is entered, but @onoff is neither "on" nor "off"
158       ** we should report an error. However, if @onoff is not entered,
159       ** we would not report error to keep the existing behavior.
160       */
161       IF (@strategy != NULL)
162       BEGIN
163   
164           IF ((lower(@strategy) NOT IN ("prefetch", "mru"))
165                   OR ((@onoff != NULL) AND (lower(@onoff) NOT IN ("on", "off"))))
166           BEGIN
167               /*
168               ** A syntax error was encountered.  Raise a usage message.
169               ** 18176, "Usage: sp_cachestrategy dbname 
170               **	 [, [ownername.]tablename
171               **	   [, indexname | `text only' | `table only'
172               **	   [, prefetch | mru, `on' | `off']]]"
173               */
174               raiserror 18176
175               RETURN (1)
176           END
177       END
178   
179       /*
180       ** Initialize some variables.
181       */
182       SELECT @stat2 = status2, @indname = name
183       FROM sysindexes
184       WHERE id = @tabid AND indid = @indid
185   
186       SELECT @updated = 0
187   
188       /*
189       **  Check if invoking the precedure in informational mode
190       **  In this case the user may not specify any strategy.
191       */
192       IF (@onoff = NULL)
193       BEGIN
194           /* Print the SHOWPLAN messages here as well */
195           IF (@stat2 & 16 = 16)
196               SELECT @largeio = "OFF"
197           ELSE
198               SELECT @largeio = "ON"
199   
200           IF (@stat2 & 32 = 32)
201               SELECT @bufstr = "OFF"
202           ELSE
203               SELECT @bufstr = "ON"
204   
205           SELECT "object name" = user_name(@ownerid) + "." +
206               object_name(@tabid),
207               "index name" = @indname,
208               "large IO" = @largeio,
209               "MRU" = @bufstr
210   
211           RETURN (0)
212       END
213   
214   
215   
216       begin tran updatesysind
217   
218       /*
219       ** Turn the sysindexes.status2 field for IND2_NOPREFETCH
220       */
221       IF lower(@strategy) = "prefetch"
222       BEGIN
223           IF lower(@onoff) = "off"
224           BEGIN
225               IF (@stat2 & 16) = 0
226               BEGIN
227                   UPDATE sysindexes
228                   SET status2 = status2 | 16
229                   WHERE id = @tabid AND indid = @indid
230                   plan "(update (i_scan csysindexes sysindexes))"
231   
232                   /*
233                   ** We updated a sysindexes row. We also need to update 
234                   ** the in-core structure representing this sysindexes 
235                   ** row as the sysindexes rows cache is not a write thru
236                   ** cache. 
237                   */
238                   dbcc refreshides(@dbid, @tabid, @indid, "cachestrategy")
239   
240                   /*
241                   ** In case of error, dbcc refreshides will
242                   ** print the error message.
243                   */
244                   if @@error != 0
245                   begin
246                       rollback tran updatesysind
247                       RETURN (1)
248                   end
249   
250                   SELECT @updated = 1
251               END
252           END
253           ELSE
254           BEGIN
255               IF (@stat2 & 16) != 0
256               BEGIN
257                   UPDATE sysindexes
258                   SET status2 = status2 & - 17
259                   WHERE id = @tabid AND indid = @indid
260                   plan "(update (i_scan csysindexes sysindexes))"
261   
262                   /*
263                   ** We updated a sysindexes row. We also need to update 
264                   ** the in-core structure representing this sysindexes 
265                   ** row as the sysindexes rows cache is not a write thru
266                   ** cache. 
267                   */
268                   dbcc refreshides(@dbid, @tabid, @indid, "cachestrategy")
269   
270                   /*
271                   ** In case of error, dbcc refreshides will
272                   ** print the error message.
273                   */
274                   if @@error != 0
275                   begin
276                       rollback tran updatesysind
277                       RETURN (1)
278                   end
279   
280                   SELECT @updated = 1
281               END
282           END
283       END
284       ELSE /* Already checked for all legal values, must be MRU now */
285       BEGIN
286           IF lower(@onoff) = "off"
287           BEGIN
288               IF (@stat2 & 32) = 0
289               BEGIN
290                   UPDATE sysindexes
291                   SET status2 = status2 | 32
292                   WHERE id = @tabid AND indid = @indid
293                   plan "(update (i_scan csysindexes sysindexes))"
294   
295                   /*
296                   ** We updated a sysindexes row. We also need to update 
297                   ** the in-core structure representing this sysindexes 
298                   ** row as the sysindexes rows cache is not a write thru
299                   ** cache. 
300                   */
301                   dbcc refreshides(@dbid, @tabid, @indid, "cachestrategy")
302   
303                   /*
304                   ** In case of error, dbcc refreshides will
305                   ** print the error message.
306                   */
307                   if @@error != 0
308                   begin
309                       rollback tran updatesysind
310                       RETURN (1)
311                   end
312   
313                   SELECT @updated = 1
314               END
315           END
316           ELSE
317           BEGIN
318               IF (@stat2 & 32) != 0
319               BEGIN
320                   UPDATE sysindexes
321                   SET status2 = status2 & - 33
322                   WHERE id = @tabid AND indid = @indid
323                   plan "(update (i_scan csysindexes sysindexes))"
324   
325                   /*
326                   ** We updated a sysindexes row. We also need to update 
327                   ** the in-core structure representing this sysindexes 
328                   ** row as the sysindexes rows cache is not a write thru
329                   ** cache. 
330                   */
331                   dbcc refreshides(@dbid, @tabid, @indid, "cachestrategy")
332   
333                   /*
334                   ** In case of error, dbcc refreshides will
335                   ** print the error message.
336                   */
337                   if @@error != 0
338                   begin
339                       rollback tran updatesysind
340                       RETURN (1)
341                   end
342   
343                   SELECT @updated = 1
344               END
345           END
346       END
347   
348       commit tran updatesysind
349   
350       /*
351       ** We need to increment the object's schema count if we changed
352       ** its cache strategy.
353       */
354       IF @updated = 1
355       BEGIN
356           /*
357           ** This transaction also writes a log record for replicating the
358           ** invocation of this procedure. If logexec() fails, the transaction
359           ** is aborted.
360           **
361           ** IMPORTANT: The name rs_logexec is significant and is used by
362           ** Replication Server.
363           */
364           BEGIN TRANSACTION rs_logexec
365   
366           IF (schema_inc(object_id(@tablename), 0) != 1)
367           BEGIN
368               /*
369               ** 17763, "The execution of the stored procedure '%1!'
370               **         in database '%2!' was aborted because there
371               **         was an error in updating the column
372               **         schemacnt in sysobjects."
373               */
374               SELECT @dbname = db_name()
375               RAISERROR 17763, "sp_cachestrategy", @dbname
376               ROLLBACK TRANSACTION rs_logexec
377               RETURN (1)
378           END
379   
380           IF (logexec() != 1)
381           BEGIN
382               /*
383               ** 17756, "The execution of the stored procedure '%1!'
384               **         in database '%2!' was aborted because there
385               **         was an error in writing the replication log
386               **         record."
387               */
388               SELECT @dbname = db_name()
389               RAISERROR 17756, "sp_bindefault", @dbname
390   
391               ROLLBACK TRANSACTION rs_logexec
392               RETURN (1)
393           END
394           COMMIT TRANSACTION
395       END
396   
397       RETURN (0)
398   


exec sp_procxmode 'sp_cachestrategy', 'AnyMode'
go

Grant Execute on sp_cachestrategy to public
go
RESULT SETS
sp_cachestrategy_rset_001

DEFECTS
 MCTR 4 Conditional Begin Tran or Commit Tran 364
 MCTR 4 Conditional Begin Tran or Commit Tran 394
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 106
 QTYP 4 Comparison type mismatch smallint = int 106
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 148
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public sybsystemprocs..sp_cachestrategy  
 MGTP 3 Grant to public sybsystemprocs..sysindexes  
 MNAC 3 Not using ANSI 'is null' 161
 MNAC 3 Not using ANSI 'is null' 165
 MNAC 3 Not using ANSI 'is null' 192
 MNER 3 No Error Check should check @@error after update 227
 MNER 3 No Error Check should check @@error after update 257
 MNER 3 No Error Check should check @@error after update 290
 MNER 3 No Error Check should check @@error after update 320
 MUCO 3 Useless Code Useless Brackets 70
 MUCO 3 Useless Code Useless Brackets 97
 MUCO 3 Useless Code Useless Brackets 101
 MUCO 3 Useless Code Useless Brackets 109
 MUCO 3 Useless Code Useless Brackets 129
 MUCO 3 Useless Code Useless Brackets 138
 MUCO 3 Useless Code Useless Brackets 161
 MUCO 3 Useless Code Useless Brackets 164
 MUCO 3 Useless Code Useless Brackets 175
 MUCO 3 Useless Code Useless Brackets 192
 MUCO 3 Useless Code Useless Brackets 195
 MUCO 3 Useless Code Useless Brackets 200
 MUCO 3 Useless Code Useless Brackets 211
 MUCO 3 Useless Code Useless Brackets 247
 MUCO 3 Useless Code Useless Brackets 277
 MUCO 3 Useless Code Useless Brackets 310
 MUCO 3 Useless Code Useless Brackets 340
 MUCO 3 Useless Code Useless Brackets 366
 MUCO 3 Useless Code Useless Brackets 377
 MUCO 3 Useless Code Useless Brackets 380
 MUCO 3 Useless Code Useless Brackets 392
 MUCO 3 Useless Code Useless Brackets 397
 QCRS 3 Conditional Result Set 205
 QFAP 3 Force abstract plan ABSTRACT_PLAN_CLAUSE plan..."(update (i_scan csysindexes sysindexes))" 227
 QFAP 3 Force abstract plan ABSTRACT_PLAN_CLAUSE plan..."(update (i_scan csysindexes sysindexes))" 257
 QFAP 3 Force abstract plan ABSTRACT_PLAN_CLAUSE plan..."(update (i_scan csysindexes sysindexes))" 290
 QFAP 3 Force abstract plan ABSTRACT_PLAN_CLAUSE plan..."(update (i_scan csysindexes sysindexes))" 320
 QISO 3 Set isolation level 57
 MRST 2 Result Set Marker 205
 MSUB 2 Subquery Marker 105
 MSUB 2 Subquery Marker 146
 MTR1 2 Metrics: Comments Ratio Comments: 52% 29
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 21 = 33dec - 14exi + 2 29
 MTR3 2 Metrics: Query Complexity Complexity: 155 29

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_aux_parse_entity  
   reads table sybsystemprocs..sysindexes  
   reads table sybsystemprocs..sysobjects  
reads table master..sysdatabases (1)  
read_writes table sybsystemprocs..sysindexes