juillet
2010
Un article de zinzineti
Avant toute opération d’optimisation d’une base de données, il est indispensable de vérifier rapidement les options automatiques de configuration de la base. Les incontournables :
–> AUTO_SHRINK = OFF
–> AUTO_CREATE_STATISTICS = ON
–> AUTO_UPDATE_STATISTICS = ON
–> AUTO_UPDATE_STATISTICS_ASYNC = OFF
/*********************************************************************************************
— DESCRIPTION : Afficher les options de configuration des bases données
— d’une instance SQL Serveur 2005 – 2008
— Auteur : Etienne ZINZINDOHOUE
*********************************************************************************************/
SELECT name AS [Nom] -- Nom de la base de données
,create_date AS [Date_Creation] -- date de création de la base de données
,Case [compatibility_level]
WHEN '80' THEN 'SQL 2000'
WHEN '90' THEN 'SQL 2005'
WHEN '100'THEN 'SQL 2008'
ELSE 'Autre Version'
END AS [Niveau_Compatibilité] -- Niveau de compatibilité
,collation_name AS[Collation] -- Collation
,CASE user_access_desc
WHEN 'SINGLE_USER' THEN 'Accès autorisé à 1 seul utilisateur à la fois'
WHEN 'MULTI_USER' THEN 'Accès autorisé à +sieurs utilisateurs à la fois'
WHEN 'RESTRICTED_USER' THEN 'Accès autorisé aux membres db_owner ou dbcreator ou sysadmin'
ELSE ''
END AS [Accès] -- Contrôle d'accès à la base
,Case is_read_only
WHEN '0' THEN 'NON'
WHEN '1' THEN 'OUI'
ELSE''
END AS [Base en lecture seule ?] -- la base est-elle en lecture seule ?
,Case is_auto_shrink_on
WHEN '0' THEN 'NON'
WHEN '1' THEN 'OUI'
ELSE ''
END AS [Auto Shrink(Réduction Automatique) activée?] -- Auto Shrink doit être à NON c-a-d OFF
,Case state_desc
WHEN 'ONLINE' THEN 'accessible'
WHEN 'OFFLINE' THEN 'inaccessible'
WHEN 'EMERGENCY' THEN 'accessible uniquement aux membres sysadmin'
ELSE '/!\ état bizarre'
END [Etat de la base] -- état de la base
,recovery_model_desc AS [Mode restauration] -- Mode de restauration
,Case page_verify_option_desc
WHEN 'CHECKSUM' THEN 'OUI'
WHEN 'TORN_PAGE_DETECTION' THEN 'OUI'
WHEN 'NONE' THEN 'NON'
ELSE ''
END AS [Vérifie page endommagée ?] -- Détecte les pages de base de données endommagées résultant d'erreurs de chemin d'E/S disque
,Case is_auto_create_stats_on
WHEN '0' THEN 'NON'
WHEN '1' THEN 'OUI'
ELSE ''
END AS [AUTO_CREATE_STATISTICS activé ?] -- crée automatiquement statistiques manquantes pour optimisation de la requête. Doit être à OUI c-a-d ON
,Case is_auto_update_stats_on
WHEN '0' THEN 'NON'
WHEN '1' THEN 'OUI'
ELSE ''
END AS [AUTO_UPDATE_STATISTICS activé ?] -- Mis à jour statistiques obsolètes, durant l'optimisation de la requête. Doit être à OUI c-a-d ON
,Case is_fulltext_enabled
WHEN '0' THEN 'NON'
WHEN '1' THEN 'OUI'
ELSE ''
END AS [Full text activé ?] -- Full text
,Case is_published
WHEN '0' THEN 'NON'
WHEN '1' THEN 'OUI'
ELSE ''
END AS [Publication réplication ?] -- participe à la réplication
,Case is_subscribed
WHEN '0' THEN 'NON'
WHEN '1' THEN 'OUI'
ELSE ''
END AS [Abonnement replication ?] -- participe à la réplication
,Case is_merge_published
WHEN '0' THEN 'NON'
WHEN '1' THEN 'OUI'
ELSE ''
END AS [Publication réplication fusion ?] -- participe à la réplication
,Case is_distributor
WHEN '0' THEN 'NON'
WHEN '1' THEN 'OUI'
ELSE ''
END AS [Distributeur réplication ?] -- participe à la réplication
,Case is_broker_enabled
WHEN '0' THEN 'NON'
WHEN '1' THEN 'OUI'
ELSE ''
END AS [Service Broker activé ?] -- Service brocké activé
,Case log_reuse_wait
WHEN '0' THEN 'NON'
ELSE 'OUI'
END AS [y-a-t-il une transaction active ?] -- Existe-il des transactions en cours ?
FROM sys.databases
--Afficher les options pour une base en particulier
--WHERE name = 'MA_BASE'
— Pour modifier par exemple l’option AUTO_SHRINK
–ALTER DATABASE [MA_BASE] SET AUTO_SHRINK OFF;
/*********************************************************************************************
— DESCRIPTION : Afficher les options de configuration des bases données
— d’une instance SQL Serveur 2005 – 2008
— Auteur : Etienne ZINZINDOHOUE
*********************************************************************************************/