55
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 on commit, or if a minimally-logged insert (like SELECT ... INTO), must have the database pages flushed to disk on commit. The way the minimal logging works ...
answered Jan 23 '20 at 1:13
David Browne - Microsoft
36.7k3 gold badges38 silver badges79 bronze badges
51
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) and run it:
sp_Blitz @CheckServerInfo = 1;
Look for warnings about CPU and/or memory nodes being offline. SQL Server Standard Edition only sees the first 4 CPU ...
45
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 quickly.
Run once in your database (detailed instructions here):
CREATE EXTENSION pg_prewarm;
Then it's simple to preload any given relation. Basic example:
...
answered Sep 29 '15 at 23:11
Erwin Brandstetter
142k19 gold badges339 silver badges482 bronze badges
32
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 anyone encountered the same questions.
The memory usage threads that I mentioned, all belonged to 2012-2014, all pre-date WiredTiger and are describing behavior ...
28
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 author was attempting to over-simplify some complicated things, and in doing so went a little too far.
SQL Server doesn't move data from memory (the buffer ...
22
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 that had a massive amount of memory.
There are two primary purposes of the pagefile:
To use as a swap file for memory. If SQL Server and other applications are ...
20
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 shorter...
Accumuated CPU ms vs logical IO
SQL Server logical memory node alignment with physical NUMA nodes
Spinlock contention in query workspace memory ...
20
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 it has to pass 1 time to sort the data and 2 means it has to pass 2 times.
Quoting from the blog:
If a query involving a sort operation generates a Sort ...
20
'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
you see verbose memory utilization:
https://www.sqlskills.com/blogs/glenn/sql-server-diagnostic-information-queries-for-november-2017/
SQL Server 2017 Diagnostic ...
18
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 its memory usage if the system runs low on physical memory.
If the system has plenty of available RAM, SQL Server will use it to cache data and execution plans, ...
answered Mar 22 at 14:37
David Browne - Microsoft
36.7k3 gold badges38 silver badges79 bronze badges
17
What is funny about temporary tables in a stored procedure is not so much the transient existence of the table (which gets dropped upon the DB connection's termination), but the scope of the stored procedure.
Someone asked this question on StackOverflow : Scope of temp tables created in MySQL stored procedure. It has been over a year and nobody answered the ...
17
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 - TF 2340; otherwise, the optimizer decides based on cardinality estimates. From SQL Server 2016 SP1 on, you can also use the query hint ...
15
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 drive?).
With memory getting cheaper and cheaper, and buyers-of-hardware getting a little more generous, these bigger memory boxes we see it is quite common to ...
14
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 service account has Locked Pages in Memory(LPIM) privilege. This is because normally task manager tracks Process Private bytes which is pageable memory and ...
13
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 limits are enforced only for the database cache (buffer pool).
And as we all know, max server memory also does not control all of SQL Server's memory. The ...
13
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 because a Compiled Plan is not the same thing as a Query Plan. A Compiled Plan is comprised of multiple Memory Objects, the combined size of which equates to ...
13
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 than can fit in memory, many pages will live a very short life in memory, resulting in a lot of I/O.
You can see this effect by looking at the "Page Life ...
13
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 particular field is considered NULL or not. As such the field itself only has to store two values - 1 or 0 - the NULL bitmap handles whether the field is actually set ...
12
Staring at the mysqltuner output, I see something disturbing
[--] Total buffers: 1.7G global + 18.6M per thread (800 max threads)
The 800 is the max_connections.
The 18M is a sum that includes read_buffer_size + sort_buffer_size + join_buffer_size
What is 800 * 18M ?
mysql> select 18 * 1024 * 1024 * 800 / power(1024,3);
+---------------------------------...
12
First all take a look at the InnoDB Architecture (courtesy of Percona CTP Vadim Tkachenko)
InnoDB
Your status for the Buffer Pool says
Buffer pool size 1310719
That's your Buffer Size in Pages. Each page is 16K. That turns out 20G - 16K.
Please note the the following: You pushed data into the InnoDB Buffer Pool. What changed ?
Buffer pool size 1310719
...
12
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 transaction. Each of those results in a lock, which each requires a small amount of shared memory. Because of this, max_locks_per_transaction limits the number of locks ...
12
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 possibly corrupted shared memory.
can be a result of at least two different underlying problems. The first one is a bad executable or failing hardware - this is why ...
12
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 describes buffers that are in use for sorting or for hashing operations (query workspace memory), or for those buffers that are being used as a generic memory ...
12
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
servers you have, among other things. A fix is coming in the next
release
12
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 parallelism
cross db ownership chaining
index create memory
max degree of parallelism
max server memory
max text repl size
max worker threads
min memory per query
...
12
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 total.
set effective_cache_size to total memory available for postgresql - shared_buffers (effectively the memory size the system has for file caching)
if you ...
11
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 stacks, etc.
You can probably expect this to be higher on NUMA (though I'm not sure 20 GB is normal); the point is, you can't expect max server memory to ...
11
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 effectively unlimited. (That's what your server is set to.) It's kinda like setting your own personal financial budget - by default, many of us are willing to ...
11
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, by default, will use the larger of either:
50% of RAM minus 1 GB, or
256 MB.
With WiredTiger, MongoDB utilizes both the WiredTiger internal cache and the ...
10
SQL Server uses that memory because it needs it, and typically won't release it unless there is pressure from the operating system to do so (since it assumes that you'll need that much memory again). So it is actually a good thing that it is using 4 GB of RAM, and if you don't need that memory for other things, why do you care?
You have basically two ...
Only top voted, non community-wiki answers of a minimum length are eligible