PI Services

Le blog des collaborateurs de PI Services

SQL Server–Optimiser les paramètre d’exécution parallèle (1/2)

Introduction :

SQL Server est conçu pour optimiser l’exécution des requêtes de manière transparente, l’une des méthodes utilisé par SQL est le calcul parallélisé de requêtes.

Cette méthode d’optimisation consiste à diviser l’exécution d’une requête à travers threads afin d’accélérer l’exécution de la requête.

Ce post composé de deux parties à pour objectif de présenter les paramètres suivants :

  • MAXDOP (partie 1/2),
  • Cost Threshold for Parallelism (partie 2/2)

Explication :

Le paramètre MAXDOP signifie “Max Degree of Parallelism”, ce paramètre permet de maitriser le nombre maximum de processeurs pouvant intervenir dans l’exécution d’une requête parallélisée, il est important de comprendre que ce paramètre ne peut être utilisé pour limiter le nombre de processeurs utilisés par SQL.

Ce lien liste les valeur recommandées par Microsoft afin de paramétrer cette valeur en fonction des versions de SQL Serveur, ce lien peut également être utilisé pour calculer la valeur optimale à positionner. Il est bien entendu nécessaire de tester les différentes configurations en fonction de la charge soumise par l’application qui utilise le serveur SQL.

Réalisation

Le paramètre MAXDOP peut être modifié depuis les propriétés de l’instance SQL concernée dans la partie Advanced->Parallelism :

image

Ou via la requête suivante (en remplaçant VALEUR par la valeur désirée) :

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
dbo.sp_configure 'max degree of parallelism', VALEUR;
GO

RECONFIGURE;
GO

La modification du paramètre MAXDOP est prise en compte sans qu’il n’y a à redémarrerredemmarer l’instance.

Sources :

SQL Server–Optimiser les paramètre d’exécution parallèle (2/2)

Introduction :

Comme vu dans le post précèdent, SQL Server dans sa configuration par défaut optimise automatiquement l’exécution des requêtes, notamment en répartissant son exécution à travers différents threads.

Ce post composé de deux parties à pour objectif de présenter les paramètres suivants :

  • MAXDOP (partie 1/2),
  • Cost Threshold for Parallelism (partie 2/2)

La partie 2/2 de ce post auras pour objectif de configurer SQL afin que seules les requêtes considérées comme volumineuses ne soient traitées en parallèle.

Explication :

Le paramètre “Count Threshold for Parallelism” permet à SQL de ne paralléliser que les requêtes dont le coût d’exécution d’un plan en série (mesuré en secondes) serait plus élevé que la valeur définie.

L’optimisation réalisée par SQL, bien qu’efficace pour les requêtes “volumineuses”, peut se révéler désastreuse pour l’exécution concurrente d’un grand nombre de petites requêtes, il est donc judicieux de revoir la valeur de ce paramètre en fonction du type de charge exercée sur le serveur SQL.

En règle générale, une valeur comprise entre 20 et 50 est un bon début qui doit être affiné par des tests spécifiques à l’application.

Par défaut, la valeur du paramètre “Count Threshold for Parallelism” est de 5, cette valeur provient du temps nécessaire à l’exécution d’une requête sur le poste de “Nick” un employé de Microsoft membre de l’équipe Query Optimizer lorsque le Query Optimizer était en cours de développement pour SQL Server 7.

Nick devait – entre autre – développer la méthode de calcul du coût d’une requête, il a donc utilisé son ordinateur comme étalon, depuis, le coût d’exécution d’une requête estimé par SQL est….le temps d’exécution qu'aurais pris la machine de Nick.

Bien entendu, les machines utilisées ne sont plus les mêmes et les temps d’exécutions sont donc plus courts :

image

La machine de Nick.

SQL Server ignore la valeur de l'option cost threshold for parallelism dans les cas suivants :

  • L'ordinateur ne dispose que d'un seul processeur.
  • Un seul UC est disponible pour SQL Server en raison de l'option de configuration affinity mask.
  • L'option max degree of parallelism a la valeur 1.

 

Réalisation

Le paramètre Count Threshold for Parallelism peut être modifié depuis les propriétés de l’instance SQL concernée dans la partie Advanced->Parallelism :

image

Ou via la requête suivante (en remplaçant VALEUR par la valeur désirée) :

sp_configure 'show advanced options', 1;
GO
reconfigure;
GO
sp_configure 'cost threshold for parallelism', VALEUR;
GO
reconfigure;
GO

La modification du paramètre Count Threshold for Parallelism est prise en compte sans qu’il n’y a à redémarrer l’instance.

Sources :

SQL Server–Rapport de découverte (Discovery Report)

Introduction

Le rapport de découverte SQL Server est utile afin de lister toutes les informations relatives à aux composants installés sur votre serveur SQL, leur version et leur édition.

L’objectif de ce post est de générer un rapport en mode GUI et en via un script.

Réalisation

  • En mode GUI :

Exécuter “SQL Server Installation Center :

image

Aller dans l’onglet “Tool” et cliquer sur “Installed SQL Server features discovery report” :

image

Le rapport de découverte SQL Server est enregistré dans “C:\Program Files\Microsoft SQL Server\VERSION\Setup Bootstrap\Log\AAAAMMDD_XXXXX

  • En mode script :

Depuis un invite de commande, exécuter la commande suivante: “Setup.exe /q /Action=RunDiscovery” depuis le répertoire “C:\Program Files\Microsoft SQL Server\VERSION\Setup Bootstrap\SQLServer2012”

image

Sources

SQL Server–Supprimer l’un des fichiers utilisé par la TempDB

Contexte

Lors de la suppression d’un des fichiers utilisé par la base TempDB, l’opération échoue avec l’erreur suivante :

Msg 5042, Level 16, State 1, Line 1
The file 'tempdev2' cannot be removed because it is not empty.

Explication

Ce comportement est provoqué par le fait que le fichier est actuellement utilisé par SQL, il faut donc vider le fichier avant de pouvoir le supprimer.

Une solution possible est le redemmarage de l’instance SQL, ce qui va regenerer la base TempDB, moyennant une coupure de service.

Résolution

Afin d’éviter toute coupure de service, il est possible d’executer le script SQL suivant pour vider puis supprimer un fichier utilisé par la TempDB :

USE [tempdb]
GO
DBCC SHRINKFILE('tempdev2', EMPTYFILE)
GO
ALTER DATABASE [tempdb] REMOVE FILE [tempdev2]
GO

Sources

https://tenbulls.co.uk/2016/01/13/problem-removing-files-from-tempdb/

SQL Server–Reconfigurer les plans de maintenances après avoir renommé une instance SQL

Introduction

Après avoir renommé un serveur SQL, les plans de maintenances existants échouent avec l’erreur suivante :

Réalisation

1. Lister les procédures stockées en exécutant le script suivant sur la base MSDB et récupérer la valeur “ID” :

SELECT  x.*,
        LocalServerConnectionString = cm.value('declare namespace DTS="www.microsoft.com/SqlServer/Dts";DTS:ObjectData[1]/DTS:ConnectionManager[1]/@DTS:ConnectionString', 'varchar(1000)')
FROM (
    SELECT  id, name, packageXML = CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML)
    FROM dbo.sysssispackages
    WHERE id IN (SELECT id FROM dbo.sysmaintplan_plans)
) x
CROSS APPLY packageXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";/DTS:Executable/DTS:ConnectionManagers/DTS:ConnectionManager[@DTS:ObjectName="Local server connection"]') p(cm)

Voici un exemple de résultat :

image

2. Modifier la valeur “Data Source” à l’aide du script suivant :

UPDATE dbo.sysssispackages SET packagedata = CAST(CAST(REPLACE(CAST(CAST(packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)), 'OldServerName', 'NewServerName') AS XML) AS VARBINARY(MAX))
WHERE id = ‘ID'

Modifier les valeur en rouge :

  • OldServerName : Ancien nom de l’instance SQL telle qu’indiquée dans la partie “Data Source” de la colonne “LocalServerConnectionString” de la requête exécutée précédemment,
  • NewServerName : Nouveau nom de l’instance SQL,
  • ID : GUID de la tâche planifiée à reconfigurée telle qu’indiquée dans la colonne“ID” de la requête exécutée précédemment.

Vérification

  • Valider en exécutant la requête listant les procédure stockées que le nom de la nouvelle instance apparaît bien dans la partie “Data Source” de la colonne “LocalServerConnectionString”,
  • Exécuter le plan de maintenance et valider qu’il s’exécute correctement.

Liens Utiles

SQL Server – Chiffrer les connexions Client/Serveur

Introduction

Afin d’améliorer la sécurité des connexions réalisées depuis les clients SQL vers une base, il est possible d’activer la fonction de chiffrement de SQL (Encryption) depuis SQL Server Configuration Manager.

Cette fonctionnalité permet de chiffrer la connexion entre le client et le serveur afin d’éviter l’interception et la lecture des commandes réalisées par le client.

Prérequis

  • Un certificat avec sa clé privée délivré pour l’authentification server d’installé sur le serveur SQL (le certificat doit avoir pour nom le même que celui utilisé dans la source de donnée du client, souvent il s’agit du FQDN du serveur),
  • Dans le cas d’une utilisation d’un cluster, le nom du certificat doit être correspondre au nom DNS utilisé par le cluster, le certificat devras être installé sur tous les nœuds du cluster,
  • Le compte de service utilisé par le service SQL Server doit avoir les droits Read sur la clé privée du certificat,
  • Le client doit être en mesure de valider et de faire confiance à l’autorité ayant délivré le certificat,
  • Une fenêtre d’interruption de l’instance SQL.

Réalisation

1. Depuis SQL Server Configuration Manager, faire un clic-droit sur l’instance souhaitée et sélectionner “Properties”

image

2. Depuis l’onglet certificate, sélectionner le certificat à utiliser pour le chiffrement des connexions,

image

3. Depuis l’onglet Flags, passer la valeur de “ForceEncryption” à ‘'Yes”,

image

4. Rebooter l’instance.

Verification

Un test réalisé à l’aide d’un outil de capture réseau, et d’un client SQL permet de prouver le chiffrement des connexions :

  • Avant

image

  • Après

image

SQL Server – Configuration de la base TempDB (Partie 1/2)

Introduction

Ce post est composé de deux parties :

  • Recommandations sur l’emplacement et la taille de TempDB (Partie 1)
  • Répartition de la base TempDB sur plusieurs fichiers (Partie 2)

Présentation de la base TempDB :

La base TempDB est une base temporaire régénérée à chaque démarrage du serveur SQL, cette base est accessible à tous les utilisateurs (il est possible de retirer l’accès à cette base aux utilisateurs, mais cela n’est pas conseillé car la base TempDB est utilisé dans des opérations de routines).

Cette base, de par sa nature temporaire, ne permet pas les actions d’administrations suivantes : Sauvegarde/Restauration, Changement du “Recovery Model”.

Dans cette première partie nous verrons comment déplacer la base TempDB vers un stockage dédié.

Recommandations sur l’emplacement et la taille de TempDB

Recommandations Générales relatives à l’emplacement des fichiers de bases de données

De manière générale, il est recommandé de séparer les fichiers Data, Logs et temp. L’objectif de cette séparation est de permettre une meilleure séparation de la charge à travers différents disques physiques.

Actuellement, avec l’augmentation des solutions de stockage virtuel, la séparation des fichiers n’est parfois plus nécessaire pour des raisons de performances, néanmoins, séparer les fichiers à travers différents disques logiques permet plus de souplesse dans l’administration.

Dans le cas d’une infrastructure SQL utilisant un stockage partagé, le déplacement de la base TempDB vers un stockage local peut apporter les bénéfices suivants :

  • Meilleure performances notamment lors de l’utilisation de disques rapides (SSD),
  • Certaines bases TempDB peuvent être très sollicitées entrainant une baisse de performance générale sur stockage partagé.

Certains administrateurs peuvent être tentés de déplacer la base TempDB vers un stockage local, attention toutefois à bien valider les points suivants pour une configuration cluster :

  • Utiliser une base TempDB locale dans une configuration Cluster n’est supporté que depuis SQL Server 2012 (les versions précédentes requièrent que toutes les bases soient présentes sur le stockage partagé),
  • Utiliser le même emplacement pour TempDB à travers tous les nœuds du cluster, le compte de service SQL Server doit avoir un droit Lecture/Ecriture sur chacun des dossiers.

Recommandations Générales relatives à la taille des fichiers de bases de données

Lors de la création d’une nouvelle base de données, il est possible de préciser les éléments suivants relatifs à la taille de la base de données :

  • Taille initiale de la base de données,

