SQL/Server : Limitations ?

Bonjour.

Je suis désemparé. Je travaille dans une usine dont la production est controlée par une base de données SQL/Server.

Régulièrement, des Verrous/IdDeProcessus se bloquent et/ou s'interbloquent paralysant toute la production.

Pourtant, rien ne semble anormal. La charge du bi-CPU ne sature pas, la RAM non plus, Par contre, le nombre des Verrous/IdDeProcessus peut dépasser 700 allégèrement et le nombre de PC connecté à notre base de données dépasse les 400.

Nous utilisons Windows Serveur 2000 Standard Edition en Français. 4Go de Ram
Sql/Serveur 2000 Standard Edition SP4.

Quelles sont les limitations connues que nous devrions surveiller ou connaitre pour au moins savoir dans quelle direction rechercher et savoir quoi répondre ?

Toute aide sera la bienvenue ! ;)

400 connexions ne signifie par nécessairement 400 requêtes simultanément. C'est même rarement le cas !
En l'occurrence SQL Server est capable de travailler dans un ratio d'environ 2000 utilisateurs par carte réseau/CPU.
Par exemple un octoprocesseur avec 8 cartes réseau devrait pouvoir traiter 16000 utilisateurs dans le cadre d'une base OLTP parfaitement optimisée.
Tout dépend donc de la qualité de l'application : modèle de données, qualité des données, logiques de séquencement et d'isolation des transactions, méthodes d'accès aux données, types de traitements...

Si les données ont été mal modélisées (clef varchar, table pas normalisées...) si l'application a été développé sans penser au client serveur (par exemple une application Access transposée en SQL Server) alors les dégâts peuvent rapidement être monstrueux. En l'occurrence un grand nombre de verrous dans un faible nombre de connexion fait penser à de très grandes tables foure tout (donc un modèle à refondre) et l'absence de dévelopement "C/S style" par exemple des SELECT * avec projection et restriction sur le client, donc une refonte du code applicatif.

Seul un audit complet permet de déterminer ce qui se passe !

A +

Bonjour.

Merci pour tes éléments de réponse.

Notre système gère la traçabilité (et la production) de l'usine.

Effectivement, nous avons identifié une erreur de conception dans notre système. Une optimisation qui aurait du être réalisée dès la conception, mais qui n'a pas été implémentée. Mais ayant détecté relativement récemment ce problème, il nous faut vivre avec pour l'instant.

Je m'explique : Alors que la quasi-totalité de nos tables ont leur clé primaire sur un champ numérique auto-incrémental, l'une de nos table centrale, celle qui stocke les numéros de série des éléments que nous fabriquons a sa clé primaire étalée sur un champ char(15) et sur un numérique (le numérique etant lui-même une clé étrangère). Cette table centrale comporte actuellement 22 millions de lignes.

Autour d'elle gravite 7 autres tables plus ou moins importantes (l'une d'elles contient 94 millions de lignes et augmente d'environ 3 lignes / secondes). Ces tables "satellites" possèdent des clés étrangère pointant sur la table des numéros de série. Bien sur, toutes ces clés étrangères ont la même structure que la clé primaire de la table des numéros de série.

Nous pensons à rectifier la clé primaire de la table des numéros de série, ce qui reviendrai à remplacer cette double-clé par une clé unique auto-incrémentale ; sans oublier de rectifier également toutes les clé étrangères pointant dessus. Cependant la charge de travail induite est estimée en première approche à 120j.

Pensez-vous que nous ayons quelque chose à y gagner en terme de performance et/ou de stabilité ? Que pensez-vous de notre idée, en avez-vous d'autre ? L'opération n'est pas sans risque ni conséquence, tous les conseils seront les bienvenus ;)

Bonjour,

Notre serveur "ouvert" traite 50 millions de requêtes par jour.

Premièrement: as-tu lancé des traces pour compter le nombre de deadlocks par minute?

