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?


Using 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!)

  • 1
    Adding row compression can minimize the extra row size for nominal CPU. Most people won't even notice the CPU cost of row compression
    – AMtwo
    Jan 23 '20 at 5:18
  • Thanks, @AMtwo! Good point, I've updated my answer with that clarification. Jan 23 '20 at 13:21

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.