Statistical analysis for performance measurement on usage of bigger datatype wherever they were not required at all

Question

If i takes larger datatype where i know i should have taken datatype that was sufficient for possible values that i will insert into a table will affect any performance in sql server in terms of speed or any other way.

eg.

IsActive (0,1,2,3) not more than 3 in any case.

I know i must take tinyint but due to some reasons consider it as compulsion, i am taking every numeric field as bigint and every character field as nVarchar(Max)

Please give statistics if possible, to let me try to overcoming that compulsion.

I need some solid analysis that can really make someone rethink before taking any datatype.

EDIT

Say, I am using

SELECT * FROM tblXYZ where IsActive=1

How will it get affected. Consider i have 1 million records Whether it will only have memory wastage only or perforamance wastage as well. I know more the no of pages more indexing effort is required hence performance will also get affected. But I need some statistics if possible.

Answer 1

You are basically wasting 7 bytes per row on bigint, this will make your tables bigger and thus less will be stored per page so more IO will be needed to bring the same amount of rows back if you used tinyint. If you have a billion row table it will add up

Answer 2

A lot of it comes down to space. Your bigints are going to take 8 times the space (8 byte vs 1 byte for tinyint). Your nvarchar is going to take twice as many bytes as a varchar. Making it max won't affect much of anything.

This will really come into play if you're doing look ups on values. The indexes you will (hopefully) be applying will be much larger.

Answer 3

All the 'wasted' space also comes into play for DR (if you are 4-6 times the size due to poor data type configuration, your recovery can be just as long).

Not only do the larger pages/extents require more IO to serve.... you also decrease your memory cache with the size. With billions of rows, depending on your server you could be dealing with constant memory pressure and clearing memory cache simply because you chose a datatype that was 8 times the size you needed it to be.

Answer 4

Defining this in statistical terms is somewhat difficult, you can literally do the maths and work out the additional IO overhead.

Let's take a table with 1 million rows, and assume no page padding, compression and use some simple figures.

Given a table whose row size is 100 bytes, that contains 10 tinyints. The number of rows per page (assuming no padding / fragmentation) is 80 (8096 / 100)

By using Bigints, a total of 70 bytes would be added to the row size (10 fields that are 7 bytes more each), giving a row size of 170 bytes, and reducing the rows per page to 47.

For the 1 million rows this results in 12,500 pages for the tinyints, and 21277 pages for the Bigints.

Taking a single disk, reading sequentially, we might expect 300 IOs per second sequential reading, and each read is 8k (e.g. a page).

The respective read times given this theoretical disk is then 41.6 seconds and 70.9 seconds - for a very theoretical scenario of a made up table / row.

That however only applies to a scan, under an index seek, the increase in IO would be relatively small, depending on how many of the bigint's were in the index or clustered key. In terms of backup and restore as mentioned, the data is expanded out and the time loss can be calculated as linear unless compression is at play.

In terms of memory caching, each byte wasted on a page on disk is a byte wasted in the memory, but only applies to the pages in memory - this is were it will get more complex, since the memory wastage will be based on how many of the pages are sitting in the buffer pool, but for the above example it would be broadly 97.6 meg of data vs 166meg of data, and assuming the entire table was scanned and thus in the buffer pool, you would be wasting ~78 megs of memory.