Re: Performance Testing: sp_configure min & max memory?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Rob Pecherer (rmp_at_sfdbs.com)
Date: 03/04/04 

Date: Thu, 04 Mar 2004 15:47:41 GMT

I'm not trying to optimize for the query, I'm trying to get

a sense of the difference in processing time for 3 queries

which produce the identical result.

Each query references every data page in 2 relations,

so each page must be accessed at least once. With unlimited

memory, it will read every data page once and I won't be able

to detect any difference. With limited memory, I want to force Server2K

to page and possibly sense any performance difference.

If not, I will have to artificially increase the size of

the tuples to reduce the number of tuples per page. Or,

increase the number of tuples. Or both. Any of these

paths implies a lot more work than (artificially) restricting

the available memory.

The 16M is not intended to be permanent, only a temporary

means to compare these queries.

"Andrew J. Kelly" <sqlmvpnoooospam@shadhawk.com> wrote in message
news:OVbARGaAEHA.1796@TK2MSFTNGP12.phx.gbl...
> I doubt it is due to the fact you have too much memory. It's most likely
> due to the fact that you are now accessing the data from disk instead of
the
> cache. You can use the Max Memory setting to set a maximum limit that sql
> server will use for the memory pool but 16MB is pretty unrealistic.
>
> --
> Andrew J. Kelly SQL MVP

Relevant Pages

  • Re: advice on loading and searching large map in memory
    ... We have a requirement to query across two disparate systems. ... to check for updates. ... To avoid 1000 relational queries I was planning to "cache" the entire ... would load the entire relational table into memory. ...
    (comp.lang.java.programmer)
  • Re: Help! Difficulty understanding DB -> Object mapping
    ... > Optimizing things like SQL queries depends upon the specific RDB schemas ... > Virtual memory is cheap. ... > Prefer single complex queries to multiple simple queries. ... in the past you had to have a pretty good knowledge of how the query was ...
    (comp.object)
  • Re: advice on loading and searching large map in memory
    ... We have a requirement to query across two disparate systems. ... to check for updates. ... To avoid 1000 relational queries I was planning to "cache" the entire ... would load the entire relational table into memory. ...
    (comp.lang.java.programmer)
  • Re: FTS Performance in SQL 2005
    ... Can you post you query plans and the output of statistics IO ... SQL Server MVP ... Because i've set the MAX sql-server memory to 3.5 GB instead of 4.0 GB ... cost relative to the whole batch, ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Help! Difficulty understanding DB -> Object mapping
    ... The bottleneck is getting it into memory as datasets; extracting particular information from the datasets in memory is the fast part. ... [I would add that whoever writes the subsystem needs to understand the real trade-offs, ... Most DBMS's have been able to give good performance with joins for some time, although in the past you had to have a pretty good knowledge of how the query was executed in order to achieve that performance. ... "Compiled" queries, say in stored procedures, are typically tokenized on the first invocation, and a query plan is computed based on the passed parameters for that invocation. ...
    (comp.object)