Apologies in advance if this is posted under the wrong heading - it's really a general performance tuning question. I have a table in an Azure SQL Database that is used to log messages from a variety of applications. It is written to frequently, let's say an average of 10 inserts per second. I need to query this table hourly in order to notify users if certain messages have been logged and then less frequently in order to perform cleanup operations such as deleting messages older than a certain date.
So what's the best strategy for handling this? I don't want to slow writes down with a lot of indexing. But then I'm running select queries against a large and poorly indexed table. As a result, these select queries don't execute very quickly, so then I worry about locking. Because of this, whenever I run select queries I'm using the WITH (NOLOCK) hint - it's not the end of the world if I get dirty reads, but it seems like there has to be a better way.
Should I leave the logging table optimized for writes and copy data out to another table that is well indexed for querying? If that is the solution, is there a best practice for implementing that copy operation so that it's quick and doesn't impact my writes?