La taille initiale de la base de donnée doit correspondre à la taille que la base va occuper à terme, ce afin d’éviter des actions d’expansion de la base (Autogrowth) qui en plus d’être lourdes entraînent une fragmentation des fichiers ainsi qu’une indisponibilité de ceux-ci durant l’expansion.

  • Taille maximum de la base de données,

A moins de parfaitement maitriser la taille maximum que la base de données doit avoir, il est préférable de conserver à “Unlimited” la taille maximum d’une base.

  • Autogrowth de la base de données

La fonction Autogrowth permet d’automatiquement augmenter la taille des fichiers de la base de données, ce qui permet une administration plus simple. L’opération d’autogrowth est généralement lourde (à moins d’utiliser l’IFI, Instant File Initialization) et comprends plusieurs contraintes.

Il est donc préférable d’éviter qui doit être considéré comme une solution de fortune. L’idéal en l’absence d’informations précises concernant la taille de la base est d’analyser l’évolution de la base en paramétrant un autogrowth fixe en MB, puis après une période d’analyse suffisante, de paramétrer la taille des fichiers utilisés par la base à une valeur légèrement supérieure à celle constatée (cette préconisation s’applique d’autant plus à la base TempDB, celle-ci étant recréée à chaque démarrage de l’instance SQL).

Afin d’estimer la taille à positionner pour l’Autogrowth, il est possible de visualiser la fréquence d’autogrowth d’une base depuis le rapport Disk de celle-ci :

clip_image001

Réalisation

Afin de déplacer la base TempDB, voici les actions à réaliser :

1. Récupérer l’emplacement actuel de la base

Use master
GO
SELECT
name AS [LogicalName]
,physical_name AS [Location]
,state_desc AS [Status]
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO

clip_image002[5]

2. Déplacer les fichiers utilisés par TempDB (attention à veiller à ce que le compte du service SQL Server y ait un droit de Lecture/Ecriture)

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'T:\MSSQL\DATA\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'T:\MSSQL\DATA\templog.ldf');
GO

3. Redémarrer le service SQL Server

Verification

Vérifier le nouvel emplacement des fichiers TempDB :

Use master
GO
SELECT
name AS [LogicalName]
,physical_name AS [Location]
,state_desc AS [Status]
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO

SQL Server – Configuration de la base TempDB (Partie 2/2)

Introduction

Ce post est composé de deux parties :

  • Recommandations sur l’emplacement et la taille de TempDB (Partie 1)
  • Répartition de la base TempDB sur plusieurs fichiers (Partie 2)

Dans cette seconde partie nous verrons comment repartir la base TempDB à travers plusieurs fichiers comme Microsoft le recommande.

Recommendation

Bien que Microsoft recommande de répartie la base TempDB à travers autant de fichiers que de processeurs logiques présents sur le serveur et ce jusqu’à 8 fichiers, dans le cas d’un serveur avec plus de 8 CPU, si malgré l’utilisation de 8 fichiers la base TempDB souffre toujours de lenteurs, Microsoft préconise d’augmenter le nombre de fichiers par un multiple de 4.

Différents membres de la communauté SQL s’accordent à penser qu’au-delà de 8 fichiers, le gain en performance seras négligeable par rapport à l’effort d’administration.

Réalisation

1. Récupérer le nombre de CPU logique du serveur SQL

clip_image001

2. Ajouter trois fichiers TempDB sur trois disques différents (attention à veiller à ce que le compte du service SQL server y ait un droit de Lecture/Ecriture), il est préférable d’avoir des options de tailles et d’Autogrowth identiques

ALTER DATABASE tempdb

ADD FILE (NAME = tempdev2, FILENAME = 'W:\tempdb2.mdf', SIZE = 1024);

ALTER DATABASE tempdb

ADD FILE (NAME = tempdev3, FILENAME = 'X:\tempdb3.mdf', SIZE = 1024);

ALTER DATABASE tempdb

ADD FILE (NAME = tempdev4, FILENAME = 'Y:\tempdb4.mdf', SIZE = 1024);

GO

SQL Server 2016 : Configuration tempdb à l’Installation

Avec SQL Server 2016 nous aurions la possibilité de configurer la base de données tempdb dans le cadre du processus d'installation.

