Tuning SQL Server 2012 under SharePoint 2013/2016. Part 2

Hello! Today, I am with you again - Lyubov Volkova, system architect of the business solutions development department. In the previous article, we began discussing the topic of tuning SQL servers for working with SharePoint 2013/2016 databases. The material discussed in detail the issues of choosing between a physical and a virtual server, planning the size, placing databases, preparing a disk subsystem, and optimizing data transmission over the network.

Today we continue the story, focusing on the specifics of setting parameters for the SQL server during the installation process, taking into account the subsequent work with the SharePoint databases, as well as the settings for deploying SharePoint and in the process of servicing the corporate portal databases.

The main goal of the article is to help system administrators, database administrators to properly prepare servers, deploy, configure and maintain SQL server, taking into account the best practices for optimizing the performance of corporate SharePoint portals.
')

Installing SQL Server 2012

Function Component Selection Screen

During the installation of the components of the SQL server, on the selection screen of the functional components, it is possible to specify the path to the folders of the file system in which you need to save files associated with these components.

The default paths point to folders located on the C: drive. It is strongly recommended to change this path by installing components on another disk.

SQL Server Instance Configuration

On the configuration page of the instance of SQL Server, you are prompted to enter data about the instance name and the location of files in the file system associated with it.

Using the default instance name is not critical in terms of optimizing SQL server performance. It is strongly recommended to set the value of the paths in the file system for file allocation in accordance with the file allocation settings associated with the SQL components.

Service Accounts

On the server configuration settings page, you need to enter data about service accounts. Although the settings on this page are not related to the performance of the SQL server, they are important for correct security settings. Due to frequent mistakes and issues related to them, their description is included in the section.

It is recommended to set up separate accounts for the SQL Server Agent services and the SQL Database Engine.



Recommended minimum list of accounts:

• Sql_installation is an Active Directory (AD) account, we use to install SQL, which, during installation, must be included in the local administrators group. After installing the account Sql_installation block. It will be needed only during the installation of updates.
• Sql_engine - Active Directory (AD) account, administrator of a separate instance of SQL Server 2012 (for each instance of its own), which is not in the local administrators group. The SQL Server services for the instance being installed (the SQL Server Database Engine service) will run under this account. This account must not be a member of local, domain or enterprise administrators.
• SQL_agent is a SQL Server Agent service account that automates some administrative tasks. The SQL Server Agent performs tasks, monitors SQL Server, and processes warnings. The SQL Server Agent service must be running to automatically start local or multi server administrative tasks. It is recommended that you select an Active Directory domain user account that is not in the local administrators group.
• SQL_browser is a SQL Server browser account that listens for incoming requests for SQL server resources and provides information about the instances installed on this computer. It is recommended that you select an Active Directory domain user account that is not in the local administrators group. If an instance is installed on the server, the name of which differs from the default name or the number of instances is more than one, the inclusion of the service is mandatory.
• SQL_admin is the SQL Server administrator account.

Sorting options

As a rule, during installation of the SQL server, the collation settings are left by default, which does not correspond to the recommended settings for the SharePoint farm. In general, SharePoint will allow you to work with any built-in collations, but for SQL Server 2012 system databases (master and tempdb) it is strongly recommended to use parameters directly adapted for SharePoint:

• The English version of SharePoint 2013 uses Latin1_General_CI_AS_KS_W S;
• The Russian version of SharePoint 2013 uses Cyrillic_General_CI_AS .

Each abbreviation in the name of the sorting parameters has a strictly defined meaning:

• CI , Case Insensitive (case insensitive). “A” and “a” are treated as the same symbol.
• A S, Accent Sensitive (sensitivity to accents, - accent marks). With the characters, “a” and “á” are treated as different characters.
  KS, Kana Sensitive (sensitivity to the characters of the Japanese alphabet). The characters Japanese Hirakana and Katakana, which look the same, are treated as different characters.
• WS , Width Sensitive (data volume sensitivity). A character requiring one byte for storage in a SQL table and the same character requiring two bytes for storage are considered by the system as two different characters.

These collation settings ensure maximum compliance with the uniqueness check conditions in Windows, as described here .

