Troubleshooting High Memory Usage by a MSDE Instance.
[Today’s post comes to us courtesy Anshul Srivastava]
You are running a Small Business Server 2003 and find that a MSDE Instance on the Server is consuming abnormally high memory. This post shows you how to identify the instance and then configure it to use the maximum amount of memory that it can use.
Identify the MSDE instance
1. Open Task Manager and select the ‘Processes’ Tab.
2. Click on the ‘View’ menu and then on ‘Select Columns...’
3. ‘Select Process Page Columns’ opens up
4. Put a check on ‘PID (Process Identifier)’ in the ‘Select Columns’ Window.
5. Click on ‘OK’ to close the ‘Select Columns’ Window.
6. You should now have a new column in Task Manager called ‘PID’.
7. Note down the PID of the SQL instance that’s consuming high memory (1648 in our case). (Click on Mem Usage Column to sort the services on memory usage)
8. Open a command prompt window. Run this command: “tasklist /svc” (excluding the quotes). Notice that the Instance of MSDE corresponding to PID 1648 is MSSQL$MSFW in this example.
Now that we have identified the instance that’s consuming high memory, let’s move on to the next step which is restricting this instance to use a certain amount of memory that we want it to use. However you may want to check for hotfixes or patches for the same before you do this. For example, in this case it may help to install the latest service pack for ISA since the identified process belongs to that app.
Restricting Memory Usage
You can use either of the two methods given below to achieve this. The first uses command line utility osql.exe and the second uses SQL Server 2005 Management Studio Express Edition.
Using osql command line utility:
1. Open a command prompt window and type in the following command:
osql -E -S YOURSERVERNAME\MSFW [hit enter] (This would take you into the osql prompt)
(Name of the instance is arrived at by removing the Prefix MSSQL$ from the name of the service. The Service in our case is MSSQL$MSFW. So removing it we get MSFW)
2. sp_configure 'show advanced options',1 [hit enter]
3. reconfigure with override [hit enter]]
4. go [hit enter]
(Note that in sp_configure ‘show advanced options’,1 we have to use single quote)
5. sp_configure 'max server memory', ### [Substitute ### with the max value that you want to use] [hit enter]
6. reconfigure with override [hit enter]
7. go [hit enter]
8. exit [hit enter]
Server Memory Options
We can use the two server memory options, min server memory and max server memory, to reconfigure the amount of memory (in megabytes) in the buffer pool used by an instance of Microsoft SQL Server.
By default, SQL Server can change its memory requirements dynamically based on available system resources. The default setting for min server memory is 0, and the default setting for max server memory is 2147483647. The minimum amount of memory you can specify for max server memory is 4 MB for SQL 2000 and 16 MB for SQL 2005.
You can visit the following URL for more information:
https://msdn.microsoft.com/en-us/library/aa214012.aspx
https://msdn.microsoft.com/en-us/library/aa196734(SQL.80).aspx (For SQL 2000)
https://technet.microsoft.com/en-us/library/ms178067.aspx (For SQL 2005)
Using SQL Server Management Studio Express
1. Download Microsoft SQL Server Management Studio Express using this link: https://www.microsoft.com/downloads/details.aspx?FamilyID=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en and install it.
Requires .Net 2 and Microsoft Core XML Services (MSXML) 6.0.) which can be downloaded using the following links:
Core XML Services 6(MSXML): https://www.microsoft.com/downloads/details.aspx?FamilyId=993c0bcf-3bcf-4009-be21-27e85e1857b1&displaylang=en
2. When you open the Management Studio, you get the ‘Connect to Server’ Dialog. Select the instance that you want to connect to. Select Windows Authentication as the Authentication method.
3. You would now see the following window.
4. Right Click on the Server Object (Topmost Item) and select Properties. The following window is displayed. Select Memory.
You can set the values for Minimum and Maximum Server memory. (Please keep in mind that the lowest value that you should use for Max Server memory is 4 MB for SQL 2000 and 16 MB for SQL 2005. Avoid using such low values as it can severely reduce performance)
It is recommended to use ‘Dynamic memory configuration’ . The other option ‘Lock Working set (in MB)’ fixes the amount of memory that SQL Server can use, turning off dynamic memory allocation. This particular setting is shown only when you have SQL 2000.
You will not see instant results after making these changes. The memory usage would slowly come down with time (a couple of minutes to a couple of hours).