10

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.

Clarifications:

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.

alpav
  • 255
  • 2
  • 8

3 Answers3

7

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.

Glorfindel
  • 2,205
  • 5
  • 19
  • 26
Remus Rusanu
  • 52,054
  • 4
  • 96
  • 172
3

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

Sankar Reddy
  • 2,655
  • 19
  • 14
2

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.

Mark Storey-Smith
  • 31,860
  • 9
  • 90
  • 125