Je viens d’apprendre deux choses avec le MS SQL 2005 Database Tuning Engine Advisor à mes dépends.
Je vais donc les partager avec vous afin que cela ne vous arrive pas! Le contexte: Je ne m’occupe pas en général de SQL 2005 (ou m’occupais pas jusqu’à présent). Ayant un problème de perf important sur une de nos futures applications, j’ai utilisé SQL Profiler pour capturer l’activité, et j’ai voulu ensuite utiliser DTA pour savoir s’il manquait des index. Mais je suis allé trop vite, et je n’ai donc pas compris que DTA ne pouvait pas utiliser la trace.
Lorsque vous donnez un fichier trace à DTA, vous pouvez lui spécifier la base de workload, et la ou les bases à optimiser. Je pensais bien faire en sélectionnant la base « tune » comme base de workload. J’ai bien vu quelques erreur après l’analyse sur des requêtes, mais rien n’indiquait un problème majeur:
[Microsoft][SQL Native Client][SQL Server]Could not find stored procedure XX.P_TM_XXXXXXXXXXXXXXXX’.
Ce message est dû au fait que la trace ne contient pas de « use my_database » avant l’appel aux procédures stockées. DTA ne trouve donc aucune procédure stockée, et n’analyse donc rien les concernants. Ca m’étonnait qu’il ne trouve aucun index ni stats à créer, mais je ne savais pas l’expliquer. Pour palier à cela, il faut donc choisir la base où sont ces procédures stockées ou jouées les requêtes en tant que base de workload.
Une fois ce message d’erreur passé, j’en ai eu un deuxième,car les problèmes viennent toujours à plusieurs:
[Microsoft][SQL Native Client][SQL Server]SHOWPLAN permission denied in database ‘XXXXX’.
Celui-là est un peu différent. Si la trace contient la colonne LoginName ou NTUserName, DTA rejoue les requêtes avec ce ou ces comptes, qui doivent donc avoir les droits de faire un showplan. Pour palier à ce problème sans donner ce droit, vous pouvez ouvrir la trace, supprimer ces colonnes, et enregistrer la trace sous un autre nom, le temps de jouer DTA. Ainsi il utilisera le compte avec lequel vous l’exécuter pour analyser les requêtes 🙂
Et voilà, maintenant il me propose l’ajout de 50 index, 125 statistiques pour une estimation de +50% en perf!!
ajouter des index est à double tranchants, comme d’habitude avec les SGBD. Si leur utilisation peut accélèrer de manière significative la lecture … elle peut aussi entraîner des lenteurs lors des écritures. L’analyse de ce que « fait la base » notamment en terme de ratio lecture/écriture permet d’affiner le positionnement des index et leur type (clustered ….).
Avec un peu de chance, on doit pouvoir trouver des infos sur un site (novice SQL Server s’abstenir) que je consulte régulièrement : sqlservercentral.com.
Et j’espère que tu ne payes pas d’abonnement à ce site et qui tu utilises mon « workaround »: