Load Analysis tool and standard for OLTP environment with hot/cold pages

Question

I am looking for a tool/standard that can simulate an OLTP environement wherein a subset of the database is frequently accessed while the majority remains untouched.  I would like to see hot pages moved automatically via storage tiering and thus want to minimize access to the entire database.

Answer 1

Hi Scott,

Thanks for your post!

All data changes occur within buffers in memory. A modified page in the buffer pool is referred to as a dirty page. When SQL Server executes a checkpoint, all dirty pages in the buffer pool are written out to the data files. It is the mechanism. I do not think that there is a tool in SQL Server could achieve that.

If I am not understand you clearly, please feel free to let me know.

TechNet Subscriber Support
If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

Maggie

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

Answer 2

You can use Visual Studio Ultimate load testing tools.

There is few tasks to be done:

1. Define database unit tests (e.g. stored procedure calls) to emulate load (http://msdn.microsoft.com/en-us/library/dd172118.aspx). Ensure that these tests will only use the "hot" area of the database.

2. Define Performance Load Test to execute these unit tests in multiple treads (http://msdn.microsoft.com/en-us/library/ff355993.aspx).

3. Depending on the required load you may need to configure multiple Test Agents to execute these tests.