Home Gadgets Setting a fixed amount of memory for SQL Server. Sql server Windows nt
Gadgets

Setting a fixed amount of memory for SQL Server. Sql server Windows nt

Why does SQL Server Consume So Much Memory

According to the company, Microsoft SQL Server is a relational database management system (RDBMS). In relational databases, all SQL Servers use the SQL programming language, which is well-known and widely used. Microsoft created SQL Server’s Transact-SQL implementation, also referred to as T-SQL, used by SQL Server. A number of programming constructs not found in SQL are also included in T-SQL.

Overall, SQL Server is an important component of Microsoft’s data platform, as it provides exciting levels of performance for mission-critical applications through in-memory technologies, faster insight and perspective from any type of data with well-known tools such as Excel, and a platform for the development of on-premises and Cloud-based solutions, as well as a platform for solution management.

SQL Server has been in existence for 32 years at the time of writing. It is a mature product that will almost certainly not meet all of the needs of every development team, but it does cover the vast majority of the requirements that a development team may have for data access.

SQL Server Diagnostic Memory Queries

A significant number of services that we use on a daily basis rely heavily on SQL Server databases. These services include financial institutions, bookmakers, government agencies, the medical industry, and well-known websites.

SQL server features and benefits

High availability is a standard feature for SQL Servers, allowing for high uptime and faster switching. All of this is accomplished without depleting system memory. SQL Server’s database and analytics engines now have more flexibility and ease of use thanks to the addition of in-memory capabilities. Probably the most notable feature is that it integrates seamlessly with the Microsoft Server family of server platforms.

How much memory does the SQL server use?

SQL Server uses about 87.5% of the RAM on my server. Many performance issues have arisen as a result of this, including slowness. When we start looking into this issue, we can find a lot of dead ends. A common solution found on the internet is to increase the SQL Server maximum execution time. A lot of work has gone into this, and it shows. If you hire a developer, you might get the help you need with this.

SQL Server will consume all of the available memory. By default, that number corresponds to the total amount of numeric memory available on your computer. As a result, your perceptions are correct. To put it another way, if you give SQL Server 24 GB of memory, it will make every effort to make the most of that memory. When SQL Server and the operating system are competing for resources, poor performance is an inevitable byproduct.

setting, fixed, amount, memory, server

SQL Server’s buffer pool is limited by the maximum server memory limit configuration set in the server’s configuration (pretty much where it stores the data pages and the procedure cache). The buffer pool is not the only memory worker in SQL Server; there are a number of other memory workers as well (for versions 2008 R2 and later). However, this will always be the application with the highest memory requirements.

The Microsoft SQL Server Database Engine’s buffer pool is limited by the server’s minimum and maximum memory settings.

Server configuration options

By customizing the minimum and maximum server memory choices for a specific SQL Server instance, you can alter the amount of memory (measured in megabytes) that SQL Server Memory Manager manages. The memory requirements of SQL Server can be adjusted dynamically based on the available resources on the system.

To set a fixed amount of memory, follow these steps:

  • Right-click a server in the Object Explorer, and select Properties from the context menu.
  • Select the Memory node from the drop-down menu.
  • Under Server Memory Options, enter the amounts you want for Minimum Server Memory and Maximum Server Memory in the appropriate fields.

If you want SQL Server to be able to change its memory requirements dynamically in response to available system resources, use the default setting. In the default configuration, the minimum server memory is set to 0 megabytes, and the maximum server memory is set to 2147483647 megabytes by default (MB).

Freeing up the server

SQL can be configured to only use a specific amount of RAM if necessary. Otherwise, it will consume the maximum amount of resources. This is the standard procedure after thoroughly investigating the server and confirming that SQL Server consumes 9GB of the server’s total 10GB of RAM capacity. Anyone would limit the amount of memory available for SQL to, say, 6GB in order to avoid crashing the server and to give it some breathing room. over, if you look at the performance, you will notice that it is in compliance.

Optimal consumption

Although the previous solution may be sufficient to get us out of trouble, it is clear that the ideal solution would be to discover a way to determine exactly how much memory each Database (DB) consumes in order to determine how much memory the SQL Server instance requires. Easy. It is only necessary to run a SQL query in order to see it.

Following our observation of total RAM memory, we can add the total of ‘db buffer MB’ and see how much RAM memory SQL has consumed in total. As an example, if we lower the SQL limit to 9GB and run the query, we can see that the databases only consume 3GB of RAM memory, which is significantly less than the previous example.

As a result, the initial hypothesis about SQL Server’s excessive memory consumption has been proven correct. Despite the fact that the databases consume only 3GB of memory, if we limit the instance to 6GB, SQL Server manages to make full use of the available memory. We would be talking about the act of observing one’s own memories.

Real application

The most important aspect of this result is that it can be put to practical use. Given that SQL requires only 3GB of RAM to function properly, is it possible to limit SQL’s RAM usage to 4GB and avoid wasting the entire allocation? Yes.

In this image, we can see the concrete consumption of a database after running a new query. over, if we run this query on a regular basis and keep track of the average, we can determine whether or not the consumption fluctuates.

The importance of monitoring

In situations such as the one we are currently dealing with, the importance of monitoring becomes immediately apparent. With a historical record of SQL Server’s RAM memory consumption, we can calculate a reasonable average and then set limits on how much memory can be allocated.

Monitoring enables us to develop a scale that determines maximum and minimum values, as well as their relationships. Neither the client nor the technician will receive an alarm email if either of these thresholds is exceeded, or if the application consumes more memory than is desired.

Important note

Increasing the maximum server memory for a single SQL Server instance above its recommended value may force the SQL Server instance to compete with other SQL Server instances running on the same server node for memory. In the event that you set your memory limit too low, you may experience significant memory shortages or performance issues. If you set the Maximum Server Memory parameter to a value that is too low, SQL Server may be unable to start properly.

After changing the max server memory parameter, if you are unable to start SQL Server, try starting it with the.f startup parameter and restoring the max server memory parameter to its previous value.

Dataquest

Dataquest teaches through challenging exercises and projects instead of video lectures. It’s the most effective way to learn the skills you need to build your data career.

Setting a fixed amount of memory for SQL Server

There is a frequently asked question among many people who work with SQL Server: “Why does SQL Server use almost all of the memory on the server, even if there is essentially no activity on the databases?” This is a common and logical question if you are not familiar with SQL Server memory management and you have not investigated the question. This tip is intended to explain the default SQL Server memory configurations and how to configure SQL Server to use a fixed amount of RAM.

When a SQL Server instance is running on your machine, you may find that memory usage is perceived as too high. Commonly, most of the memory is used by SQL Server. over, when you increase memory on the machine, but the database load remains the same, it is possible that even after the memory upgrade SQL Server will be the top consumer of memory. The reason is that by default SQL Server dynamically allocates memory during its activity and does not release it until there is a request from Windows.

It is normal for SQL Server, because it tries to keep as much memory as possible for performance purposes. It reserves memory and caches data into memory to decrease access to disk drives and therefore increasing performance. When other processes on the same server require memory, SQL Server releases the needed memory. So, SQL Server can use almost all available memory on the server.

If your server is a dedicated database server there is no problem regarding memory allocation, however sometimes there are many applications running on the database server. over, some applications use memory available at their starting time and may be unable to request additional memory from Windows, if needed. In this case, to permit the other applications to work properly, we can limit SQL Server’s access to memory by setting the “max server memory” option. By default it is set to 2147483647 MB, which allows SQL Server to use approximately all of the server’s memory. We can change this setting to a lower value, according to our requirements. It could be done either by SQL Server Management Studio or a T-SQL script.

Setting “max server memory” using SQL Server Management Studio

At a very high level, let’s monitor memory usage on our machine where SQL Server is running, then set “max server memory” in SQL Server Management Studio (SSMS) and view the changes in memory usage. In our example, the test server has 1 GB of Memory (RAM) and SQL Server 2014 is installed, but not in a running state. Also, there are no other resources consuming memory on our server. By running Task Manager we can monitor memory usage in the Performance tab to get a sense of the memory usage prior to starting SQL Server:

Now we will start SQL Server and monitor memory usage again:

We can see that the amount of used memory has increased:

And the top memory “consumer” is SQL Server:

In SSMS we can run the following query and find the memory used by SQL Server in megabytes:

SELECT (physical_memory_in_use_kb/1024) AS Used_Memory_By_SqlServer_MB FROM sys.dm_os_process_memory

In our server it is 117 MB:

Now let’s do some activity in our instance. We have TestDB database on our instance and TableA in it, which contains 300,000 rows. Let’s select all rows from this table:

SELECT FROM TestDB.dbo.TableA

When the query finishes we will check memory usage again in SQL Server:

SELECT (physical_memory_in_use_kb/1024) AS Used_Memory_By_SqlServer_MB FROM sys.dm_os_process_memory

In this case we can see that 432 MB is used by SQL Server, however there are no active queries on our instance:

And 93% of our server memory is used (mostly by SQL Server):

Set “max server memory” in SQL Server Management Studio

Now we will set the “max server memory” option to limit the memory usage by SQL Server. We can do this by right-clicking on our instance and choosing “Properties”:

After than choose the “Memory” page:

We can see above that “Maximum server memory” is set to 2147483647 MB. This is the default value. We will change it to 200 MB and click “OK”:

By running this query again we can see that memory used by SQL Server decreased to 244 MB:

SELECT (physical_memory_in_use_kb/1024) AS Used_Memory_By_SqlServer_MBFROM sys.dm_os_process_memory

