Server Performance – The Evil of Large Data Types in SQL Server

In this article, we will see how choosing unnecessarily large data types affect your server performance and production cost.  

Databases are designed to store and retrieve your business data with multiple layers of security. Data is defined by business and stored in a database in the form of tables, views, stored procedures, etc.

Now here is the tricky part, databases have some defined data types and their sizes which are used to allocate memory as well as a mechanism to store and retrieve the data. So we would need to carefully plan then DDL/schema. If not, then all sorts of data related problems start to happen when your business/data grows.

Before we dive into the technicalities of this, Imagine like this, you have a beer mug and a whisky glass. If you pour whisky in a beer mug, it would be a waste of space. Likewise, if you pour beer in a whisky glass, it would not be an efficient way to drink beer. So what do we do? We make informed choices. 

We should do the same when we plan our databases. For example, in SQL Server, we have different sizes for date and numeric. Your predecessor will thank you if you choose a type that is the best fit for whatever you are storing. 

Don’t buy a beer mug if you plan to drink whisky.

DATA TYPE & SIZE

Let’s take a look at SQL Server numeric data – 

INT types

tinyint: 1 byte (0 to 255)
smallint: 2 bytes (-32,768 to 32,767)
Int: 4 bytes (-2,147,483,648 to 2,147,483,647)
Bigint: 8 bytes (-9,223,372,036,854,775,808 to 9,223,372,036,854,775,808)

Numeric types (Decimal & Numeric)
Precision:
1-9 (5 bytes)
10-19 (9 bytes)
20-28 (13 bytes)
29-38 (17 bytes)

Float (4 or 8 bytes)
real (4 bytes)
money (8 bytes)
smallmoney (4 bytes)

THE EVIL

Let’s say you are designing a college fee database. You are storing details about students, their course fee amount, how much they have paid, and their dues.

Here, the logical choice may be smallmoney (4 bytes) which can support the amount up to $214,748.36 which is fairly good for most colleges. 

But for some reason, you may choose the decimal type (5 bytes) which can store the amount up to $999,999.99. That is a really large amount and a wastage of space unless you know what you are doing.

Now let’s say some visionary developer chose DECIMAL (10,2). That is going to take 9 bytes of space and will store the amount up to $99,999,999.99. That’s almost 100 Million. For a college fee? This is just ridiculous. And you can easily find these mistakes in many production databases.

How would this affect query performance? 

Let’s say there is a query involving multiple joins of different tables with inner queries doing some in-place calculations. Internally all these operations and calculations are done by the mechanism of memory pages by the operating system.

With larger data, we will have more pages. If we make mistakes as we did with DECIMAL (10,2) for college fee, we end up having more data pages. This may very well shoot over the RAM size so extra pages will go to the secondary storage, increasing the cost of query and decreasing the performance.

So, lesser pages improve the performance, not only for querying but for things like maintenance, index rebuilds, backups and restores. Whenever possible, be sure to select the right data type and size for your data to ensure the best performance for your production server.