Targeted Plan Cache Removal

Azure, SQL Server 2008, SQL Server 2012, SQL Server 2014, TSQL
A lot of times you'll hear how people are experiencing sudden, intermittent, poor performance on a query, bad parameter sniffing at work, so they'll fix it by running the following code: [crayon-5a6d47887c823691122515/] BOOM! Yeah, you just nuked the cache on your server because you wanted to take out a single terrorist query. Now, yes, that problematic query is going to recompile and hopefully have a better execution plan. Also, so are all the other queries on your system. That spike in CPU and the slow-down all your business people are experiencing... Your fault for going nuclear. Instead of a nuke, why not use a sniper rifle to just remove the one problematic plan. Here's a little piece of code to help out: [crayon-5a6d47887c831579855152/] Take a look at the documentation for FREEPROCCACHE.…
Read More