![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(48, 86%, 14%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,018 questions
Hi @悠 高島
Is it possible that changing to the 64bit version of SQL Server will result in a high incidence of SQL Server Connection Error 17189?
Maybe it will be possible. Though this error is not caused by 32bit or 64bit.
If the server supports a 64-bit operating system, and there is no special reason, please install a 64-bit operating system. In this way, SQL Server can most effectively use more than 2 GB of memory. If you must use 32-bit, you must turn on AWE, but do not use the /3GB switch.
Note:
With the release of SQL Server 2012, AWE as a feature is no longer supported in the 32-bit SQL Server release.
You will find:
The configuration option "AWE enabled" has been removed in sp_configure.
The option "Use AWE to allocate memory" in the memory configuration tab in SQL Server Management Studio is gone.
If you choose to upgrade from a previous version of SQL Server to SQL Server 2012, and you set the "AWE eabled" option in the version in between
Set to 1, then the upgrade cannot be performed, and you will be given a corresponding warning.
32-bit SQL Server 2012 can use up to 4GB of physical memory. If you need to use more physical memory, you must upgrade
it to 64-bit version.
If the change to 64 bit might cause a lot of errors, what's the solution?
This question is good.
Error17189 is a serious problem for SQL Server. When all processes on SQL Server are occupied by user requests and a new client sends a connection request, SQL Server needs to create a new thread to respond to this request. If the connection cannot be established, the SQL Server will not connect intermittently. Connections already in SQL Server may still work.
As long as there are problems that cannot be created by threads, there will be records in SQL Server errorlog. The error message will be: " error: 17189, severity: 16, status: 1. SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection. Check the SQL Server error log and the Windows event logs for information about possible related problems."
After encountering such an error, you need to confirm the problem from two aspects.
- Check how many threads are used by SQL Server and whether it has reached the upper limit
A simpler way is to run the following query to check how many SPIDs are KPID<>0. When the KPID of a SPID is not 0, it means that it is running in a thread. Coupled with the threads required for SQL Server to run itself (usually 10 to 20), it is almost the number of threads used by SQL Server. select count(*) from master.sys.sysprocesses
where kpid <>0 - If the number of threads is far less than the maximum number set, then you have to consider whether MemToLeave is under pressure. You can run the following query to check the size of the MultiPage Allocator of each Memory Clerk, and confirm that the pressure comes from the SQL Memory Clerk, or code from a third party.(use select @@version to check your current version)
--sqlversion >=sql2012
SELECT M.type,
sum(M.virtual_memory_reserved_kb) AS VirtualMemoryReservedKB,
SUM(M.virtual_memory_committed_kb) AS VirtualMemortCommitedKB,
SUM(M.shared_memory_committed_kb) AS SharedMemroyCommittedKB,
SUM(M.shared_memory_reserved_kb) AS SharedMemroyReservedKB,
SUM(M.pages_kb) AS PagesKB
FROM sys.dm_os_memory_clerks M
GROUP BY M.type
ORDER BY PagesKB DESC
--sqlversion<sql2012
SELECT M.type,
SUM(M.virtual_memory_reserved_kb) AS VirtualMemoryReservedKB,
SUM(M.virtual_memory_committed_kb) AS VirtualMemortCommitedKB,
SUM(M.shared_memory_committed_kb) AS SharedMemroyCommittedKB,
SUM(M.shared_memory_reserved_kb) AS SharedMemroyReservedKB,
SUM(M.multi_pages_kb) AS MultiPagesKB,
SUM(M.single_pages_kb) AS SinglePagesKB,
SUM(M.multi_pages_kb)+SUM(M.single_pages_kb) AS TotalPagesKB
FROM sys.dm_os_memory_clerks M
GROUP BY M.type
ORDER BY TotalPagesKB DESC
In short, after confirming that SQL Server has not reached the thread limit, it is necessary to confirm that SQL Server has enough MemToLeave so that SQL Server can always create new threads when needed.
Note:
To configure memory, follow these two principles:
(1) The Windows system and other key application services must have enough memory, and they should not grab the memory that SQL Server has requested because of insufficient memory during operation.
(2) Under the premise of meeting point 1, SQL Server uses as much memory as possible, and the stability of the amount of memory used must be ensured.
According to these two principles, the following suggestions are provided for your reference.
(1) If the server supports a 64-bit operating system, and there is no special reason, please install a 64-bit operating system. In this way, SQL Server can most effectively use more than 2 GB of memory. If you must use 32-bit, you must turn on AWE, but do not use the /3GB switch.
(2) Try to make the server dedicated to the database. Do not install other services (such as IIS, middle-tier application services, etc.) on the same machine.
(3) It is recommended to set SQL Server Max Server Memory to ensure that Windows has enough memory for the system itself. If other application services use memory on the same server, their memory usage must also be deducted. Generally, Min Server Memory setting is of little significance.
(4) It is recommended to give the SQL Server the right to activate the account Lock pages in memory.
(5) The SQL system parameter "Set working set size" cannot play a role in fixing the physical memory of SQL Server on current Windows, so please never use it.
More information: monitor-and-troubleshoot-memory-usage
BR,
Mia
If the answer is helpful, please click "Accept Answer" and upvote it.