SQL Server memory usage Solution

In the last two weeks, the memory of the DB server has not been used up, and 32 GB is used. As a result, the memory usage has been around 4 GB, which is really confusing. Normally, around 17 GB should be used. After troubleshooting for half a day, it turned out that awe didn't restart the service after it was turned on. It was really too difficult, so I had to find it for half a day. After the service is restarted, the memory usage gradually increases to a normal level.

Although there is no technical content, the search process is quite interesting, and the search method can also be used in the opposite way, so record it.

First, check the server version and find it is Windows 2003x64. The surprising result is that the SQL Server 2008x86 is installed, which is too confusing, is it better to Install SQL Server 2008x64? On a 64-bit system, 32-bit SQL server can only occupy 4 GB at most. Therefore, we will check whether awe is configured as the main starting point.

Run "sp_configure 'awe enabled'" directly, but "the configuration option 'awe enabled' does not exist, or it may be an advanced option. "error, the original needs this:

Exec sp_configure 'show advanced options', 1
Reconfigure
Go

Exec sp_configure

Later, we found that config_value is 1, while run_value is 0, which is really depressing. Obviously, the configuration is successful, but it is not successful. It must be that the SQL Server service is not restarted after the configuration.

Exec sp_configure is easy to use. It can check whether some configuration items take effect, mainly by comparing config_value, run_value and default values.

The following table lists all available configuration options, possible setting ranges, and their default values. Configure the option by the following letterCodeMARK:

• A = Advanced option. Only experienced database administrators or authenticated SQL Server technicians are allowed to make changes.Show advanced
  OptionsSet to 1.

• RR = specifies the option to restart the database engine.
• SC = custom configuration option.
  

  Configuration Options	Minimum value	Maximum Value	Default Value
  Ad hoc distributed Queries ()	0	1	0
  Affinity I/O Mask (A, RR)	-2147483648	2147483647	0
  Affinity64 I/O Mask (A, only applicable to 64-bit SQL Server)	-2147483648	2147483647	0
  Affinity mask ()	-2147483648	2147483647	0
  Affinity64 Mask (A, only applicable to 64-bit SQL Server)	-2147483648	2147483647	0
  Agent XPS ()	0	1	0 (When the SQL Server proxy is started, it is changed to 1. If the SQL Server Agent is set to automatically start during installation, it is 1 .)
  Allow Updates (expired. Do not use. Will cause Errors During reconfiguration .)	0	1	0
  Awe Enabled (A, RR)	0	1	0
  Blocked Process Threshold ()	0	86400	0
  C2 Audit Mode (A, RR)	0	1	0
  CLR Enabled	0	1	0
  Common criteria compliance Enabled (A, RR)	0	1	0
  Cost threshold Parallelism ()	0	32767	5
  Cross dB ownership Chaining	0	1	0
  Cursor threshold ()	-1	2147483647	-1
  Database mail XPS ()	0	1	0
  Default full-text Language ()	0	2147483647	1033
  Default language	0	9999	0
  Default trace Enabled ()	0	1	1
  Disallow results from Triggers ()	0	1	0
  Fill Factor (A, RR)	0	100	0
  FT crawl bandwidth (max), see ft crawl bandwidth ()	0	32767	100
  FT crawl bandwidth (min), see ft crawl bandwidth ()	0	32767	0
  FT sort y bandwidth (max), see ft sort y Bandwidth ()	0	32767	100
  FT sort y bandwidth (min), see ft sort y Bandwidth ()	0	32767	0
  Index create Memory (A, SC)	704	2147483647	0
  In-doubt xact Resolution ()	0	2	0
  Lightweight Pooling (A, RR)	0	1	0
  Locks (A, RR, SC)	5000	2147483647	0
  Max degree Parallelism ()	0	64	0
  Max full-text crawl Range ()	0	256	4
  Max Server Memory (A, SC)	16	2147483647	2147483647
  Max text repl size	0	2147483647	65536
  Max worker Threads (A, RR)	128	32767 (For 32-bit SQL Server, we recommend that you set the maximum value to 1024. For 64-bit SQL Server, we recommend that you set the maximum value to 2048 .)	0 The zeroth operation automatically configures the maximum number of worker threads based on the number of processors. You can use the formula (256 + (<Number of processors>-4) * 8) to calculate 32-bit The number of SQL Server threads. The number of 64-bit SQL Server threads is twice that of 32-bit.
  Media Retention (A, RR)	0	365	0
  Min memory per Query ()	512	2147483647	1024
  Min Server Memory (A, SC)	0	2147483647	8
  Nested triggers	0	1	1
  Network Packet Size ()	512	32767	4096
  OLE Automation Procedures ()	0	1	0
  Open Objects (A, RR, obsolete)	0	2147483647	0
  Ph_timeout ()	1	3600	60
  Precompute rank ()	0	1	0
  Priority Boost (A, RR)	0	1	0
  Query Governor cost Limit ()	0	2147483647	0
  Query wait ()	-1	2147483647	-1
  Recovery Interval (A, SC)	0	32767	0
  Remote Access (Rr)	0	1	1
  Remote admin Connections	0	1	0
  Remote Login Timeout	0	2147483647	20
  Remote proc Trans	0	1	0
  Remote Query Timeout	0	2147483647	600
  Replication XPS options ()	0	1	0
  Scan for startup Procs (A, RR)	0	1	0
  Server trigger Recursion	0	1	1
  Set Working Set Size (A, RR, obsolete)	0	1	0
  Show advanced Options	0	1	0
  SMO and DMO XPS ()	0	1	1
  SQL mail XPS ()	0	1	0
  Transform Noise Words ()	0	1	0
  Two digit year Cutoff ()	1753	9999	2049
  User Connections (A, RR, SC)	0	32767	0
  User instance Timeout (A, only available in SQL Server 2005 express Edition)	5	65535	60
  User instances Enabled (A, only available in SQL Server 2005 express Edition)	0	1	0
  User options	0	32767	0
  Web Assistant Procedures ()	0	1	0
  Xp_mongoshell ()	0	1	0