Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
In importing data to SQL server, one can often not account for how large the imported string fields will be. Can I just lazily keep using a large value for the char field definitions, and does it have any impact on performance and speed if I spend the effort to find the proper char field maximum sizes?
I have SQL server 2016 and 2019 if that matters.
Often SQL Server will have to allocate working memory to run a query. The amount of memory granted depends on many things including column data type and length. For (n)varchar it guesses actual values are, on average, half the declared length and calculates required memory accordingly.
So overly-large columns can result in overly-large memory reservations, reduced throughput and poorer system performance.
To illustrate this I've created a series of tables. All are of the form
create table dbo.t10(c varchar(10) null);
:
create table dbo.t2000(c varchar(2000) null);
The column's length changes from table to table. The shortest will be 10 (as shown) and the longest will be 2,000.
Each table is populated with one million rows. Each row consists of the single letter 'a'. So all tables hold the identical amount of data and occupy the same amount of space on disk (exec sp_spaceused 'dbo.t10';).
I use a very simple query
select c from dbo.t10 order by c option(maxdop 1);
The sort means a memory grant will be requested. Using maxdop 1 avoids the risk of some plan going parallel and complicating the comparison.
I run this for each table t10 through t2000, capture the actual execution plan and record the MemoryGrantInfo. Here's the plot of how desired memory (in KB) varies by column length. I think it is quite convincing.
There is no meaning in the size range 10 to 2,000. They are just arbitrary, chosen for convenience, not to illustrate any particular point.
Similarly the letter 'a' was the first thing my fingers typed.
My system has ample memory. Desired, Requested and Granted are identical in every test. I chose to plot Desired memory as it would be the largest if there were a difference.
Presumably, the point of importing the data is to allow someone to use it. If so, then someone has to figure out how much space to allocate. And in the vast majority of cases, it makes much more sense to do that once during the import process than several times downstream as reports, applications, and other services try to figure out whether they really need to allow 4000 characters. Frequently, those downstream applications are going to be allocating memory buffers based on the declared size of the column in which case you may be creating performance and scalability issues for them.
Ignoring the pain of downstream users, defining everything as [n]varchar(4000) makes indexing a pain. For SQL Server 2016, you can only have 900 bytes in a clustered index key or 1700 bytes in a non-clustered index key. If you define columns with the appropriate length, you'll know whether the index you want to create will exceed those limits. If you define everything as [n]varchar(4000), you may be able to define the index today but tomorrow your load will fail because it's trying to insert a row whose key would be too long. It's way easier to design and debug a system where the column length tells you the actual limit than to have a system where a random group of indexes define that limit.
Can I just lazily keep using a large value for the char field definitions, and does it have any impact on performance and speed if I spend the effort to find the proper char field maximum sizes?
Yes, it can have an impact on performance if you oversize your data types. There are many other factors that can impact performance as well, so this is not an all inclusive answer.
The main issue you're going to run into with creating overly wide data types is the way SQL Server obtains memory grants. In simple terms, SQL Server must grant memory to a query before it even knows how much data is actually going to be returned.
It estimates the size of the memory grant in two ways.
Taking those two values, SQL Server has a good idea how much memory it will need, assuming both of those values are reliable. With that said, if you create all of your datatypes as varchar(4000) and all you really needed was varchar(100), SQL Server will be granting 40x more memory to your queries.
Why does it matter?
SQL Server performs best when the data it needs is already in memory, as this reduces reading data from disk. So, how does overly wide data types effect this? Well, a single query can obtain a memory grant up to a quarter the size of RAM. If your data types are 40x wider than they need to be, you'll be kicking other cached data pages out of memory, in the anticipation your query will need it. This happens on the front end of processing, before your query even attempts to load any data into memory from disk. Now, all your other queries on the system are going to need to reread the same data back from disk into memory.
If you're queuing the overly wide data types often, this could be a significant drain on your SQL Server's memory, as it will be constantly flushing data from memory, only to reread it repeatedly from disk.
One of the top ways to maintain a highly performant SQL Server it to let it keep the most commonly needed data in memory, and avoid unnecessary trips to disk. You can help SQL Server in this area by keeping your data types as narrow as possible, while still meeting the needs of the data.
In general, specifying fields to the exact length needed has advantages in memory and disk space usage. However, a lot depends on the particular database system.
Historically, some systems would allocate the specific maximum space requested for small fields (let's call them VARCHAR for the moment) but use a pointer for larger fields (let's call them TEXT for the moment). Those larger fields would in turn (based on a maximum specified size and/or a particular subtype - e.g., TEXT vs. TINYTEXT vs. MEDIUMTEXT vs. LONGTEXT) use one or more bytes stored in the primary record to indicate the actual length. A single byte could be used up to 255 characters, 2 bytes up to 65,535 characters, etc. Which meant that a field specified as a maximum of 1,000 characters or 50,000 characters would be the same (2 bytes) but 200 vs. 300 would make a big (relatively speaking) difference of 1 vs. 2 bytes.
All that being said, more recent systems (e.g., PostgreSQL, MySQL InnoDB engine) both hide a lot of the details from the user and (b) decide whether to store any particular field in-line (like original CHAR/VARCHAR) or in a separate block somewhere (like original TEXT) based on system configuration, particular string sizes and other factors. As I understand it, in at least some systems now, a VARCHAR with a specific size, a VARCHAR with no specific size or a TEXT field are essentially treated the same.
There are, however, some advantages to specifying field lengths in many situations. For example, name, address and similar fields have real-world practical limits. You can't let someone create a 500 character name and then expect to print it on a name badge or a mailing label. Specifying maximum field lengths in a system where either the database itself or some library/framework will enforce the maximum helps avoid a lot of potential problems. In addition, specifying maximum lengths for all fields except for fields that really need to be arbitrarily big (free-form text) will effectively specify a maximum load on the system for any operations that don't involve the arbitrarily big fields. That doesn't matter much in a small database, but if you are working with millions of records it can make the difference between "unbearably slow", "good enough" and "lightning fast".
It saves bugs in the first place.
An early crash with varchar size exceeded will force the developer to think about what goes into the varchar field and how the data is sanitized and conditioned before going into the db.
A 4000-char field is pretty much able to silently absorb e.g. a stack dump instead of a proper 50-char value.
p.s. imho the idea to estimate the memory needed by the varchar size halved is wrong. A varchar field that deserves its 'var' in the data type usually has either normally distributed size or Pareto-like exponential distributed size. Neither is likely to be near-half.
