![]() This proves the fact that PBM can indeed be used to prevent some conditions which you don’t want to happen in your db/environment. What actually happens here is the transaction will be rolled back as it violated the policy which was defined. Msg 3609, Level 16, State 1, Procedure sp_syspolicy_dispatch_event, Line 65 Statement: ‘CREATE PROCEDURE dbo.FindSuperMan Policy ‘Policy_Prevent_Recompile’ has been violated by ‘SQLSERVER:\SQL\TESTServer\DEFAULT\Databases\SuperMan\StoredProcedures\dbo.FindSuperMan’. CREATE PROCEDURE dbo.FindSuperManĪs soon as I ran the command there was an error which said – I tried to create a simple stored procedure named FindSuperMan which will do a simple SELECT * from the table.This stored procedure will try to force a RECOMPILE hint. For this purpose I will be using a database called SuperMan (Yes,just for fun).This db got a table called People and it has 3 rows of data. ![]() Next step is to try and create a stored procedure with recompile hint. Once the policy is created I made sure that its in enabled state. This can be checked against a single db using another condition and that’s the recommended approach. Note – I’m doing this check for every database and every stored procedures as this is a test case. I went ahead and created a policy which used the condition which was created in the first step, and chose evaluation mode as On Change : Prevent įirst of I created a condition using facet stored procedure and added an expression like what is shown in the image below – So, this post is my test case to try and see if we can actually prevent users from creating stored procedures with RECOMPILE hint using PBM. I did reply to the user that I’m positive that there will be some option, but it needs to be tested out. Kendra Little( B/ T) has an excellent video on recompile, and you can find it here. I might not use it within a stored procedure, but at times I might have to use it during run time. Will I use RECOMPILE hint ever in my stored procedure? That’s a huge ‘It depends’ question. I was pretty sure that Policy Based Management did provide us a Stored Procedure Facet, and there will be some expressions which we can create using the same to ensure that no new stored procedures are created with this hint. Explicit recompilation eliminates the need for implicit run-time. The user was checking if there was a way to prevent procedures which are created with RECOMPILE hint. Use the ALTER PROCEDURE statement to explicitly recompile a standalone stored procedure. Therefore a subsequent execution of that object finds no plan in cache which triggers a. The way I find them is: SELECT OBJECTNAME(ID)AS SPNAME, FROM SYSCOMMENTS WHERE TEXT LIKE WITH RECOMPILE when I look at: select from sys.procedures I find no indication of recompiles. When you execute sprecompile on a stored procedure/trigger/UDF, that object is immediately removed from plan cache (Cache Remove). In my databases I have some Stored procedure with recompile. I got motivated to write this post as there was a question on twitter which was like – Running sprecompile on a stored procedure, function or a trigger causes it to be recompiled the next time they are executed.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |