SQL Inserts - performance issue

We have a database that is getting a lot of inserts - sometimes around 1500 per second. We have a bit of software (3rd party) that performs these writes, and it has a buffer. If the SQL server cannot perform the writes fast enough, the buffer queues up the data, and inserts it when it can.

We've found that generally it inserts ok - but if for instance we put a heavy trigger on the table it's inserting to, the SQL Server won't being able to write quick enough, and it queues up - an undesirable scenario.

This is all fine; however, we've found that when we read out of this table at the same time as it's been written too, this appears to cause queuing as well; it seems the SQL server cannot write as quickly when we are reading from the table at the same time.

There are about 30k rows of data in the table - never more, but we need to find a way to read from this table at the same time as it's been written to, without causing SQL to slow down on the inserts. There's a single PK on the data table, and that's got a clustered index on it.

The queries against the table always reference the same PK.

Does anyone know why the reads might be slowing down the inserts, and what we might do about it?
LVL 4
Fuzzyfish1000Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Bryan ButlerCommented:
Maybe mirroring or a load-balancing cluster would work?

http://www.extremeexperts.com/sql/articles/DBMirroring2.aspx
0
 
Ephraim WangoyaCommented:

You can try doing a dirty read

select * from table with(nolock)
0
 
lcohanDatabase AnalystCommented:
"There are about 30k rows of data in the table  - never more" and "a lot of inserts - sometimes around 1500 per second"

To me this means something must delete records pretty fast from that table if the table does not grow and this can be another potential candidate for slowing down inserts.
Here are few other factors:
- is there any clustered index, and eventualy is that clustered index included in other nonclustered ones?
- are there any foreign keys?
- how's your t-log and tempdb located? are they on split phisycal disks/raids?
- use NOLOCK hint foe selects as ewangoya suggested
- use ROWLOCK hint for DELETEs and UPDATEs
- try to avoid (push back) triggers as much as possible because they are hidden objects and as you noted effecs can be disastruous not just for inserts
- refresh stats (like UPDATE STATISTICS) and reindex (use REBUILD with ONLINE =ON) periodicaly.
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
I'd suggest the following :
> isolating the table on a separate fliegroup and put it on a RAMDrive.  That will correct your issues.  
> If the application uses the table as a RAM array, deleting lots of rows and doing a lot inserts, I recommend switching the database to READ_COMMITED_SNAPSHOT to physically separate writes(delete, inserts) from selects
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
you can also replace the DELETE/INSERT statement by UPDATE/INSERTS but that would work only if you have some control over the application.
0
 
Fuzzyfish1000Author Commented:
Thanks chaps - a lot of that was really useful. We've implemented the no-lock hint in the main view that queries that data; the data is time sensitive, not particularly value sensitive, so this looks like it might be a good solution for us.

We don't have the option at the moment of splitting the temp table and logs to a separate physical disk - it's currently configured in RAID 10 with 6 disks. We're looking at a SAN for future upgrades, but not something we can do yet.

I actually screwed up with my initial post - we de-compiled the 3rd party code, and it's not exactly as we thought - it's mostly UPDATEs rather than INSERT/DELETEs, but the issue is pretty much the same I think.

We've done quite a lot of indexing already; there's a clustered index on the PK, and that's all that's really needed initially; we removed a number of other indexes to reduce the overheads.

We looked at loadbalancing, and mirroring; due to the volume of throughput, I don't think that will work well for us; we did consider linked SQL servers to spread out the load, but that just puts off the problem, giving us a little less than 50% boost.

Thanks for all the help ; I'll feed back a bit more once we've completed our testing.
0
 
Anthony PerkinsCommented:
>>We've implemented the no-lock hint in the main view that queries that data; the data is time sensitive, not particularly value sensitive, so this looks like it might be a good solution for us.<<
Be careful with this, as a NOLOCK hint means that you are reading uncommited data as in "dirty reads", which may not be what you want.

>>it's currently configured in RAID 10 with 6 disks. We're looking at a SAN for future upgrades, but not something we can do yet.<<
I would not expect a SAN to be any faster than a RAID 10 configuration.

0
 
lcohanDatabase AnalystCommented:
Please take my comment below just a FYI KB and not as any marketing or suggestion for your environment but worth reading and being aware of if not already:

We are using "Fast RAID6" on 3PAR HP SAN for about 7TB SQL Server 2005/2008 data and so far is faster and better that the old RAID10 SAN. Now a few DB files were split a bit further from old SAN with new SAN however our IT tests are definitely showing better IO and overall lot less disk queue.
http://www.3par.com/products/inform_software/inform_os.html
http://www.3parstorage.de/documents/3PAR-sqlserver-wp-09.0.pdf
0
 
Fuzzyfish1000Author Commented:
Thanks chaps - we're trying elements of both those solutions, and we've seen some improvements already. Many thanks for the help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.