• Adam Thurgar

Testing queries


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/


10 views

Recent Posts

See All

Cardinality estimator

Recently I was asked by a software vendor to review a particular query that ran in under a second on a SQL Server 2014 installation at a compatibility level of 110 (SQL Server 2012), but when run unde

Index fragmentation

A law firm client, occasionally has issues with their legal software, that is provided by the global leader in this field. The response from the software provider is always the same - you have index f

Deleting large amounts of data

I had a client call me about wanting to delete a large amount of data from their database. They knew what tables they wanted to delete from. They also knew that deleting large amounts of data causes t