After installing the SQL Server, the collation settings for the instance cannot be changed. In this connection, it is recommended to set the values ​​recommended for SharePoint on the configuration page of the server settings of the installation wizard.



To set the parameter values, the following values ​​must be set on the sorting parameters selection page:



When creating any SharePoint database using the GUI or the command line, the Latin1_General_CI_AS_KS_WS (for the English version) or Cyrillic_General_CI_AS (for the Russian version) sorting parameters are automatically configured for them.

SQL Server Kernel Configuration

Server settings

On the server configuration settings page, you need to enter information about the authentication mode of SQL Server and the administrator account. These settings do not affect server performance, but are important in terms of security settings.



It is strongly recommended that you select a mixed authentication mode that allows users to connect using Windows authentication or SQL Server. On the server configuration settings input page, you must enter the data for the sa system account password, and also select the SQL administrator from among the Active Directory accounts. The sa account is always present as the login to the kernel component of the SQL server and is a member of the sysadmin fixed server role .



If the SQL Server kernel component is installed using only Windows authentication (that is, SQL Server authentication is not enabled), the sa login will still be present, but will be disabled.

Default database file location

On the default database file location setup page, you need to specify the location of the files for hosting the data files, SQL server database transaction logs and backup copies. When preparing an instance of a SQL server for installation and then storing SharePoint data on this page, it is recommended to specify the location for:

1. Tempdb system database data files;
2. The transaction log of the tempdb system database;
3. SharePoint content database data files;
4. SharePoint content database transaction logs;
5. Backup SharePoint database.

A general recommendation is to place all the listed file categories on different disks:

Configuration after installing SQL Server

After the installation of the SQL server is completed, you need to perform a number of important settings before you start the installation of SharePoint Server 2013. If the SharePoint installation has already been completed, you can also make changes in order to improve the performance of the portal.

In order to optimize SharePoint performance, it is recommended to configure the following SQL server parameters:

• Set the maximum and minimum memory, the fill factor for the index, the maximum level of parallelism for the SQL server;
• Set paths for saving default data files, transaction logs and default backups;
• Set compression during backup and page check during recovery;
• For each of the SharePoint databases and tempdb, set the initial size of data files and transaction logs, the parameters for automatically increasing the size of data files and logs
• Configure automatic statistics creation and update.
• Configure instant file initialization.
• Configure the in-memory page lock options

Express audit of use of the RAM

By default, the SQL server is configured to use up to 2TB of RAM. This means that it can use all the available RAM on the server, leaving nothing for the operating system and other applications. This competition for memory access between the operating system, applications, and the database server usually adversely affects the performance of SharePoint.

To solve this problem, limit the maximum amount of RAM allocated for use by the SQL server.
The script below will allow you to get express information about the current state and status of the use of RAM:

SELECT total_physical_memory_kb , available_physical_memory_kb , total_page_file_kb, available_page_file_kb , system_memory_state_desc FROM sys.dm_os_sys_memory WITH (NOLOCK) OPTION (RECOMPILE); 

The best result indicating that the system is not experiencing problems with RAM is getting the value “Available physical memory is high” in the “system_memory_state_desc” field. Obtaining the value “Available physical memory is low” will indicate the opposite.

Example result:



You can get information about the current server settings related to setting up the use of RAM using the following script:

 SELECT name, value, minimum, maximum, value_in_use FROM sys.configurations WHERE name IN ( 'min server memory (MB)' ,'max server memory (MB)' ) 

Example result:

Maximum memory capacity

To calculate the maximum amount of memory, use the following formula:

SQL Maximum RAM Size = TotalPhyMem - (NumOfSQLThreads * ThreadStackSize) - (1GB * ROUND (NumOfCores / 4)) - RAMOSReserved - RAMForOtherApps, where:

• TotalPhyMem - total physical size of RAM on the server.
• NumOfCore s - number of processor cores.
• NumOfSQLThreads - the number of threads that are used on the server to process database requests. With the number of kernels up to 4, the value of NumOfSQLThreads is always constant and equal to 256. With the number of kernels above 4, the calculation is performed according to the formula: NumOfSQLThreads = 256 + (NumOfCores- 4) * 8.
• ThreadStackSize = 2Mb for x64 servers. For IA64 ThreadStackSize servers = 4Mb.
• RAMOSReserved - RAM for the operating system. 20% for servers with TotalPhyMem not more than 15 GB and 12.5% ​​for a larger volume.
• RAMForOtherApps - RAM for other SQL server instances and applications;

You can get information about the processors and the amount of physical memory on the server using the following script:

 SELECT cpu_count AS [Logical CPU Count] , hyperthread_ratio AS [Hyperthread Ratio] , cpu_count / hyperthread_ratio AS [Physical CPU Count] , osi.physical_memory_kb / 1024 AS [Physical Memory (MB)] , sqlserver_start_time FROM sys.dm_os_sys_info as osi; 

An example of the results:



Let us give an example of calculating the maximum amount of RAM for a SQL server. Baseline data for performing calculations:

• TotalPhyMem = 8191 MB;
• NumOfCores = 4;
• X64 server;
• RAMForOtherApps = 2000 MB.

SQL maximum size of RAM = ROUNDDOWN (8191 MB - (256 * 2 MB) - (1024 MB * ROUNDUP (4/4)) - 0.2 * 8191 MB - 2000 MB) = ROUNDDOWN (8191 MB - 512 MB - 1024 MB - 1638.2 Mb - 2000 Mb) = 3017 Mb.

To automate the execution of calculations, you can use SQL MAX MEMORY CALCULATOR .
Starting with SQL 2008 R2, if there is only one instance of SQL on the server, there is no need to manually set the maximum amount of allocated memory. This value is calculated automatically by the Microsoft SQL Server memory management component based on data on the current use of RAM by the operating system and other applications and dynamically changes in the event of a decrease / increase in the load on this subsystem.

Minimum amount of memory

Setting the value of the minimum allocated RAM for the SQL server ensures that when the system calculates the allocated memory for the needs of the operating system and other applications for the SQL server, at least the specified amount of RAM will be reserved. The value “0” by default allows situations in which the minimum possible number of resources will be allocated to the needs of the database server, which can adversely affect the performance of SharePoint.

Note that setting a non-zero value for the “Min Server Memory” parameter does not mean that SQL will automatically receive all of the specified amount of RAM and will constantly use it. Memory allocation will always depend on the current load. Current figures may differ from the specified parameter in a smaller or in a big way.
The recommended values ​​for the Min Server Memory parameter are as follows:

• SharePoint is mainly used for collaboration, content is being actively edited.
• 60% of the value of the “Max Server Memory” parameter in case the corporate SharePoint portal is mainly used for searching and viewing content.
• In other cases, the value is set between 25% and 60% in proportion to the percentage ratio between reading / changing data on the corporate portal.

For this example, the value of the Min Server Memory parameter should be set in the range from 754 MB to 1810 MB.

Index fill factor

The fill factor (the “fill factor” parameter) is used to fine tune the storage and index performance. When you create or rebuild an index, the fill factor displays the percentage of space occupied by each page in the final level, which allows you to reserve space for free space for future expansion. For example, if you specify a value of 80 for the fill factor, 20 percent of the disk space occupied by it will be reserved on each page of the final level. This disk space will be used to expand the index when new data is added to the base table. Empty space is reserved not at the end of the index, but between the rows of the index.

The fill factor is a percentage from 1 to 100; The default value on the server is 0, which means complete filling of the pages of the final level.

For SharePoint, 80 is best for supporting database growth and reducing index fragmentation.



The following script will allow you to get information from the current default index fill factor settings at the SQL server level:

 SELECT name,value,minimum, maximum,value_in_use FROM sys.configurations WHERE name IN ( 'fill factor (%)' ) 

Example result:

Maximum degree of parallelism

In SharePoint 2010, setting the value “1” was optional, but for SharePoint 2013, a value other than 1 would prevent the launch of the SharePoint farm configuration wizard. Setting the specified value for the degree of parallelism ensures that the SQL server that hosts the SharePoint databases for each query is processed by only one single process. Any other value may cause a less optimal execution plan to be selected for the query and may degrade the overall performance of SharePoint Server 2013.



