sql server - SQL Table Size And Query Performance

Question

We have a number of items coming in from a web service; each item containing an unknown number of properties. We are storing them in a database with the following Schema.

Items
- ItemID
- ItemName

Properties
- PropertyID
- PropertyName
- PropertyValue
- PropertyValueType
- TransmitTime
- ItemID [fk]

The properties table is growing quite large since it stores the properties for each item, each time the web service is called. My question is this: at what point should we stop adding new records to the Properties table, and archive older Property records according to their transmit time? When does the properties table become too large, and take too long to query? Is there a rule of thumb?

Thanks.

Answer 1

There is no rule of thumb

Some thoughts:

• define "large" (we have 160 million rows tables)
• do you have a problem now? if no, don't fix it
• have you run profiler or some of the whizzy dmvs to find out bottlenecks (missing indexes etc)
• if you need the data to be in hand, then you can't archive it
• you could partition the table though