JOIN Users as U2 on Posts.LastEditorUserId = U2.Id JOIN Users as U on Posts.OwnerUserId = U.Id To see the cost of compilation, just use statistics time. Okay, but what about the cost of compilation? To prove that, I’ll right-click on the top SELECT INTO and view the properties. exec 4Įach “query” in this example is a separate statement. Then, I’ll get the actual execution plan for post type 4. I’ll run the stored procedure with the value of 3, first. This stored procedure has OPTION(RECOMPILE) on only one statement INT) asįROM Posts where PostTypeId = PostTypeId, Tags, Body Except for this example, I can’t use the estimated execution plan because there’s a temp table. I’m going to re-use the stored procedure from this post on parameter sniffing and using the estimated execution plan. There’s also the fact that the hint applies strictly to the statement level, not the entire query. Using option recompile will use extra cpu and memory every time the statement compiles. I have a few reasons why this hint is dangerous. In fact, the statement with option recompile won’t be stored in cach e. It also means that the statement itself won’t be vulnerable to parameter sniffing from other queries in cache. There’s some benefits, like something called “ constant folding.” To us, that just means that the execution plan might be better than a normal execution plan compiled for the current statement. SQL Server will compile an execution plan specifically for the statement that the query hint is on. What happens when a statement has OPTION(RECOMPILE) I’ve used it before without fully understanding the impact. I wish I knew that when I started query tuning. The query by the application.This is one dangerous query hint. SQL Server 2008 has introduced a little known feature called " OPTIMIZE FOR UNKNOWN": This works well if the values in the rows are static. The case of a dynamic where clause built up inside a stored procedure can be handled using sp_executesql to execute the TSQL rather than adding WITH RECOMPILE to the stored procedure.Īnother solution (SQL Server 2005 onwards) is to use hint with specific parameters using the OPTIMIZE FOR hint. Putting it on every stored procedure is NOT a good idea, because compiling a query plan is a relatively expensive operation and you will not see any benefit from the query plans being cached and re-used. But generally speaking, the implementers of a database are going to be smarter about that on the whole than you are.Īs with anything performance related, don't take shots in the dark figure out where the bottlenecks are that are costing 90% of your performance, and solve them first. So by forcing it to recompile every time, you're telling it that it has to go through that process over and over again, even if the plan it got before was perfectly good.ĭepending on the vendor, there should be automatic triggers for recompiling query plans - for example, if the statistics on a table change significantly (like, the histogram of values in a certain column starts out evenly distributed by over time becomes highly skewed), then the DB should notice that and recompile the plan. For really complex queries, it can't even guarantee an optimal plan, it has to just use heuristics to come up with a pretty good plan. There are lots of "tricks" the database query pre-processor can do and choices it can make - what order to join the tables, which indexes to use, whether to apply WHERE clauses before or after joins, etc.įor a simple SELECT statement, it might not make a difference, but for any non-trivial query, the database is going to spend some serious time (measured in milliseconds, as opposed to the usual microseconds) to come up with an optimal plan. With recompile sql how to#Because the syntax of a SQL statement only specifies what you want, and not (generally) how to get it, that allows the database a wide degree of flexibility when creating the physical plan (that is, the step-by-step instructions to actually gather and modify data). Why is that potentially a big deal? Computing a query plan is a lot more intensive than compiling regular procedural code. By doing so, you'd be eliminating one of the primary benefits of stored procedures: the fact that it saves the query plan. As others have said, you don't want to simply include WITH RECOMPILE in every stored proc as a matter of habit.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |