Blocking while inserting and deleting

Question

Hi,

I have like more than 100 machine where I pull all the performance monitor(perf log) data from this machines using a script and load into a single table on other machine.

As this Insert occurs at the same time from all this machines to this single table. And this is taking much longer time to insert and delete data into that table due to BLOCKING. 

There will be millions of data for single day.

Can anyone help me how to prevent this BLOCKING.

Thanks,

Answer 1

>As this Insert occurs at the same time from all this machines to this single table

That is not a good idea. Spread it out.

>is taking much longer time to insert and delete data into that table due to BLOCKING

So why are you surprised?  You described the problem and the solution is trivial: don't overload the table.  Have the data be transmitted into a folder as files and process the data from the folder one by one. Alternative, separate staging tables.

---

Kalman Toth SQL 2008 GRAND SLAM
New Book: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 2

Thanks Kalman, for your solution,

but in this case insert occurs for every 5 mins into this single table...as we need real time data to present at the earliest. But even though thanks for your idea and I will keep this as one of the solution.

Thanks,

Answer 3

The description is somewhat vague. It seems that you have both insertion and deletion in the table. Do you age out data after some time?

Can you post the CREATE TABLE and CREATE INDEX statements for the table.

Can you post the output from "SELECT @@version"?

---

Erland Sommarskog, SQL Server MVP, [e-mail]