• Article

SQL Q & A Cohérence de la base de données, tables temporaires et plus encore

Paul S. Randal

QAi j'remarqué un comportement très étrange sur un de nos bases de données SQL Server 2005. Nous exécuter un DBCC CHECKDB sur la base de données dans le cadre de notre maintenance nocturne base de données et des nuits le DBCC renvoie erreurs endommagé. La chose étrange est que nous n'apparaît pas défaillances des sommes de contrôle page pendant la journée précédente et si je manuellement exécute DBCC CHECKDB le matin après l'échec de travail de maintenance, les altérations ont disparu. Pouvez vous expliquer ce qui se passe ? Il a été passe pour un mois et je suis concernée que je ne peut pas faire confiance DBCC CHECKDB.

AIl est préférable que vous disposez des DBCC CHECKDB cadre de votre gestion standard et que les sommes de contrôle de page sont activées. Le comportement de vous décrire peut être très déconcertant lorsqu'il se produit, elle ressemble presque à DBCC CHECKDB est ce qui donne des résultats incorrects. Mais ce scénario se produit every si souvent, sont sans indications au moment de l'exécution de corruption, DBCC CHECKDB recherche endommagé et puis plusieurs heures plus tard les altérations sont disparues Lorsque DBCC CHECKDB est exécuté à nouveau. Nous allons ce étape par étape.

Tout d'abord, il est très courant des altérations à déclarer par DBCC CHECKDB que n'ont pas été signalées lors des opérations de base de données ordinaire. Bien que les sommes de contrôle de page soient une méthode très de détection des altérations provoquées par le sous-système d'E / S, elles sont efficaces uniquement lorsque les pages de données sont lues par SQL Server après qu'une altération s'est produit.

Imaginez qu'une page de données a un total de contrôle page appliquée à et il est ensuite endommagé ultérieurement par le sous-système d'E / S. Même si la somme de contrôle page détecte la corruption, il est uniquement lorsque la page est lu dans la mémoire par SQL Server que le total de contrôle de page est validée et la corruption est détectée. Si cette page est jamais lu par SQL Server, la corruption jamais à découvrir. C'est pourquoi il est essentiel pour activer les sommes de contrôle de page et effectuer régulièrement des vérifications de cohérence, vérifications de cohérence lit toutes les pages de la base de données, validation de leurs sommes de contrôle page et découverte de corruption dès que possible.

Dans votre cas, il semble que si les altérations ont été dans les pages de données qui ont été non lus dans le cadre d'opérations de base de données ordinaire et les altérations découverts pas jusqu'à ce que DBCC CHECKDB lire les pages endommagées. Bien qu'il semble que si les checksums page n'a pas détecter la corruption qu'ils doivent, ce n'est pas le cas.

Ensuite, « disparaissent altérations peuvent se produire assez facilement entre les exécutions de DBCC CHECKDB, mais uniquement sur les modifications se produisent entre les les exécutions DBCC deux bases de données. Supposons une page est en effet endommagée et DBCC CHECKDB signale comme étant endommagé. Maintenant que la page est ensuite deallocated à partir d'une table (par exemple, car il devient vide). Un DBCC CHECKDB suivantes ne pas lire cette fois et donc ne pas signaler comme étant endommagé. Lectures de DBCC CHECKDB allouée uniquement des pages (signification qui sont actuellement en cours d'utilisation). Dans votre cas, je suis deviner que le reste du travail de la maintenance nocturne inclut recrée des index ou réorganisez, qui peuvent modifier considérablement l'ensemble des pages qui sont alloués à un objet particulier ou un index. Ce serait compte pour le comportement que vous vous voyez. Une reconstruction d'index deallocates les pages endommagées comme un effet secondaire et ensuite la prochaine DBCC CHECKDB vient revenir en mode minimal.

Pour intercepter les pages endommagées, modifier votre travail de gestion afin qu'il s'arrête si l'étape DBCC CHECKDB échoue. De cette manière, vous serez en mesure de vérifier les altérations et de prendre plus action manuellement.

QNous vous envisagez de mettre à niveau à partir de SQL Server 2000 directement vers SQL Server 2008, ignorer SQL server 2005. Mais je suis soucieux sur tempdb. Cela déjà dû des problèmes pour nous dans SQL Server 2000, que nous avons beaucoup de courte durées tables temporaires. À partir de ce que je comprends, tempdb est beaucoup plus largement utilisé maintenant (dans SQL Server 2005 et SQL Server 2008) et mise en service si spécial a à empêcher la suppression après une mise à niveau des performances. Pouvez vous expliquer pourquoi ce est le cas et que nous doit faire ?

