Optimiser les performances de SQL Server Express sur les serveurs Front-End

Microsoft ne donne pas de bonnes pratiques particulières concernant les bases de données existantes dans SQL Server Express sur les serveurs Front-End. On dit toujours qu’il n’y a rien de particulier à faire, et ce n’est pas faux. Par contre, dans certains cas, on peut observer des temps de réponse de Lync Server / Skype for Business Server qui deviennent plus long sans raison apparente.

Dans SQL Server, on retrouve des indexes sur les tables, qui permettent à SQL d’accéder à l’information plus vite, et on observe que l’ajout de plan de maintenance qui gère les indexes et met à jour les statistiques peut améliorer les performances des serveurs Front-End.

Voici une petite procédure pour mettre en place ce type de plan de maintenance. Il existe d’autres méthodes pour arriver à la même opération mais celle-ci est simple compte-tenu du fait que l’Agent SQL ne fonctionne pas par défaut sur les serveurs Front-End, et que je cherche à éviter de faires des modifications qui pourraient disparaître lors d’une mise à jour.

Les étapes sont relativement simples :

• Aller sur https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html et télécharger le fichier MaintenanceSolution.sql
• Modifier le fichier MaintenanceSolution.sql pour mettre les variables @CreateJobs et @LogToTable à ‘N’

SET @CreateJobs = 'N'
SET @LogToTable = 'N'

• Se connecter depuis une machine distante qui a SQL Server Management Studio à l’instance RTCLOCAL de chaque Front-End. Si nécessaire pour cette opération temporaire, désactiver le pare-feu Windows ou créer une exception pour SQL Server Express
• Lancer un New Query editor et exécuter le contenu du fichier MaintenanceSolution.sql
• Faire la même opération avec l’instance LYNCLOCAL de chaque Front-End

A partir de ce moment, l’outillage est en place. Puisque l’Agent SQL n’est pas fonctionnel par défaut, on va utiliser une tâche planifiée sur chaque Front-End pour exécuter le plan de maintenance :

• Créer un fichier IndexOptimize.sql avec le contenu suivant et le place dans le répertoire C:\Tools\IndexOptimize de chaque Front-End :

EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y',
@PageCountLevel = 10

• A l’aide du Task Scheduler de Windows, créer une tâche planifiée avec les paramètres suivants :
  • Tâche exécutée par : NT AUTHORITY\SYSTEM
  • Planification : Journalière, à 5h00
  • Action 1 :
    • Programme : osql.exe
    • Arguments : -E -S .\RTCLOCAL -i IndexOptimize.sql
    • Répertoire : C:\Tools\IndexOptimize
  • Action 2 :
    • Programme : osql.exe
    • Arguments : -E -S .\LYNCLOCAL -i IndexOptimize.sql
    • Répertoire : C:\Tools\IndexOptimize

Quelques notes :

• Pour la simplicité, l’exemple ci-dessus utilise le compte NT AUTHORITY\SYSTEM mais il est possible d’utiliser un autre compte qui fait partie des administrateurs locaux (c’est le seul groupe qui a le role « sysadmin » dans les deux instances RTCLOCAL et LYNCLOCAL)
• J’ai choisi 5h pour l’exécution de la tâche planifiée mais ça peut être à un autre moment en fonction de l’occupation des serveurs et des autres traitements.
• Pour vérifier que le script fonctionne bien, il est possible d’ajouter la ligne @LogToTable = ‘Y’ (sans oublier une virgule à la fin de la ligne précédente) dans le fichier IndexOptimize.sql et ainsi voir le contenu de la table LogCommand de la base master de chaque instance. S’il y a des lignes,c’est que ça fonctionne, sinon il y a probablement un problème dans le fichier IndexOptimize.sql ou dans la ligne de commande exécutée par la tâche planifiée. Ne pas oublier de supprimer le @LogToTable après le test et de vider la table LogCommand car celle-ci ne se vide jamais automatiquement

Aussi, il ne faut pas oublier d’appliquer les Service Packs et les CU sur les instances SQL Express des serveurs Front-End !

— Stefan