Supplying the WITH RECOMPILE option: EXEC MySproc_Select '' WITH RECOMPILE No wonder performance would plummet so dramatically. Plan, searching for the target company name using the OrderDate If I switched the search to the CompanyName column, SQL would blindly use the cached query For instance, say I was searching on the column OrderDate. The first search I performed created a query plan and stored The moment I switched columns, however, performance plummeted. Long as I searched on that particular column, everything would work asĮxpected. Multiple searches, regardless of the order, the first would be fast, andįinally, I realized that the first time the procedure was called, a query plan was devised and stored in the cache. Least be approximately the same, but that isn’t what happened. In theory, the performance of each search should at I knew something was wrong when I began to test my allegedlyĬlever stored procedure. Several queries, depending upon the column to search. I examined the parameter using a CASE block, and then executed one of Then the page called a sproc, passing a parameter to indicate which column to Its users to search by any of several columns. My experience with the WITH RECOMPILE optionĪ while back, I was supporting a search page that allowed Then SQL Server compiles it and places it in the cache. In the cache first, finds the sproc there, andĭoesn’t compile it. Plan and stores it in its procedure cache. By default, it doesn’t compile the sproc at creation time. The system tables: sysobjects, sysdepends,Īnd syscomments (the latter stores the body of the sproc). If itĭoesn’t find any errors, then it adds the sproc to They run more quickly than the equivalent SQL statements executed from QueryĪnalyzer (or perhaps passed in from some front-end app such as a Web page or VBįirst, you need to know what SQL Server does with a new sproc. The generally accepted wisdom about stored procedures (or sprocs) is that because SQL can optimize and compile them, TechRepublic’s free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. See how Arthur Fuller executes a stored procedure using the WITH RECOMPILE option, and then consider whether you need to revisit any stored procedures in your database. SQL Server's ability to optimize and compile a stored procedure is great but, if you aren't careful, it can bite you when you least expect it. Summary of the downsidesĮach statement with option recompile consumes extra cpu and memory and doesn’t store the execution plan in cache, preventing performance tuners from seeing metrics like total execution count, or total worker time in dm_exec_query_stats.Understanding SQL Server’s WITH RECOMPILE option ![]() SET STATISTICS TIME ON Įvery time that statement runs, it will be compiled again, costing those 5 ms of CPU time. 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.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |