But sometimes, when you know that SQL can work out the right answer, it’s the best way to fix the problem. Normally I wouldn’t recommend OPTION (RECOMPILE), as the fix is usually to sort out your indexes, or the joins, or the columns you’re selecting, or your statistics, or countless other things. By using OPTION (RECOMPILE), we will incur a cost to compile the plan every time it runs, but for a stored proc that use to run in 20 minutes to now run in 20 seconds, this works out well. We don’t want to add a heap of new indexes, or maintain some sort of indexed view for this query. This stored proc is run frequently for about 4 weeks of the year. SQL has to take the time to decide again what the best way is to retrieve the data you requested.įor this scenario, it appears to be the best option. OPTION (RECOMPILE) does exactly what it sounds like -it recompiles the code and finds a new execution plan based on the parameters passed in. ![]() I ended up deciding to add OPTION (RECOMPILE) to the critical select statement in the stored proc. I spent some time changing indexes and trying to get a plan that would be suitable for all (or almost all) scenarios but I couldn’t find a suitable option. I needed it to pick the best option every single time. If it picked the wrong option, it could run for 10-20 minutes. Now again creating that stored procedure with RECOMPILE option. When the stored proc picked the best plan for the parameters passed in, it would run in about 10-20 seconds. However, it would then cache that plan and use from then on. When I cleared my cache and ran each of the test scenarios, it would always pick a great option to filter the data as quickly and efficiently as possible. The problem was that depending on the data chosen from table1, it could be better to filter by either table2, table3 or table4. Most of the times, reusing a plan saves time. The data from all of the tables was then compared with the other 3 tables to get a final result. When you refer to recompiling a stored procedure, you are basically talking about removing this plan from the cache so that SQL Server will be forced to regenerate a new plan. A very small table (table1) was being joined to 4 large tables (table2, table3, table4). DBCC FREEPROCCACHE clears the procedure cache and causes ad hoc queries to. 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. ![]() The database ID number to be affected must be entered as part of the command. In my databases I have some Stored procedure with recompile. In this case, the data was all over the place. DBCC FLUSHPROCINDB: Used to clear out the stored procedure cache for a specific database on a SQL Server, not the entire SQL Server. ![]() In most cases, this is the best thing for SQL to do, as the plan it comes up with is usually the best option. When SQL Server first runs a stored procedure, it works out the ideal execution plan based on the parameter passed in and saves it for next time. So I tried to work out how to make it run well for all scenarios. This week, I was trying to improve the performance of a bit of SQL and noticed that it generated significantly different query plans depending on the parameters passed in.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |