Having a database like this:
The main problems are.
These are Json strings that contain large information. These have different info all the time.
Database size: It can go to 60GB every 10 days, so we are cleaning it constantly.
Response: A select from one row can take 30 seconds and for more rows 1-2 minutes.
The DB is sql 2008 R2 and the server is a new HP one (that is probably why it can handle 60GB selects)
Web site. This db is bound to a web site. Each time we do a select the web site is blocked.
We do not have set any indexes as we are not sure if is worth to set to json nvarchar and what the complications would be. As this site is live, we cannot do much experiment.
So the question is, what can we do to improve performance and if we need to set some indexes and where.