max memory per query

How can I configure the maximum memory that a query (select query) can use in sql server 2008?

I know there is a way to set the minimum value but how about the max value? I would like to use this because I have many processes in parallel. I know about the MAXDOP option but this is for processors.

Update:

What I am actually trying to do is run some data load continuously. This data load is in the ETL form (extract transform and load). While the data is loaded I want to run some queries ( select ). All of them are expensive queries ( containing group by ). The most important process for me is the data load. I obtained an average speed of 10000 rows/sec and when I run the queries in parallel it drops to 4000 rows/sec and even lower. I know that a little more details should be provided but this is a more complex product that I wor开发者_运维问答k at and I cannot detail it more. Another thing that I can guarantee is that my load speed does not drop due to lock problems because I monitored and removed them.

There isn't any way of setting a maximum memory at a per query level that I can think of.

If you are on Enterprise Edition you can use resource governor to set a maximum amount of memory that a particular workload group can consume which might help.

In SQL 2008 you can use resource governor to achieve this. There you can set the request_max_memory_grant_percent to set the memory (this is the percent relative to the pool size specified by the pool's max_memory_percent value). This setting in not query specific, it is session specific.

In addition to Martin's answer

If your queries are all the same or similar, working on the same data, then they will be sharing memory anyway.

Example:

A busy web site with 100 concurrent connections running 6 different parametrised queries between them on broadly the same range of data.

• 6 execution plans
• 100 user contexts
• one buffer pool with assorted flags and counters to show usage of each data page

If you have 100 different queries or they are not parametrised then fix the code.

Memory per query is something I've never thought or cared about since last millenium