Posted by Tony Rogerson on 08/03/07 15:14

Hi David,

A lot of people who dev think single user when they are writing queries; say
a query costs 10,000 logical reads thats around 78MBytes of data SQL Server
needs to process (through memory and CPU); that is fine for a single
concurrent user but what happens when 100 people run it and now the box has
to process 78MBytes x 100 = 7,800MBytes? Memory speed becomes a limiting
factor, memory is only good for a few GBytes per second of data transfer.

So, to answer your question, if your data volumes are such and you just
bloat column sizes out then you will reduce your scalability threshold, you
will also increase the size of backups, the size of data transmitted between
server and clients (again, cost that out for the number of users you have
concurrently).

Anyway, this is academic because you should size columns acording to the
data that will fit in there; for instance if you had a column that held the
number of minutes in a day then we know that is finite and is between 0 and
1,440 so that will fit in smallint so that's all you need; no point in
making it int or especially not bigint.

Hope that helps David.

Tony

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]


"David Greenberg" <davidgr@iba.org.il> wrote in message
news:46B199ED.5030205@iba.org.il...
> Hi
> This is a question of "what does it cost me".
>
> Lets say I have an integer value which would fit into a smallint field but
> the field is actually defined as int or even larger as bigint.
> What would that "cost" me ? How would definitions larger than I need for
> the values in the field affect me ?
> Its obvious that the volume of the database would grow but with the size
> of resources etc that we have nowadays disc space isn't a problem like it
> used to be and i/o is much faster and many people would tell me "who
> cares" , or IS it a problem ?
> How does it affect performance of data retrieves ? Searches ? Updates and
> inserts ? How would it affect all db access if tables are pointing at each
> other with foreign keys ?
>
> Thanks !
>
> David Greenberg
>

[Back to original message]