57
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 ...
- 47.3k
54
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) ...
- 43.1k
40
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 ...
- 1,011
29
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 ...
- 29.6k
24
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
...
- 740
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,974
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 ...
- 9,179
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 ...
- 19.1k
19
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 ...
- 19.1k
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 ...
- 47.3k
17
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 ...
- 40.7k
17
votes
Accepted
SQL Server LOB variables and memory usage
The 2GB LOB limit only applies to persistent storage (except FILESTREAM); variables have no limit.
SQL Server starts with an pure in-memory implementation.
It changes to a tempdb-based backup scheme ...
- 88.2k
15
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 ...
- 285
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) ...
- 88.2k
15
votes
Accepted
SQL Server: Max memory same as Min memory?
this is stupid advice
That book might better serve you by keeping a coffee table level or something. When you set min server memory equal to max server memory, SQL Server can no longer use memory ...
14
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 ...
- 266
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 ...
- 70.3k
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,570
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,428
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 ...
- 4,033
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,477
12
votes
Accepted
AppDomain 2 SSISDB is Marked for Unload due to Memory Pressure
About your server
This query spills badly in two places, despite a 29 GB memory grant. That's about the high end of what a query can ask for on your system with max server memory set to 115 GB.
The ...
11
votes
Maximum memory for SQL Server 2000 TB when my RAM is just 512 GB
Think of the "Maximum server memory" setting in your right screenshot as a limit setting. You can configure SQL Server so that it won't use any more than a certain amount.
By default, this number is ...
- 43.1k
11
votes
Accepted
How Important is the buffer pool really?
The test query that you ran to measure the difference a cold and warm cache is going to spend most of its execution time waiting on ASYNC_NETWORK_IO. Sending 14 million rows to SSMS likely takes about ...
- 32.6k
11
votes
SQL Server is using a lot more RAM than it should
The fact that dm_os_memory_clerks doesn't show the memory strongly suggests that the memory was allocated outside of the SQL Server engine.
"PC SOFT OLE DB provider for HFSQL"
That'll be ...
- 47.3k
10
votes
Accepted
Set Maximum Server Memory Higher Than What is Available to OS
Presumably you have the "Standard" edition of Windows Server 2008 R2.
You'll need to move to the Enterprise Edition (or DataCenter) of Window Server 2008 R2 to utilize more than 32GB, regardless of ...
- 70.3k
10
votes
How can I find the source of postgresql per-connection memory leaks?
If you were using community PostgreSQL, the definitive answer to figuring out where the memory is going on Linux is to connect to a bloated backend with gdb, and do p MemoryContextStats(...
- 40.7k
10
votes
Accepted
How SQL Server caches data in memory
Test Data
CREATE TABLE dbo.Table1( A INT IDENTITY(1,1) PRIMARY KEY NOT NULL
,B varchar(255),C int,D int,E int);
INSERT INTO dbo.Table1 WITH(TABLOCK)
(B,C,D,E)
SELECT TOP(...
- 16.5k
10
votes
Accepted
At what point is a (disk-based) table removed from memory after data is read from it?
This is a complicated topic, much of which is covered in this Microsoft Docs article.
To answer a couple of points:
Are (disk-based, so no Hekaton) tables persisted in memory for longer than the ...
- 29.6k
10
votes
Accepted
Changing to compatibility level 150 makes SQL Server consume all available memory
You're encountering a known bug with scalar UDF inlining. You can disable inlining using one of the methods here (or by using a lower compat level, as you've discovered yourself):
Disabling Scalar ...
- 29.6k
Only top scored, non community-wiki answers of a minimum length are eligible