Hot answers tagged

56 votes
Accepted

Is it generally faster to select into a temp table than selecting into an actual table?

writing to tempdb is faster than an actual table not in tempdb It's true. There are two IO enhancements in TempDb. Writes to a table in a user database must have their log records flushed to disk ...
52 votes
Accepted

SQL Server's "Total Server Memory" consumption stagnant for months with 64GB+ more available

I bet you've configured the virtual CPUs in a way that some of the CPU nodes and/or memory nodes are offline. Download sp_Blitz (disclaimer: I'm one of the authors of that free open source script) ...
  • 41.5k
46 votes

PostgreSQL: Force data into memory

Postgres 9.4 finally added an extension to preload data from relations into the OS or database buffer cache (at your choice): pg_prewarm This allows reaching full operating performance more ...
32 votes
Accepted

MongoDB using too much memory

Okay, so after following the clues given by loicmathieu and jstell, and digging it up a little, these are the things I found out about MongoDB using WiredTiger storage engine. I'm putting it here if ...
  • 871
28 votes
Accepted

What happens when there is no available physical memory left for SQL Server?

when there is no physical memory left for data, then SQL Server moves the already existing data into TEMPDB The article you linked to is misleading at best, and incorrect in some places. I think the ...
  • 27.7k
22 votes
Accepted

Increased memory to server, but do I need to change page-file size?

I believe the 2x size of RAM recommendation is outdated and unrealistic. If a server only has 32 GB of RAM it might make sense, but it's a waste of expensive disk space to make it 2x on a system ...
  • 7,801
22 votes
Accepted

Memory Requirements for SQL Server

This is a hard question to answer/prove without knowing a lot more about your workload than provided. But, I think an easy way to check before committing to a smaller server would be to just change ...
21 votes
Accepted

Operator used tempdb to spill data during execution with spill level 2

what about level 2? I could not find anything related to level 2. As per this Old MS Doc the number in Tempdb spill signifies how many passes is required over data to sort the data. So Spill 1 means ...
  • 18.4k
21 votes

Memory Usage by SQL Server

'I am trying to determine if memory pressure is an issue to some of my problem.' very useful script: https://github.com/ktaranov/sqlserver-kit/blob/master/Scripts/SQLServer_Memory_Information.sql ...
  • 681
20 votes
Accepted

Does CPU utilization affect the cost of foreign NUMA access?

A hefty question :-) I'll outline some of the factors involved. In any given context, these factors and others can vary and produce an interesting result. Sorry I wasn't able to make this much ...
19 votes

SQL Server Instance How to recover memory used

Is this Normal? Yes. my boss and colleagues state that this is not normal behaviour for the database SQL Server will grow its memory usage up to your configured max server memory and only reduce ...
17 votes

Why over 1.3TB desired memory for this query, it should have no memory grant?

The memory grant is for prefetching and batch sorting on the nested loops joins. See the "Optimized" and "WithUnorderedPrefetch" properties. There is a documented trace flag to turn Optimized off - ...
  • 73.8k
15 votes
Accepted

Can SQL Server's Page Life Expectancy be too high?

No, I can't think of any situation or theory in my head that could illustrate a negative side effect for an astronomically high PLE (unless you enjoy the humming sound of the platters in a hard disk ...
15 votes
Accepted

Memory Usage by SQL Server

Can someone direct to a good/proper explanation. I would start by saying Task Manager is not a correct place to gauge SQL Server memory consumption, it will not tell you correct value when SQL Server ...
  • 18.4k
15 votes
Accepted

What does the maximum SQL Server memory configuration limit?

On SQL Server 2012 and later, max server memory limits almost all memory committed by SQL Server. The buffer pool is just one cache that competes with others (like memory grants and the plan cache) ...
  • 73.8k
15 votes
Accepted

SQL Server LOB variables and memory usage

The 2GB LOB limit only applies to persistent storage; variables have no limit. SQL Server starts with an pure in-memory implementation. It changes to a tempdb-based backup scheme if the size of the ...
  • 73.8k
13 votes
Accepted

PostgreSQL complaining about shared memory, but shared memory seems to be OK

Your comment about intensive dropping and creating and the notice you received regarding increasing max_locks_per_transaction hint that you're dropping and creating many objects in the same ...
13 votes
Accepted

SQL Server 2012 Standard Edition using more than 64GB of memory, why and how?

The 64GB limit (and 128GB in 2014) applies only to buffer pool. The KB article "Memory configuration and sizing considerations in SQL Server 2012" says: Starting with SQL Server 2012, these memory ...
13 votes
Accepted

Plan cache size and reserved memory

The reason that the size_in_bytes field of the sys.dm_exec_cached_plans DMV, at least in terms of "Compiled Plans", is larger than the CachedPlanSize attribute of the QueryPlan node in the XML plan is ...
13 votes
Accepted

How does SQL Server handle the data for a query where there is not enough room in the buffer cache?

Pages are read into memory as required, if there is no free memory available, the oldest unmodified page is replaced with the incoming page. This means if you execute a query that requires more data ...
  • 67.6k
13 votes
Accepted

Analyze memory usage of PostgreSQL – why is it growing constantly?

A trigger that is defined as AFTER INSERT...FOR EACH ROW will queue up info all the inserted rows and then fire the trigger for each one at the end of the statement. So if you insert millions of ...
  • 33.2k
13 votes
Accepted

Multiple BIT datatype columns in the same table

For a fixed length field (such as a bit value), the field will always take up the same amount of space - in your case one bit. The NULL bitmap is in the data row itself - this stored whether a ...
12 votes
Accepted

PostgreSQL could not fork autovacuum worker process: Cannot allocate memory

Hardware problems... This log entry: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and ...
  • 29.4k
12 votes
Accepted

DB Engine Stolen Server Memory is too high

How many times do you see such messages? If it is once or twice it can be ignored. 'Stolen memory' as described in this older support article and How to use the DBCC MEMORYSTATUS is: Stolen memory ...
  • 18.4k
12 votes
Accepted

SQL Server Management Studio 2016 (Release 16) keeps on crashing

You are not alone. See more details here. Posted by Microsoft: Turns out there's a thread leak in a utility class. The number of threads leaked will be proportional to the number of registered ...
  • 1,348
12 votes
Accepted

Will Changing the Max Memory Setting Invalidate Query Plans?

Per Microsoft documentation changing the max server memory will clear the plan cache, as will changing: access check cache bucket count access check cache quota clr enabled cost threshold for ...
  • 3,953
12 votes
Accepted

Improving RAM and CPU utilization by PostgreSQL 9.6

The default settings in postgresql.conf are very conservative and normally pretty low. I suggest the following changes: raise shared_buffers to 1/8 of the complete memory, but not more than 4GB in ...
12 votes
Accepted

MongoDB terminates when it runs out of memory

As per MongoDB BOL Here Changed in version 3.4: Values can range from 256MB to 10TB and can be a float. In addition, the default value has also changed. Starting in 3.4, the WiredTiger internal cache,...
11 votes

SQL Server 2012 memory consumption outside the buffer pool

Max server memory controls buffer pool and all page size allocations, but still does not control things like direct Windows allocations (linked servers, sp_OA, XPs), memory required for threads/thread ...
11 votes

MongoDB using too much memory

Docs You may like to read basic memory concerns for MongoDB and also this brief discussion about checking memory usage. Memory usage overview The command db.serverStatus() (docs) can provide an ...

Only top scored, non community-wiki answers of a minimum length are eligible