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 ...
49
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'...
48
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 ...
46
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 ...
41
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 ...
38
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
111k12 gold badges243 silver badges375 bronze badges
30
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 ...
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 ...
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 ...
26
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 ...
21
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
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. From SQL Server 2016 SP1 on, you can also use the query hint ...
17
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 ...
16
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 ...
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,
...
16
'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 ...
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
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
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 ...
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
