Solution to the high memory footprint of W3wp.exe and sqlservr.exe on the server

How SQL Server uses memory

The biggest overhead is typically used for data caching, and if enough memory is available, it will throw the used data and the data that you would like to use in memory, until the memory is low, and the data that is less than the hit is cleared. So generally when we look at statistics IO, we see that the physics read is 0. The second is the cost of the query, generally, the hash join will bring a relatively large memory overhead, and the merge join and nested loop overhead is relatively small, there are sorting and intermediate tables, cursors also have a relatively large overhead. Finally, let's look at the cost of the query, which is obviously the lower the better, because we can not benefit from it, on the contrary, the more memory used means that the query speed is reduced. So we generally avoid the use of intermediate tables and cursors, and build indexes on columns that are often associated and sorted.

Let's take a look at the performance impact of data caching, and if there are no other applications competing for memory in the system, the data cache is generally more or less the better, and sometimes we force some data to be stored in the cache. Server if there are other applications, although MS SQL frees up memory when needed, thread switching, io waiting for these tasks also takes time, so it can cause performance degradation. Sqlservr.exe is very memory-eating, if not controlled, he will take up all the physical memory, so we must set the maximum memory usage of MS SQL.

Solutions to reduce sqlservr.exe memory usage:

1, in general, the SQL statement optimization is not good enough, or improper use of caching schemes, it is recommended to optimize SQL statements and use a reasonable caching scheme.

2, optimize the website program, less use or no session, cache, application, such as the cache scheme, there is a page turn processing is not good also will occupy a lot of memory.

3, set the maximum server memory: Open SQL Server Management Studio, right-click Server, Properties---memory----Set the maximum server memory, which can be based on server configuration and requirements, this example takes 2G memory as an example, Set to 60% of physical memory.

If the memory is greater than or equal to 4G, I usually set this (total memory in GB):

Initial value = (Total memory-(total memory/8)-2) *1024
EXEC sys.sp_configure n ' max server memory (MB) ', n ' initial value '
GO
RECONFIGURE with OVERRIDE
GO
Reserve 1GB for OS first
4~16GB RAM, 1GB is left per 4GB,
16GB ram+, then 1GB per 8GB
Then, based on the value of the Memory\Available MBytes (recommended around 300MB), then gradually increase the max Memory value.

Error after limiting server memory

Program error: Resource pool ' default ' does not have enough system memory to run this query

You can try to resolve it by updating the statistics for the database by executing the following SQL statement:

EXEC sp_updatestats

Simple understanding of IIS application Pools

W3wp.exe is an IIS process, under IIS6, often occurs w3wp.exe memory and CPU usage can not be released in time, resulting in slow server response.

The default recycle interval for the program pool for IIS is 1740 minutes (29 hours), and the application pool is emptied during the auto-recycle process, and the data that remains in memory is cleaned up (equivalent to IIS restarts). For Internet applications, in order to reduce the burden on the database server, may choose to put a large amount of data in memory, recycling will cause memory data loss, if not saved to the database in a timely manner, may lead to application problems. In the event of peak system usage, recycling can cause the application to be unresponsive (in suspended animation) for a period of time, giving the user a very bad experience.

Before we optimize the application pool, we apply a few configuration information for the next program pool:

prevent reclamation When configuration changes occur: If true, the application pool will not be reclaimed when configuration changes occur.
fixed interval (minutes): After the set time is exceeded, application pool recycling, 0 means that the application pool is not recycled at a fixed interval. The system default setting is 1740 minutes (29 hours).
Disable overlapping reclamation: If true, application pool recycling occurs so that existing worker processes are exited before another worker process is created.
Request limit: The maximum number of requests that an application pool can handle before it is reclaimed. A value of 0 indicates that there is no limit to the number of requests that the application pool can handle.
Generate Recycle Event log entries: an event log entry is generated for each occurrence of a specified collection event, and the details are not described.

Problem Analysis: Is it reasonable to recycle every 1740 minutes (29 hours)?

Less reasonable, during this cycle, it is possible that the application is in peak access. Because the daily recycling time is not the same, it is likely to be recycled during the peak, it will cause a short period of site access problems. Therefore, to avoid minimizing the impact on the user, we need to fully analyze the application's access, such as which time period is peak,

Which time period is the fewest number of visitors. Aware of this, the application deployer should set a fixed collection time, such as a site two o'clock in the morning visitors are the fewest, then it can set a "specific payback time" at two o'clock in the morning, the application pool within the specific time is supported by setting multiple, please note.

Solutions to reduce w3wp.exe memory usage

Open IIS Service Manager, application pool, select the application pool for the Web site, click "Recycling"

In the popup window, set a specific collection time (before you right-click the site application pool, select Advanced Settings, set its fixed interval (minutes) to 0), and set the maximum memory-based value as shown in:

resolving excessive CPU usage

1. Separate application pool configuration for each Web site in IIS. That is, do not affect each other.
2. Add the Display PID field to the Task Manager. You can see the process PID that consumes memory or the highest CPU
3. Run iisapp-a at the command prompt. Note that the first run, will prompt without JS support, click OK. And then you run it again. This allows you to see the application pool corresponding to the PID. (Iisapp is actually a VBS script that is stored in the C:\windows\system32 directory with the full name iisapp.vbs, if you and I also prohibit the VBS default affiliate program, then you need to manually go to the directory, select Open mode, and then select " Microsoft (R) Windows Based Script Host "to execute, you can get the corresponding relationship between the PID and the application pool." ）
4, to the IIS to view the application pool corresponding to the site, OK, make the above memory or CPU restrictions, or check the program has a dead loop and so on.

5, find out the most CPU-consuming SQL statement to optimize

--Find the top 50 CPU-consuming SQL syntax select top 50qs.total_worker_time/qs.execution_count as [Avg CPU time],substring (Qt.text, QS.STATEMENT_START_OFFSET/2, (case is Qs.statement_end_offset =-1 Then len (convert (nvarchar (max), Qt.text)) * 2 Else QS . Statement_end_offset end-qs.statement_start_offset)/2) as Query_text,qt.dbid, Dbname=db_name (qt.dbid), Qt.objectid From sys.dm_exec_query_stats Qscross apply Sys.dm_exec_sql_text (Qs.sql_handle) as Qtorder by [AVG CPU time] DESC

Solution to the high memory footprint of W3wp.exe and sqlservr.exe on the server