Friday, December 28, 2012 10:38 PM
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.
Saturday, December 29, 2012 12:41 AMModerator
>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
- Edited by Kalman TothMicrosoft Community Contributor, Moderator Saturday, December 29, 2012 12:45 AM
- Edited by Kalman TothMicrosoft Community Contributor, Moderator Saturday, December 29, 2012 1:07 AM
- Marked As Answer by SQL_Gun Saturday, December 29, 2012 1:50 AM
Saturday, December 29, 2012 1:50 AM
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.
Saturday, December 29, 2012 11:20 AM
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, firstname.lastname@example.org