none
TempDB saturé : troubleshooting / Dimensionnement ? RRS feed

  • Question

  • Bonjour,

    Notre prestataire applicatif a réalisé des imports de données.

    Coincidence ou non :le tempDB est saturé (100 Go)

    Vu qu'ils n'ont pas d'admin SQL Server, ils vont nous demander certainement d'augmenter la taille.

    1) Comment dimensionner ce TempDB ? Comment connaitre la taille idoine sans céder aux "vous pouvez augmenter la partition ?" des developpeurs ?

    2) Shrink ? Augmentation de la taille ? Comment décidé t-on de la méthode employée ?

    Merci

    mercredi 27 avril 2022 17:10

Réponses

  • Bonjour

    Il n'y a pas de règle matématique magique pour déterminer la taille de la TempDB.
    Et pour cause...
    La TempDB pourrati se subdiviser en plusieurs "sous-ensembles":

    • Les user_objects : en gros les tables temporaires que l'on peut créer via du code
    • Les internal_objects qui sont liés aux requêtes, comme le résultat de tris ou de jointures que SQL Server doit flusher sur disque par manque de mémoire / choix du query optimiser
    • Le version store qui permet de rendre accéssible des enregistremetn en cours de modification en renvoyant une version antérieure (mais toujours valide car il n'y a pas eu de commit / rollback de la transaction qui a posé les verrous). Pour résumer, le RCSI et le Snapshot Isolation

    Bref, comment donc estimer la taille nécéssaire ? La jouer au dés. Car on pourrait imaginer qu'à un instant T, on créé un table # ou ## pour recopier 100Gb ou 500GB de données d'une table de production. Et au même moment un utilisateur qui va faire uen jointure de plusieurs tables de quelques dizaines de GB (pour ne pas dire plus volumineux) et pour couronner le tout la génération de plusieurs versions d'enregistrements sur un grand nombre d'enregistrements ... 

    La calculatrice magique n'existe pas, désolé.

    Si vous souhaitez suivre quel "store" consomme de l'espace, la requête suivant est faite pour cela.

    SELECT
    SUM (user_object_reserved_page_count)*8     as usr_obj_kb,
    SUM (internal_object_reserved_page_count)*8 as internal_obj_kb,
    SUM (version_store_reserved_page_count)*8   as version_store_kb,
    SUM (unallocated_extent_page_count)*8       as freespace_kb,
    SUM (mixed_extent_page_count)*8             as mixedextent_kb
    FROM sys.dm_db_file_space_usage


    La question est donc : quelle méthode a été utilisée pour l'import de données? Utilisation d'une table temporaire à un moment donné ?
    Quels traitements étaient actifs à ce moment là ?
    Ceci dit, uen fois l'opération terminée, il est possible de stopper l'instance, supprimer les ficiers de data de la base TempDB et de rédémarrer l'instance. Il vont être recréés, avec une taille correspondant aux settings d'avant AutoGrowth.

    Si vous souhaitez tracer les augmentations, et les requêtes en cause : un petit xEvent que je déploie fréquement:

        CREATE EVENT SESSION [TempDBAutogrowth] ON SERVER
        ADD EVENT sqlserver.database_file_size_change(
            ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text)
            WHERE ([database_id]=(2) AND [session_id]>(50))),
        ADD EVENT sqlserver.databases_log_file_used_size_changed(
            ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text)
            WHERE ([database_id]=(2) AND [session_id]>(50)))
        ADD TARGET package0.event_file(SET filename=N'TempDBAutogrowth',max_file_size=(50),max_rollover_files=(10))
        WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=ON)


    Cdlt
    Christophe


    Christophe LAPORTE - Independent Consultant & Trainer - SQL Server MVP-MCM

    vendredi 15 juillet 2022 14:04