Below is a script that allows you to get information about the current settings of the SQL server:

 SELECT name,value,minimum, maximum,value_in_use FROM sys.configurations WHERE name IN ( 'max degree of parallelism' ) 

Example result:

Default file location and compression during backup

File locations

It is highly recommended that you store transaction log files, data files, and database backup files on different disks.
The current settings for the SQL server instance can be found out using the following script:

 --     SQL: --  ,      Declare @DataDir nvarchar(4000), @LogDir nvarchar(4000), @BakDir nvarchar(4000), @Instance sysname Set @Instance = IsNull('\' + Cast(ServerProperty('InstanceName') as sysname), '') Exec xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @DataDir output, 'no_output' Exec xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @LogDir output, 'no_output' Exec xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @BakDir output, 'no_output' Select Data = @DataDir, Logs = @LogDir, BAK = @BakDir 

Example result:



If you change the default location of files, you must restart the SQL Server service associated with the instance whose settings have been changed.

Compression during backup

Compressing backups allows you to speed up backups in SharePoint. It is available in SQL Server Standard Edition and Enterprise Edition. By setting the compression option in the backup script or by setting up SQL Server for the default compression, you can significantly reduce the size of database backups and logs delivered.
Below is a script to get data about the current compression settings during backup:

 SELECT name,value,minimum, maximum,value_in_use FROM sys.configurations WHERE name IN ( 'backup compression default' ) 

Example result:



Changing settings related to the default location of files and compression during backup can be performed through the user interface by opening the server settings.

Setting the model database parameters

The model database template is used to create all databases in the SQL server instance. The settings and content of this database are copied to all new user databases when they are created.

Even before installing SharePoint 2013, you need to prepare the model database to ensure that most parameters are automatically copied and to avoid subsequent manual configuration for each individual database after it is created.
The following settings are recommended for the model database before deploying SharePoint:

• Page check during restoration (Page Verify), value “CHECKSUM”;
• Automatic creation of statistics (Auto Create Statistics), the value is “False”;
• Auto Update Statistics (Auto Update Statistics), False;
• Automatic asynchronous statistics update (Auto Update Statistics Asynchronously), value “False”;
• Automatic database compression (Auto Shrink), value "False".

By default, the SQL system database settings do not match the recommended ones:



If you have already installed SharePoint, you need to make changes to the parameters of all databases whose settings differ from those recommended.
The following script allows you to get information about the current database configuration (using the example of model and two SharePoint databases):

 SELECT name , is_auto_create_stats_on , is_auto_update_stats_on , is_auto_update_stats_async_on , is_auto_shrink_on ,page_verify_option_desc FROM sys.databases WHERE name IN ( --   ,      'model' ,'WSS_Content' ,'SharePoint_Config' ) 

Example result:

Create / update statistics

Automatic creation / updating of statistics for SharePoint Server is not supported, therefore it should be disabled for all databases. You must set the value to "False" for the "Auto Create Statistics", "Auto Update Statistics", and "Auto Update Statistics Asynchronously".

SharePoint Server configures the required settings when preparing and updating system databases, content databases, and service applications. When manual creation of statistics is enabled in the SharePoint database manually, the query execution plan can significantly change.

We recommend daily updating the SharePoint content database statistics using the FULLSCAN option on SQL Server. Although SharePoint has a system timer job for updating statistics, it is highly recommended that you set a scheduled maintenance schedule on SQL Server so that database statistics are updated daily. More information about setting up a maintenance plan and updating statistics can be found here .

Check pages during recovery

It is strongly recommended that you set the “CHECKSUM” value for the page verification parameter during restoration (Page Verify), at least for the SharePoint system databases and content databases. In this case, the SQL server calculates the checksum for the content of the entire page and saves this value in its header when writing to disk. When reading a data page from a disk, the checksum of the data is recalculated and the obtained value is compared with the one recorded in the header. This provides continuous support for high data integrity.

Automatic database compression

Care should be taken to ensure that no compression is enabled in any way. Compression can be used to reduce the size of a data file or transaction log, but it is a very coarse, resource-intensive process that causes extensive logical fragmentation of browsing in data files and leads to poor performance. Manual compression of individual data files and log files may be acceptable under special circumstances.

Automatic compression is especially harmful because it runs every 30 minutes in the background and tries to compress databases for which the automatic compression option is set. This process is not entirely predictable in that it only compresses databases with more than 25% free space. Automatic compression uses a lot of resources and causes degradation of performance, so that it is undesirable under any circumstances. It should always be turned off.

Configuring tempdb database settings

The tempdb system database is a global resource accessible to all users connected to this instance of SQL Server, which stores the following objects:

• explicitly created temporary objects, such as global or local temporary tables, temporary stored procedures, table variables, and cursors;
• Internal objects created by the SQL Server Database Engine, for example, work tables that store intermediate results of buffers or sorts;
• row versions generated by data modification transactions in a database that uses snapshot isolation transactions with fixed reads and snapshot isolation transactions;
• row versions created by data modification transactions for functions such as network index operations, MARS mode functions, and AFTER triggers.

By default, the tempdb database is configured to work in simple recovery mode.

Number, initial size and automatic growth of files

The tempdb database is one of the most intensively used databases and should always be located on the fastest disks. In case several data files are created for this database, SQL-server provides simultaneous recording of data in each of them, thereby increasing the performance of the specified operation.
A general recommendation is to create one file per processor core. Microsoft also talks about the permissibility of creating one additional data file per 2/4 cores.

The size of the tempdb database files can have a significant impact on the performance of the system as a whole. If its size is too small, the system will often perform automatic database increment operations, initiating a regular additional load.

An example of logging frequent events of automatic increment of a content database file (a similar situation is possible for tempdb):



In order to avoid frequent increments, it is recommended that tempdb be set to an acceptable initial value for the size of the database files and the size of the automatic increment.

The size of each of the files must match, and the total size of all the original sizes of the data files must be adjusted to 10-25% of the size of the largest content database. Recommendations from Microsoft for the size of automatic file growth - 10% of the size of the data file.

The size of the transaction log file is 50% of the original size of the data file. Automatic growth of the transaction log file - 50% of its original size.

Example:

• memory - 8GB;
• 4-core processors;
• 95 GB content base.

The initial size of the transaction log file is about 25% of the total initial size of all data files, therefore:

• the number of files is 4 pieces, the total size is 24 GB, that is, each file is 6 GB;
• automatic growth - 614 MB;
• The initial size of the transaction log file is 3 GB;
• automatically increase log files - 1.5 GB.

Important: It is strongly recommended that each of the tempdb database data files be set to the same initial size and auto increment parameters. These parameters will allow the SQL server to more effectively manage the proportionality of filling files with data.

Page check

You must make sure that the CHECKSU M value is set for Page Verification .

Additional recommendations for configuring tempdb

Here are some additional recommendations for tempdb used on the instance of SQL Server that stores SharePoint databases:

• Avoid compressing the tempdb database, perform this operation only if you are absolutely sure of its necessity, and the amount of free space is about 50%;
• the collation must match the settings of this parameter for the SQL server;
• Do not change the database owner from sa to another;
• do not delete the tempdb database;
• do not delete the guest account from the list of database users;
  
  
  
  
• Do not replace the Simple recovery mode with any other;
• make sure tempdb uses RAID 1, RAID 1 + 0, or RAID 5 for storing data files and log files in order to prevent SQL server from crashing due to disk issues. It must be remembered that in the event of a failure in the tempdb database, the entire instance of the SQL server stops its work.

More information on configuring tempdb can be found here .

Individual settings for SharePoint databases

It is important to know that SharePoint does not copy all the model database settings when creating content or service application databases.

In addition, the values ​​of the following parameters are configured on the basis of data on the features of the use of a particular database and they must be insisted individually after creation:

• The initial size of the data files and transaction log files;
• Automatic increment size for SharePoint database files;
• Automatic statistics creation

Initial size of data files and transaction logs

For each of the SharePoint databases, follow these guidelines:

1. Estimate the expected size of the database given the growth over the next year.
2. As the initial total size of data files, set 25% of the value obtained in item 1.
3. as the initial size of the transaction log file, set 25% of the value in step 2.

Below is a table with typical sizes of SharePoint database sizes and comments on the expected growth in their sizes.

Database	General size information	Growth rates	Growth expectations
Central Administration Content Database		SharePoint PowerPivot, Excel, PowerPivot, , . PowerPivot SharePoint 2013 , 365 , . - ( , , .)	<1
		, ,	<1Gb

SharePoint Server

				/
App Management Database
			,
-
-		Average
- SharePoint
Power Pivot Database			PowerPivot (WSS_Content).
PerformancePoint Services
		Average	, , , ,	/
	Average	Big
	Average	Average
	Average	Big	, , 1 . — 1
Secure Store			, , . ,	/
-		Big	,
	Big		, , , -, , , ,
		Big	- . . ,
	Average	Big	,	/
			,
Word Automation				,

SharePoint

– 50% .

, SharePoint :

• (Auto Create Statistics), «False»;
• (Auto Update Statistics), «False»;
• (Auto Update Statistics Asynchronously), «False»;
• (Auto Shrink), «False».

SQL Server . :

1. .
2. , .
3. ( ).
4. .

, . , . .

, SQL- . Active Directory:

1. , SQL-:
   
   • 
     
     
   • SQL-.
     
     
   
   
2. (secpol.msc).
   
3. , .
   
4. .
   
5. , .1.
   
6. .
   
   
   
   
7. SQL Server .
   

64- SQL AWE – « » SQL-. – AWE .

, , SQL Server . SQL Server , , SQL Server.

PowerShell. , .

Set-ExecutionPolicy -ExecutionPolicy RemoteSigned

:

Set-NetFirewallProfile -DefaultInboundAction Block -DefaultOutboundAction Allow -NotifyOnListen True -AllowUnicastResponseToMulticast True

Windows Database Engine

New-NetFirewallRule -DisplayName «SQL Server» -Direction Inbound –Protocol TCP –LocalPort 1433 -Action allow



New-NetFirewallRule -DisplayName «SQL Admin Connection» -Direction Inbound –Protocol TCP –LocalPort 1434 -Action allow



New-NetFirewallRule -DisplayName «SQL Database Management» -Direction Inbound –Protocol UDP –LocalPort 1434 -Action allow



New-NetFirewallRule -DisplayName «SQL Service Broker» -Direction Inbound –Protocol TCP –LocalPort 4022 -Action allow



New-NetFirewallRule -DisplayName «SQL Debugger/RPC» -Direction Inbound –Protocol TCP –LocalPort 135 -Action allow



SharePoint SQL, . , SQL Server ( | | TCP/IP| ).



New-NetFirewallRule -DisplayName «SQL NamePort» -Direction Inbound –Protocol TCP –LocalPort -Action allow

New-NetFirewallRule -DisplayName «SQLSERVR» -program " C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\Sqlservr.exe" -direction Inbound -Action Allow

Windows Analysis Services

New-NetFirewallRule -DisplayName «SQL Analysis Services» -Direction Inbound –Protocol TCP –LocalPort 2383 -Action allow

New-NetFirewallRule -DisplayName «SQL Browser» -Direction Inbound –Protocol TCP –LocalPort 2382 -Action allow

New-NetFirewallRule -DisplayName «HTTP» -Direction Inbound –Protocol TCP –LocalPort 80 -Action allow

New-NetFirewallRule -DisplayName «SSL» -Direction Inbound –Protocol TCP –LocalPort 443 -Action allow

New-NetFirewallRule -DisplayName «HTTP» -Direction Inbound –Protocol TCP –LocalPort 80 -Action allow

New-NetFirewallRule -DisplayName «SSL» -Direction Inbound –Protocol TCP –LocalPort 443 -Action allow

, SQL .

SQL

SQL Server, :

1. SQL Server Native Client SQL, , , .
2. . .
3. IP-. .
4. , .
   
5. DNS- A, IP- SQL-.

SharePoint

, SharePoint Server.

. ( > > > > ).

. 1,5. : 8192 MB RAM 1,5 = 12288 MB. — .

( -> > > > > ).

. .

> > .

1117

TraceFlags 1117 . , .

SQL Server > SQL Server > SQL Server < > > > > , «;-T1117».



SQL Server 2016 AUTOGROW_SINGLE_FILE AUTOGROW_ALL_FILES , 1117 .

, !