But why the recompile didn't help?
Using OPTION (RECOMPILE) is not magic. It does often produce a better-performing execution plan (at the cost of a full compilation each time) because the query is compiled each time for the specific values of any parameters at the time, and also enables parameter embedding, which may produce further benefits.
Determining the specific reasons that OPTION (RECOMPILE) did not help in your particular case would require details you are unable to provide.
Local variables. The procedure was faster than ever.
Using local variables prevents sniffing of parameter values, so queries are compiled based on average distribution statistics. This was the workaround for some types of parameter sensitivity problem before OPTION (OPTIMIZE FOR UNKNOWN) and trace flag 4136 became available.
For more details please see:
Parameter Sniffing, Embedding and the RECOMPILE Options
There's a lot going on here, and most of it is pretty broad and vague.
2008R2 RTM came out on April 21, 2010. It's totally out of support. You'll want to prioritize getting on the latest Service Pack, which came out just about 3 years ago to the day. That way you'll be covered if you're hitting a weird bug or something. Head on over here to figure out what you need to download.
Since you added vCPUs (from 1 to 4) and didn't change any settings, your queries can now go parallel. I know this sounds like they'll all be faster, but hang on!
You may have added RAM, but you may not have changed Max Server Memory so your server can take advantage of it.
Figure out what your server is waiting on. An open source project I work on provides free scripts to help you measure your SQL Server. Head on over here if you wanna give them a try.
You'll wanna grab sp_BlitzFirst to check out your server's wait stats. You can run it a couple ways.
This will show you what your server has been waiting on since it started up.
EXEC dbo.sp_BlitzFirst @SinceStartup = 1;
This will show you what queries are waiting on now, during a 30 second window.
EXEC dbo.sp_BlitzFirst @Seconds = 30, @ExpertMode = 1;
Once you figure out what queries are waiting on (there's a ton of stuff written about wait stats out there), you can start making changes to get things under control.
If you see them waiting on CXPACKET, that means your queries are going parallel, and maybe trampling over each other. If you hit this, you'll probably want to consider bumping Cost Threshold for Parallelism up to 50, and maybe dropping MAXDOP down to 2.
After this step is when you want to use something like sp_WhoIsActive or sp_BlitzWho (the latter is in the GitHub repo from earlier) to start capturing query plans. Aside from wait stats, they're one of the most important things you can look at to figure out what's going wrong.
You may also want to check out this article by Jonathan Kehayias about VMWare Counters to check out in relation to SQL Server.
Update
Reviewing the wait stats and boy are they weird. There's definitely something up with the CPUs. Your server is mostly sitting around bored, but when things heat up, things get bad. I'll try to break this down easily.
You're hitting a poison wait called THREADPOOL. You don't have a ton of it, but that makes sense because your server isn't terribly active. I'll explain why in a minute.
You have really long average waits on SOS_SCHEDULER_YIELD and CXPACKET. You're on a VM, so you're gonna wanna make sure that the SQL Server has reservations, or that the box isn't horribly oversubscribed. A noisy neighbor can really ruin your day here. You're also going to want to make sure that the server/VM guest/VM host aren't running in Balanced Power mode. This makes your CPUs spin down to unnecessarily low speeds, and they don't immediately spin back up to full speed.
How do they tie in? With 4 CPUs you have 512 worker threads. Keep in mind, you had the same amount with a single CPU, but now that your queries can go parallel, they can consume many more worker threads. In your case 4 threads per parallel branch of a parallel query.
What's going parallel? Most likely everything. The default Cost Threshold for Parallelism is 5. That number was made the default sometime in the late 90s working on a desktop that looked like this.
Granted, your hardware is smaller than most laptops, but you're still a bit ahead of that thing.
When lots of parallel queries get going, you're running out of those worker threads. When that happens, queries just sit around waiting for threads to get going. That's also where SOS_SCHEDULER_YIELD comes in. Queries are stepping off CPUs and not getting back on for a long time. I don't see any blocking waits, so you're most likely just all stuffed up on intra-query parallelism waits.
What can you do?
- Make sure nothing is in Balanced Power mode
- Change MAXDOP to 2
- Change cost threshold for parallelism to 50
- Follow the Jon K. article above to validate VM health
- Use the script called
sp_BlitzIndex to look for any missing index requests.
For more thorough troubleshooting, check out the whitepaper I wrote for Google on hardware sizing in the cloud.
Hope this helps!
Best Answer
Basically what happens is, when SQL Server sees a query that it needs to compile, it is going to use the first-time-called parameters to generate the execution plan. This may or may not be a good thing, but it is what happens.
For instance, say you have a table of fruit (100 rows). There are 98 rows that are Apple, and only 2 rows that contain the fruit Orange. If you query that table for Apple, then the plan will most likely compile with a Scan. This is a good thing, as it is optimized for that Apple query. But then when you want to query for the Orange, that Scan is inefficient. But it is the stored plan that is being used.
The fact of the matter is that it happens. It happens all the time. It's typically not an outstanding issue, but in some cases it can be a pretty big problem. A solution to an ongoing bad parameter sniffing problem is that you can utilize the
OPTIMIZE FORquery hint to force SQL Server to use certain parameter values when generating the execution plan upon initial compilation.