Description
In Instance Configuration Settings that describes about the operating system issues that might run with the SQL Server instance. And the operating system configuration setting that are related to SQL Server that has to be handled by the administrator. And these has to be performed before the installing the SQL Server. These Instance Configuration Settings are really important for the performance and scalability. At the same time it provides a best practice for performance and scalability that are related to the operating system and the instance level configuration settings.
Description
There are several aspects that describes the operating system issues and features, such as:
- Operating system issues will not used in windows server 2012 or any new version.
- And new server operating system are much better for SQL Server performance.
- New version will have Microsoft support for a longer period of time.
- Operating system will have better clustering, higher license limits, less expensive licensing cost using windows server 2012 standards editions.
- Server messages block will gives much better network performance.
Conceptual
figure
- Application - Is a program design for performing a task.
- Services - Exchange the values that occurred.
- Version - It is unique set of number assigned to a specific release of a software program.
- Instance - Is specific realization of an object.
Description
Negotiated version of server message block describes on what version the operating system has been connected to the operating system on either side.
| OS | WS 2016 | WS 20122 R2 | WS 2012 | WS 2008 R2 | WS 2008 |
|---|---|---|---|---|---|
| WS 2016 | SMB 3.1.1 | SMB 3.0.2 | SMB 3.0 | SMB 2.1 | SMB 2.0.2 |
| WS 2012 R2 | SMB 3.02 | SMB 3.0.2 | SMB 3.0 | SMB 2.1 | SMB 2.0.2 |
| WS 2012 | SMB 3.0 | SMB 3.0 | SMB 3.0 | SMB 2.1 | SMB 2.0.2 |
| WS 2008 R2 | SMB 2.1 | SMB 2.1 | SMB 2.1 | SMB 2.1 | SMB 2.02 |
| WS 2008 | SMB 2.0.2 | SMB 2.0.2 | SMB 2.0.2 | SMB 2.0.2 | SMB 2.0.2 |
Description
Lock pages in memory is an operating system that grants to the SQL Server services so that the database engine has the authorization. And these operating system will not allowed to page out memory when it's under immense pressure. And the server memory set to an appropriate value so that the operating system is not under memory pressure. And the local group policy editor and navigate down the hierarchy until the user has the permission to access the assignment page and then find lock page in the memory so that it can grant from the SQL Server service account.
Description
Granting OS Right to SQL Server means granting all the access permission to the user for performing the accurate number of tasks.
Following are the steps that describe the how to grant the permissions.
Step - 1
In the search box type gpedit.msc.
Step - 2
After opening gpedit.msc the below Local Group Policy Editor will get displayed.
Step - 3
Expand Windows Settings.
Step - 4
Expand Security Settings.
Step - 5
Expand Local Policies.
Step - 6
Double click on User Right Assignments.
Step - 7
In Local Group Policy Editor,double click on Lock pages in memory.
Step - 8
Lock pages in memory properties get displayed under it select Add User or Group.
Step - 9
Step - 10
Under select user or groups,enter the name and check the name wheather it is present in the folder or not.Click on Ok button.
Step - 11
Check the permission as been granted in the Local Group Policy Editor.
Description
There are few really key global trace flags that enabled every instance of SQL Server.
It will prevents the SQL Server from writing out a message to the SQL Server error log every single time that user have a successful database backup, and this occurs for full backups, differential backups and log backups for all the databases. so all that information goes into the SQL Server error log by default, and it's really not useful. And if suppose the backup failed then it's gets a message for enabling the trace flag and at the same time the user won't get any message if the backup is succeeded. Even if the user is having this trace flag is turned on, then all the information will be send through MSDB for each and every backups.
In trace flag 2371 it lower down the threshold for automatic statistics updates and too much lower value will make the table get larger, because by default the user don't get an automatic statistics update until 20% of the rows in a table have been updated, so the automatic statistics updates doesn't happen frequently enough on a large tables. And with this effect, the table will get larger then the threshold for the number of rows that have to be updated and automatically get turned on by default in SQL Server 2016.
In trace flag 1118 it will alleviates bit map contentions in tempdb when lots of objects are being created and destroyed, so this basically makes the tempdb to work better. And these are very really important to have this in SQL Server 2000, but even for newer versions of SQL Server will be helpful. In some cases the older versions of SQL Server it just enable the trace flags.
In trace flag 3023 it just enables the backup of the check sums and it's don't use it in the backup command. If the user run a backup, it will have checksum set up for the page verification of the database backup. And this trace flag write out a checksum for the entire backup problems and then backing up the SQL Server 2014 will adds an SP configuration settings. The SQL configurations manager will add these startup parameters so that every time SQL Server will be effects.
- Trace Flag 3226
- Trace Flag 3271
- Trace Flag 1118
- Trace Flag 3023
Key Points
- Instance Configuration Settings - Describes the operating system issues that run on SQL Server instances.
- Operating system issues - Describes the features and issues of the operating system.
- Negotiated version of SMB - Describes the versions of the operating system that have been connected.
- Lock pages in memory - Is another type of operating system that grants the SQL Server Services in the authorization.