• Home
  • About
  • Blog
  • Speaking
  • Contact

THE SQL SERVER MISCONFIGURATION CHRONICLES: PART 2 Instance Memory Settings

17 December 2015SQL StijnSQL Server Misconfiguration ChroniclesNo Comments

In the first part of the SQL Server Misconfiguration Chronicles we handled the database level issue of AutoClose & AutoShrink (https://swyssql.wordpress.com/2015/12/08/the-sql-server-misconfiguration-chronicles-part-1-database-autoclose-autoshrink/)

The second part of the misconfiguration chronicles will handle the default memory settings of a freshly installed SQL Server instance. SQL Server by default configures the instance memory settings to take a maximum memory of 2147483647MB which is about 2 Petabytes.

Why is this a bad thing?

Let’s say you have configured your SQL server memory to be able to take 2 petabytes of data, but your server only has 64GB of memory available. This will mean that SQL Server will be able to take all memory that exists on the server. But what happens to the other processes on your server. As you know you will also have to run an OS on your server, and maybe also SSIS & SSAS & god knows what. If SQL Server takes all the memory, it will be constantly fighting with the other processes to get the free memory of the server. Which will result in to memory pressure and with that big CPU consumption, because data has to be swapped from memory to disk constantly. Which will then result in a bad performing server.

How do you solve this?

That answer is quite straightforward, lower the default memory setting of your SQL Server to a value which will not take all memory of your server. For example, if you have a server with 12 GB of RAM, I would start off by giving 8GB of ram to the SQL Server leaving 4GB for the OS & other processes. This will result in a better performance of your server.

Following code will allow you to alter the default MaxMemory setting of your SQL Server

———————————————————————-
———————————————————————-
— Created By Stijn Wynants
— SQL MaxMemoryFixer
— Description: Execute Script to PRINT ALTER ServerMemory
— Statements for SQL Server Instance
———————————————————————-
———————————————————————-
DECLARE @ServerMemory int
DECLARE @SM nvarchar(200)
DECLARE @CMD varchar(max)
DECLARE @MemoryForOS int
DECLARE @MemoryOtherProcesses int
———————————————————————-
———————————————————————-
— SET YOUR MEMORY FOR SQL SERVER HERE !!!!
SET @ServerMemory = 14000        –> In MB
SET @MemoryForOS = 4096            –> In MB (Recommended minimum of 2048 MB Optimal 4096 MB, with more memory(64 gb,256,…) leave about 10%.
SET @MemoryOtherProcesses = 0    –> In MB (If there are no other processes just set to 0)
———————————————————————-
———————————————————————-
SET @SM = cast(@ServerMemory as nvarchar)
DECLARE @FullSystemMemory as int
SET @FullSystemMemory = (select top 1 total_physical_memory_kb/1024 from sys.dm_os_sys_memory)
IF @ServerMemory < (@FullSystemMemory – @MemoryForOS – @MemoryOtherProcesses)
Begin
SET @CMD =
‘
EXEC sp_configure ”show advanced option”, ”1”;
GO
RECONFIGURE
GO
EXEC sys.sp_configure N”max server memory (MB)”, ”’+@SM+”’
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure ”show advanced option”, ”0”;
GO
RECONFIGURE
GO
‘
END
ELSE
BEGIN
SET @CMD =
‘There is only ‘+cast((@FullSystemMemory – 2048) as varchar)+‘ MB available on this server, you have tried to choose more memory then the server can deliver. Pick a lower value then ‘+@SM+‘ MB’
END
PRINT @CMD

Thank you all for reading and happy reconfiguring!

The next part will be on Data File Misconfiguration!

(https://swyssql.wordpress.com/2015/12/24/the-sql-server-misconfiguration-chronicles-part-3-database-files/)

Stay Tuned!

Tags: Default memory, MaxMemory, Memory, Misconfiguration, SQL, SQL Script, SQL SERVER, T-SQL

Related Articles

Distributed Availability Groups: Availability groupception!

4 July 2016SQL Stijn

PDW Distribution & Replication

21 January 2014SQL Stijn

THE SQL SERVER MISCONFIGURATION CHRONICLES: PART 3 Database Files

24 December 2015SQL Stijn

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

Recent Posts

  • Building a Modern Lakehouse Data Warehouse with Azure Synapse Analytics: Moving your Database to the lake
  • Azure Synapse Dedicated SQL Pools: Workload Management
  • Synapse Spark – Playing with Data Frames with Python
  • Synapse Spark – Reading CSV files from Azure Data Lake Storage Gen 2 with Synapse Spark using Python
  • Azure Synapse Analytics – Serverless SQL Pools: Data Elimination

Recent Comments

    Archives

    • June 2022
    • December 2021
    • May 2021
    • April 2021
    • March 2021
    • February 2021
    • January 2021
    • December 2020
    • August 2016
    • July 2016
    • March 2016
    • February 2016
    • December 2015
    • October 2015
    • August 2015
    • March 2015
    • December 2014
    • November 2014
    • July 2014
    • February 2014
    • January 2014
    • December 2013

    Categories

    • Azure Synapse Analytics
    • Building a Modern Lakehouse Data Warehouse
    • Geen categorie
    • MS PDW
    • SQL 2016
    • SQL Maintenance
    • SQL Server Migration
    • SQL Server Misconfiguration Chronicles
    • SQL2014
    • Troubleshooting

    Contact Stijn now


    © SQL Stijn 2017 - Privacy Policy - Cookie Policy
    Manage Cookie Consent
    To provide the best experiences, we use technologies like cookies to store and/or access device information.
    Functional Always active
    The technical storage or access is strictly necessary for the legitimate purpose of enabling the use of a specific service explicitly requested by the subscriber or user, or for the sole purpose of carrying out the transmission of a communication over an electronic communications network.
    Preferences
    The technical storage or access is necessary for the legitimate purpose of storing preferences that are not requested by the subscriber or user.
    Statistics
    The technical storage or access that is used exclusively for statistical purposes. The technical storage or access that is used exclusively for anonymous statistical purposes. Without a subpoena, voluntary compliance on the part of your Internet Service Provider, or additional records from a third party, information stored or retrieved for this purpose alone cannot usually be used to identify you.
    Marketing
    The technical storage or access is required to create user profiles to send advertising, or to track the user on a website or across several websites for similar marketing purposes.
    Manage options Manage services Manage vendors Read more about these purposes
    View preferences
    {title} {title} {title}