A question arises: why 244 MB, when we set “maximum server memory” for SQL Server to 200 MB? The reason is that our query returns currently used memory, however max server memory controls memory used by the buffer pool, compiled memory, all cache and so on, but it does not control memory for linked server providers other than SQL Server, memory allocated by a non SQL Server DLL, memory for thread stacks, memory heaps, etc. and therefore the result of our query can be a bit more than the value of “max server memory”.

Set “max server memory” in SQL Server using T-SQL

We can set “max server memory” also by using a T-SQL script:

DECLARE @maxMem INT = 2147483647.-Max. memory for SQL Server instance in MB EXEC sp_configure ‘show advanced options’, 1 RECONFIGURE EXEC sp_configure ‘max server memory’, @maxMem RECONFIGURE

This script sets “max server memory” to its default value. 2147483647 MB:

We can check in SSMS that “max server memory” has changed:

Recommendations on setting “max server memory

Microsoft recommends to allow SQL Server to use memory dynamically, however in some situations it is preferable to limit the memory usage by SQL Server. For example, when our server is not a dedicated database server and there are other applications running on the same server which are unable to request memory from Windows, it would be better if we estimate the maximum memory which can be used by SQL Server and set “max server memory” appropriately.

Also, when there are more than one SQL Server instances running on your server, we can set “max server memory” for each of them, considering their load, to control memory usage among these instances. Note that we should consider that the sum of “max server memory” values for all instances will be less than the total physical memory in our server. This ensures that free memory will be available immediately for instances after start up. However if one of instances is not running, the running instances will be unable to use the remaining free memory.

Conclusion

As we can see, SQL Server tends to use all available memory on a server. However in some cases, when we need to limit memory used by SQL Server or properly distribute memory among SQL Server instances we can set the “max server memory” option for each instance. One most important thing which we should consider before managing memory for SQL Server is to correctly estimate the maximum amount of memory which will be needed for each instance.

Next Steps

SQL Server authentication vs. Windows authentication: Which to use and when

Authentication is a critical component of any security strategy. Today, we are going to discuss SQL Server authentication and how it is essential to securing your SQL Server environment, and the role Windows authentication plays.

Establishing a connection

It all starts with a connection. In order to establish a successful database connection, the client or application requires the following information:

  • SQL Server fully-qualified domain name
  • Instance name
  • Port number
  • Credentials (username and password) for authentication

For example, suppose you use online banking. In order to access your account, you are required to enter credentials for authentication purposes. The bank identifies you when you provide valid credentials and allows access to its services upon verification.

Similarly, when logging into SQL Server, users need to specify valid credentials so that SQL Server can authenticate their identity and grant the appropriate access.

SQL Server provides two modes of server authentication:

You can define these authentication methods during the installation of SQL Server, or change them later via a restart. It’s critical for database administrators to understand the differences between these authentication methods and implement them per their organization’s specific requirements.

Let’s dive in further to understand the advantages and disadvantages of both SQL Server and Windows authentication.

An overview of SQL Server authentication

Database administrators create SQL logins and provide appropriate permissions for users to authenticate themselves to SQL Server. Users need to specify the login and password while connecting to SQL Server as shown below.

The user’s credentials are validated through the information stored in the master database. You can enforce the following policies for SQL Server logins.

setting, fixed, amount, memory, server
  • Enforce password policy: The administrators can check this option to implement the Windows password policy for SQL Server logins. It includes specifying password length and complexity.
  • Enforce password expiration: You can enforce the maximum age of a password. The password will be expired and needs to change as defined by the age criteria.
  • User must change password at next login: The administrator assigns a password during SQL login creation. Once the user logs in with their credentials, they need to specify a new password, and the administrators will not be aware of this new password.

Note: All these configurations are at the individual SQL login level. Therefore, if you need to create multiple SQL logins, you must configure each account with the required policy.

We cannot enable only SQL authentication. To enable it, use the mixed authentication option which includes both Windows and SQL authentication.

setting, fixed, amount, memory, server

Disadvantages of SQL Server authentication

There are quite a few limitations and disadvantages of using SQL Server authentication alone.

  • Users need to remember the SQL login credentials and provide them in the connection string each time they connect to SQL Server. If you have multiple SQL Servers, it might be difficult for the user to keep track of the passwords for each instance.
  • SQL Server stores the password in the master database in encrypted (hash) form. Hackers can steal the information by accessing the database. Since these encrypted credentials need to be passed over the network, this can increase the chances of user credentials being stolen.
  • You cannot implement additional (customized) account policies with the SQL Server authentication logins.
  • It increases the task of login management for database administrators. Database administrators do not have a central management console for managing logins across all instances.

