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
34.6k3 gold badges36 silver badges77 bronze badges
54
SQL Server will consume as much memory as you will allow it. By default, that number would encompass 100% of your numerical memory on your machine. That's why you're seeing what you're seeing. If you give SQL Server 24 GB of memory, then SQL Server will do its best to use 24 GB of memory. Then you have SQL Server and the OS battling for resources, and it'...
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 ...
44
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
136k17 gold badges324 silver badges471 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
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 ...
19
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 ...
19
'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
34.6k3 gold badges36 silver badges77 bronze badges
17
It is holding that memory because you used it once so, obviously, you will probably use it again. This is the way SQL Server works: it will take the memory it needs (up to the max you've allowed), and will only give it back to the OS if the OS demands it. If you're not demanding memory back from SQL Server, why do you expect it to give it up? Allocating and ...
17
Standard Edition is limited to a 64 GB buffer pool.
To use all the available memory of the server, you'll need to upgrade to either Enterprise or Datacenter Edition, or scale out to multiple instances of Standard Edition.
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 ...
16
You can find whats stored in the buffer pool (data cache) using below query :
From here :
select
count(*)as cached_pages_count,
obj.name as objectname,
ind.name as indexname,
obj.index_id as indexid
from sys.dm_os_buffer_descriptors as bd
inner join
(
select object_id as objectid,
...
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
SQL Server is designed to use all the memory on the server by default. The reason for this is that SQL Server cache the data in the database in RAM so that it can access the data faster than it could if it needed to read the data from the disk every time a user needed it. If we needed to go to the disks every time that SQL Server needed the same data over ...
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
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 ...
Only top voted, non community-wiki answers of a minimum length are eligible
