My application is simply copying table from local database to distributed database server. The data to be copied is quite big about thousand records, and finished the jobs for about 30s (not sure how long exactly it takes). This application is run on scheduled or user input (force method).
The database server is using JHA (Jack Henry and Association).
The problem is, sometimes the database server suffer very high cpu and memory usage, this causes some other applications that using same database server failed to fetch data from the database. After doing some trace, we found out that the operation that comes from my application is using very high cpu and memory usage.
My solution is when copying the data, im copying 100 record at one time, then my put my application to sleep for 100ms, then copying next 100 record, and so on until all data is copied. Is this enough? Is my solution will have significant effect? What is your suggestion?
FYI, my application is run from several computer, so several client computer and one database server. And i really-really need your helps here :)
Although this link http://msdn.microsoft.com/en-us/library/ff649152 is retired it's general process guidance is still usefull for nailing performance issues.
Also invest in some tooling. VS comes with some performance analyzer tools, the SDK has some and you can use PerfView http://www.microsoft.com/en-us/download/details.aspx?id=28567. You als need to look at your DB Platform. What is going on there? Maybe a index missing? or an unused index?