I actually found an easy solution to this fix since I had the same issue.
I was trying to remove the filegroup [fg_LMeterDetail_13] but got the "cannot be removed because it is not empty" error. This filegroup's only sin was being associated with a partition scheme. This filegroup had no data.
So after finding there's no opposite to NEXT USED, I experimented and found that you can issue another NEXT USED but pointing it to the previous filegroup:
ALTER PARTITION SCHEME ps_LMeterDetail
NEXT USED [fg_LMeterDetail_12]
After that, I was able to issue the REMOVE FILEGROUP which then automatically removed it from the partition scheme
ALTER DATABASE [WACOE]
REMOVE FILEGROUP [fg_LMeterDetail_13]
Voila! It worked!
You're welcome everyone.
5000 inserts per minute are about 83 inserts per second. With 5 indexes that's 400 physical rows inserted per second. If the workload was in-memory this would not pose a problem even to the smallest of servers. Even if this was a row-by-row insert using the most inefficient way I can think of. 83 trivial queries per second are just not interesting from a CPU standpoint.
Probably, you are disk-bound. You can verify this by looking at wait stats or STATISTICS IO.
Your queries probably touch a lot of different pages so that the buffer pool does not have space for all of them. This causes frequent page reads and probably random disk writes as well.
Imagine a table where you only physically insert at the end because of an ever-increasing key. The working set would be one page: the last one. This would generate sequential IO as well wen the lazy writer or checkpoint process writes the "end" of the table to disk.
Imagine a table with randomly-placed inserts (classic example: a guid key). Here, all pages are the working set because a random page will be touched for each insert. IOs are random. This is the worst case when it comes to working set.
You're in the middle. Your indexes are of the structure (SomeValue, SequentialDateTime). The first component partially randomizes the sequentiality provided by the second. I guess there are quite a few possible values for "SomeValue" so that you have many randomly-placed insert-points in your indexes.
You say that data is split into 10GB tables per week. That's a good starting point because the working set is now bounded by 10GB (disregarding any reads you might do). With 12GB of server memory it is unlikely, though, that all relevant pages can stay in memory.
If you could reduce the size of the weekly "partitions" or increase server memory by a bit you are probably fine.
I'd expect that inserts at the beginning of the week are faster then at the end. You can test this theory on a dev server by running a benchmark with a certain data size and gradually reducing server memory until you see performance tank.
Now even if all reads and writes fit into memory you might still have random dirty page flushing IO. The only way to get rid of that is to write into co-located positions in your indexes. If you can at all convert your indexes to use (more) sequential keys that would help a lot.
As a quick solution I'd add a buffering layer between the clients and the main table. Maybe accumulate 15min of writes into a staging table and periodically flush it. That takes away the load spikes and uses a more efficient plan to write to the big table.
Best Answer
In theory, absolutely. In practice, it depends and most likely not.
Data warehouses will be a lot more likely to do full scans and disk reads/writes due to the nature of a DW. Optimizing a DW can be very important to get reports out in time, saving money in not buying expensive disks if not needed, and also to reduce wear/tear on the machines doing too many IOPS trying to get the same data. They will often do a big (or a few) table scan(s) and get all the relevant data which pushes a lot of items out to disk.
A lot of OLTP can be ran out of memory in a relatively managed environment where bad ad hoc queries aren't causing memory buffer issues. If for some terrible reason your OLTP environment is doing a lot of table scans, you see memory buffer pool pressure from table data, with high disk read/sec and disk write/sec metrics in perfmon, AND you're not able to fix the code, this would be helpful.
Note though with customization comes overhead. The additional complexity have to be properly documented, perhaps automated, and maintained. If you don't see any benefits to it then why introduce additional complexity?