Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dcarva
Posting Yak Master
140 Posts |
 Posted - 2006-05-12 : 01:02:14
|
| Hello,I wrote a service that automatically downloads a set of files and imports them into SQL Server, among other things. It does a ton of data manipulation on each row in the file to be imported - lots of inserts and updates. Each cycle takes 2 hours to complete and runs late at night. The service does not use up much CPU because I have it set to release control back to the OS often. However, SQL Server is busy processing all of the requests that the service is passing to the stored procedures and SQL completely pegs the CPU and the server comes to a crawl. Is there a way to prevent SQL from pegging the CPU through some sort of setting? I didn't see such a setting in Enterprise Manager. I do realize I am slamming SQL with inserts and updates, but I was just wondering if this was possible.Thanks!Danny |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-12 : 01:35:25
|
| You could have SQL Server monitor the CPU usage and report that back to your service - allowing the service to adjust the posting rate.Or you could bulk-up the inserts so that they can be done en-masse, rather than one-by-one. For example, you could transfer data to SQL Server by XML via staging tables and then use set-based methods to process the data into the real tables.Kristen |
 |
|
|
dcarva
Posting Yak Master
140 Posts |
Posted - 2006-05-12 : 14:34:19
|
| Thanks Kristen. That gives me good ideas. I'll look into staging the calls. In fact, I've done this before where I had to queue up commands.Thanks |
|
|
|
|
|
|
SQL and CPU