Wondering if anyone could offer some advice on the following problem.
We have a steady flow of inserts into a single large table (approx 300 inserts per second, around 200 million records in table), the insert time is very important. Customers execute reports on this large table using date range which can slow this insert times.
1) initially this cause a problem due to exclusive locks until NO LOCK was added to the report query. Problem solved.
2) recently however very large concurrent reports have again caused insert times to increase. I dont think its locking but suspect it I/O and hardware resources. (i have Quest Spotlight installed, just not caught the problem yet). Pretty sure its not a locking problem as NOLOCK used in query, when i see the reports running concurrently i see the insert time increase therefore I think its I/O disk usage (will check MS performance monitor next time).
3) To combat this we are looking into the possibility of using a reporting database or table . We think the key maybe allowing the inserts incoming to a store table without any influence from queries other than the constant read/trickle to the reports db/or table. This new reports table / db would be added to a new disk array. (please note we have already invested in a heavy duty SAN storage with 2 disk arrays to keep thing efficient. Adding to another disk array is the preferred option to keep away from existing I/O traffic).
A. I understand i can move a table to a different disk array (different file) only in SQL enterprise ? i.e standard does not have this feature ?
B. One of the problems stopping development in the past was the choice to use replication, ssis or our own service to copy the data to the reports db/tbl. We have very little experience with replication and are aware it requires much management. A consultant from one of our ISPs has warned us off replication , great when it works but has many problems. How would you do it ?
C. Have investigated SNAPSHOT ISOLATION. I believe that data is held in the TEMPDB but i dont believe this would solve our problem long term. Not sure how it would help us in this situation. Please advise ?
D. Would be interested to know if anyone can suggest any other designs that may be helpful to us ?
Thank you anyone for taking the time to reply.