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 |
|
Craig L
Starting Member
3 Posts |
 Posted - 2010-10-04 : 10:32:24
|
| Hello,Thanks in advance for any advice. This is my first post - I did some searching to see if this topic has been covered before and couldn't find anything, so my apologies if I've missed anything.My situation is that we have several SSIS packages and other import processes that are simply a series of queries. The problem is that when these packages or queries are executed the consume all the resources of the sql server that while running the server does not respond to any other requests (the database is the back-end to a very busy website).So, I'd like to find a solution to be able to "throttle back" or limit the amount of resources that running the packages can use so that the database maintains its ability to also respond to other requests while the packages are running.I've modified some of the more complex queries with the MAXDOP hint to try and limit the processor to 1 (it's a 4-processor box), unfortunately that didn't seem to do the trick either.The packages read a set of rows from a flat file containing anywhere from 30,000-400,000 rows and put them into a "holding" table. Then rows in the main table (about 8 million rows total) are either inserted or updated based on the rows in the holding table.I hope I've explained the situation adequately. Thanks for any advice.-Craig |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-10-04 : 11:59:35
|
| Have you verified that the problem is actually resource usage, and is not a result of blocking?CODO ERGO SUM |
 |
|
|
Craig L
Starting Member
3 Posts |
Posted - 2010-10-04 : 14:22:42
|
Hi Michael,Thanks for writing back. As far as I know, it's not blocking because it effects the whole server and not just the table being updated.In other words, if table A has the data read in from the flat file and table B is the big table being updated, while the package is running it's not unusual for queries on tables C-Z to also timeout.Thanks,Craigquote:
Originally posted by Michael Valentine Jones
Have you verified that the problem is actually resource usage, and is not a result of blocking?CODO ERGO SUM
|
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-10-04 : 15:43:37
|
| try running the imports in smaller batches.what isolation level are you using? try using read_committed_snapshot so the writes don't block the reads.also, may simply need to reschedule your import jobs at the least busy time |
|
|
|
Craig L
Starting Member
3 Posts |
Posted - 2010-10-05 : 21:11:22
|
Hi Russell,Thanks for the suggestions. I will definitely look into the isolation level.As for smaller batches or least busy time, those aren't as practical for us as the nature of the data that we get all needs be updated at once and our window of "least busy time" is rapidly shrinking (a good problem to have, I know) and since running the package pretty much renders the site unusable, it's a very, very small window.Thanks again,Craigquote:
Originally posted by russell
try running the imports in smaller batches.what isolation level are you using? try using read_committed_snapshot so the writes don't block the reads.also, may simply need to reschedule your import jobs at the least busy time
|
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-10-05 : 22:30:21
|
| Definitely a good problem to have from the business side. Rough on the DBA team though :D |
|
|
|
|
|
|
|
|
Limiting SSIS Processor Usage