I have ASP.NET web site that does it's own independent caching of data and data does not change for long periods of time, so it does not need to query SQL Server second time with same query. I need to improve performance of first time (virgin) queries that go to that SQL Server. Some queries process so much data that they may cause SQL Server to use tempdb. I don't use temp table variables or temp tables, so SQL Server decides to use tempdb by itself whenever it needs to.

My db size is 16Gb, I have 32Gb of physical RAM available on my server machine.

I understand that MS SQL Server caching strategy tries to keep data in RAM to speed-up performance of similar queries if they need same data to be loaded again. In addition to that it will try to use available RAM instead of tempdb to speed-up performance without causing disk access.

I suppose that when query that needs to store something in tempdb SQL Server comes and there is not enough RAM available, SQL Server has 2 choices:

1) to unload some cached data and use spared RAM instead of tempdb to avoid disk writes

2) keep cached data for future queries and start using tempdb, which causes writes to slow disk.

I don't know what choice SQL Server will make in this situation, but I would like it to make choice #1 because I care only about performance of first-time (virgin) queries, because I never send same query to SQL Server again (though I may send similar query).

What is SQL Server caching strategy for this scenario ?

How does it balance usage of RAM between avoiding tempdb for virgin queries and speed of second time queries ?

Is it possible to configure SQL Server in such a way that it will make choice #1 ? If yes then how ?

How else can I boost performance of all virgin SQL queries ?

Since I don't know about SQL Server caching strategy I want to place database on RAM Disk. This will make sure that any virgin query has high speed of loading of uncached data even if SQL Server always makes choice #1. The risk of it is that SQL Server may start using more tempdb with less available RAM (only 16Gb left after I use 16Gb for RAM Disk) if it keeps making choice #2, which will slow down those virgin queries that cause spills into tempdb.

I am interested in solution for SQL 2008 R2, but I guess it's probably the same for SQL 2008, SQL 2005 and may be SQL 2000.


There are no other applications running on that box, it's dedicated to SQL Server. Website runs on separate box.

It's SQL Server 2008 R2 Standard Edition 64 bit on Windows Server 2008 R2 Enterprise 64 bit.

I run only read-only queries and database is set to be read-only.

Let's assume that there are already good indexes. This question is about SQL Server making choice #1 vs choice #2, how does it make it, if there is a way to control it and if RAM Disk helps it to make the right choice for virgin queries.

  • What makes you think that tempdb is being used even though you are not creating temp tables? Are you using distinct or group by tables? – Darin Strait May 18 '12 at 18:51
  • 3
    32/64 bit? Physical or virtual? Is this server dedicated to SQL Server or are you also running IIS or other apps on the same box? Have you done any analysis of the query execution plan? Can you post example queries and/or execution plans? And one more for luck... follow Kendra's guide to logging sp_whoisactive while your problem query is running and post the output. – Mark Storey-Smith May 18 '12 at 18:52
  • @darinstrait Most likely explanation would be a sort or hash spill. – Mark Storey-Smith May 18 '12 at 18:54

Your question can be basically rephrased as 'How does the query memory grant work?'. A good read on the subject is Understanding SQL server memory grant. Before a query is launched into execution it may require a memory grant for sorts and hashes and other memory hungry operations. This memory grant is an estimate. Based on current system state (number of requests running and pending, memory available etc) the system grants the query a memory grant up to the required amount. Once the memory is granted, the query starts execution (it may have to wait in the dreaded 'resource semaphore' queue before it gets the grant). At execution it memory grant is guaranteed by the system. This amount of memory can be shared with data pages (since they can always flush to disk) but never with other memory usage (ie. it cannot be subject 'steal'). So when the query starts asking for committed memory from its grant, the engine will deploy what you call 'strategy #1': data pages may be evicted (flushed if dirty) in order to give the query the memory it was promised. Now if the estimate was correct and the grant was 100% of the requested memory, the query should not 'spill'. But if the estimate was incorrect (boils down to cardinality estimates, therefore is subject to stale stats) or if the query did not got the entire grant it had asked for, the query will 'spill'. This is when tempdb comes into picture and performance usually tanks.

The only knob you have at your disposal that controls something in this process is the Resource Governor. Since the RG can be used to specify a MIN setting for a pool, it can be used to reserve memory for a certain workload so that it actually gets the memory grant it requests. Of course, after you did the proper investigation that shows that reduced memory grants are the culprit, and of course after the impact on other workloads was evaluated. And tested, of course.

Now lets go back to your original question. If your investigation is correct (a very big if) I would like to point out two problems:

  • you run in production queries that require memory grants for a web site. This is a big no-no. Memory grants are indicative of analytical queries which have no place in serving HTTP requests.
  • your queries are probably not event getting the memory grant they request. Again, even more of a no-no for a latency critical workload as web sites are.

