When you test potential changes to a query, how do you test it to make sure it is reliable and repeatable. One of the most common mistakes is to not clear the caches and buffers. if you test without clearing then subsequent executions of a query may be quicker because of a plan being in the procedure cache or data being in the buffers. If you are testing you should run the following before you run each query

DBCC FREESYSTEMCACHE -- Flush the ad hoc and prepared plan cache DBCC FREESESSIONCACHE -- Flush the distributed query connection cache used by distributed queries against an instance

DBCC FREEPROCCACHE -- Removes all elements from the plan cache for the entire instance DBCC DROPCLEANBUFFERS -- Removes all clean buffers from the buffer pool, and columnstore objects from the columnstore object pool.

DO NOT do this in any production or critical environments (use with care in dev, test and your own sandpit).

There is a good article on different ways to clear the plan cache by Glenn Berry.

https://www.sqlskills.com/blogs/glenn/eight-different-ways-to-clear-the-sql-server-plan-cache/

Now that we have a good consistent baseline from which to test queries. How can we generate some load that may simulate a more production like environment.

1. You could use Microsofts RML Utilities which includes ostress.exe along with some other components and the run this as a scheduled job every N seconds.

https://www.brentozar.com/archive/2017/02/simulating-workload-ostress-agent-jobs/

2. Use SQL Query Stress an excellent tool for stress testing written by Adam Machanic.

https://github.com/ErikEJ/SqlQueryStress

Then you can use the clever example by Brent Ozar to really hammer your server.

https://www.brentozar.com/archive/2015/05/how-to-fake-load-tests-with-sqlquerystress/