I inherited a database in which many of the tables have bigint as a datatype for many of the fields, and when you see the content it does not requiere all the space that bigint offers. Does using bigint for fields that dont need it affect the database performance?
bigint compared to
int has, at least, these potential performance drawbacks:
- the data will use more pages on disk
- this can affect how long it takes to read data from disk when it's not in RAM
- it will also make any maintenance operations involving those fields take longer (backups, index rebuilds, CHECKDB)
- these data pages will take up more space in RAM
- this means you'll either need to purchase more RAM, or incur the cost of reading from disk more often
- memory grants that include these columns will be larger
- this affects memory-consuming query plan operators, like sorting and hashing data
- as a secondary impact, this has the effect of reducing concurrency
That being said, how much practical impact these things have for you is very dependent on your specific environment and workload.
Note: the increases in disk and RAM usage issues can be mitigated by using row compression, at the cost of increased CPU usage. As servers often have more CPU overhead than RAM, this is generally a good tradeoff (thanks to Andy for this reminder!)
Yes, performance got affected.
In the optimization phase, query optimizer uses 50% data size per column to estimate row data size. If we use larger data type for columns that store smaller data, estimated data size coming from tables will be higher and cause higher ideal memory grant requirement for queries.
Over estimated memory grant cause memory pressure for SQL Server and effect Page Life Expectancy badly (make them lower). Lower PLE value means data pages read from disk are cached in memory (RAM) for shorter period.
To avoid performance problems, every variable length columns, local variables and parameters should be designed properly. We can use int instead of bigint for an identity column and set -2,147,483,648 as start value to get double size benefit from int data type.