6

I run long and complex queries which mostly use one big table - 8GB, 40M rows. AFAIK, all/most rows are used in each query. I'm seeing lots of IO in the activity monitor - for the first query and for every subsequent one. The server is currently using 6.5GB of memory and I want to upgrade. Question is, how much memory is needed to avoid all these disk reads? Is it in the ballpark of the size of the table or more?

This is the SET STATISTICS IO output. BigTable is the one I'm asking about, SmallTable has a 1-to-many relation with BigTable. #entrance holds the output of the query (several hundred rows of output).

Table 'SmallTable'. Scan count 249005, logical reads 2829948, physical reads 2605, read-ahead reads 10395, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'BigTable'. Scan count 194004, logical reads 13482115, physical reads 33841, read-ahead reads 1181136, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#entrance__000000000023'. Scan count 0, logical reads 1568, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
3
  • 3
    Does this server/database do nothing else other than run your queries over 1 table of 8GB of data? I'm also scratching my head somewhat over the 1 table but complex query aspect of this question. Any chance of DDL or a little insight into the nature of this complexity? Sep 14, 2011 at 22:20
  • 1
    Just a basic check, what version of SQL server are you using? if its not enterprise then you will not be able to use all of your current memory for sql server. Sep 16, 2011 at 15:30
  • I eventually upgraded the RAM of the server and did a test. I posted the results as an answer below.
    – ytoledano
    Feb 9, 2013 at 8:50

5 Answers 5

3

what is the nature of your queries? SELECTs only? Or a mixture of DUI (DELETE, UPDATE, INSERTs)? and what is your recovery model? are you using the default transaction isolation level?

If you want to put all those pages in memory, then you will need to get more memory (but you knew that already). You want to know 'how much', but the truth is that depending on the nature of your queries you could still see some I/O issues even with enough memory to hold the entire table.

Consider ing all of the above...yeah, ballpark the size of the table on disk as the amount of memory. Just don't be surprised if you find you still have disk I/O afterwards.

1
  • I upgraded the server RAM and "ballpark the size of the table on disk as the amount of memory" is absolutely correct. See my answer below.
    – ytoledano
    Feb 8, 2013 at 11:59
8

This is a two part answer.

The first part is buy as much RAM as you can afford (as long as it'll fit into the server).

The second part is that you'll always see reads on data. The trick is to see if those reads are physical or logical. Physical reads are going down to the disk. Logical reads are read from the buffer pool in memory. You can see this by putting "SET STATISTICS IO ON" at the top of the SQL Script then running it in SSMS. On the messages tab you'll get specific IO information for each query that is being run, specifically you'll get the number of both logical and physical operations (reads in your case) that are happening on a table by table basis. If the physical read numbers are pretty high (these numbers are basically the number of blocks that were read so multiple by 8 then divide by 1024 to get a number in Megs) and then you need more RAM. If the logical read numbers are high, then you aren't actually going to disk but your query may need tuning, indexes created, etc.

(Note: For the purposes of this answer, I'm assuming SELECT statements only. INSERT/UPDATE/DELETE operations are a little more complex, but the same basics apply.)

2
  • I'll try: SET STATISTICS IO ON and report back. More info: The queries are 100% selects. Each query takes several minutes. The server also runs the small app which does the querying and dumps results into Excel, but I doubt this affects server performance.
    – ytoledano
    Sep 15, 2011 at 5:00
  • If you need some help reading the output, feel free to post the execution plan and SET STATISTICS IO output.
    – mrdenny
    Sep 15, 2011 at 5:05
4

I upgraded the server RAM from 8GB to 64GB and did a test. The table size is now 16.05GB and the index space is 187MB (1 clustered and 1 unique unclustered indexes). I checked total server RAM usage before and after running a query which averages every one of the 137 fields in the table. Total memory rose from 4.22GB to 20.4GB = 16.18GB which is 100.8% of the size of the table. SQLRockstar was absolutely correct in predicting that the amount of RAM usage is a ballpark of the size of the table.

More info:

  • Page fullness of the clustered index is 98.1%
  • Further queries on the table caused no disk IO.
  • SQL Server 2008R2 Developer
  • Execution plan shows that the clustered index was used
3

I'm always late to the party. :) This query most likely can be tuned to great effect. But, for the question of how much RAM to cache an 8 GB table... a preliminary question is how many memory nodes does SQL Server report for the server? Each memory node (logical equivalent to the physical NUMA nodes) has an independently managed buffer pool cache. Tasks that run on processors in that node insert blocks into the buffer pool in that node.

So, if there's a chance that the tasks that will read the 8 GB table in its entirety will run on a single NUMA node, that node's buffer pool must be able to contain the 8 GB table. So start at 8 GB of max server memory per NUMA node. Then lets start increasing it. Query memory can consume up to 70% of the buffer pool, leaving only 30% for database block contents. So the 8 GB table needs to be no more than 30% of the max server memory on a single NUMA node for the server. 26 2/3 GB for max server memory. Per NUMA node. If its SQL Server 2012, the stuff that SQL Server 2008 R2 and previous used the multi-page allocator for (which was thus outside of the max server memory scope) is now within max server memory scope and needs to be accounted for. It has to be included in the total server memory either way, whether its part of the SQL Server max server memory limit or not.

Oh yeah... any use of temp tables or other tempdb use should be accommodated in the max server memory as well, so as not to let the table get evicted from cache.

Add enough memory for the Windows OS (4 GB at least). Add enough for SQL Server worker threads, the SQL Server core process, SQL Agent jobs, any other application memory needs.

On a single NUMA node server I'd ballpark about 36 GB of RAM with max server memory at least 27 GB to reliably cache the 8 GB table. And if there are multiple NUMA nodes, it'll increase by at least 26 2/3 GB of max server memory per NUMA node, as well as for the additional worker threads to accompany the additional cores in the additional NUMA nodes.

While that may seem excessive, its due in part to how SQL Server handles NUMA. But you might be in luck! If a single lazy writer will suffice, you can use startup trace flag 8015 to ignore NUMA and manage a single buffer pool. That way, about 36 - 40 GB should suffice to keep the 8 GB table memory-resident no matter how many NUMA nodes there are on the server. discuss this a bit more in the following blog post. If you decide to use trace flag 8015, I recommend evaluating trace flag 8048 along with it, to prevent escalating spinlock contention and CMEMTHREAD waits associated with memory allocation. http://sqlsasquatch.blogspot.com/2013/02/sql-server-cache-database-working-set.html

1

Not sure that more RAM is going to help here. Quick calculation suggests your query is processing somewhere in the region of 125GB of data, quite impressive for a table that contains 8GB.

(2829948 + 13482115 + 1568) x 8 / 1048576 = 125GB

The 270MB of physical reads will account for a portion of the slow running but nothing like as much as the in-memory overheads.

You're going to get better results from re-working the query than by adding additional memory. Post DDL/query/execution plan in a fresh question and someone will certainly be able to help.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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