{"id":513,"date":"2012-01-18T13:01:00","date_gmt":"2012-01-18T13:01:00","guid":{"rendered":"\/blogs\/joe\/post\/A-small-scale-test-measuring-the-impact-of-data-compression-on-the-transaction-log.aspx"},"modified":"2012-01-18T13:01:00","modified_gmt":"2012-01-18T13:01:00","slug":"a-small-scale-test-measuring-the-impact-of-data-compression-on-the-transaction-log","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/joe\/a-small-scale-test-measuring-the-impact-of-data-compression-on-the-transaction-log\/","title":{"rendered":"A small-scale test measuring the impact of data compression on the transaction log"},"content":{"rendered":"

\nI received a question today about the impact of data compression on the transaction log.  <\/span>While most of the time we talk about data compression from a data page and memory utilization perspective, I hadn’t actually directly tested the impact to transaction logging and I wanted to see it for myself.<\/font>  <\/font><\/span><\/font>\n<\/p>\n

\nHere is just a very small scale test using data from AdventureWorks.HumanResources.Employee into two tables – one with a clustered index that is not using compression and one that is using page compression (I used a separate database just to keep things clean):<\/font><\/font>\n<\/p>\n

\nUSE<\/font><\/font><\/span> [CompressionTest]<\/font>;<\/font><\/span><\/span><\/font>\n<\/p>\n

\nGO<\/font><\/font><\/span>\n<\/p>\n

\n<\/font><\/font><\/span>\n<\/p>\n

\nCREATE<\/font><\/font><\/span> TABLE<\/font><\/span> [dbo].<\/font><\/span>[Employee]<\/font>(<\/font><\/span><\/span><\/font>\n<\/p>\n

\n      <\/font><\/span>[EmployeeID] [int] IDENTITY<\/font><\/span>(<\/font><\/span>1,<\/font><\/span>1)<\/font><\/span> NOT<\/font><\/span> <\/font>NULL,<\/font><\/span><\/font><\/span>\n<\/p>\n

\n      <\/font><\/span>[NationalIDNumber] [nvarchar](<\/font><\/span>15)<\/font><\/span> NOT<\/font><\/span> <\/font>NULL,<\/font><\/span><\/font><\/span>\n<\/p>\n

\n      <\/font><\/span>[ContactID] [int] NOT<\/font><\/span> <\/font>NULL,<\/font><\/span><\/font><\/span>\n<\/p>\n

\n      <\/font><\/span>[LoginID] [nvarchar](<\/font><\/span>256)<\/font><\/span> NOT<\/font><\/span> <\/font>NULL,<\/font><\/span><\/font><\/span>\n<\/p>\n

\n      <\/font><\/span>[ManagerID] [int] <\/font>NULL,<\/font><\/span><\/font><\/span>\n<\/p>\n

\n      <\/font><\/span>[Title] [nvarchar](<\/font><\/span>50)<\/font><\/span> NOT<\/font><\/span> <\/font>NULL,<\/font><\/span><\/font><\/span>\n<\/p>\n

\n      <\/font><\/span>[BirthDate] [datetime] NOT<\/font><\/span> <\/font>NULL,<\/font><\/span><\/font><\/span>\n<\/p>\n

\n      <\/font><\/span>[MaritalStatus] [nchar](<\/font><\/span>1)<\/font><\/span> NOT<\/font><\/span> <\/font>NULL,<\/font><\/span><\/font><\/span>\n<\/p>\n

\n      <\/font><\/span>[Gender] [nchar](<\/font><\/span>1)<\/font><\/span> NOT<\/font><\/span> <\/font>NULL,<\/font><\/span><\/font><\/span>\n<\/p>\n

\n      <\/font><\/span>[HireDate] [datetime] NOT<\/font><\/span> <\/font>NULL,<\/font><\/span><\/font><\/span>\n<\/p>\n

\n      <\/font><\/span>[SalariedFlag] [bit] NOT<\/font><\/span> <\/font>NULL,<\/font><\/span><\/font><\/span>\n<\/p>\n

\n      <\/font><\/span>[VacationHours] [smallint] NOT<\/font><\/span> <\/font>NULL,<\/font><\/span><\/font><\/span>\n<\/p>\n

\n      <\/font><\/span>[SickLeaveHours] [smallint] NOT<\/font><\/span> <\/font>NULL,<\/font><\/span><\/font><\/span>\n<\/p>\n

\n      <\/font><\/span>[CurrentFlag] [bit] NOT<\/font><\/span> <\/font>NULL,<\/font><\/span><\/font><\/span>\n<\/p>\n

\n      <\/font><\/span>[rowguid] [uniqueidentifier] NOT<\/font><\/span> <\/font>NULL,<\/font><\/span><\/font><\/span>\n<\/p>\n

\n      <\/font><\/span>[ModifiedDate] [datetime] NOT<\/font><\/span> <\/font>NULL,<\/font><\/span><\/font><\/span>\n<\/p>\n

\n      <\/font><\/span>[EncryptedNationalIDNumber2] [varbinary](<\/font><\/span>128)<\/font><\/span> <\/font>NULL<\/font><\/span><\/font><\/span>\n<\/p>\n

\n)<\/font><\/font><\/span> ON<\/font><\/span> [PRIMARY]<\/font>;<\/font><\/span><\/span><\/font>\n<\/p>\n

\nGO<\/font><\/font><\/span>\n<\/p>\n

\n<\/font><\/font><\/span>\n<\/p>\n

\nCREATE<\/font><\/font><\/span> CLUSTERED<\/font><\/span> INDEX<\/font><\/span> [IX_Employee] <\/font><\/span><\/font>\n<\/p>\n

\nON<\/font><\/font><\/span> [dbo].<\/font><\/span>[Employee] <\/font><\/span><\/font>\n<\/p>\n

\n(<\/font><\/font><\/span>\n<\/p>\n

\n      <\/font><\/span>[EmployeeID] <\/font>ASC<\/font><\/span><\/font><\/span>\n<\/p>\n

\n)<\/font><\/font><\/span>\n<\/p>\n

\nON<\/font><\/font><\/span> [PRIMARY]<\/font>;<\/font><\/span><\/span><\/font>\n<\/p>\n

\nGO<\/font><\/font><\/span>\n<\/p>\n

\n<\/font><\/font><\/span>\n<\/p>\n

\nCREATE<\/font><\/font><\/span> TABLE<\/font><\/span> [dbo].<\/font><\/span>[Employee_Compressed]<\/font>(<\/font><\/span><\/span><\/font>\n<\/p>\n

\n      <\/font><\/span>[EmployeeID] [int] IDENTITY<\/font><\/span>(<\/font><\/span>1,<\/font><\/span>1)<\/font><\/span> NOT<\/font><\/span> <\/font>NULL,<\/font><\/span><\/font><\/span>\n<\/p>\n

\n      <\/font><\/span>[NationalIDNumber] [nvarchar](<\/font><\/span>15)<\/font><\/span> NOT<\/font><\/span> <\/font>NULL,<\/font><\/span><\/font><\/span>\n<\/p>\n

\n      <\/font><\/span>[ContactID] [int] NOT<\/font><\/span> <\/font>NULL,<\/font><\/span><\/font><\/span>\n<\/p>\n

\n      <\/font><\/span>[LoginID] [nvarchar](<\/font><\/span>256)<\/font><\/span> NOT<\/font><\/span> <\/font>NULL,<\/font><\/span><\/font><\/span>\n<\/p>\n

\n      <\/font><\/span>[ManagerID] [int] <\/font>NULL,<\/font><\/span><\/font><\/span>\n<\/p>\n

\n      <\/font><\/span>[Title] [nvarchar](<\/font><\/span>50)<\/font><\/span> NOT<\/font><\/span> <\/font>NULL,<\/font><\/span><\/font><\/span>\n<\/p>\n

\n      <\/font><\/span>[BirthDate] [datetime] NOT<\/font><\/span> <\/font>NULL,<\/font><\/span><\/font><\/span>\n<\/p>\n

\n      <\/font><\/span>[MaritalStatus] [nchar](<\/font><\/span>1)<\/font><\/span> NOT<\/font><\/span> <\/font>NULL,<\/font><\/span><\/font><\/span>\n<\/p>\n

\n      <\/font><\/span>[Gender] [nchar](<\/font><\/span>1)<\/font><\/span> NOT<\/font><\/span> <\/font>NULL,<\/font><\/span><\/font><\/span>\n<\/p>\n

\n      <\/font><\/span>[HireDate] [datetime] NOT<\/font><\/span> <\/font>NULL,<\/font><\/span><\/font><\/span>\n<\/p>\n

\n      <\/font><\/span>[SalariedFlag] [bit] NOT<\/font><\/span> <\/font>NULL,<\/font><\/span><\/font><\/span>\n<\/p>\n

\n      <\/font><\/span>[VacationHours] [smallint] NOT<\/font><\/span> <\/font>NULL,<\/font><\/span><\/font><\/span>\n<\/p>\n

\n      <\/font><\/span>[SickLeaveHours] [smallint] NOT<\/font><\/span> <\/font>NULL,<\/font><\/span><\/font><\/span>\n<\/p>\n

\n      <\/font><\/span>[CurrentFlag] [bit] NOT<\/font><\/span> <\/font>NULL,<\/font><\/span><\/font><\/span>\n<\/p>\n

\n      <\/font><\/span>[rowguid] [uniqueidentifier] NOT<\/font><\/span> <\/font>NULL,<\/font><\/span><\/font><\/span>\n<\/p>\n

\n      <\/font><\/span>[ModifiedDate] [datetime] NOT<\/font><\/span> <\/font>NULL,<\/font><\/span><\/font><\/span>\n<\/p>\n

\n      <\/font><\/span>[EncryptedNationalIDNumber2] [varbinary](<\/font><\/span>128)<\/font><\/span> <\/font>NULL<\/font><\/span><\/font><\/span>\n<\/p>\n

\n)<\/font><\/font><\/span> ON<\/font><\/span> [PRIMARY]<\/font>;<\/font><\/span><\/span><\/font>\n<\/p>\n

\nGO<\/font><\/font><\/span>\n<\/p>\n

\n<\/font><\/font><\/span>\n<\/p>\n

\nCREATE<\/font><\/font><\/span> CLUSTERED<\/font><\/span> INDEX<\/font><\/span> [IX_Employee_Compressed] <\/font><\/span><\/font>\n<\/p>\n

\nON<\/font><\/font><\/span> [dbo].<\/font><\/span>[Employee_Compressed] <\/font><\/span><\/font>\n<\/p>\n

\n(<\/font><\/font><\/span>\n<\/p>\n

\n      <\/font><\/span>[EmployeeID] <\/font>ASC<\/font><\/span><\/font><\/span>\n<\/p>\n

\n)<\/font><\/font><\/span>WITH <\/font><\/span>(<\/font><\/span>DATA_COMPRESSION<\/font><\/span>=<\/font><\/span><\/font>PAGE)<\/font><\/span> ON<\/font><\/span> [PRIMARY]<\/font><\/span><\/font>\n<\/p>\n

\nGO<\/font><\/font><\/span>\n<\/p>\n

\n<\/font><\/font><\/span>\n<\/p>\n

\nNow the test is very simple.  <\/span>I perform a full backup, and an initial transaction log backup:<\/font><\/font>\n<\/p>\n

\nBACKUP<\/font><\/font><\/span> DATABASE<\/font><\/span> CompressionTest TO<\/font><\/span> DISK<\/font><\/span> =<\/font><\/span> 'c:\\temp\\CompressionTest.bak'<\/font><\/span><\/font>;<\/font><\/span><\/span><\/font>\n<\/p>\n

\n<\/font><\/font><\/span>\n<\/p>\n

\nBACKUP<\/font><\/font><\/span> LOG<\/font><\/span> CompressionTest TO<\/font><\/span> DISK<\/font><\/span> =<\/font><\/span> 'c:\\temp\\throwaway.trn'<\/font><\/span><\/font>;<\/font><\/span><\/span><\/font><\/span>\n<\/p>\n

\nNext, I insert 290,000 rows into the uncompressed table:<\/font><\/font>\n<\/p>\n

\nSET<\/font><\/font><\/span> NOCOUNT<\/font><\/span> ON<\/font><\/span><\/font>;<\/font><\/span><\/span><\/font>\n<\/p>\n

\n<\/font><\/font><\/span>\n<\/p>\n

\nINSERT<\/font><\/font><\/span> dbo.<\/font><\/span>Employee<\/font><\/span><\/font>\n<\/p>\n

\n(<\/font><\/font><\/span>NationalIDNumber,<\/font><\/span> ContactID,<\/font><\/span> LoginID,<\/font><\/span> ManagerID,<\/font><\/span> Title,<\/font><\/span> BirthDate,<\/font><\/span> <\/font><\/span><\/font>\n<\/p>\n

\nMaritalStatus,<\/font><\/span> Gender,<\/font><\/span> HireDate,<\/font><\/span> SalariedFlag,<\/font><\/span> VacationHours,<\/font><\/span> <\/font><\/font><\/span>\n<\/p>\n

\nSickLeaveHours,<\/font><\/span> CurrentFlag,<\/font><\/span> rowguid,<\/font><\/span> ModifiedDate,<\/font><\/span> EncryptedNationalIDNumber2<\/font>)<\/font><\/span><\/font><\/span>\n<\/p>\n

\nSELECT<\/font><\/font><\/span> NationalIDNumber,<\/font><\/span> ContactID,<\/font><\/span> LoginID,<\/font><\/span> ManagerID,<\/font><\/span> Title,<\/font><\/span> BirthDate,<\/font><\/span> <\/font><\/span><\/font>\n<\/p>\n

\nMaritalStatus,<\/font><\/span> Gender,<\/font><\/span> HireDate,<\/font><\/span> SalariedFlag,<\/font><\/span> VacationHours,<\/font><\/span> <\/font><\/font><\/span>\n<\/p>\n

\nSickLeaveHours,<\/font><\/span> CurrentFlag,<\/font><\/span> rowguid,<\/font><\/span> ModifiedDate,<\/font><\/span> EncryptedNationalIDNumber2<\/font><\/font><\/span>\n<\/p>\n

\nFROM<\/font><\/font><\/span> AdventureWorks.<\/font><\/span>HumanResources.<\/font><\/span>Employee<\/font>;<\/font><\/span><\/span><\/font>\n<\/p>\n

\nGO<\/font><\/font><\/span> 1000<\/font><\/span><\/font><\/span>\n<\/p>\n

\nData used is 74,288 KB:<\/font><\/font>\n<\/p>\n

\nEXEC<\/font><\/font><\/span> sp_spaceused<\/font><\/span> <\/font><\/span>'dbo.Employee'<\/font><\/span><\/font>;<\/font><\/span><\/span><\/span><\/font>\n<\/p>\n

\nQuerying the log record length from fn_dblog shows 120,503,216 bytes:<\/font><\/font>\n<\/p>\n

\nSELECT<\/font><\/font><\/span> SUM<\/font><\/span>(<\/font><\/span>[log record length])<\/font><\/span> <\/font><\/span><\/font>\n<\/p>\n

\nFROM<\/font><\/font><\/span> fn_dblog<\/font><\/span> <\/font><\/span>(NULL,<\/font><\/span> <\/font>NULL);<\/font><\/span><\/span><\/font><\/span>\n<\/p>\n

\nBacking up the transaction log produces a file that is 121,964 KB in size:<\/font><\/font>\n<\/p>\n

\nSELECT<\/font><\/font><\/span> SUM<\/font><\/span>(<\/font><\/span>[log record length])<\/font><\/span> <\/font><\/span><\/font>\n<\/p>\n

\nFROM<\/font><\/font><\/span> fn_dblog<\/font><\/span> <\/font><\/span>(NULL,<\/font><\/span> <\/font>NULL);<\/font><\/span><\/span><\/font><\/span>\n<\/p>\n

\nNow to insert rows into the table with data compression:<\/font><\/font>\n<\/p>\n

\nINSERT<\/font><\/font><\/span> dbo.<\/font><\/span>Employee_Compressed<\/font><\/span><\/font>\n<\/p>\n

\n(<\/font><\/font><\/span>NationalIDNumber,<\/font><\/span> ContactID,<\/font><\/span> LoginID,<\/font><\/span> ManagerID,<\/font><\/span> Title,<\/font><\/span> BirthDate,<\/font><\/span> <\/font><\/span><\/font>\n<\/p>\n

\nMaritalStatus,<\/font><\/span> Gender,<\/font><\/span> HireDate,<\/font><\/span> SalariedFlag,<\/font><\/span> VacationHours,<\/font><\/span> <\/font><\/font><\/span>\n<\/p>\n

\nSickLeaveHours,<\/font><\/span> CurrentFlag,<\/font><\/span> rowguid,<\/font><\/span> ModifiedDate,<\/font><\/span> EncryptedNationalIDNumber2<\/font>)<\/font><\/span><\/font><\/span>\n<\/p>\n

\nSELECT<\/font><\/font><\/span> NationalIDNumber,<\/font><\/span> ContactID,<\/font><\/span> LoginID,<\/font><\/span> ManagerID,<\/font><\/span> Title,<\/font><\/span> BirthDate,<\/font><\/span> <\/font><\/span><\/font>\n<\/p>\n

\nMaritalStatus,<\/font><\/span> Gender,<\/font><\/span> HireDate,<\/font><\/span> SalariedFlag,<\/font><\/span> VacationHours,<\/font><\/span> <\/font><\/font><\/span>\n<\/p>\n

\nSickLeaveHours,<\/font><\/span> CurrentFlag,<\/font><\/span> rowguid,<\/font><\/span> ModifiedDate,<\/font><\/span> EncryptedNationalIDNumber2<\/font><\/font><\/span>\n<\/p>\n

\nFROM<\/font><\/font><\/span> AdventureWorks.<\/font><\/span>HumanResources.<\/font><\/span>Employee<\/font>;<\/font><\/span><\/span><\/font>\n<\/p>\n

\nGO<\/font><\/font><\/span> 1000<\/font><\/span><\/font>\n<\/p>\n

\nData used is 36,800 KB (versus 74,288 KB):<\/font><\/font>\n<\/p>\n

\nEXEC<\/font><\/font><\/span> sp_spaceused<\/font><\/span> <\/font><\/span>'dbo.Employee_Compressed'<\/font><\/span><\/font>;<\/font><\/span><\/span><\/span><\/font>\n<\/p>\n

\nQuerying the log record length from fn_dblog shows 100,677,612 bytes versus the uncompressed 120,503,216 bytes:<\/font><\/font>\n<\/p>\n

\nSELECT<\/font><\/font><\/span> SUM<\/font><\/span>(<\/font><\/span>[log record length])<\/font><\/span> <\/font><\/span><\/font>\n<\/p>\n

\nFROM<\/font><\/font><\/span> fn_dblog<\/font><\/span> <\/font><\/span>(NULL,<\/font><\/span> <\/font>NULL);<\/font><\/span><\/span><\/span><\/font>\n<\/p>\n

\nBacking up the transaction log produces a file that is 102,158 KB in size (versus 121,964 KB):<\/font><\/font>\n<\/p>\n

\nSELECT<\/font><\/font><\/span> SUM<\/font><\/span>(<\/font><\/span>[log record length])<\/font><\/span> <\/font><\/span><\/font>\n<\/p>\n

\nFROM<\/font><\/font><\/span> fn_dblog<\/font><\/span> <\/font><\/span>(NULL,<\/font><\/span> <\/font>NULL);<\/font><\/span><\/span><\/font>\n<\/p>\n

So on a much larger scale – you can imagine the transaction log size reduction could be non-trivial.  <\/span>Mileage will vary on the overall compression you can get from specific tables and data – but this is just another potential benefit to be aware of.  <\/span>And as an aside, compressed rows are<\/em> written to the transaction log in the ROW compression format and not the PAGE type. <\/font><\/font><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

I received a question today about the impact of data compression on the transaction log.  While most of the time we talk about data compression from a data page and memory utilization perspective, I hadn’t actually directly tested the impact to transaction logging and I wanted to see it for myself.  Here is just a […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28],"tags":[],"class_list":["post-513","post","type-post","status-publish","format-standard","hentry","category-performance"],"yoast_head":"\nA small-scale test measuring the impact of data compression on the transaction log - Joe Sack<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/a-small-scale-test-measuring-the-impact-of-data-compression-on-the-transaction-log\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"A small-scale test measuring the impact of data compression on the transaction log - Joe Sack\" \/>\n<meta property=\"og:description\" content=\"I received a question today about the impact of data compression on the transaction log.  While most of the time we talk about data compression from a data page and memory utilization perspective, I hadn’t actually directly tested the impact to transaction logging and I wanted to see it for myself.  Here is just a […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/a-small-scale-test-measuring-the-impact-of-data-compression-on-the-transaction-log\/\" \/>\n<meta property=\"og:site_name\" content=\"Joe Sack\" \/>\n<meta property=\"article:published_time\" content=\"2012-01-18T13:01:00+00:00\" \/>\n<meta name=\"author\" content=\"Joseph Sack\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Joseph Sack\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/a-small-scale-test-measuring-the-impact-of-data-compression-on-the-transaction-log\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/a-small-scale-test-measuring-the-impact-of-data-compression-on-the-transaction-log\/\",\"name\":\"A small-scale test measuring the impact of data compression on the transaction log - Joe Sack\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\"},\"datePublished\":\"2012-01-18T13:01:00+00:00\",\"dateModified\":\"2012-01-18T13:01:00+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/a-small-scale-test-measuring-the-impact-of-data-compression-on-the-transaction-log\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/joe\/a-small-scale-test-measuring-the-impact-of-data-compression-on-the-transaction-log\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/a-small-scale-test-measuring-the-impact-of-data-compression-on-the-transaction-log\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Performance\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/category\/performance\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"A small-scale test measuring the impact of data compression on the transaction log\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/\",\"name\":\"Joe Sack\",\"description\":\"SQL Server Performance Tuning, High Availability and Disaster Recovery Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\",\"name\":\"Joseph Sack\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g\",\"caption\":\"Joseph Sack\"},\"description\":\"Joe Sack is a Principal Consultant with SQLskills. He has worked as a SQL Server professional since 1997 and has supported and developed for SQL Server environments in financial services, IT consulting, manufacturing, retail and the real estate industry. Prior to joining SQLskills he worked at Microsoft as a Premier Field Engineer supporting very large enterprise customer environments. He was responsible for providing deep SQL Server advisory services, training, troubleshooting and ongoing solutions guidance. His areas of expertise include performance tuning, scalability, T-SQL development and high-availability. In 2006 Joe earned the \u201cMicrosoft Certified Master: SQL Server 2005\u201d certification and in 2008 he earned the \u201cMicrosoft Certified Master: SQL Server 2008\u201d certification. In 2009 he took over responsibility for the entire SQL Server Microsoft Certified Master program and held that post until 2011. He was given the SQL Server MVP award in 2013.\",\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/joe\",\"https:\/\/twitter.com\/https:\/\/twitter.com\/josephsack\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/author\/joe\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"A small-scale test measuring the impact of data compression on the transaction log - Joe Sack","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/joe\/a-small-scale-test-measuring-the-impact-of-data-compression-on-the-transaction-log\/","og_locale":"en_US","og_type":"article","og_title":"A small-scale test measuring the impact of data compression on the transaction log - Joe Sack","og_description":"I received a question today about the impact of data compression on the transaction log.  While most of the time we talk about data compression from a data page and memory utilization perspective, I hadn’t actually directly tested the impact to transaction logging and I wanted to see it for myself.  Here is just a […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/joe\/a-small-scale-test-measuring-the-impact-of-data-compression-on-the-transaction-log\/","og_site_name":"Joe Sack","article_published_time":"2012-01-18T13:01:00+00:00","author":"Joseph Sack","twitter_misc":{"Written by":"Joseph Sack","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/a-small-scale-test-measuring-the-impact-of-data-compression-on-the-transaction-log\/","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/a-small-scale-test-measuring-the-impact-of-data-compression-on-the-transaction-log\/","name":"A small-scale test measuring the impact of data compression on the transaction log - Joe Sack","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website"},"datePublished":"2012-01-18T13:01:00+00:00","dateModified":"2012-01-18T13:01:00+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/a-small-scale-test-measuring-the-impact-of-data-compression-on-the-transaction-log\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/joe\/a-small-scale-test-measuring-the-impact-of-data-compression-on-the-transaction-log\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/a-small-scale-test-measuring-the-impact-of-data-compression-on-the-transaction-log\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/joe\/"},{"@type":"ListItem","position":2,"name":"Performance","item":"https:\/\/www.sqlskills.com\/blogs\/joe\/category\/performance\/"},{"@type":"ListItem","position":3,"name":"A small-scale test measuring the impact of data compression on the transaction log"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/","name":"Joe Sack","description":"SQL Server Performance Tuning, High Availability and Disaster Recovery Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/joe\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648","name":"Joseph Sack","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g","caption":"Joseph Sack"},"description":"Joe Sack is a Principal Consultant with SQLskills. He has worked as a SQL Server professional since 1997 and has supported and developed for SQL Server environments in financial services, IT consulting, manufacturing, retail and the real estate industry. Prior to joining SQLskills he worked at Microsoft as a Premier Field Engineer supporting very large enterprise customer environments. He was responsible for providing deep SQL Server advisory services, training, troubleshooting and ongoing solutions guidance. His areas of expertise include performance tuning, scalability, T-SQL development and high-availability. In 2006 Joe earned the \u201cMicrosoft Certified Master: SQL Server 2005\u201d certification and in 2008 he earned the \u201cMicrosoft Certified Master: SQL Server 2008\u201d certification. In 2009 he took over responsibility for the entire SQL Server Microsoft Certified Master program and held that post until 2011. He was given the SQL Server MVP award in 2013.","sameAs":["http:\/\/3.209.169.194\/blogs\/joe","https:\/\/twitter.com\/https:\/\/twitter.com\/josephsack"],"url":"https:\/\/www.sqlskills.com\/blogs\/joe\/author\/joe\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/513","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/comments?post=513"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/513\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/media?parent=513"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/categories?post=513"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/tags?post=513"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}