Procédure SQLPRO : indexes/maintenance

* Bonjour, *

Questions par rapport à la procédure de maintenance très complète : http://blog.developpez.com/sqlpro/p8...es-index-et-s/

Nous avons au sein de notre entreprise une base de production de 320 Go utilisé quotidiennement avec tout de même des périodes de très faible activité le soir entre 18h et 22h et le dimanche entre 2h et 18h.
J'ai donc repris ces plans de maintenance, mais je suis perplexe concernant la durée d'exécution de ces plans.
Concernant la défragmentation et la reconstruction selon un seuil, rien à redire la collecte est très rapide et l'exécution par la suite est "normale" (dépend évidemment de ma machine, la configuration de cette dernière, l'activité sur la base etc.).
J'ai toujours rechigné cependant a faire des rebuild même lors d'une faible activité, lors du switch une fois l'index reconstruit nous avons pu rencontrer des erreurs du type : la requête s'est terminée en erreur car le schéma de la table a changé (ou quelque chose d'approchant, excusez mon approximation)

Je m'interroge sur la mise à jour des statistiques fait sur un GRAND nombre d'indexes et tables (2700 lignes). Ainsi nuit après nuit, j'ai l'impression que tout est fait et refait et cela me parait un peu lourd pour mes zones de maintenance assez réduite. Je ne sais pas si on peut considérer ma base comme une VLDB et du coup changer de fonctionnement.

Enfin l'ensemble des check sur les objets de ma tables même s'ils sont en général assez rapide je me retrouve avec une table "Historique_maintenance" avec plus de 4100 lignes à chaque collecte, occupant ma base (de qualité pour le moment) toute la nuit.

A votre avis ?

* Merci *

Êtes vous en édition Enterprtise ? Si oui, reconstruisiez vos index en ONLINE. Ceci évitera de perdre d'index en cours de maintenance.
Si vous préférez, optez pour l'autre solution : http://sqlpro.developpez.com/optimis...ntenanceIndex/
Vous pouvez aussi parraléliser la maintenance en plaçant plusieurs processe de maintenance déclencher en //. Mais dans ce cas il faut trouver un moyen que les defrag ne se marchent pas sur les pieds (par exemple en scindant les process par tables).
Vider la table de log est important, mais elle n'occupe pas tant de place que ça.
J'ai publié une seconde version il n'y a pas longtemps, car il y avait un bug dans certains cas sur la proc que vous utilisé ('si vous la lancez pas dans la base de prod.

A +

Merci pour ta réponse SQLPRO,

J'ai pourtant rencontré le cas lors d'une maintenance Online sur les indexes en production par le passé, nous avons pu déplacer les jobs (fonctionnels) pour nous laisser une vrai plage de maintenance et ainsi ne pas craindre une erreur sur un job critique.
Pour les installations SQL en version standard nous les réservons pour les environnements de test, cela nous pose peu de problèmes (puisque peu d'activité).
Pour la solution VLDB, il va falloir que je décortique (non pas que je ne fais pas confiance à la solution proposée) parce que j'aime bien comprendre ce qui est effectué (avec mon faible niveau en transac-sql il me faut du temps :)) notamment pour détailler aux autres ce qui est effectué.
Pour la parallélisation on pourrait penser à une table recueillant les différents check sur les tables system/prod/vues et une autre pour les opérations sur les indexes (stats, rebuild, reorganize) . Qu'en pensez vous ?

Citation:

---

Pour la parallélisation on pourrait penser à une table recueillant les différents check sur les tables system/prod/vues et une autre pour les opérations sur les indexes (stats, rebuild, reorganize) . Qu'en pensez vous ?

---

Non, pas bon car le risque de téléscopage sur des opération en // sur la même table est grand.
A priori ce que je ferais est la chose suivante :
créer une table des tables avec une colonne "thread" pour laquelle vous mettez alternativement les nombres de 1 à N, N étant le nombre de process que vous parralléliserez.
Chaque process devra traiter les les tables de son rang. Le process 1 traitera uniquement les tables de rang 1, etc...
ATTENTION : pas plus de N process, N étant le nombre de processeurs physiques. De plus si vous êtes en 2008, faites ces processus avec un compte de connexion particulier et avec le gouveneur de ressources restreignez le nombre de CPU logique au nombre de coeurs (quad = 4)

A +


PS vous avez parfaitement raison de relire mes procs :
a) je ne suis pas à l'abri d'une erreur
b) cela permet de comprendre ce qui est fait
c) cela permet de les améliorer et de les customiser !

