Breaking the Row Size Limit in SQL Server

Microsoft SQL Server, as of version 9.0 (2005), allows you to cheat the max row size limit of 8K. If we have a row of variable length data types (ie. varchar), the total byte count can be more than 8K. SQL Server will magically spill the data over to the next page.

Cool right? Well... yes and no. Cool if your hands are tied and it gets you out of a jam. Not cool if you care about database performance and scalability.

Having a row span more than one page (in Oracle we call them blocks), results in page (or block) chaining. The overhead involved in block chaining can cause some significant performance hits depending on how often it happens, size of the table, fragmentation, etc.

This goes for most any popular RDBMS. Let's look at Oracle (no point in just picking on SQL Server). Oracle allows a 64K max row size (and that's a hard limit... no loosy goosy there). However, block size is determined by the value of the db_block_size init parameter set during database creation. Oracle recommends an 8K db_block_size for a general purpose (OLTP with some reporting) database. If your row is wider than 8K, then, you guessed it, block chaining.

Same goes for MySQL (sort of). The InnoDB engine, like Oracle, has a 64K limit. The default, uncompressed, block size is 16K. However, anything over 8K will be written to the next page (block) and will result in block chaining.

In contrast, IBM DB2 does NOT allow block chaining. By default, DB2 page size is 4K, but is configurable to 4, 8, 16, or 32K, and unlike Oracle, this is on a per tablespace basis rather than at the database level.

So why is block chaining bad? Well, it's not horrible, but the resulting performance does depend on a lot of factors. One major factor in particular is fragmentation. If the blocks aren't contiguous, it can result in some pretty significant database performance degradation. I'll leave database fragmentation and how to manage it for another post... or maybe three... seems appropriate to discuss this tuning knob in a few database systems.

While were on the topic of Max Row Size in SQL Server, tables exceeding the 8K limit (per their column definitions, not data content) cannot participate in SS replication. Tables containing more than 246 columns cannot participate in merge replication and tables with more than 1000 columns cannot participate in transactional replication.

There's a lot more to blocks/pages, row size, how it all relates, and how it impacts performance and scalability, but is out of scope for this post. Each engine handles it a little differently. In general, however, if your row exceeds the block size, it will result in block chaining, and could result in poor performance.

I left PostgreSQL out of the post intentionally. With a 1.6TB (yes, terabyte) max row size, it's a whole discussion in and of itself (and yes, just because you can, doesn't mean you should).

Comments

Post a Comment

Popular posts from this blog

Using NHibernate in Asp.Net Core

Starting with version 5.1, NHibernate now supports .Net Core and .Netstandard as well as the full framework. Let’s take a quick look at how to set it up in an ASP.Net Core 2.x/3.x application. Configuration As of this writing, NHibernate doesn’t support the configuration system within Asp.Net Core based on Microsoft.Extensions.Configuration. The good news is, you don’t have to use a  hibernate.cfg.xml  file or the   section in app.config. You can add the properies manually, and pull the values from Asp.Net Core’s configuration, taking advantage of all the goodies that come with it. This opens up a world of configuration deployment possibilities. var config = new Configuration (); IDictionary < string , string > properties = new Dictionary < string , string > { { "connection.connection_string" , Configuration . GetConnectionString ( "Default" ) }, { "dialect" , "NHibernate.Dialect.SQLiteDialect" }, ...
Read more

Adding New Microsoft Extensions to Legacy WCF and ASMX Web Services

I was asked by several people recently: Can I use dependency injection with my legacy WCF and ASMX web services? Microsoft has some new stuff they released with core–specifically, a new configuration system, caching, and logging providers. Can I use them in my legacy WCF and ASMX web services? The answer to both is yes you can! In fact, I recommend it! To illustrate this, we’ll take a look at taking a sample application that has a WCF service and a ASMX service and we’ll: Add dependency injection using Ninject. Add the new Configuration provider from the new  Microsoft.Extensions.Configuration  packages. Add caching functionality using Microsoft’s new  Microsoft.Extensions.Caching  packages. Use the new logging providers from  Microsoft.Extensions.Logging  packages. Dependency Injection We use Ninject, but you can just as easily use AutoFac or SimpleInjector as they also have packaged integrations for WCF. You can use other containers, bu...
Read more

Code Coverage for Multiple Projects in a Single Build using Dotnet Test and Coverlet

Most of the time, your solution will have more than one project and a test unit project for each of those. Azure DevOps only, as of this writing, only allows you to update a single code coverage summary. If you upload more than one, each overwrites the next and only the last one remains. To accomplish this, we can use the merge functionality in Coverlet . I use the MSBuild extension, because it is better suited for CI pipelines. In your test project XML, add the package... <PackageReference Include="coverlet.msbuild" Version="2.7.0">     <PrivateAssets>all</PrivateAssets>     <IncludeAssets>runtime; build; native; contentfiles; analyzers</IncludeAssets> </PackageReference> Then, in your dotnet test command or msbuild command, tell it to use Coverlet and to merge results. If you're using Azure DevOps, your test task looks this... (line wrapped for read ability in the article) - task: DotNetCoreCLI@2 displayName: Test i...
Read more