Load Memory usage in SQL Server

Question

I would like to load test a dev sql server . I want to make sql server memory usage close to 100%. Is there scripts available? I got certain scripts which only increase CPU.. Not memory.

Answer 1

Create a table and fill it up with data, so that it's size exceeds the available memory SQL Server with a factor of 2 or so. Tip: add a char(8000) column with a default of ' ', so you don't need that many rows.

Once you have done that, run an operation which forces SQL Server to scan that table, for instance

SELECT * FROM tbl WHERE bigcol LIKE '%junk%'

Then again, you may have more specific ideas of what memory you to fill up. This fills up the buffer cache, which is by far the simplest.

Answer 2

Hi，

The caching of data pages usually occupies most of the memory usage of SQL Server. As long as there is no memory pressure, SQL Server will keep the read data in the memory to achieve optimal performance, because it is faster to read from the memory than to read the data from the hard disk. As Erland said, the most direct way is for the user to send a large data query request, which will fill up the buffer cache.

Not sure if you manually specify the maximum server memory (Max Server memory) for your SQL Server instance. If you do not specify this option, SQL Server will dynamically change its memory requirements based on the available system resources, which is also the recommended practice. .