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
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |