What you often want to try and do in these cases is to work towards plan stability, so that no matter what happens (outside of changing the schema), then a good enough plan is always generated. To answer why this suddenly changed one night - well there could be updated statistics, or a 'better' plan got pushed out of cache? I can envisage lots of scenarios where 'nothing' has changed, and yet the performance suddenly tanks. The slower plan does not have the early termination, which means that it thinks it has arrived at the best plan for the estimates and statistics that it has - clearly this is not the case as borne out by your experience. (see for more info) It's not unusual for this seemingly negative position to actually deliver you a plan that is good - just because it didn't evaluate every single possibility, doesn't mean it hasn't already got the best plan. We also see that the faster plan has a Reason for early termination: Time Out - which means that the optimiser reached a threshold and had to abandon looking for any further possible plans and go with the best it had so far. Unfortunately the plan and query are so complex it is difficult to pinpoint exactly why. However in your case the actual performance is improved by this 'wrong' plan, when compared to the 'better' plan. So the faster plan wrongly assumes it is going to be doing less work and so chooses a plan and operators based on that assumption. Conversely the '22 hour' plan (with option recompile) estimates a higher row count of 8,659. ![]() Looking at the plans (and yes they are fairly complex!) we can see that the '42 min' plan (without option recompile) has an estimated output row count of 2 rows, whereas the actual output is 21k. She primarily focuses on the database domain, helping clients build short and long term multi-channel campaigns to drive leads for their sales pipeline.I think the first point I would make is that option(recompile) is not necessarily an optimisation, I'd view it as a 'change of behaviour', which can affect performance either way. Nupur Dave is a social media enthusiast and an independent consultant. If you need help with any SQL Server Performance Tuning Issues, please feel free to reach out at is also a CrossFit Level 1 Trainer (CF-L1) and CrossFit Level 2 Trainer (CF-L2). Pinal is an experienced and dedicated professional with a deep commitment to flawless customer service. To freely share his knowledge and help others build their expertise, Pinal has also written more than 5,500 database tech articles on his blog at. Pinal has authored 13 SQL Server database books and 40 Pluralsight courses. He holds a Masters of Science degree and numerous database certifications. Pinal Dave is an SQL Server Performance Tuning Expert and independent consultant with over 17 years of hands-on experience. Sys.objects o ON ps.object_id = o.object_id SELECT SCHEMA_NAME(SCHEMA_ID) SchemaName, name ProcedureName, Here is the simple script which you can run to identify which stored procedure is cached for your database and various associated properties of the stored procedure. One method can be used when you execute the stored procedure and another method that can be used to make stored procedure is ready for recompilation and recompiles during its first run. So now you know different methods to recompile stored procedures. Essentially this method removes the cache from the procedure cache. This method will recompile the stored procedure in the first run of the stored procedure. Here is a neat trick where you can make your stored procedure for recompilation but not actually execute it. Method 2: sp_recompile for Recompile Stored Procedures This method will recompile the stored procedure as soon as the stored procedure executes it. You can recompile your stored procedure while you execute it. Here are two easy methods: Method 1: WITH RECOMPILE To recompile a stored procedure you do not have to recreate the stored procedure. ![]() At that point, I realized that I should have a blog post that explains to easy way to recompile stored procedures. To recompile the stored procedure, DBA opened the stored procedure in SSMS and recreated it by using the alter statement. I told the DBA who was working with me to recompile the stored procedure so we have to brand new plans for the stored procedure. Recently while working with a large financial organization on Comprehensive Database Performance Health Check, I found an instance where stored procedure had a very incorrect execution plan cache.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |