Loading cache overloads database

When my site starts I open a second thread that loads lots of data into the cache.

The web layer work as ex开发者_运维问答pected, but it leads to to much load on the database. Other queries are not served in reasonable time anymore. The DB is read only, so there are no locks involved.

Is there a good way to tell my thread to take it easy? Something like: Please go at 50% or Please only continue loading when there is little pressure on the DB?

You should look at the Resource Governor feature of Sql Server 2008.

With it, you can set up a resource pool that your ASP.Net website can use, and limit it to X% of the resources. Here's an example of setting it up.

Make sure the amount of data you load is smaller than the amount of RAM on the machine. Also, spend some effort tuning the queries you use. There's no reason why it should be hard work for SQL to load data into memory.

I'm thinking you need to do some tuning on the database because it is taking too much time to serve up the data needed to populate the cache. Have you looked at the execution plans for the queries you run to populate the cache? Or run the Tuning Advisor against the population process?