I am currently skimming through Pro SQL Server 2019 Administration: A Guide for the Modern DBA and I have found one thing that somewhat confuses me. In Chapter 5: CONFIGURING THE INSTANCE, the section on Min and Max Server Memory (pages 139-140) says: In many environments, it is likely that you will want to provide the same value for both Min and Max Server Memory. This will avoid the overhead of SQL Server dynamically managing the amount of memory it has reserved. If you have multiple instances, however, then dynamic memory management may be beneficial so that the instance with the heaviest workload at any given time can consume the most resources. ...Assuming that you have one instance and no other applications, such as SSIS packages, running on the server, you would normally set both the min and max memory setting to be the lowest value from the - RAM - 2 GB - (RAM / 8 ) * 7 However, the advice to "provide the same value for both Min and Max Server Memory" is in contradiction with the documentation, which says: It isn't recommended to set max server memory (MB) and min server memory (MB) to be the same value, or near the same values. In what situations would it be good idea to set Max Server Memory and Min Server Memory to be the same(ish) value? Or am I misunderstanding something about the advice?

At this moment I run into some database performance problems for a SaaS application. During the day the RESOURCE_SEMAPHORE wait stats shoot up to 30 to 60 seconds for 1 or 2 minutes. During that time I also receive one or more Severity 17 alert mails from our server with the warning "There is insufficient system memory in resource pool 'internal' to run this query." We've already resolved the most inefficient queries that had large memory grants (1,5 to 2,5 GB grants with only 5% or less usage). To pinpoint these queries we used Brent Ozar's sp_BlitzCache. Unfortunately the performance issues still occur after these changes. Mind you at this moment we have a agent job that runs DBCC FREEPROCCACHE every 5 minutes. Doing this makes the problem more sporadic. Change this job to run every half hour seems to make the problems worse. Of course running this job has other implications like higher compilations/sec and a higher CPU utilization but at this moment is a "best of both worlds" kinda solution. I'm afraid the memory pressure issues are a result of the server configured with not enough RAM memory? Is this assumption correct or are these problems created by something else? Server stats - SQL Server 2022 (16.0.4085.2) - 6 logical processors (max DOP = 4) - 16 GB total RAM, configured as 14 GB max server memory for SQL Server and 2 GB for OS - A total of 1381 databases - Total database size: 302GB

I'm currently running a MySQL server (version 5.7.42) in production, and I've noticed a gradual increase in memory usage throughout the day. I suspect there might be a memory leak issue causing this behavior. Here are some details about my server configuration: ``` MySQL version: 5.7.42 Server specs: 4 vCPUs, 8 GB of RAM MySQL data directory: SSD (no IOPS limit applied) SO: Ubuntu 22.04 ``` I'm using the Prometheus exporter to monitor the server, and the memory usage keeps growing gradually over time. Before upgrading to version 5.7.42, I was using version 5.7.22, and I didn't encounter this issue with memory usage. I'm wondering why this problem has arisen after the upgrade. To investigate the issue further, I tried reproducing the problem in a similar environment using Ubuntu 18.04, as the MySQL 5.7.42 repositories are not officially compatible with Ubuntu 22 bu I obtained the same results. It significantly worsens when the automysqlbackup process is triggered during the early morning hours. Also I'm using the --quick and --single-transaction options in process to avoid locking issues and improve the performance. This is the actual configuration that I'm using: ``` [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 default-character-set = utf8mb4 [mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp lc-messages-dir = /usr/share/mysql skip-external-locking max_connections = 200 max_allowed_packet = 128M interactive_timeout = 600 wait_timeout = 600 table_definition_cache = 4096 key_buffer_size = 128M max_heap_table_size = 256M tmp_table_size = 256M join_buffer_size = 4M thread_cache_size = 16 log_error = /var/log/mysql/mysql.err log_error_verbosity = 2 long_query_time = 5 slow-query-log = on slow_query_log_fil

When I use a variable of a large object (LOB) data type in SQL Server, is the whole thing kept in memory at all times? Even if it is 2GB in size?

We have a machine with 256 GB RAM. Max server memory for the SQL Server is set to 180 GB. Out of 180 GB, SQL Server typically uses: - Database Cache memory - ~ 140-150 GB - Plan Cache - ~ 10 GB - Stolen Server Memory: ~ 30 GB - Free Memory: ~ 9 GB - Granted Workspace Memory: this usually pretty low, peaks can be 0.5-1 GB Buffer Cache hit ratio - hovers over 99.9% all the time. Page Life Expectancy is pretty high number. Total Size of Database Data Files - 650 GB. Rate of data growth is about 500-1500 MB daily (but! older data is deleted every 6-8 months, so basically data files grow much slower than that). Question There is a requirement to migrate SQL Server to another machine. Targeting SQL Server 2022 when it comes out. It is mixed OLTP and OLAP-type workload, same databases used by many applications; most of RAM is used by cached database pages, meaning SQL Server does not have to read it from disk all the time. It feels like 128 GB for new machine will be pretty much enough, with Max Server Memory set to ~ 110 GB, leaving 18-13 GB for the OS. Target server will be in Azure VM and there you can't scale vCPU and RAM independently from each other. A machine with 256 GB RAM will have twice as many cores compared with a 128 GB machine, resulting in a pretty big cost difference. I think reducing the cost compared to what was projected initially, may have benefit for me in the long run so I think it is worth exploring. In Azure you can scale up anytime if needed. If you were me, how would you prove scientifically to your manager, that cutting memory in half will not kill SQL Server performance, not blow up Buffer Cache hit ratio or anything like that? I know that for a DBA, it may look safe to downsize this machine to 128 GB given that workload is not going to change. But how would you convince a manager based on your experience?

Suppose I limit the SQL Server memory to 100 GB. Does this only limit the buffer to 100 GB or does it also limit the query memory grants?

I have a SQL Server 2012 instance using more RAM that it should. The SQL Server process is using about 22.5GB RAM: The instance is configured to use a maximum of 10GB: Which is way more then expected. (It will lead to a server crash and we will have to reboot to get it back). I checked the memory usage (clerks) with this query: ``` select type, name, pages_kb/1024.0/1024.0 "size Gb" from sys.dm_os_memory_clerks order by pages_kb desc ``` SQL Server only seem to see about 7GB RAM being used: I know it's an old version of SQL Server (and it's not patched to the latest sadly) but I wasn't able to find any clear documentation regarding a memory leak in SQL Server 2012 SP2. Where should I look to find why SQL Server is using about 200% what it should? There is a linked server on this instance. A lot using SQL drivers (SQLNCLI and SQLNCLI11) but there is also some using a "PC SOFT OLE DB provider for HFSQL" which I've never seen before. Is there any way I could 'prove' this driver is the problem? The client will probably not agree to changing the setup based on an assumption, so if there is any way (other then disabling) to clearly show how much RAM is being used by the linked server, that would be priceless. @Aleksey: This is what the prod returns

