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 ...
- 41.6k
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 ...
- 156k
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 ...
- 7,846
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 ...
- 901
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 ...
- 41.6k
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 ...
- 41.4k
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 ...
- 651
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 ...
- 177k
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 ...
- 66.3k
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 ...
- 5,478
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 ...
- 246
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,...
- 6,143
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 ...
- 177k
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 ...
- 245
Only top scored, non community-wiki answers of a minimum length are eligible