S'il y a présence de deadlocks, il va falloir soit:

- Réviser toutes les requêtes possibles concurentes qui deviennent clientes asynchronique des mêmes resources. Exemple: J'update le username unique de deux clients en même temps à partir de deux procédures différentes et qui vérifie l'intégrité des données secondaires dans un ordre différent ce qui fait que l'une et l'autre ne peuvent locker la table principale. Cette option demande beaucoup de temps.

- Relaxer les contraintes physiques entre la table principale et ses tables de références et implémenter des dirty reads pour les informations secondaires (WITH (NOLOCK)).

Pour ce qui est des clés et des contraintes, il n'y a pas grand chose de pire qu'une clé incrémentale. Tu es condamné à perpétuer une clé arbitraire partout dans ton système.

Dans notre cas, pour nos tables qui dépassent les 100 milliards de records, nous employons une clé "bitwise mapped" qui est fabriquée on the fly. De cette façon, je peux à la fois faire des joins classiques (générateurs de conflits) mais aussi "parser" la clé pour faire des connections directes.

Par exemple, tous nos produits sont groupés par marque (brand) (nous en avons 258 en ce moment). Donc, les douze premiers bit de la clé sont réservés et me donne un possibilité de 2 à la 12 marques, 4092 (une durée de vie de d'environ 75 ans). Donc, mon masque est de 9222246136947933184 et mon diviseur de 1125899906842623.

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

USE [UBI_PRODUCTS] GO   /****** Object: UserDefinedFunction [dbo].[UDF_PRODUCT_ID] Script Date: 03/02/2007 15:53:03 ******/ IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[UDF_PARSE_PRODUCT_KEY]') AND xtype in (N'FN', N'IF', N'TF')) DROP FUNCTION [dbo].[UDF_PARSE_PRODUCT_KEY] GO   CREATE FUNCTION [dbo].[UDF_PARSE_PRODUCT_KEY] ( @p_PRODUCT_KEY bigint, @p_MASK_NAME varchar(50)) RETURNS bigint AS BEGIN DECLARE @v_MASK bigint, @v_DIVIDER bigint, @v_KEY bigint   SELECT @v_MASK = MASK, @v_DIVIDER = DIVIDER FROM UBI_PRODUCTS.dbo.UBI_PRODUCT_BITMASK WHERE MASK_NAME = @p_MASK_NAME   SELECT @v_KEY = (@v_MASK & @p_PRODUCT_KEY) / @v_DIVIDER   RETURN @v_KEY   END

---

Donc, si je suis très creux dans mon système et que je veux faire une opération uniquement au niveau de la marque de produit, je n'ai pas à passer par notre table principale qui contient plusieurs centaines de milliards de records. Dans une clé semblable, tu peux garder une hiérarchie de 6 à 12 éléments complexe ou insérer des attributs, bitwise ou bitwise mapped.

Pour un système simple, l'idée d'utiliser ce genre de clé est ridicule et c'est pourquoi beaucoup de concepteurs de BD qui n'ont jamais eu à travailler avec des systèmes très lourds en décrient l'usage. Je suis d'accord avec eux qu'un système de moins de 1000 tables sur moins de 10 serveurs et qui possède moins d'un terabyte de données deviendrait trop complexe.

Par contre, pour un environnement de production qui oblige la traçabilité d'une opération internationale, il faut transférer une partie de la structure à même la clé unique principale afin qu'elle soit "self-aware" (je ne connais pas de terme français équivalent, désolé) afin qu'elle puisse d'elle-même savoir à quel service de données s'adresser sans avoir à ajouter du stress sur les autres structures.

Ce ne sera peut-être pas la solution pour votre système. Par contre, je te conseille fortement de lire ceci:

http://www.microsoft.com/technet/pro...erf_top10.mspx

d'analyser ton système par rapport à cette liste et de faire une présentation à une équipe de gens qui ont des connaissances en bases de données pour alimenter la discussion.