With all this, EXEC() made perfect sense. And if a black-hat can do that, we have bigger things to worry about.
Sql server option recompile code#
The only way to inject malicious code would be to be able to alter the stored procedure. Plus, just at an intuitive level, the vast majority of the work being done on the database is NOT ad-hoc.Īs to vulnerability to injection, it's a non-issue because the configuration of the DSQL string is statically typed at the top of the procedure. I'm going out on a limb here, but I'm betting good money our DBAs don't want to turn this trace flag on just for the sake of this cleanup job. Since this pattern is re-used over and over with the only difference being essentially the table name, the column to check agains and the rows to delete per batch, I opted to do this all in a configurable loop rather than have a procedure 2000 lines long.Īs to why Optimize for Ad Hoc wouldn't work, admittedly I don't know much about it, but it looks like it changes how plans work at the database level. (There's slightly more to it than that, but not enough to warrant including above) The procedure has about 50 statements, all of the form The procedure I was handed performs cleanup on many of these tables once a day. Realtime stuff, web facing stuff, ETL stuff, etc. You seem to be building a very ad hoc focused process here.Īlso, exec does not allow for parameterized queries which introduces the possibility of SQL Injection. Why would Optimize for Ad Hoc not be applicable? Except for discussion around the RECOMPILE hint & caching, it sounds very applicable. I ended up using exec() in my actual code, but I'm trying to understand this specific case on a firm conceptual foundation. P.s again, I'm intentionally focusing on sp_executesql and recompilation here. I'm inferring yes because there's no way to recompile the statement as a whole.There is no way to force recompile the entire DSQL statement (I'm ignoring your Optimize for Ad Hoc, because it's not viable in my environment) you have to put option (recompile) after EVERY statement in the DSQL. Any and all caching is at the statement level within the dsql No, sp_executesql has no concept of option (recompile) being used.So to my original questions, here's what I understand your response to be: If you are going to have lots of single use plans, make sure you enable Optimize For Ad Hoc on your database. Most of the time, this is a very good thing because the compile process is costly, so reusing plans from cache is to your advantage. Sp_executesql will attempt to reuse execution plans. You're going to want the RECOMPILE hint to be after the WHERE clause as defined in the documentation. Where ' + quotename(DateColumn) + ' < ' + DateString + = += int output' Insert tables and parameters which follow a format simple enough they can be deleted with no joins by simple date offset.ĭelete top (' + cast(DeleteBatchSize as varchar(30)) + ')įrom alerts.dbo.' + quotename(TableName) + ' UnitsBack int not null check (UnitsBack >= 0),ĭateString as convert(varchar(50), dateadd(day, -UnitsBack, getdate(), 120)) RID int not null identity(1,1) primary key clustered, If object_id('tempdb.dbo.#GeneralizedCleanup') is not null drop table #GeneralizedCleanup Yeah yeah, I can probably refactor that away, but humor int I know EXEC() is probably better in this case, but I have that pesky output variable to deal with. if you DO have to option (recompile) each statement, does that mean that you store something like a partial cache for anything without that on it?.in my example I only have the loop, but if I had more things to do there as well, would I have to put that option after each statement?.If not, do you have to put option recompile after every statement you want recompiled.Does sp_executesql avoid creating and using plans if it sees option (recompile) ANYWHERE in the sql string?.I've provided a pretty verbose bit of sample code, but before getting too wrapped up in that, let me state precisely my questions. It used to be about 40 statically typed statements which did virtually the same thing (delete in batches of n until predicate = false). I've got a procedure which performs maintenance on a bunch of tables.
0 kommentar(er)
