Skip to main content
58 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 ...
David Browne - Microsoft's user avatar
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) ...
Brent Ozar's user avatar
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 ...
SpiXel's user avatar
  • 1,001
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 ...
Josh Darnell's user avatar
  • 29.3k
22 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 ...
Igor's user avatar
  • 720
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 ...
Tony Hinkle's user avatar
  • 7,924
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 ...
Jonathan Fite's user avatar
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 ...
Shanky's user avatar
  • 19k
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 ...
David Browne - Microsoft's user avatar
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 - ...
Paul White's user avatar
  • 84.3k
17 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 ...
Shanky's user avatar
  • 19k
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 ...
Paul White's user avatar
  • 84.3k
16 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 ...
jjanes's user avatar
  • 39.9k
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 ...
joeytwiddle's user avatar
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) ...
Paul White's user avatar
  • 84.3k
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 ...
Erik Darling's user avatar
  • 40.8k
14 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 ...
Solomon Rutzky's user avatar
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 ...
bjoernhaeuser's user avatar
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 ...
Hannah Vernon's user avatar
  • 70.1k
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 ...
George.Palacios's user avatar
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 ...
Danilo Braga's user avatar
  • 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 ...
Nic's user avatar
  • 4,013
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,...
Md Haidar Ali Khan's user avatar
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 ...
Brent Ozar's user avatar
11 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

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 ...
David Browne - Microsoft's user avatar
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 ...
Hannah Vernon's user avatar
  • 70.1k
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(...
Randi Vertongen's user avatar
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 ...
Josh Darnell's user avatar
  • 29.3k
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 ...
Josh Darnell's user avatar
  • 29.3k

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