StevenWalsh wrote:its flatlined at 100% CPU usage all day
What process is usingthe CPU?
What is the physical disk subsystem? IDE drives can be very processor intensive, especially when writing.
StevenWalsh wrote: I've done all the SQL optimizations i can think of (indexing, and what not)
What indices do you have? Adding indices will only help querying performance - it will slow down inserts, updates and deletes.
Are you in auto-commit mode, with an implicit commit after every insert, or do you perform number of inserts in a transaction and then commit? Auto-commit hurts performance since the database engine updates the indices for every insert.
As a suggestion, if you are not querying the data during the day, you could consider writing it to a formatted text file, and use SQL Servers bulk copy to load it once per day.