Suppose you have 500 SQL instances and a user requires access to all these instances. In this case, it would be a tedious task for the database administrator to connect to each instance and create user logins. Similarly, if a person left the organization, the database administrator needs to find out that individual’s SQL logins and remove them from all these instances. This can be a very time-consuming process.

  • You might get orphan user issues when moving a database to different instances, and it might happen due to a SID mismatch in the master and user database on the new instance.
  • You need to manage the security policies for each SQL login. You cannot define a universal policy for all accounts in your organization. For a large database footprint, it is an arduous task to define the policy for each individual login.

An overview of Windows authentication

In Windows authentication, the user should first authenticate himself within Active Directory. SQL Server authenticates users through the Windows principal token in the OS. With that, SQL Server does not ask for a password for identity validation. Therefore, Windows confirms users’ identities for authentication. SQL Server does not store the credentials in the Windows authentication. The connection using Windows authentication is called a trusted or integrated connection.

Note: Windows authentication is the default authentication method when you install SQL Server.

Disadvantages of Windows authentication. Summary

This blog post outlines the key components of SQL Server authentication and Windows authentication. I hope it helps you understand the differences between these authentication methods to decide which works best for your business and circumstances.

SQL Server authentication can be used on the same machine as SQL Server or on a remote connection. If you work in an Active Directory environment, Windows authentication is recommended to use. If you work in a non-Active Directory environment, you can utilize SQL Server authentication for database connections.

Windows authentication does provide more security and flexibility for managing logins in SQL Server. Therefore, you should use it whenever feasible.

Troubleshooting “Could not obtain information about Windows NT group/user”

This is one of those typical blog posts that I write for my future self, the guy who keeps fixing the same stuff over and over and forgets what he did the next minute.

If you want to query information about a Windows user or group and its access path in SQLServer, you can use the extended stored procedure “xp_logininfo”. Here’s an example:

EXEC xp_logininfo ‘MyDomain\SomeUser’,’all’;

If everything is configured correctly, you will see a list of Windows accounts and the login(s) they are mapped to in SQLServer.

However, in some cases, the command fails with the infamous error message:

Could not obtain information about Windows NT group/user ‘MyDomain\SomeUser’, error code 0x5

This happens every time SQLServer tries to query information about the Windows user from Active Directory and receives an error.

Understanding where the error comes from can be tricky, but it can become easier to troubleshoot when you understand what happens behind the scenes and what are the most likely causes.

The user does not exist

This is very easy to check: does the user exist in Windows? Did you misspell the name?

You can check this from a cmd window, issuing this command:

If you spelled the user correctly, the command will return information about it, like description, password settings, group membership and so on.

If the user name is incorrect and cannot be found in AD, you will get an error message

The user name cannot be found.

Easy peasy: check your spelling and check your AD.

The service account does not have enough privileges to query AD

As I said, SQL Server needs to query AD to retrieve information about the user: if its service account doesn’t have enough privileges, the query will fail.

The most likely cause for this is a misconfiguration of the service account settings in SQL Server. To be more specific, it is very likely that SQL Server is configured to run as a local user who has no access to Active Directory at all. This happens when SQL Server runs as a per-service SID or one of the built-in local accounts (local service or localsystem).

SQL Server DBA Tutorial 143-How to Find out How Much Memory is Allocated to SQL Server Instance

It is very easy to check what account is being used to run SQL Server: all you need to do is query sys.dm_server_services.

SELECT servicename, service_account FROM sys.dm_server_services;

If you see a local account being returned, go ahead and change your service account to a domain account, using the Configuration Manager.

If you still can’t query AD, maybe there is something wrong with the permissions on your AD objects. Try impersonating the SQL Server service account, open a cmd Windows and issue the net user command.

net user SomeUser /domain The request will be processed at a domain controller for domain MyDomain System error 5 has occurred. Access is denied

If you get the “Access is denied” error message, you need to go to your AD and grant read permissions on that user/OU to the service account.

The service account does not have enough privileges to impersonate the Windows user

This was a bit of a surprise for me. In order to retrieve information about the Windows user, SQL Server needs to impersonate it first and then will contact AD impersonating that user.

In order to impersonate a user, SQL Server needs to run under a service account user that has enough privileges to impersonate another user. This privilege is granted through a local policy.

Open the local security policy MMC (secpol.msc) and expand “Local Policies”, “User Rights Assignment”. Find the policy named “Impersonate a client after authentication” and double click it. You can verify whether the service account for SQL Server is granted this privilege, directly or through one of its groups.

Generally speaking, you don’t have to change this, because by default Windows grants this privilege to the “SERVICE” special identity. Any process running as a service is acting as the SERVICE special identity, including SQL Server. If you don’t find it listed here, add it back.

Windows permissions can get tricky at times. I hope that this post helps you (and me!) taming the beast.

Author

Laidred

| Denial of responsibility | Contacts |RSS