SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

reporting queires cauing problems with insert times

reporting queires cauing problems with insert times

Say Hey Kid
Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)

Group: General Forum Members
Points: 680 Visits: 395
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.


You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum