Configuration optimale de Windows Server pour SQL Server

Mode d’alimentation de Windows

En modifiant le mode d’alimentation de Windows Server à « high performances »,  il est possible d’obtenir un gain jusqu’à 40% sur SQL Server  

Sources :

https://www.mssqltips.com/sqlservertip/3396/windows-server-power-management-effect-on-sql-server/

https://support.microsoft.com/en-us/help/2207548/slow-performance-on-windows-server-when-using-the-balanced-power-plan

LOCKED PAGES

A partir de SQL Server 2012, il semble assez facile de configurer les « Locked Pages »    Il suffit que l’utilisateur du service ait le droit « Lock pages in memory »    

Sources :

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/enable-the-lock-pages-in-memory-option-windows?view=sql-server-2017

https://support.microsoft.com/en-us/help/2659143/how-to-enable-the-locked-pages-feature-in-sql-server-2012

https://blog.sqlauthority.com/2019/09/16/sql-server-enable-lock-pages-in-memory-lpim/

Optmised SQL Server Database Settings

Voilà différents scripts pour mettre en place les valeurs les plus optimisées possibles

Script d’optimisation au niveau de la base, Ce script est à exécuter sur chacune des bases

https://github.com/alexiscomte/SQL/blob/master/OPTIMIZE_DB.sql

Ce second script est valable pour l’ensemble des bases sur la même instance

https://github.com/alexiscomte/SQL/blob/master/OPTIMIZE_SRV.sql

Le script suivant mettra à jour les Traces les plus adaptées

https://github.com/alexiscomte/SQL/blob/master/OPTIMISE_TRACE.sql

Le script suivant va vérifier le niveau de compatibilité de la base et le comparer avec celui du serveur.

https://github.com/alexiscomte/SQL/blob/master/OPTIMIZE_COMPATIBILITY_LEVEL.sql

En supprimant la condition « name = DBNAME() » dans ce script, on peut faire l’opération sur l’ensemble des bases d’une instance. Attention pour ce dernier script, changer le mode de compatibilité peut changer les plans d’exécution des requêtes et donc il est possible que certaines requêtes soient ralenties. Autre effet de bord, certaines fonctions peuvent devenir obsolètes d’une version à une autre de SQL Server, il conviendra de vérifier les effets de bord.

Source :

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-2017#differences-between-compatibility-level-130-and-level-140

Détection des objets SQL Invalides

Après modifications de structures de tables, le script suivant permettra d’identifier les impacts sur les objets devenus invalides.

DECLARE @Name NVARCHAR(1000);

