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 |
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2012-11-19 : 11:54:14
|
Hi All -I've got a question regarding handling multiple jobs/procs inserting data into a single table at the same time.Background: - We've got a monthly job that needs to query, summarize and insert data into table. - There are about 20 jobs that run now (one per region) and that will increase as our company adds/defines new regions - The amount of data per region varies. So, the time to query/summarize varies....some regions have a query that runs in a few seconds and the larger volume regions can take 20 mins and up to an hour. - The data that we query is from a set of views into various tables (separated by region) - The desire is to reduce the total time that this monthly process consumes. - The table ONLY has a single Clustered Index - SQL 2008 R2Approaches and Questions:(1) Have a job that executes a stored proc that runs the query/inserts serially. - The simplest solution to maintain - Takes the longest to complete the process(2) Schedule 20 jobs (for now), one for each process so that the query/inserts can happen in Parallel. - Maintenance issue for 20 jobs and later as regions get added. - Not sure if there is any blocking of the parallel proceses that could cause more performance issues than running the jobs serially. The PK is a single column (uniqueidentifier) that is unique across all regions (comes from the regional tables). Will there be more contention because of the single Clustered Index? - However, this appears to provide a way for the faster/smaller queries to complete and not have to wait Anyway, just wondering if anyone has a thought regarding this and if my approach (#2) seems like it is going to cause problems.thanks for any thoughts/suggestions - will |
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-11-19 : 13:23:55
|
Well the suggestion depends on1) What are the indexes you have on Table? What are the queries? Is it cursor?2) How many processors you have in server? Is it Enterprise Edition? |
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2012-11-19 : 13:46:06
|
quote: Originally posted by sodeep Well the suggestion depends on1) What are the indexes you have on Table? What are the queries? Is it cursor?2) How many processors you have in server? Is it Enterprise Edition?
Thanks for the review and questions.The Index is just a single column clustered index on a uniqueidentifier data type. The GUIDs are unique across the all regions/enterprise.We are running Enterprise Edition, two CPUs and 128GB of RAM. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-11-19 : 14:07:49
|
quote: Originally posted by dhw Hi All -I've got a question regarding handling multiple jobs/procs inserting data into a single table at the same time.Background: - We've got a monthly job that needs to query, summarize and insert data into table. - There are about 20 jobs that run now (one per region) and that will increase as our company adds/defines new regions - The amount of data per region varies. So, the time to query/summarize varies....some regions have a query that runs in a few seconds and the larger volume regions can take 20 mins and up to an hour. - The data that we query is from a set of views into various tables (separated by region) - The desire is to reduce the total time that this monthly process consumes. - The table ONLY has a single Clustered Index - SQL 2008 R2Approaches and Questions:(1) Have a job that executes a stored proc that runs the query/inserts serially. - The simplest solution to maintain - Takes the longest to complete the process(2) Schedule 20 jobs (for now), one for each process so that the query/inserts can happen in Parallel. - Maintenance issue for 20 jobs and later as regions get added. - Not sure if there is any blocking of the parallel proceses that could cause more performance issues than running the jobs serially. The PK is a single column (uniqueidentifier) that is unique across all regions (comes from the regional tables). Will there be more contention because of the single Clustered Index? - However, this appears to provide a way for the faster/smaller queries to complete and not have to wait Anyway, just wondering if anyone has a thought regarding this and if my approach (#2) seems like it is going to cause problems.thanks for any thoughts/suggestions - will
The above red is what concerns. You have to find out why it is taking 1 hour for some query to run. You can post the query here so smart people over can give you best approach considering that you have provided all the indexes?If you are joining based on clustered index then it should run faster? Only thing i see with Step 2 is it can cause blocking issues. If it is possible for you to run steps in Weekend or very low activity time,then it should be good. |
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2012-11-19 : 16:22:39
|
Thanks for the continuing review and help.I share your concern and one of my main tasks is to analyze the queries/performance to see if there is anything I can do to improve the performance. Based on our current job schedule, I do believe we are targeting a weekend or early AM time slot.thanks - will |
|
|
|
|
|
|
|