Consulta de temas

Limiting the amount of memory available to Microsoft SQL Server on servers exhibiting performance issues



Article Summary: This article provides information on limiting the amount of memory available to Microsoft SQL Server on servers exhibiting performance issues


Issue:

Poor server performance occurs, the operating system becomes unresponsive or clients disconnect from servers running Microsoft SQL Server. This occurs because Microsoft SQL Server is configured to use all available memory.

Solution:

Use one of the following solutions to resolve the issue. Microsoft SQL Servers using the Express version will need to use the command line version of the solution as SQL Server Management Studio isn't installed. Database developers can use the query version.


I. Use SQL Server Management Studio to change the Maximum server memory.

1. Press Windows Logo+R, type ssms.exe to open SQL Server Management Studio.

2. Click Connect to connect to the correct SQL Server.


3. Right-click the name of the server in Object Explorer and select Properties.

4. Click on the Memory page.

5. Under Server Memory Options, enter the amount of memory you require for Maximum server memory (in MB).

Note: Replace 8192 with the amount of maximum memory specified in megabytes. The example uses 8192 MB or 8 GB.

6. Click OK to save changes.




7. Press Windows Logo+R, type services.msc to start Services.

8. Right-click on the MSSQLServer service.

9. Click Restart.




II. Use a Command Prompt and OSQL to change the Maximum server memory.

Note: Log on to the server with credentials that have access to SQL Server or you will encounter and error: "Could not open a connection to SQL server [53].
Note: Replace 8192 with the amount of maximum memory specified in megabytes. The example uses 8192 MB or 8 GB.

1. Press the Windows Logo+R, type 'cmd', click 'OK' and type the following command followed by the Enter key.

2. osql -E -S SQLInstance

3. USE master

4. EXEC sp_configure 'show advanced options', 1

5. RECONFIGURE WITH OVERRIDE

6. GO

7. USE master

8. EXEC sp_configure 'max server memory (MB)', 8192

9. RECONFIGURE WITH OVERRIDE

10. GO

11. USE master

12. EXEC sp_configure 'show advanced options', 0

13. RECONFIGURE WITH OVERRIDE

14. GO

15. Press Windows Logo+R, type services.msc start Services.

16. Right-click on the MSSQLServer service.

17. Click Restart.




III. Use a query or script to change the Maximum server memory.

Note: Replace 8192 with the amount of maximum memory specified in megabytes. The example uses 8192 MB or 8 GB.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'max server memory', 8192;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO


2. Restart the SQL Server service.

3. Press Windows Logo+R, type services.msc start Services.

4. Right-click on the MSSQLServer service.

5. Click Restart.

Additional Information:

This applies to SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and SQL Server 2012.

The default Maximum server memory for SQL Server is 2147483647 MB or 2097152 GB.

Excessive memory usage by SQL Server can generally be identified in Windows Task Manager. Find sqlservr.exe, SQL Server Windows NT or SQL Server (MSSQLSERVER) and review the amount of memory consumed by the executable.

Note: The SQL Server Windows NT process may not consume an excessive amount of memory. SQL Server may still be responsible for consuming all available memory. Check the Maximum server memory as described above in the Solutions section and ensure the correct value is present.



Quick Tips content is self-published by the Dell Support Professionals who resolve issues daily. In order to achieve a speedy publication, Quick Tips may represent only partial solutions or work-arounds that are still in development or pending further proof of successfully resolving an issue. As such Quick Tips have not been reviewed, validated or approved by Dell and should be used with appropriate caution. Dell shall not be liable for any loss, including but not limited to loss of data, loss of profit or loss of revenue, which customers may incur by following any procedure or advice set out in the Quick Tips.

Identificación del artículo: SLN289076

Última fecha de modificación: 08/27/2014 08:46 AM


Califique este artículo

Preciso
Útil
Fácil de comprender
¿Este artículo fue útil?
No
Envíenos sus comentarios
Los comentarios muestran caracteres no válidos, los caracteres especiales que no se aceptan son <> () &#92;
Disculpe, nuestro sistema de comentarios está actualmente inactivo. Vuelva a intentarlo más tarde.

Gracias. Sus comentarios se han enviado.