They don’t give us much choice when it comes to upgrading, though. I’m still suffering from some of the “advances” they came up with in 20. I didn’t know it, but this also solved a major pain point in return times on the floor for a particular screen in the application that used the proc.Īs for the “advances” they came up with in this area for 20, I’m scared to death to move off 2016. Once I did my tweaks to the rather short code in the proc, not only did the recompiles go away but the execution is now measured in single digit ms. Then I did a recompile analysis and the damned thing wasn’t only recompiling EVERY time it was called, it was taking between 2 and 22 seconds to recompile every time with the average coming in at 20 seconds. It’s been a while since I’ve looked at our recompile situation at work but we had a proc that “only” took 100ms to execute, which I also thought was taking too long but couldn’t get management to budge on even though I explained the tens of thousands of times it was being hit each hour. They’re especially useful in some of those large processing runs where you need for a recompile to occur to actually get minimal logging to work. They aren’t necessarily the sin that a lot of people would have you believe. YMMV but, for me, using DBCC FREEPROCCACHE is not the no-no that everyone makes it out to be.Īs for recompiles, I agree. It takes way too long to isolate every single stored procedure that needs a recompile and so I DO use DBCC FREEPROCCACHE on my production box from time to time with no serious ill effects and it cleans up a wealth of sins immediately. Heh… on the DBCC FREEPROCCACHE thing and with the understanding that my server probably isn’t anywhere near a place where it would cause a real concern, we have a heavy mix between OLTP and large processing runs, especially during the daytime. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly. I’m offering a 75% discount to my blog readers if you click from here. If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m not mad.Īnd yeah, there’s advances in SQL Server 20 that start to address some issues here, but they’re still imperfect. Using a plan guide doesn’t interfere with that precious vendor IP that makes SQL Server unresponsive every 15 minutes.
0 Comments
Leave a Reply. |