So what that tells me is that you have a fundamental design and architectural problem. Web sites are latency driven and should create an OLTP like workload, with no memory grants and with no memory pressure on queries. Not to mention no spills. Analytical queries should be run in offline jobs and store the pre-processed results for quick availability when HTTP requests desire them.

  • @Mark: Most queries require no memory grant. Only a few operators (most notably sort and hash join) need a work buffer and thus request a grant. This is the standard 'nomenclature'. You may be thinking of execution environment and query execution plan, of which every single query requires one and it includes some memory. A memory grant is much bigger (MBs). Secondly, look at sys.dm_exec_query_memory_grants: you have requested (the max), required (the min) and granted (the actual). – Remus Rusanu May 20 '12 at 7:23
  • Apologies. I had picked up from somewhere that minimum per query was allocated from the same memory clerk, which was incorrect. – Mark Storey-Smith May 20 '12 at 10:05
  • Still not sure I agree with your two bullet points. All manner of trivial sorts and hash join operations require grants at the minimum level, so suggesting they must be eliminated entirely seems excessive. That spills to tempdb from insufficient grants are a red flag is certainly reasonable but a blanket ban on any operation requiring a grant might set many people on an unnecessary pre-emptive optimisation path? – Mark Storey-Smith May 20 '12 at 10:35
  • OP claims it has all necessary indexes. If that's true and the workload has enough memory grant (and even spill) issues as to be noticeable, then I would say that the workload is too analytical for a web site. Ultimately performance optimization is always a game of investigation to determine the root cause. All blanket statements and bans are always going found a counter example that proves them wrong, that is a given. Does the OP have a design issue that creates a too analytical workload? I don't know. Do I think it does? I would say 87.5% confidence yes. – Remus Rusanu May 20 '12 at 13:46
  • @Remus: Your guess was good, my web site queries are 100% analytical. It allows users to construct any possible queries in UI to send any possible combination of filters, aggregates and groupings to SQL Server (which, of course, makes indexing tough). Yes, I could make them to run in async mode saving results for later retrieval, but the goal is to make any query to run so fast that result is immediately available after 2-10 seconds and also analytical querying is the only function of that website,I think making them async makes sense only if there are other queries that are not analytical. – alpav May 21 '12 at 14:57

What you haven't mentioned is what kind of queries are run against the database and if there are right indexes to speed up the performance of your queries.

You also need to make sure if there are any other applications running on the same box. Even though the box has 32 GB of RAM, have you set any max memory setting on the database server to put any artificial limit. If there are apps running on the same server then SQL and the other apps may be competing for resources and note that SQL is very memory hungry.

SQL Server will use tempdb for internal sorting or hash joins/aggregates or spool operators etc and you can't control this behavior. What you can do is limit the amount of data being returned back.

Have you checked the wait statistics on this box? Every time SQL Server is waiting on a resource, SQL Server will track the wait resource and looking at that information helps.

Look at Glenn Berry diagnostic queries and that will be a good start for you.

Also look at PARAMETERIZATION FORCED as mentioned in http://weblogs.sqlteam.com/dang/archive/2009/06/27/Forced-Parameterization-A-Turbo-Button.aspx

  • ok, let's assume that there are already right indexes. I forgot to mention that this is read-only database with read-only queries and there are no other application running on SQl Server box. – alpav May 18 '12 at 20:51
  • Are your statistics up to date? Read-Only databases can't create statistics if they are missing or out of date. Is your data skewed or has unique values for the key. There are lot of factors that can cause this behavior. – Sankar Reddy May 18 '12 at 21:02
  • What do you mean by "this behavior" ? I didn't mention that something is going wrong. I just want to boost performance under my special circumstances. SQL Server is optimized to run in any situation, but it may or may not not run the best way in my situation. I am not sure if I can trust SQL Server to make balanced choice #1 vs #2. Every time I put new data on it I run sp_updatestats. – alpav May 18 '12 at 21:08
  • 2
    When you are running sp_updatestats, what is the sample ratio you chose. Default ratio is very sample and depends on the size of the index. If your queries query mostly(only) the new data and even if you do sp_updatestats, SQL Server can't make god decisions on the execution plans. – Sankar Reddy May 18 '12 at 21:22

This question currently reads like a solution looking for a problem. You've decided that a RAM disk is the solution and you want someone to validate that choice. Sorry, not going to happen.

If you have measured and observed a spill to tempdb, it will almost certainly be due to a sort or hash operation and an insufficient query memory grant. Depending on the volume of data to be processed this may be inevitable but good odds the query and/or indexing could be improved to avoid it.

Take a look at Buffer Management to better understand how SQL Server manages memory and SQL Server Memory Management Explained for some basic tools and DMV queries to understand where your memory is allocated.

How else can I boost performance of all virgin SQL queries?

This is a big topic. Post the query and plan and you'll get targeted feedback.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.