Gérer les ressources du serveur
10 minutes à lire
tempdb
tempdb est la base de données système qui recueille tous les objets temporaires créé dans SQL Server. Elle est détruite et reconstruite à chaque démarrage de l’instance SQL, à partir de la base de données model, tout comme les bases utilisateurs. Si vous voulez changer une option de tempdb, ou sa taille initiale, vous pouvez modifier ces options dans model. Tempdb est en mode de récupération simple. Cela ne peut être changé, et il n’y a aucune raison de vouloir mettre tempdb dans un mode complet.
tempdb contient non seulement les objets temporaires (tables, procédures, clés de chiffrement, etc.) créés avec les préfixes # ou ##, et les variables de type table, mais on peut également y créer des objets « persistants », comme dans n’importe quelle base de données. Ces objets y seront conservés jusqu’à leur suppression manuelle par un DROP, ou jusqu’au prochain redémarrage de l’instance.
Outre les objets temporaires, tempdb recueille aussi deux autres types de structures : des dépôts de version (version stores) pour des lignes impliquées dans du row versioning (voir section 4.2.1), et des objets internes (internal objects). Le row versioning est utilisé pour quelques fonctionnalités comme le niveau d’isolation SNAPSHOT, les pseudo-tables de déclencheurs, MARS (multiple active result sets), la génération d’index ONLINE et certaines commandes DBCC comme CHECKDB).
Les objets internes permettent de stocker des données intermédiaires pour tous types d’opérations SQL Server (plans de requête, variables LOB ou XML, résultats de curseurs, messages Service Broker en transit, etc.).
Les objets internes et les versions stores ne sont pas inscrits dans le journal de transaction de tempdb. Ils sont optimisés autant que faire se peut. Vous trouverez plus de détails sur leur stockage interne dans le livre blanc « Working with tempdb in SQL Server 2005 » (http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx).
Les objets utilisateurs (tables temporaires, fonctions table et variables de type table) génèrent, eux, des écritures dans le journal de transactions.
Au fil des versions de SQL Server, tempdb apporte de plus en plus d’optimisations, dans la façon de gérer la journalisation des transactions, l’allocation et la déallocation des objets. C’est un problème critique, car tempdb subit une grande quantité de créations et destructions d’objets, contrairement à une base de données utilisateur, où en utilisation normale, il y a très peu de modifications d’objets. Ainsi, lorsque tempdb est très sollicité, à cause par exemple d’une forte utilisation de tables temporaires dans le code SQL, il peut se produire une contention sur les structures du fichier de données de tempdb (dûe aux allocations et déallocations de pages et d’extensions pour accommoder le contenu), ou sur les tables de métadonnées (dûe à un grand nombre de création ou de suppression d’objets). Ce risque de contention est diminué par différentes méthodes (diminution du verrouillage des structures de données, gestion en cache des tables). À partir de SQL Server 2005, les variables de type table et les tables temporaires locales de petite taille sont gardées en cache mémoire, et pour les tables de moins de 8 Mo, la page d’allocation (IAM) et une page de données sont conservées en mémoire au cas où la table serait recrée.
Attention : certaines pratiques empêchent SQL Server de cacher les tables temporaires. La modification de sa structure après création par exemple, ou si elles sont utilisées dans du code SQL dynamique, ou un batch de requêtes plutôt que dans une procédure stockée.
Si malgré cela, une contention se produit dans tempdb, les performances peuvent s’en retrouver sérieusement affectées. C’est une situation rare, rencontrée sur des serveurs très sollicités. Pour détecter une contention, vous pouvez surveiller les attentes à l’aide de la vue de gestion dynamique sys.dm_os_waiting_tasks, qui liste les tâche en attentes. La contention des structures de données se traduit sur des attentes de libération de latches sur des pages de PFS ou de SGAM. Les latches sont des verrous léger de protection d’accès physique (voir section 7.2.1).
SELECT session_id, wait_duration_ms, resource_description
FROM sys.dm_os_waiting_tasks
WHERE wait_type like 'PAGE%LATCH_%' AND resource_description like '2:%'
Des compteurs du moniteur de performances donnent aussi des indications sur les latches en général. Ils se trouvent dans l’objet de compteurs SQL:Latches (référez-vous à la section 5.3).
Vous pouvez lire ce document pour obtenir plus de détails sur l’optimisation de tempdb : http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx
Drapeau de trace 1118 : le document mentionné ci-dessus évoque le drapeau de trace 1118, qui placé globalement, change la méthode d’allocation de nouveaux objets dans tempdb pour éviter la contention sur les SGAM. Ce drapeau de trace est d’usage délicat, et peut être contre-performant. Référez-vous à cet article de blog pour une analyse raisonnée : http://sqlblog.com/blogs/linchi_shea/archive/2007/08/10/reduce-the-contention-on-tempdb-with-trace-flag-1118-take-2.aspx
Taille et utilisation de tempdb
Les objets qui peuvent prendre de la place dans tempdb sont des objets utilisateur volumineux, comme des tables temporaires. Les tables temporaires sont supprimées à la fin de la session qui les a créées. Si une session reste ouverte, et qu’une table temporaire n’a pas été explicitement supprimée à l’aide d’un DROP, elle peut occuper de l’espace un certain temps. C’est donc une habitude importante à prendre, de supprimer une table temporaire lorsqu’on n’en a plus besoin. De même, si la table temporaire est enrôlée dans une transaction, le journal de transactions de tempdb ne pourra se tronquer que lorsque la transaction sera terminée.
Les version stores peuvent aussi occuper une place importante dans tempdb. Ils contiennent chaque ligne impliquée dans un row versioning. Il existe en tout et pour tout deux version stores dans tempdb : l’un est dédié aux opérations sur index online, et l’autre à toutes les utilisations de row versioning. Nous verrons dans le chapitre sur les index ce qu’impliquent les opérations d’index ONLINE, sachez simplement que plus la création de l’index en mode ONLINE prendra de temps, plus le version store est susceptible de grandir. L’autre version store est susceptible de grandir avec le nombre d’opérations impliquant du row versioning, et dans le cas où une transaction est maintenue, qui utilise une fonctionnalité de row versioning.
La vue de gestion dynamique sys.dm_tran_active_snapshot_database_transactions permet de lister toutes les transactions qui génèrent, ou peuvent potentiellement accéder à des versions de lignes. Elle peut vous servir à détecter des transactions longues qui maintiennent des versions :
SELECT *
FROM sys.dm_tran_active_snapshot_database_transactions
ORDER BY elapsed_time_seconds DESC;
La vue de gestion dynamique sys.dm_db_file_space_usage, affiche l’utilisation des fichiers de données. Comme son nom ne l’indique pas, elle s’applique uniquement à tempdb. Donc, quel que soit le contexte de base dans lequel vous l’exécutez, elle affichera les informations de tempdb. Elle donne le nombre de pages dédiées au version stores (version_store_reserved_page_count), aux objets internes (internal_object_reserved_page_count) et aux objets utilisateur (user_object_reserved_page_count), par fichier de données. Pour obtenir le volume en octets, il vous suffit de multiplier ces nombres par 8192.
Exemple :
SELECT
SUM(user_object_reserved_page_count)*8 as user_object_kb,
SUM(internal_object_reserved_page_count)*8 as internal_object_kb,
SUM(version_store_reserved_page_count)*8 as version_store_kb
FROM sys.dm_db_file_space_usage;
-- ou en Mo
SELECT
SUM(user_object_reserved_page_count)*1.0/128 as user_object_mb,
SUM(internal_object_reserved_page_count)*1.0/128 as internal_object_mb,
SUM(version_store_reserved_page_count)*1.0/128 as version_store_mb
FROM sys.dm_db_file_space_usage;
La vue sys.dm_db_session_space_usage est intéressante car elle indique le nombre d’allocations et de déallocations d’objets internes et d’objets utilisateur par session. Si vous souhaitez connaître l’impact d’une procédure ou d’un batch de requêtes sur tempdb, exécutez le code voulu, puis, dans la même session, lancez une requête comme celle-ci :
SELECT *
FROM sys.dm_db_session_space_usage
WHERE session_id = @@SPID;
La vue sys.dm_db_task_space_usage donne la même information par tâche en activité. Elle peut être jointe à sys.dm_exec_requests pour trouver le plan d’exécution :
SELECT
tsu.*,
DB_NAME(er.database_id) as db,
er.cpu_time, er.reads, er.writes, er.row_count,
eqp.query_plan
FROM sys.dm_db_task_space_usage tsu
JOIN sys.dm_exec_requests er ON tsu.session_id = er.session_id
AND tsu.request_id = er.request_id
CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) eqp;
Plus d’informations avec des exemples de supervision vous sont données dans les BOL, sous l’entrée « Résolution des problèmes d’espace disque insuffisant dans tempdb » « Troubleshooting Insufficient Disk Space in tempdb »
Comment changer l’emplacement de tempdb
Vous pouvez modifier l’emplacement des fichiers de données et de journal de tempdb. Comme la base est recréée à chaque démarrage, vous devez appliquer les commandes suivantes, puis redémarrer votre instance, pour que le changement soit effectif.
ALTER DATABASE tempdb MODIFY FILE (name = tempdev, filename =
'E:\Sqldata\tempdb.mdf')
GO
ALTER DATABASE tempdb MODIFY FILE (name = templog, filename =
'E:\Sqldata\templog.ldf')
GO
Après redémarrage du service, vous pouvez exécuter sp_helpfile tempdb ou SELECT * FROM tempdb.sys.database_files pour vérifier que le changement est effectif.
contrôler l’attribution des ressources
le gouverneur de requêtes
SQL Server intègre un contrôleur de coût de requête, qui vous permet d’éviter l’exécution d’instructions qui sont – avant leur exécution – estimées lourdes. Il est nommé le gouverneur de requêtes (query governor). Vous le configurez pour toute l’instance :
exec sp_configure 'show advanced options', 1
reconfigure
exec sp_configure 'query governor cost limit', 10
reconfigure
exec sp_configure 'show advanced options', 0
reconfigure
Ou pour une session :
SET QUERY_GOVERNOR_COST_LIMIT 10;
Le nombre passé en paramètre correspond au nombre de secondes estimées par le moteur SQL, par rapport à une configuration de machine de test de Microsoft, il ne s’agit pas de vraies secondes de votre serveur. L’estimation elle-même ne compte bien entendu pas les attentes diverses auxquelles l’exécution peut être soumise. Il s’agit donc d’une estimation très... estimée, mais cela peut offire un premier niveau de contrôle de charge de votre serveur.
Si une instruction est demandée, dont l’estimation dépasse ce nombre, la session recevra en retour le message d’erreur ci-dessous, et l’exécution sera annulée avant même de commencer.
Msg 8649, Level 17, State 1\...
The query has been canceled because the estimated cost of this query
(1090057) exceeds the configured threshold of 10. Contact the system
administrator.
Au programme client de récupérer l’erreur...
SQL Server 2008 : le gouverneur de ressources
SQL Server 2008 offre un outil d’attribution de ressources système (CPU et mémoire), par sessions
Fig. 4.12 - configurer le gouverneur de ressources
Nous pouvons aussi créer un pool par code :
CREATE RESOURCE POOL [intrus]
WITH( min_cpu_percent=0,
max_cpu_percent=20,
min_memory_percent=0,
max_memory_percent=10)
Lors de chaque modification du gouverneur de ressources, vous devez activer la modification :
ALTER RESOURCE GOVERNOR RECONFIGURE;
Ainsi, le gouverneur de ressources est activé. Si vous souhaitez le désactiver (les sessions ne seront ainsi plus contrôlées) :
ALTER RESOURCE GOVERNOR DISABLE;
Un ALTER RESOURCE GOVERNOR RECONFIGURE le réactive.
Les ressources sont partagées entre les pools au pro rata des valeurs indiquées, selon un algorithme décrit dans l’entrée des BOL 2008 « Resource Governor Concepts » (Concepts du gouverneur de ressources).
Dans les pools sont placés des groupes de charges de travail. Ces groupes rassemblent simplement des instructions SQL lancées sur le serveur, qui sont attribuées à ce groupe automatiquement selon des règles de classification. Exemple de création de groupe :
CREATE WORKLOAD GROUP [méchantes requêtes]
WITH( group_max_requests=10,
importance=Low,
request_max_cpu_time_sec=0,
request_max_memory_grant_percent=25,
request_memory_grant_timeout_sec=0,
max_dop=1)
USING [intrus]
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
Comme vous le voyez, vous pouvez déterminer l’importance du groupe, et le degré de parallélisme (max_dop).
Vous créez ensuite une fonction de classification, que le gouverneur de ressources va appeler pour attribuer chaque nouvelle ouverture de session à un groupe. Vous ne pouvez faire qu’une seule fonction, qui effectuera le routage global. En l’absence de fonction de classification, toutes les sessions utilisateurs seront attribuées au groupe default. Lorsque la fonction est écrite, vous la déclarez au gouverneur de ressources. Exemple :
CREATE FUNCTION dbo.rgclassifier_v01() RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
DECLARE @grp_name AS SYSNAME
IF (SUSER_NAME() = 'Nicolas') or (APP_NAME() LIKE '%Elysee%')
SET @grp_name = 'méchantes requêtes'
ELSE
SET @grp_name = NULL
RETURN @grp_name
END
GO
-- Register the classifier function with Resource Governor
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_v01)
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO
Lorsque la fonction retourne NULL, la session est attribuée au groupe par défaut.
L’appartenance des sessions au groupe peut se requêter ainsi :
SELECT *
FROM sys.dm_exec_sessions es
JOIN sys.resource_governor_workload_groups rswg
ON es.group_id = rswg.group_id;
Vous pouvez déplacer dynamiquement un groupe d’un pool à un autre sans perturber les sessions actives :
ALTER WORKLOAD GROUP [méchantes requêtes]
USING [default];
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO
Vous disposez de compteurs de performances indiquant l’état du gouverneur de ressources (entrée BOL 2008 « Resource Governor Monitoring »), ainsi que des vues de métadonnées et de gestion dynamique (entrée BOL 2008 « Resource Governor DDL and System Views »).