I've been asked to assist with the optimization of a new project that has been built with Silverlight/C# and SQL Server. As somebody who's experience lies mainly in the Java/MySQL realm I'm hoping somebody here can help with some suggestions.
The first problem lies with a table used to store notifications from an ecommerce application. There can be hundred of thousands of records posted to this table every day 24 x 7. Hence, over time performance starts to suffer and there's no window to purge old data from the table. Are there any special SQL-Server faciltiies to handle this problem? Or would it be better to have a separate physical table for each day of the month? (e.g. use 'table1' for all entries posted on the 1st, 'table2' for asll data frorm the 2nd etc)
Another problem area with this project is where it takes in real time pricing feeds and has to store the incoming data. There are enormous volume spikes with this activity, and the underlying database can't handle the volume and this ends up being a huge bottleneck. One solution would be to introduce a messaging tier between the incoming price and the database to make the process asynchronous, but are there any specific SQL Server capabilities available to address this type of problem?
TIA