Toutes les réponses

  • Bonsoir  Amstrad44,

    Voici deux articles Microsoft qui vous aideront dans vos actions : 

    A bientôt

    Alexis


    Microsoft propose ce service gratuitement, dans le but d'aider les utilisateurs et d'élargir les connaissances générales liées aux produits et technologies Microsoft. Ce contenu est fourni « tel quel » et il n'implique aucune responsabilité de la part de Microsoft. S'il vous plaît n'oubliez pas de « Marquer comme réponse » les réponses qui ont résolu votre problème. C'est une voie commune pour reconnaître ceux qui vous ont aidé, et rend plus facile l’accès aux solutions.

    mercredi 27 avril 2022 17:29
    Modérateur
  • Bonsoir Alexis,

    Merci pour ces deux articles mais je ne vois pas comment on détermine la taille de la partition dédiée au TempDB.

    Quelles sont les bonnes pratiques ?

    Merci

    jeudi 28 avril 2022 00:41
  • Bonjour

    Il n'y a pas de règle matématique magique pour déterminer la taille de la TempDB.
    Et pour cause...
    La TempDB pourrati se subdiviser en plusieurs "sous-ensembles":

    • Les user_objects : en gros les tables temporaires que l'on peut créer via du code
    • Les internal_objects qui sont liés aux requêtes, comme le résultat de tris ou de jointures que SQL Server doit flusher sur disque par manque de mémoire / choix du query optimiser
    • Le version store qui permet de rendre accéssible des enregistremetn en cours de modification en renvoyant une version antérieure (mais toujours valide car il n'y a pas eu de commit / rollback de la transaction qui a posé les verrous). Pour résumer, le RCSI et le Snapshot Isolation

    Bref, comment donc estimer la taille nécéssaire ? La jouer au dés. Car on pourrait imaginer qu'à un instant T, on créé un table # ou ## pour recopier 100Gb ou 500GB de données d'une table de production. Et au même moment un utilisateur qui va faire uen jointure de plusieurs tables de quelques dizaines de GB (pour ne pas dire plus volumineux) et pour couronner le tout la génération de plusieurs versions d'enregistrements sur un grand nombre d'enregistrements ... 

    La calculatrice magique n'existe pas, désolé.

    Si vous souhaitez suivre quel "store" consomme de l'espace, la requête suivant est faite pour cela.

    SELECT
    SUM (user_object_reserved_page_count)*8     as usr_obj_kb,
    SUM (internal_object_reserved_page_count)*8 as internal_obj_kb,
    SUM (version_store_reserved_page_count)*8   as version_store_kb,
    SUM (unallocated_extent_page_count)*8       as freespace_kb,
    SUM (mixed_extent_page_count)*8             as mixedextent_kb
    FROM sys.dm_db_file_space_usage


    La question est donc : quelle méthode a été utilisée pour l'import de données? Utilisation d'une table temporaire à un moment donné ?
    Quels traitements étaient actifs à ce moment là ?
    Ceci dit, uen fois l'opération terminée, il est possible de stopper l'instance, supprimer les ficiers de data de la base TempDB et de rédémarrer l'instance. Il vont être recréés, avec une taille correspondant aux settings d'avant AutoGrowth.

    Si vous souhaitez tracer les augmentations, et les requêtes en cause : un petit xEvent que je déploie fréquement:

        CREATE EVENT SESSION [TempDBAutogrowth] ON SERVER
        ADD EVENT sqlserver.database_file_size_change(
            ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text)
            WHERE ([database_id]=(2) AND [session_id]>(50))),
        ADD EVENT sqlserver.databases_log_file_used_size_changed(
            ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text)
            WHERE ([database_id]=(2) AND [session_id]>(50)))
        ADD TARGET package0.event_file(SET filename=N'TempDBAutogrowth',max_file_size=(50),max_rollover_files=(10))
        WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=ON)


    Cdlt
    Christophe


    Christophe LAPORTE - Independent Consultant & Trainer - SQL Server MVP-MCM

    vendredi 15 juillet 2022 14:04