DECLARE ObjectCursor CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR SELECT QUOTENAME(SCHEMA_NAME(o.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(o.object_id))
	FROM sys.objects AS o
	WHERE type IN('FN', 'P', 'TF', 'TR', 'V ')
		  AND ISNULL(OBJECTPROPERTY(o.object_id, 'IsSchemaBound'), 0) = 0;

OPEN ObjectCursor;

FETCH NEXT FROM ObjectCursor INTO @Name;

WHILE @@FETCH_STATUS = 0
BEGIN
	BEGIN TRY
		EXEC sp_refreshsqlmodule @Name;
	END TRY
	BEGIN CATCH
		PRINT @Name + ' : ' + ERROR_MESSAGE();
	END CATCH;

	FETCH NEXT FROM ObjectCursor INTO @Name;
END;

CLOSE ObjectCursor;
DEALLOCATE ObjectCursor;

SET vs SELECT

SET est la norme ANSI pour l’affectation des variables.

SET ne peut affecter qu’une variable à la fois alors que SELECT peut en effectuer plusieurs.

Pour une requête qui renvoie plusieurs valeurs / lignes, SET génère une erreur.

DECLARE @TAB TABLE(libe VARCHAR(100));
INSERT INTO @tab(libe) VALUES('Test'), ('TOTO'), ('Titi'), ('tutu');

DECLARE @SET VARCHAR(100)
SET @SET = ( SELECT LIBE FROM @TAB )

SELECT affectera l’une des valeurs à la variable et masquera le fait que plusieurs valeurs ont été renvoyées.

DECLARE @TAB TABLE(libe VARCHAR(100));
INSERT INTO @tab(libe) VALUES('Test'), ('TOTO'), ('Titi'), ('tutu');
DECLARE @SELECT VARCHAR(100)
SELECT @SELECT= LIBE FROM @TAB 
SELECT @SELECT

Lors de l’affectation à partir d’une requête s’il n’y a pas de valeur renvoyée, SET affectera NULL, alors que SELECT ne fera pas du tout l’affectation (donc la variable ne sera pas modifiée par rapport à sa valeur précédente)

DECLARE @SELECT VARCHAR(100) = 'SELECT'
DECLARE @SET VARCHAR(100) = 'SET'
SELECT @SELECT= 'NEW' WHERE 1=2
SET @SET  = (SELECT 'NEW' WHERE 1=2 )
SELECT @SELECT AS [SELECT] , @SET AS [SET]

Il n’y a pas de différences de performance entre SET et SELECT.

STRING_AGG : Nouvelle fonction introduite en SQL2017

STRING_AGG concatène les valeurs des expressions de chaîne et place les valeurs de séparateur entre elles. Le séparateur n’est pas ajouté à la fin de la chaîne.

Cette fonction est équivalente au stuff utilisé dans les précédentes versions de SQL Server

DECLARE @TAB TABLE(libe VARCHAR(100));
INSERT INTO @tab(libe) VALUES('Test'), ('TOTO'), ('Titi'), ('tutu');

SELECT ISNULL(STRING_AGG(LIBE, '/'), '') FROM @TAB;
SELECT STUFF((SELECT '/' + LIBE FROM @TAB FOR XML PATH('') ), 1, 1, ''); 

Autre Méthode :

DECLARE @TAB TABLE(libe VARCHAR(100));
INSERT INTO @tab(libe) VALUES('Test'), ('TOTO'), ('Titi'), ('tutu');

Declare @CONCAT VARCHAR(MAX) = ''
SELECT @CONCAT = @CONCAT + CASE WHEN @CONCAT != '' THEN '/' ELSE '' END + LIBE FROM @TAB
SELECT @CONCAT

Encodage des Fichiers SQL

Attention à l’encodage des fichiers « .sql » , notamment s’ils sont ensuite exécutés par SqlCmd, un mauvais encodage peut avoir pour effet de faire perdre des informations sur les caractères accentués.

Voilà ci dessous un exemple avec deux fichiers, l’un encodé en UTF8 et l’autre en UTF8-BOM

https://github.com/alexiscomte/SQL/blob/master/testUTF8BOM.sql

https://github.com/alexiscomte/SQL/blob/master/testUTF8.sql

Lorsqu’on les exécute avec SQLCMD, ce dernier les convertit en UTF16 et cette conversion se passe mal lorsque le format est UTF8 et pas UTF8-BOM.

Exemple :

sqlcmd -S SQLSRV  -i test.sql -d MaBase 
sqlcmd -S SQLSRV  -i testWITHBOM.sql -d Mabase

UNION & UNION ALL

UNION va se comporter comme un DISTINCT UNION ALL va conserver les enregistrements des différents select.

Exemple : 

declare @t1 table ( ids  varchar(50) )
declare @t2 table ( ids  varchar(50) )
insert into @t1 ( ids ) values ('tutu')
insert into @t1 ( ids ) values ('tutu')
insert into @t1 ( ids ) values ('toto')
insert into @t2 ( ids ) values ('toto')
insert into @t2 ( ids ) values ('toto')
insert into @t2 ( ids ) values ('tutu')
select ids from @t1 union select ids from @t2 
select ids from @t1 union all select ids from @t2