Un nouvel onglet a été ajouté à la rubrique de configuration du serveur de base de donnés sous le nom de tempdb et il nous permettra de préciser :

  1. Le nombre de fichiers de données de la base de données tempdb
  2. La taille initiale de chaque fichier et le taux de croissance en Mb
  3. La possibilité d'ajouter plusieurs emplacements pour le stockage des fichiers tempdb
  4. L'emplacement et la taille du fichier log de la base de données tempdb

SQL Server – Mettre en place une réplication asynchrone via le Log Shipping

Contexte

Le log shipping est l’une des technologies proposée par SQL Server pour mettre en place une haute-disponibilité d’une ou plusieurs bases de données à moindre prix.

Le fonctionnement du log shipping peux être résumé en trois étapes “backup-copy-restore”.

Les logs de transactions des bases de donnée à répliquer sont sauvegardées depuis le serveur primaire puis copiés vers le (ou les) serveur secondaire où ils y seront restaurés sur les bases secondaires correspondantes.

Plus d’informations sur le fonctionnement du log shipping sont disponibles ici.

Prérequis

Les prérequis à l’installation sont les suivants :

Prérequis Applicatifs :

  • Au moins deux serveurs avec SQL Server 2005 (Standard, Entreprise ou Workgroup) ou ultérieur,
  • Un partage de fichier pour la copie des logs de transactions,
  • Le service SQL Server Agent,
  • Il est fortement conseillé d’avoir des instances SQL partenaires configurés à l’identique.

Permissions nécessaires :

  • Disposer des droits sysadmin sur l’instance primaire et la secondaire,

Configuration du log shipping

Les actions décrites sont effectuées depuis le serveur primaire.

1. Vérifier que la base de donnée à répliquer est en mode de recovery “full” ou “bulk-logged” à l’aide de la requête suivante :

SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'MyPrimaryDB'

 

2. Depuis les propriétés de la base à répliquer, depuis l’onglet “Transaction Log Shipping” (1) cochez la case “Enable this as a primary database in a log shipping configuration” (2) puis cliquer sur “Backup Settings” (3) :

image

3. Depuis la fenêtre Backup Settings, indiquer le chemin réseau où le serveur primaire écriras les backups des logs de transactions des bases de données à répliquer (1) et éventuellement le chemin local (2) si un chemin local est utilisé. Noter que dans les deux cas le compte utilisé par le service SQL Server Agent du serveur secondaire doit avoir un accès en lecture à ce dossier, et le service SQL Server du serveur primaire doit avoir un accès en read/write.

Indiquer le nom du job de backup du serveur primaire (3) de manière à facilement le reconnaitre, éventuellement modifier le schedule proposé (4) par un schedule correspondant mieux à vos contraintes spécifiques puis cliquer sur OK.

image

4. Depuis la fenêtre Secondary Database Settings, se connecter au serveur secondaire (1), choisir une base de donnée existante ou indiquer le nom d’une nouvelle base (2) qui seras utilisée/créée sur le serveur secondaire, puis choisir le mode pour initialiser la base de donnée secondaire.

Ayant déjà créé initialisé la base distante, j’ai choisi l’option “No, the secondary database is initialized” (3)

image

5. Depuis l’onglet “Copy Files” (1), indiquer le chemin où les sauvegardes des logs de transactions seront copiés (2), indiquer la rétention des fichiers copiés (3) en faisant attention à ce que les fichiers ne soient pas supprimés avant leur restauration sur le serveur secondaire, personnaliser le nom du job de copie (4) et modifier son schedule si nécessaire (5)

image

6. Depuis l’onglet “Restore Transaction Logs” (1), choisir “Standby Mode” et cocher la case “Dissconnet users in the database when restoring backups” (2) celà permet d’accéder à la base de données secondaire en Read-Only contrairement au mode “No-Recovery” qui met la base en état de restauration, personnaliser le nom du job de restauration (3) et en modifier le schedule si nécessaire (4)

image

Vérification

Il convient suite à la mise en place du log shipping de vérifier son fonctionnement en mode manuel (c-à-d en exécutant manuellement les jobs de Backup-Copy-Restore) et automatiquement selon les schedule définis pour chaque jobs.

Il est possible de mettre en place des alertes par email pour informer le DBA de la bonne exécution des jobs de réplication depuis l’onglet Transaction log shipping des propriétés de la base répliquée.