54
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
29.2k2 gold badges31 silver badges66 bronze badges
51
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 ...
47
The SQL Server development team work on the principle of least surprise - so SQL Server generally has new features disabled in the interests of maintaining behaviour as previous versions.
Yes, optimize for adhoc workloads is great at reducing plan cache bloat - but always test it first!
Kalen Delaney tells an interesting anecdote that she asked one of her ...
43
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
126k16 gold badges289 silver badges429 bronze badges
29
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
If I have 8GB of RAM in a server and I run 4 instances of SQL Express,
will the total memory limit used by SQL Server be 1GB or 4GB?
Each instance can use up to 1GB of memory for the buffer pool. Each instance can use a bit more than 1GB in total because not all memory allocations go via the buffer pool. In your case, the maximum memory used by the four ...
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
Below is a little code that will help you decide if "switching optimize for ad hoc workloads ON/OFF" will be beneficial or not. We normally check this as a part of our health check for in-house and client servers.
It is the safest option to enable and is described well by Brad here and by Glenn Berry here.
--- for 2008 and up .. Optimize ad-hoc for ...
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 ...
19
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 ...
18
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 ...
18
'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 ...
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
I would recommend using pgtune written by Greg Smith.
Simply run it on your server as follows:
pgtune -i postgresql.conf -o postgresql-tuned.conf
It has few more options, but just doing that and using generated postgresql.conf will do wonders for your server performance.
I think PostgreSQL should include this in standard install, and even run it by ...
13
IMO you are making what is probably a pretty common mistake when it comes to web pages which is to assume that the answer to performance problems due to initial result size on MySQL is to jump to NoSQL solutions often with little understanding of what the tradeoffs are or how to use them appropriately and effectively.
I would be surprised if a well-tuned db ...
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
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
This is an awesome post.
To answer your final question, I'd speculate that your answer is "yes".
That said, I probably would have pursued soft numa before resorting to the trace flags. I think you are right about the numa node allocation and that's could be at the root of your problem. Via soft numa, you could scale out the requests, depending on your ...
12
SQL Express is limited to 1GB for the Buffer Pool, but there are many other memory pools in SQL Server. What I find surprising is the excessive use of non-buffer memory pools.
To find out memory usage per memory clerk, run this:
SELECT type, SUM(pages_kb)/1024 AS MemoryMB
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY 2 DESC
Hope this helps
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
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 ...
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
...
Only top voted, non community-wiki answers of a minimum length are eligible