Re bonjour,

(pour l'ensemble des infos ci dessous voir : référence SQLPRO )
Je pense adopter ce fonctionnement cette semaine, peut être certaines de vos remarques/doutes pourraient m'aiguiller (ou me remettre dans le droit chemin).

Après la collecte des statistiques (inchangée) à 18H00 je chaîne une suppression des lignes correspondant aux travaux que je ne veux pas exécuter en activité via la requête :

Code:

---

1 2 3 4

delete FROM [msdb].[S_MAINT].[T_HISTORIQUE_MAINTENANCE_HMT] where HMT_ACTION='CHECKTABLE' OR HMT_ACTION='REBUILD' AND HMT_BEGIN_DATE IS NULL

---

Et ainsi garder mon historique et ne pas faire les checktable + rebuild indexes.

En essayant d'anticiper vos remarques :

Pourquoi enlever les checktables ?
Honnêtement je ne sais pas, chose que nous ne faisions pas avant (manuellement en tout cas) et que je planifierai le weekend. Avant tout c'est le nombre de tables à scanner et la taille de certaines tables qui m'effraient.

Pourquoi ne pas faire les rebuilds puisque nous sommes en version Enterprise ?
J'ai déjà eu par le passé, malgré le paramètre "ONLINE = ON" un job tombant en erreur sans explication coté éditeur (rappelons que l'on fonctionne en mode curseur (j'entends déjà vos hurlements)). Donc je m'abstiens depuis de le faire lors de jobs potentiels.

Pourquoi ne pas évincer dès la collecte ce qui ne vous intéresse pas ? Cela me permet d'avoir la même procédure de collecte pour l'ensemble de mes opérations et donc une plus grande simplicité (pour moi déjà) pour mes collègues qui ne suivent pas forcément toutes ces refontes d'aussi près.


Pour les weekends j'ai modifié de la procédure stockée concernant les reconstructions des indexes j'ai rajouté l'option ONLINE = ON.
Pourquoi je fais ça, parce que je ne sais pas si ce paramètre par défaut est à ON. Et donc ça donne :

-- avant

Code:

---

1 2 3 4 5 6 7 8 9 10 11

-- index à reconstruire IF @HMT_ACTION = 'REBUILD' AND @HMT_SUB IS NOT NULL SET @SQL = 'ALTER INDEX [' + @HMT_SUB + '] ON [' + @HMT_DATABASE + '].' + @OBJET + ' REBUILD; ' ;   -- table à reconstruire IF @HMT_ACTION = 'REBUILD' AND @HMT_SUB IS NULL SET @SQL = 'ALTER INDEX ALL ON [' + @HMT_DATABASE + '].' + @OBJET + ' REBUILD ; ' ;

---

--après

Code:

---

1 2 3 4 5 6 7 8 9 10 11

-- index à reconstruire IF @HMT_ACTION = 'REBUILD' AND @HMT_SUB IS NOT NULL SET @SQL = 'ALTER INDEX [' + @HMT_SUB + '] ON [' + @HMT_DATABASE + '].' + @OBJET + ' REBUILD WITH ONLINE = ON; ' ;   -- table à reconstruire IF @HMT_ACTION = 'REBUILD' AND @HMT_SUB IS NULL SET @SQL = 'ALTER INDEX ALL ON [' + @HMT_DATABASE + '].' + @OBJET + ' REBUILD WITH ONLINE = ON; ' ;

---

Enfin dernier point je m'interroge sur le paramétrage passé lors de l'exécution de la procédure : S_MAINT.P_SET_MAINTENANCE_BASE
Il s'agit du seuil :
@SEUIL_STAT INT = 1000 -- correspondant au seuil maximal au delà duquel seul un échantillon des données est utilisé pour les stats.

Je n'ai pas l'impression que change quelque chose de le mettre à 100 ou à 1000 par chez moi.

Merci pour vos réponses et vos précisions :)

Citation:

---

Envoyé par Mothership

Pourquoi enlever les checktables ?
Honnêtement je ne sais pas, chose que nous ne faisions pas avant (manuellement en tout cas) et que je planifierai le weekend. Avant tout c'est le nombre de tables à scanner et la taille de certaines tables qui m'effraient.

---

Cela vérifie l'intégrité physique des données dans les pages. Cela pour permettre de remonter une alerte en cas de problème. Si problème il y a c'est dans 99,9 % des cas que le disque dur est en train de claquer.. Dilemme : si je continue à inscrire des données dedans, alors le "trou" s'agrandira, comme à la manière d'un nid de poule avec des véhicules qui passent dessus...
D'où l'intérêt pour ne pas perdre trop de données de le faire le plus régulièrement possible (en effet la réparation logique d'un disque abimé, c'est cautère sur jambe de bois, alors qu'on peut tenter si il n'y a pas trop de dégât de faire une restauration de pages).

Citation:

---

Pourquoi ne pas faire les rebuilds puisque nous sommes en version Enterprise ?
J'ai déjà eu par le passé, malgré le paramètre "ONLINE = ON" un job tombant en erreur sans explication coté éditeur (rappelons que l'on fonctionne en mode curseur (j'entends déjà vos hurlements)). Donc je m'abstiens depuis de le faire lors de jobs potentiels.

---

Avez vous mis un FILL FACTOR sur tous les index non clustered ?
Les index clustered sont-ils créé sur des données monotones (auto incrément, horodatage) ?
Avez vous dimensionné suffisamment large les espaces de stockage ?

Citation:

---

Pourquoi ne pas évincer dès la collecte ce qui ne vous intéresse pas ? Cela me permet d'avoir la même procédure de collecte pour l'ensemble de mes opérations et donc une plus grande simplicité (pour moi déjà) pour mes collègues qui ne suivent pas forcément toutes ces refontes d'aussi près.


Pour les weekends j'ai modifié de la procédure stockée concernant les reconstructions des indexes j'ai rajouté l'option ONLINE = ON.
Pourquoi je fais ça, parce que je ne sais pas si ce paramètre par défaut est à ON. Et donc ça donne :

-- avant


--après




Enfin dernier point je m'interroge sur le paramétrage passé lors de l'exécution de la procédure : S_MAINT.P_SET_MAINTENANCE_BASE
Il s'agit du seuil :
@SEUIL_STAT INT = 1000 -- correspondant au seuil maximal au delà duquel seul un échantillon des données est utilisé pour les stats.

Je n'ai pas l'impression que change quelque chose de le mettre à 100 ou à 1000 par chez moi.

Merci pour vos réponses et vos précisions :)

---

A+

Rebonjour,
La collecte des actions à effectuer étaient les suivantes :

Citation:

---

WHERE avg_fragmentation_in_percent > 30% OR (avg_page_space_used_in_percent < 60) --Page density
AND page_count > 20
and Index_id > 0

---

SQLPRO : Avez vous mis un FILL FACTOR sur tous les index non clustered ?
Ci dessous les options qui étaient appliquées lors de la reconstruction des indexes :
REBUILD WITH (FILLFACTOR = 80, ONLINE = ON, PAD_INDEX = ON)
Lors de leur création, je ne sais pas, en parcourant quelques indexes le paramètre semble être le même : 80%


SQLPRO : Avez vous dimensionné suffisamment large les espaces de stockage ?
Hmm ne sachant pas exactement à quoi vous faites référence en exécutant la procédure sp_spaceused j'obtiens :

Citation:

---

database_name : Mabase
Database_size : 396813.5 MB
unallocated space : 28751.69 MB

reserved : 3000095296 KB
data : 172785400 KB
index_size : 126385824 KB
unused : 924072 KB

---

Une petite précision pour, ça vous parait peut être évident, j'avoue avoir du mal à me le visualiser

Citation:

---

@SEUIL_STAT INT = 1000 -- correspondant au seuil maximal au delà duquel seul un échantillon des données est utilisé pour les stats.

Je n'ai pas l'impression que change quelque chose de le mettre à 100 ou à 1000 par chez moi.

---

Merci

tant pis pour la fin.

Merci à tous pour vos réponses.