We are running a local SQL Server 2017 to support a datawarehouse database. The database is loaded on a schedule through SSIS largely through the use of staging tables and the MERGE function. Recently, we have begun seeing the error "There is insufficient system memory in resource pool 'internal' to run this query.". It has become more and more rampant over the last couple weeks. We have tried: - Turning off query store - Updating SQL Server thru CU27 - Tracing the query (no good results) - Running as many reports as we can find to pinpoint the issue Recent changes that correlate to the timing of the error: - Turning on Query Store - Addition of a large number of indexes Other information: - We have 32gb of memory on the server and allocate 26gb to sql server - The TARGET table of the MERGE is a CLUSTERED COLUMNSTORE INDEX - The SOURCE table of the MERGE is a HEAP - Over time/multiple failures, the SOURCE table that has staged the changed records has grown to over 200,000 records. The TARGET table is ~10 million rows. Any help would be appreciated. I have scoured the internet for the last couple days looking for any guidance. All I've seen so far is: - Update SQL version - Modify your query - Add memory to the server MERGE STATEMENT: ``` DROP TABLE IF EXISTS #Changes; DROP TABLE IF EXISTS #TransformedChanges; CREATE TABLE #Changes ( [Change Type] VARCHAR(100) ); MERGE [dbo].[FactOrderLine] AS TARGET USING ( SELECT [FactOrderLine].[OrderLine_Key], [FactOrderLine].[BookedDate_Date_Key], [FactOrderLine].[BookedDate_Time_Key], [FactOrderLine].[Account_Key], [FactOrderLine].[CCN_Key], [FactOrderLine].[BillTo_SalesOffice_Key], [FactOrderLine].[BillTo_Territory_Key], [FactOrderLine].[ShipTo_SalesOffice_Key], [FactOrderLine].[ShipTo_Territory_Key], [FactOrderLine].[AssemblyLocation_Key], [FactOrderLine].[ProductDivision_Key], [FactOrderLine].[Product_Key], [FactOrderLine].[Booked Date], [FactOrderLine].[Ordered Quantity], [FactOrderLine].[Unit Price

I have a stored procedure that joins three large tables together (about 20 million records each) and loads records into a temp table. Data in the temp table is then merged into an existing table of about 60 million records. The server went offline with an error message of: AppDomain 2 (SSISDB.dbo.[runtime].1] is marked for unload due to memory pressure. Once I got the server back online, I restarted SQL services to clear out any processes that may have been lingering. Kicked off the job again and it completed without issue. I’m running SQL Server 2019 with 128GB of RAM. Maximum server memory is 117964MB on a 64-bit virtual server. Someone told me in Task Manager the memory usage was at 94% and that could be the issue. But doesn’t SQL take all of the memory available and hold on to it? So that seems like it’s operating as intended. `sp_WhoIsActive` revealed some queries with a status of 'Suspended' and 'Awaiting_Command', but I don’t believe those had much impact. 128GB of ram seems adequate, but I guess it’s relative to the job it’s being asked to do. Any idea how to troubleshoot or prevent from happening again? The data drive for the server is about 1.6TB. The two larger databases in the join are 10GB with 19 million rows and 13GB with 20 million rows. Those go into a temp table and then `MERGE` into a 26GB table with 53 million rows. The requested memory grant is 45GB and the actual is 30GB. The job ran outside of regular business hours so there should have been no competing queries, but I can't 100% confirm that someone wasn't working late. I did notice that it is having to do a `CONVERT_IMPLICIT` as well. Does that have a significant impact on required memory? Link to the query plan: https://www.brentozar.com/pastetheplan/?id=SyXaty7xK

Running on Oracle 12.1.0.2 AIX Power9 (yes, I know it's 2021, but that's the customer I've got) Executing a plsql block that includes 1000 inserts as plain text and commit in the end. ``` begin insert into tab1 values (1); insert into tab1 values (2); ...-- 998 more inserts commit; end; ``` It fails consistently on `ORA-04030: out of process memory when trying to allocate 20504 bytes (callheap, KTI call freeable small pool)` The same plsql block runs successfully on hundreds of other databases, including this very version and OS. The database was rebooted, no sessions except of mine. The memory parameters are: ``` *.db_16k_cache_size=0 *.db_block_size=8192 *.db_cache_size=30g *.java_pool_size=200m *.large_pool_size=1g *.lock_sga=TRUE *.pga_aggregate_target=10g *.shared_pool_size=8g ``` I have tried to use internal parameters - no luck, same error ``` *._use_realfree_heap=TRUE *._realfree_heap_pagesize = 262144 ``` Changed `_use_realfree_heap=FALSE` - same error. The trace file generated claims that the process consumes 95MB only.