{"id":818,"date":"2013-02-24T18:05:24","date_gmt":"2013-02-24T17:05:24","guid":{"rendered":"http:\/\/www.sqlserver.fr\/blog\/?p=818"},"modified":"2026-05-02T14:30:59","modified_gmt":"2026-05-02T12:30:59","slug":"taille-des-transactions","status":"publish","type":"post","link":"https:\/\/www.sqlserver.fr\/blog\/taille-des-transactions\/","title":{"rendered":"Taille des transactions"},"content":{"rendered":"

J’ai tendance \u00e0 r\u00e9p\u00e9ter \u00e0 l’envi que les op\u00e9rations dans SQL Server doivent \u00eatre effectu\u00e9es au maximum en ensembliste plut\u00f4t qu’en unitaire. Cela concerne aussi les transactions qui, \u00e0 partir du moment o\u00f9 cela r\u00e9pond au fonctionnel, ne doivent pas \u00eatre d’une granularit\u00e9 trop fine.<\/p>\n

Tout comme il vaut mieux modifier une fois mille enregistrements que mille fois un enregistrement, il vaut mieux avoir une transaction de mille instructions que mille transactions de une instruction.<\/p>\n

Pour montrer les effets de trop nombreuses transaction, je vais consid\u00e9rer une table dans laquelle je dois ins\u00e9rer 50000 enregistrements et comparer l’action de ces nombreuses insertions\u00a0avec une transaction par insertion d’une part et avec une transaction globale d’autre part.<\/p>\n

Tout d’abord, plantons le d\u00e9cor, en cr\u00e9ant une base et une table. Nous affecterons notamment \u00e0 la base un fichier de journal de transactions suffisamment volumineux pour que le test ne soit pas perturb\u00e9 pas des grossissements automatiques.<\/p>\n

USE master;\r\nGO\r\nIF EXISTS( SELECT *\r\n             FROM sys.databases\r\n             WHERE name = 'TestLog'\r\n         )\r\n    BEGIN\r\n        DROP DATABASE TestLog\r\n    END;\r\nGO\r\nCREATE DATABASE TestLog;\r\nGO\r\nALTER DATABASE TestLog SET RECOVERY SIMPLE;\r\nGO\r\nALTER DATABASE TestLog MODIFY FILE( NAME = N'TestLog_log' , SIZE = 1024000 KB , MAXSIZE = UNLIMITED , FILEGROWTH = 102400 KB\r\n                                  );\r\nGO\r\nUSE TestLog;\r\nGO\r\nCREATE TABLE Donnees( Id int PRIMARY KEY\r\n                             IDENTITY , \r\n                      Donnee varchar( 20\r\n                                    )\r\n                    );\r\nGO<\/pre>\n
Ensuite, mettons la base en mode de r\u00e9cup\u00e9ration complet, afin de se pr\u00e9munir des purges du journal de transactions, et de bien pouvoir consulter toutes les informations \u00e9crites dans ce journal lors du test.<\/p>\n
CHECKPOINT;\r\nALTER DATABASE TestLog SET RECOVERY FULL;\r\nGO<\/pre>\n
Ensuite, lan\u00e7ons un script qui, en boucle, effectuera 50000 insertions en base. Pr\u00e9alablement, on remettra \u00e0 z\u00e9ro le compteur des attentes internes SQL Server. A la fin du processus, on mesurera la dur\u00e9e du test,\u00a0les attentes principales, ainsi que les principaux types d’op\u00e9rations enregistr\u00e9es dans le journal de transactions avec la volum\u00e9trie que cela repr\u00e9sente.<\/p>\n
DBCC SQLPERF( \"sys.dm_os_wait_stats\" , CLEAR\r\n            );\r\n\r\nSET NOCOUNT ON;\r\nDECLARE\r\n   @t0 datetime = GETDATE(\r\n                         );\r\nDECLARE\r\n   @i int;\r\nSET @i = 0;\r\nWHILE @i < 50000\r\n    BEGIN\r\n        INSERT INTO Donnees( Donnee\r\n                           )\r\n        VALUES( 'Test'\r\n              );\r\n        SET @i+=1;\r\n    END;\r\nSELECT DATEDIFF( ms , @t0 , GETDATE(\r\n                                   )\r\n               )AS [Dur\u00e9e op\u00e9ration];\r\n\r\nWITH [Waits]\r\n    AS ( SELECT wait_type , \r\n                wait_time_ms \/ 1000.0 AS WaitS , \r\n                (wait_time_ms - signal_wait_time_ms) \/ 1000.0 AS ResourceS , \r\n                signal_wait_time_ms \/ 1000.0 AS SignalS , \r\n                waiting_tasks_count AS WaitCount , \r\n                100.0 * wait_time_ms \/ SUM( wait_time_ms\r\n                                          )OVER(\r\n                                               )AS Percentage , \r\n                ROW_NUMBER(\r\n                          )OVER( ORDER BY wait_time_ms DESC\r\n                               )AS RowNum\r\n           FROM sys.dm_os_wait_stats\r\n           WHERE wait_type NOT IN( N'CLR_SEMAPHORE' , N'LAZYWRITER_SLEEP' , N'RESOURCE_QUEUE' , N'SQLTRACE_BUFFER_FLUSH' , N'SLEEP_TASK' , N'SLEEP_SYSTEMTASK' , N'WAITFOR' , N'HADR_FILESTREAM_IOMGR_IOCOMPLETION' , N'CHECKPOINT_QUEUE' , N'REQUEST_FOR_DEADLOCK_SEARCH' , N'XE_TIMER_EVENT' , N'XE_DISPATCHER_JOIN' , N'LOGMGR_QUEUE' , N'FT_IFTS_SCHEDULER_IDLE_WAIT' , N'BROKER_TASK_STOP' , N'CLR_MANUAL_EVENT' , N'CLR_AUTO_EVENT' , N'DISPATCHER_QUEUE_SEMAPHORE' , N'TRACEWRITE' , N'XE_DISPATCHER_WAIT' , N'BROKER_TO_FLUSH' , N'BROKER_EVENTHANDLER' , N'FT_IFTSHC_MUTEX' , N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP' , N'DIRTY_PAGE_POLL' , N'SP_SERVER_DIAGNOSTICS_SLEEP'\r\n                                 )\r\n             AND wait_time_ms\r\n                 > \r\n                 0.\r\n       )\r\n    SELECT W1.wait_type AS WaitType , \r\n           CAST( W1.WaitS AS decimal( 14 , 2\r\n                                    )\r\n               )AS Wait_S , \r\n           CAST( W1.ResourceS AS decimal( 14 , 2\r\n                                        )\r\n               )AS Resource_S , \r\n           CAST( W1.SignalS AS decimal( 14 , 2\r\n                                      )\r\n               )AS Signal_S , \r\n           W1.WaitCount AS WaitCount , \r\n           CAST( W1.Percentage AS decimal( 4 , 2\r\n                                         )\r\n               )AS Percentage , \r\n           CAST( W1.WaitS \/ W1.WaitCount AS decimal( 14 , 4\r\n                                                   )\r\n               )AS AvgWait_S , \r\n           CAST( W1.ResourceS \/ W1.WaitCount AS decimal( 14 , 4\r\n                                                       )\r\n               )AS AvgRes_S , \r\n           CAST( W1.SignalS \/ W1.WaitCount AS decimal( 14 , 4\r\n                                                     )\r\n               )AS AvgSig_S\r\n      FROM\r\n           Waits AS W1 INNER JOIN Waits AS W2\r\n           ON W2.RowNum\r\n              <= \r\n              W1.RowNum\r\n      WHERE W1.WaitCount\r\n            > \r\n            0.\r\n      GROUP BY W1.RowNum , \r\n               W1.wait_type , \r\n               W1.WaitS , \r\n               W1.ResourceS , \r\n               W1.SignalS , \r\n               W1.WaitCount , \r\n               W1.Percentage\r\n      HAVING SUM( W2.Percentage\r\n                ) - W1.Percentage\r\n             < \r\n             95; -- percentage threshold\r\nGO\r\n\r\nSELECT TOP 10 Operation , \r\n              COUNT( *\r\n                   )AS [Nb op\u00e9rations] , \r\n              SUM( [Log Record Length]\r\n                 )AS [Volume (bytes)]\r\n  FROM fn_dblog( NULL , NULL\r\n               )\r\n  GROUP BY Operation\r\n  ORDER BY SUM( [Log Record Length]\r\n              )DESC;\r\nSELECT SUM( [Log Record Length]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )AS [Volume \u00e9criture Log]\r\n\u00a0 FROM fn_dblog( NULL , NULL\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 );<\/span>\r\n\r\nGO<\/pre>\n
Les r\u00e9sultats parlent d’eux-m\u00eames :<\/p>\n
\"Resultat_50000Transactions\"<\/a><\/p>\n
L’insertion dure 17,6 secondes, et 99,97 du temps est pass\u00e9 sur des attentes WRITELOG, c’est-\u00e0-dire des \u00e9critures dans le journal de transactions. On a 50134 op\u00e9rations de d\u00e9but de transaction\u00a0et autant de fins de transactions. R\u00e9unis, les enregistrements de ces deux types d’op\u00e9rations repr\u00e9sentent la moiti\u00e9 de la volum\u00e9trie ins\u00e9r\u00e9e.<\/p>\n
Mais pourquoi a-t-on autant de transactions, alors que nous n’en avons explicitement ouvert aucune ? Et bien justement parce que lorsqu’aucune transaction n’est explicitement ouverte, alors chaque instruction se retrouve encadr\u00e9e dans une transaction implicite. Nous avons donc 50000 transactions implicites, plus quelques transactions internes li\u00e9es \u00e0 la mise \u00e0 jour des index (ajouts de pages de stockage, …).<\/p>\n
Au final, cela repr\u00e9sente 16Mo de journal, \u00e9crit petit morceau par petit morceau (une transaction n’est valid\u00e9e que lorsque le syst\u00e8me de stockage confirme avoir \u00e9crit les informations correspondantes dans le journal).<\/p>\n
Maintenant, cherchons \u00e0 modifier le script en int\u00e9grant l’ensemble des 50000 insertions dans une seule et m\u00eame transaction.<\/p>\n
DECLARE\r\n   @i int;\r\nSET @i = 0;\r\nBEGIN TRANSACTION;\r\nWHILE @i < 50000\r\n    BEGIN\r\n        INSERT INTO Donnees( Donnee\r\n                           )\r\n        VALUES( 'Test'\r\n              );\r\n        SET @i+=1;\r\n    END;\r\nCOMMIT TRANSACTION;<\/pre>\n
La diff\u00e9rence en termes de dur\u00e9e d’ex\u00e9cution et d’attentes est flagrante :<\/p>\n
\"Resultat_1Transaction\"<\/a><\/p>\n
Nous avons totalement gomm\u00e9 les attentes WRITELOG (\u00e9tant donn\u00e9 que l’on a seulement demand\u00e9 au syst\u00e8me de faire une \u00e9criture massive dans le journal, au lieu de n micro-\u00e9critures), et la dur\u00e9e totale du traitement a \u00e9t\u00e9 r\u00e9duite dans un ratio sup\u00e9rieur \u00e0 10 !<\/p>\n
Il est donc au final tr\u00e8s important de comprendre que de multiples petites transactions peuvent tr\u00e8s fortement ralentir un syst\u00e8me, et que si on peut regrouper des \u00e9critures multiples dans une transaction englobante, il ne faut surtout pas s’en priver…<\/p>\n","protected":false},"excerpt":{"rendered":"
J’ai tendance \u00e0 r\u00e9p\u00e9ter \u00e0 l’envi que les op\u00e9rations dans SQL Server doivent \u00eatre effectu\u00e9es au maximum en ensembliste plut\u00f4t qu’en unitaire. Cela concerne aussi les transactions qui, \u00e0 partir du moment o\u00f9 cela r\u00e9pond au fonctionnel, ne doivent pas … Continuer la lecture →<\/span><\/a><\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-818","post","type-post","status-publish","format-standard","hentry","category-article_sql"],"_links":{"self":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/818","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/comments?post=818"}],"version-history":[{"count":17,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/818\/revisions"}],"predecessor-version":[{"id":1936,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/818\/revisions\/1936"}],"wp:attachment":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/media?parent=818"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/categories?post=818"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/tags?post=818"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}