concurrent run - help needed

Thank you Joe,
I generated the sysmon reports and there were 24 Ex-Row to Ex-Table and 2 Ex-Page to Ex-Table promotions. But i'm not sure if this is related to this process in question as a few other processes were running at that time. However, i did not see any lock promotions from the sysmon report when the threshold was set to 15000 at the server level.
I shall look at generating the report again with executing only this process.
Will partitioning the table provide better results?

Thanks,
jennifer

Hi Joe,
Based on your suggestions, I'm going to suggest following recommendations to achieve concurrency. please let me know if this is sound.  
1) Increase number of locks to the maximum possible. Atleast to 500000
2) Modify stored procedures to execute each query in a batch of size 30000. Commit transaction after each batch loop. This will release locks obtained by the transaction.
3) HWM threshold to be increased to the size of the batch which will be 30000. The only set back as you said in increasing the HWM very high is that we might run out of locks.  But since we are releasing locks by committing the transaction after each batch loop this situation will be eliminated (right?)
Also HWM will NOT be inreased at the server level. change HWM for only those tables affected.

Setting the HWM to a high value is to just avoid even the rarest possibility of a genuine lock promotion. Please let me know if i'm wrong in this understanding

Thanks,
Jennifer

Hi Joe,
we ran sp_object_stats and we found there were no lock contentions. the message below was printed in the report.
No contention on any tables!
But we noticed that one of the huge tables got excalated to a table lock. Could this have been avoided if the HWM was increased?
FOllowing was the config setting in th Database and procedure
1) No of locks --> 500000
2) LWM and HWM --> 200
queries were executed in batch of size 25000 with frequent COMMITs. Both jobs finished successfully.
please let me know if you want me to publish the entire object_stats report.
We also looked at the query plans and noticed that the query was going for a deferred update instead of a direct due to a "not exists" condition that we introduced to achieve batch control. (adding this condition was an attempt to make sure record that was inserted in the previous batch loop is not inserted again in the next loop)
This deferred update increases the total run time of the procedure.
Appreciate your help and assistance and thanks in advance!

Thanks,
Jennifer

how could we eliminate the deferred update and still achieve the batch control?

Hi Joe,
I fear i have not understood your suggestions to avoid the dererred update. Do you want me to 1) create a holding table and populate the PKs in it 2) use this table to populate my main table? 3) And then delete those PKs that are populated in the main table from the holding table?

if my understanding is correct, would this not increase the run time as i will be introducing a huge insert? also how would i avoid populating duplicate rows in this holding table as well?
i apologize if i had not understood your suggestion correctly. please help me understand. My need is to avoid populating a record that was already inserted and thus eliminate duplicate insertion failures.

Also please note that we had changed the lock schema in the table to "page" lock before this run.

Thanks,
Jennifer

Without knowing your existing code it's hard to make specific suggestions. How do you know which rows to update currently? If it's a huge update then inserting a whole of PKs will add up, yes. Can you express your condition as anything other than a NOT something? Perhaps show us the existing logic you added for that?

Was that datapages or allpages locking, BTW?

Hi Joe,
I have attached the code snippet here fyr. lock schema was DATAPAGES. All columns that i added inside the NOT EXISTS conditions are used in unique clustered index.
Please let me know if you need more details.

Thanks,
Jennifer

INSERT INTO claim_extract
 
SELECT
	core.CLAIM_ID                                  ,
	line.SEQ_NO                              ,
	core.CUR_STS                             ,
	line.CUR_STS                             ,
	core.MEME_CK                             ,
	core.SBSB_CK                             ,
	SPACE(9) AS SBSB_ID                           ,
	core.PROV_ID                                  ,
	core.GROUP_ID                                  ,
	SPACE(9) AS TAX_ID                            ,
	SPACE(4) AS PROV_SPEC                     ,
	'N' AS PAR_NPAR                           ,
	core.MEM_AGE                              ,
	core.MEM_SEX                            ,
	5 AS REC_TYPE                             ,
	line.UMAUTH_ID                           ,
	line.REV_ID                                  ,
	line.SERVICE_ID                                  ,
	SPACE(4) AS BILL_TYPE                     ,
	0 AS UPDATE_IND
 
FROM  
	claim_core             core NOHOLDLOCK,
	claim_line_dtl         line,
	#TMP_GRGR_GROUP                     TMPG
WHERE  core.CLAIM_ID = line.CLAIM_ID
AND    core.GROUP_ID = TMPG.GROUP_ID
AND    core.CLCL_PAID_DT BETWEEN @from_dt AND @to_date
AND    NOT EXISTS ( SELECT 1 
                    FROM claim_extract extr
                    WHERE extr.CLAIM_ID = core.CLAIM_ID
		    AND   extr.SEQ_NO = line.CDML_SEQ_NO
		    AND   extr.GRGR_ID = core.GRGR_ID
		    AND   extr.REC_TYPE = 5
		  )

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:

Could you batch it one GROUP_ID at a time, and then delete all rows with that GROUP_ID from the temp table?

By the way "noholdlock" doesn't mean what you think it means, that's only relevant to a certain type of cursor from the client side. You're still getting locks there.

Hi Joe,
During concurrent runs, each instance of the process will handle only one GROUP_ID. This is one of the input parameter to the procedure. Basically we are trying to populate the "claim_extract" table concurrently, each instance populating records belonging to one GROUP. Each GROUP contains more than 200,000 records qualifying the criteria.
Temp table would contain multiple GROUP_IDs only during non-concurrent (single) executions.

Thanks,
Jennifer

it's a lot easier to make sure you're only inserting the correct rows in the first place rather than fetching rows and only then checking to make sure they aren't ones you've already handled...

Basically put the smarts in the first step of how the underlying work set is built rather than just building it and then checking to see if the rows you've got are the right ones.

Also have a look at the various ignore_dup_key/ignore_dup_row options, you can silently drop duplicate insertions to a table. Sometimes that's the fastest way of handling this sort of issue. :)

Those options apply to indexes, BTW.

Hi Joe,
I'm still working on executing my process and simultaneously run the sysmon. I shall keep you updated about the findings soon.
BTW, ignore_dup_key will be helpful to only "delete" the duplicate records records that were already loaded right? It wont avoid populating the duplicate rows in the first place right?

Thanks,
Jennifer

No, you're thinking of ignore_dup_row, which cleans out existing duplicates (and prevents new ones) - it only works for entire rows though. ignore_dup_key might be helpful here, it rejects inserts that would cause a duplicate key (not row). Possibly I'm just distracting you with this though. :)

Hi Joe,
We have arrived at a logic similar to the holding table suggestion that you provided and have eliminated the deferred update inserts :)
We are right now executing the process several times to check the runtimes. Following are the changes we are making in the code and DB to achieve good concurrent results.
1) Perform each query in a batch.
2) Table locks changed to datarows
3) use a holding table to avoid deferred updates
4) Increase the no of locks in the adaptive server to a greater extent possible.

as you said HWM thresholds were never changed.
Thank you so much for the excellent suggestions provided. I shall go ahead and accept all solutions that worked for me.
Once again thanks a ton!

Thanks,
Jennifer

Happy to help, I always enjoy making the database go faster! :)

Good luck!