Consulta de temas

---

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).

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.

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.

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.

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.