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 ...
46
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'...
44
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!
[Edit: Kalen Delaney tells an interesting anecdote that she asked one ...
40
There are many tweakable constants, initialised via postgres.conf. The most important ones are:
max_connections: the number of concurrent sessions
work_mem : the maximal amount of memory to be used for intermediate results such as hash tables, and for sorting
shared_buffers the amount of memory dedicated to 'pinned' buffer space.
effective_cache_size the ...
33
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
103k10 gold badges219 silver badges347 bronze badges
29
This is one of the most controversial topics I have ever dealt with over the years as a MySQL DBA and in the DBA StackExchange.
To put it mildly, there is simply no other way to shrink ibdata1. With innodb_file_per_table disabled, every time you run OPTIMIZE TABLE on an InnoDB table, ibdata1 grows rapidly. Data that are dropped using DROP TABLE and DROP ...
27
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 ...
27
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 ...
25
You could NEVER, EVER trust Task Manager to tell you how much memory SQL Server is using (maybe you are remembering a 32-bit system with a very small amount of memory). Stop using Task Manager for this, period. Use the performance counter - you can also query the performance counter using DMVs:
SELECT object_name, cntr_value
FROM sys....
23
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 ...
21
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 ...
20
SQL Servers max memory setting defines the limits for buffer pool usage only. There will be variable but significant allocations required over and above that limit.
Jonathan Kehayias's, Christian Bolton and John Samson have level 300/400 posts on the topic. Brent Ozar has an easier to read article that might be a better place to start.
Also related: SQL ...
19
Consider using pgtune to help you tune the PostgreSQL configuration. From PgFoundry:
pgtune takes the wimpy default postgresql.conf and expands the
database server to be as powerful as the hardware it's being deployed
on
PostgreSQL's default configuration is very conservative and that tool is meant to help with this exact situation. The documentation ...
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 ...
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.
You may be able to tweak the query to lower the expected row counts such that the ...
16
As said Buffer pool and procedure cache are about the only things which are controlled by max server memory. There are lots of other things within SQL Server that can eat up memory beyond that limit. They include (but are not limited to):
Database Mail
SQLCLR
Extended Stored Procedures
The binaries them selves
SQL Mail
SSIS
SSAS
SSRS
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
You won't get a true picture of memory usage from Task Manager if the account the service is running under has the lock pages in memory privilege (edit: as per Mark Rasmussen's comment/link). To determine how much memory is being used you can look at:
SQLServer:Memory Manager\Total Server Memory perfmon counter
DMVs
I can't recall if there is a DMV or ...
15
mysqltuner rarely provides any useful information. It uses mostly irrelevant statistics about "hit rates" and puts arbitrary limits on what is an acceptable number of widgets are acceptable. If you are not facing a performance problem, then you don't actually need to solve any of the problems that it presents to you. That being said, here's a little ...
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
Stop/start the service, nothing else will release the memory back to the OS.
Obviously not something you'd ever want to do with an operational server but perfectly reasonable for a local sandbox. With 3 different instances on my laptop, its the only viable way.
Edit following @Nick's comment.
As James noted, if it reserves more than the minimum you ...
13
The only way to prove is not a memory issue is by prooving is something else. And this requires you to identify the root cause of your performance problems. I recommend you follow a methodology like Waits and Queues. The SQLCAT team has also published a Troubleshooting Flowchart poster that you can follow.
As a general side comment: if someone is offering ...
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
what about level 2? I could not find anything related to level 2.
As per this blog.msdn 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 ...
12
It is normal behaviour for SQL Server to allocate as much memory as it can into it's buffer pool. Databases work best with lots of buffer. If you want to change the behaviour, you can set the 'max server memory' setting. Some good background reading on that is here.
Only top voted, non community-wiki answers of a minimum length are eligible