AÀ partir de votre question, je suis deviner que vous avez eu à implémenter l'architecture d'une fichier par processeur tempdb qui est généralement obligatoire lorsque de courte durées des tables temporaires sont créés par plusieurs connexions de base de données. Et vous pouvez ont même avait activer le suivi 1118-indicateur (voir » Améliorations de simultanéité d'accès aux données de la base de données tempdb« Pour plus d'informations sur ce).

Bien qu'il soit le potentiel de plus lourd tempdb Utilisation dans SQL Server 2005 recommence, vous pourriez verrez pas ce sauf si vous utilisez spécifiquement des fonctionnalités qui dépendent de tempdb. Notez que si vous prenez une solution qui a rencontré des problèmes de performances tempdb dans SQL Server 2000, mettre à jour pour SQL Server 2008 sur le même matériel, vous devriez dans de nombreux cas voir les problèmes de performance atténuées légèrement raison de certaines modifications apportées tempdb-spécifique à SQL Server Storage Engine dans SQL Server 2005.

Les fonctionnalités qui font Utilisation intensive de tempdb sur SQL Server 2005 et SQL Server 2008 sont :

  • Opérations d'index en ligne
  • Déclencheurs DML
  • Jeux de résultats multiples actif (MARS)
  • Isolement de capture instantanée (au niveau des transactions et au niveau instruction)

Quatre de ces fonctionnalités utiliser une technologie sous-jacente appelée contrôle de version pour stocker les différentes versions de moment d'enregistrements de données. Plus simplement, ces versions enregistrement sont stockées dans le magasin de version dans tempdb, avec partage la même banque des versions dans la même tempdb de toutes les bases de données utilisateur. Le plus vous utilisez ces fonctionnalités, le heavier l'utilisation de la banque des versions donc tempdb et sera le plus potentiel là sera pour un impact sur les performances.

La clé pour une mise à niveau réussie consiste à implémenter une charge représentative de production sur un système de test avec le nouveau schéma et de mesurer les performances pour éviter les surprises que vous pouvez rencontrer si vous passez directement en production.

Malheureusement, cette discussion est moyen au-delà de la portée de cette colonne à couverture approfondie, mais certaines ressources excellente disponibles que je vous recommande de vous à l'extraction :

QNous vous implémentation un plan de maintenance de base de données nocturne qui inclut l'amélioration des performances d'un index. J'ai entendu que l'option de facteur de remplissage pour les index peut supprimer complètement le besoin de maintenance des index. Est-ce vrai ? Il semble que des index dans notre base de données pâtir de la fragmentation et certains n'est pas. Nous définissez un facteur de remplissage par défaut pour la base de données qui s'appliquent à tous les index et si tel est le cas, quelle valeur doivent nous utiliser ?

ALe paramètre de facteur de remplissage peut bien servir à partiellement réduire le besoin de maintenance des index, mais rarement peut servir pour supprimer complètement la nécessité. En bref, le paramètre de facteur de remplissage force le moteur de stockage pour laissez un certain pourcentage d'espace libre dans les pages d'index en cluster et non cluster lors de leur création ou reconstruits. (Notez que le paramètre de facteur de remplissage n'est pas conservé pendant les opérations régulières sur Insertion/mise à jour/Supprimer). Un taux de remplissage de 90, laisse par exemple, 10 % d'espace libre. Facteurs de remplissage de 0 ou 100 ne laissez aucun espace libre (il a été la source de beaucoup de confusion).

L'idée est qu'espace est laissé dans les pages, qui permet d'enregistrements sur la page pour développer ou de nouveaux enregistrements à insérer dans la page sans provoquer une opération coûteuse, à l'origine de fragmentation appelée un fractionnement de la page. Vous spécifiez un pourcentage d'espace libre pour les pages peuvent devenir plus permanente complets jusqu'à l'opération de maintenance des index suivante, qui réinitialise le facteur de remplissage à nouveau. L'astuce consiste à choisir un pourcentage qui minimise les fractionnements de pages entre les opérations de maintenance des index.

Pour une base de données OLTP (online transaction processing), il n'y a aucune réponse simple, sauf pour choisir un facteur de remplissage pour chaque index basé sur trial et une erreur. Pour les data warehouses, dans laquelle les index ne pas modifier, le facteur de remplissage doit être de 100 % (signification aucun espace libre n'est laissé dans les pages). Il est très rare que le taux de remplissage par défaut pour une base de données est modifié à partir la valeur par défaut à 100 %, car les facteurs de remplissage meilleures pour les différents index sont généralement différents. La rubrique documentation en ligne de SQL Server 2008 » Facteur de remplissage"dispose de beaucoup plus d'informations sur ce.

Une autre option consiste à modifier l'index afin que les fractionnements de pages n'ont pas lieu. Cela peut impliquer la modification de la clé d'index afin que les insertions ne soient pas aléatoires (par exemple, en utilisant ne pas une clé de principale de GUID aléatoire) ou refuser les opérations qui modifier la taille de colonnes de longueur variable.

QNous allons être passage à SQL Server 2008 dès que SP1 provient des et une des fonctionnalités que nous avez hâte à l'utilisation est FILESTREAM, qu'il supprime la limite de taille 2 Go pour les valeurs de colonne. Avant de commencer la version suivante du notre schéma d'utiliser le type de données FILESTREAM de conception, y a-t-il des inconvénients ou problèmes nous doit être conscients de qui peut entraîner des problèmes de production ?

AIl est toujours une idée pour déterminer toutes les caractéristiques d'une nouvelle fonctionnalité avant de créer dans un nouveau schéma ou d'une application, en particulier qui utilise des technologies en dehors de SQL Server, comme c'est le cas avec FILESTREAM active. La plupart des informations vous avez besoin est contenu dans un livre blanc J'AI écrit pour l'équipe SQL Server appelée » Stockage FILESTREAM dans SQL Server 2008." Je vous recommande vivement vous lisez ce document pour une étude complète, mais je résumer les principales zones de préoccupation ici.

Tout d'abord, les données FILESTREAM sont stockées dans le système de fichiers NTFS, plutôt que dans les fichiers de données SQL Server. Il existe plusieurs étapes de configuration qui doivent être prises pour s'assurer que NTFS exécute également avec très grand nombre de fichiers dans un répertoire unique, telles que la désactivation de la génération de nom 8.3, définissant le NTFS cluster taille appropriée et éventuellement séparant la FILESTREAM que données sur séparez disques physiques à partir d'autres données.

Ensuite, vous devez garantir que la taille moyenne des données stockées à l'aide de FILESTREAM sera 1 Mo ou plus. Recherche a affichés que pour formats de données de moins de 256KB et parfois entre 256 Ko et 1 Mo, améliorer les performances peuvent être obtenus en stocker les données directement dans SQL Server plutôt que d'utiliser un mécanisme comme FILESTREAM.

Enfin, vous devez envisager les opérations qui va être effectuées sur les données FILESTREAM. Mises à jour partielles sont prises en pas charge pour données FILESTREAM, donc même mise à jour d'un octet unique d'une valeur de données de 200 Mo produira une valeur de 200 Mo entièrement nouveau en cours de création. À part en cours d'une opération coûteuse, cela peut conduire à la fragmentation au niveau de NTFS, qui peut réduire davantage les performances. Si mises à jour partielles sont commonplace dans l'application, un type de mécanisme de traitement par lots peut être nécessaire pour éviter les mises à jour répétées à une seule valeur FILESTREAM.

Enfin, vous devez envisager la compatibilité inter-fonctionnalité de FILESTREAM avec les technologies de haute disponibilité. FILESTREAM entièrement prend en charge les opérations de sauvegarde et de restauration (y compris dans moment récupération), l'envoi de journaux et réplication. Il n'est, toutefois, pas compatible avec la mise en miroir de base de données dans une manière dans SQL Server 2008. (J'ai été dit que ce sera résolu dans la prochaine version de SQL Server.)

C'est juste un avant-goût des choses à prendre en compte. Pour l'image complète, vous devez lisez le blanc. Comme avec toute nouvelle fonctionnalité, cependant, avant de créer une application, veillez à faire des tests complète pour voir si ses fonctionnalités correspondent à vos besoins. Étant donné que FILESTREAM englobe également stockage NTFS, je souhaitez également ne performances pré-production et tests pour vous assurer que rien ne récupération après incident trips haut lorsque vous accédez en.

S Paul Randal est le directeur Gestion de SQLskills.comet un MVP de SQL Server. Il a travaillé dans l'équipe SQL Server Storage Engine chez Microsoft de 1999 à 2007. Paul écrit DBCC CHECKDB/réparation pour SQL Server 2005 et était responsable pour le moteur de stockage base pendant le développement de SQL Server 2008. Paul est un expert de la récupération après incident, haute disponibilité et la maintenance de base de données et est un présentateur standard à des conférences dans le monde entier. Blogs il à SQLskills.com/blogs/paul.