Datafly.pro

Concernant la mémoire maximale, il est utile de configurer ce paramètre pour éviter que SQL Server lutte avec l'OS pour sa consommation de mémoire. S'il s'agit d'un serveur dédié à SQL Server, la règle suivante peut s'appliquer :

• Réserver 1 Go pour l'OS
• Y ajouter 1 Go pour chaque 4 Go de RAM installée entre 4 et 16 Go
• Y ajouter 1 Go pour chaque 8 Go de RAM installée à partir de 16 Go
• Configurer max server memory avec la mémoire qui reste.

Le script suivant fait le calcul et applique la modification :
 

-- configurer max server memory
DECLARE @memOsBase DECIMAL(9, 2)
      , @memOs4_16GB DECIMAL(9, 2)
      , @memOsOver_16GB DECIMAL(9, 2)
      , @memOsTot DECIMAL(9, 2)
      , @memForSql DECIMAL(9, 2)
      , @sql NVARCHAR(MAX)
      , @memInMachine DECIMAL(9, 2);
-- Get current machine memory configuration
SELECT @memInMachine = physical_memory_kb / (1024 * 1024.)
FROM sys.dm_os_sys_info;
SET @memOsBase = 1;
SET @memOs4_16GB = CASE
                       WHEN @memInMachine <= 4 THEN
                           0
                       WHEN @memInMachine > 4
                            AND @memInMachine <= 16 THEN
(@memInMachine - 4) / 4
                       WHEN @memInMachine >= 16 THEN
                           3
                   END;
SET @memOsOver_16GB = CASE
                          WHEN @memInMachine <= 16 THEN
                              0
                          ELSE
(@memInMachine - 16) / 8
                      END;
SET @memOsTot = @memOsBase + @memOs4_16GB + @memOsOver_16GB;
SET @memForSql = @memInMachine - @memOsTot;

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
SET @sql
    = N'EXEC sp_configure ''max server memory'',' + CAST(CAST(@memForSql * 1024 AS INT) AS VARCHAR(10))
      + N'; RECONFIGURE;';
PRINT @sql;
EXEC (@sql);

Bien sûr, il faut adapter cette configuration si d'autres processus tournent sur le serveur.

N'oublions pas de configurer la compression et le checksum des sauvegardes, qui améliorent leur performance, leur volumétrie et leur sécurité :

EXEC sys.sp_configure N'backup compression default', N'1'
GO
EXEC sys.sp_configure N'backup checksum default', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

Concernant le parallélisme, c'est plus délicat : cela dépend du type de travail exécuté sur notre serveur SQL. Une règle de départ pourrait être :

• Positionner "max degree of parallelism" (nombre de processeurs pour le parallélisme) à la moitié des CPU
• Augmenter "cost threshold for parallelism" (seuil de coût) à 30
• On peut aussi activer "Optimize for adhoc workload" (optimiser pour les charges de travail adhoc permet d'éviter de saturer le cache de plans avec des requêtes qui ne sont exécutées qu'une seule fois)

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
DECLARE @cpu_count INT, @sql NVARCHAR(MAX);
-- Get current machine cpu configuration
SELECT @cpu_count=cpu_count
FROM sys.dm_os_sys_info;
SET @sql=N'EXEC sys.sp_configure ''max degree of parallelism'','
         +CAST(CASE WHEN @cpu_count<=3 THEN 2 ELSE FLOOR(@cpu_count / 2)END AS VARCHAR(3))+N'; RECONFIGURE;'+CHAR(10);
SET @sql=@sql+N'EXEC sys.sp_configure ''cost threshold for parallelism'',30; RECONFIGURE;'+CHAR(10);
SET @sql=@sql+N'EXEC sys.sp_configure ''optimize for ad hoc workloads'',1; RECONFIGURE;';
PRINT @sql
EXEC (@sql)