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.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Allow Page Locks

Author  Topic 

Sarat
Constraint Violating Yak Guru

265 Posts
Posted - 2003-12-23 : 15:36:20
Hello,
I would like to know that if I do the following, will the setting stay forever or it is reset every time I build indexes?
--
sp_indexoption PS_STATE_TAX_DATA,'AllowPageLocks',false
--

Thanks,
Sarat.

**To be intoxicated is to feel sophisticated, but not be able to say it.**

X002548
Not Just a Number

15586 Posts
Posted - 2003-12-23 : 16:09:23
Glad to say, I didn't know about this...

Why would you want to mess with that?

BOL

quote:

sp_indexoption
Sets option values for user-defined indexes.

Note Microsoft® SQL Server™ automatically makes choices of page-, row-, or table-level locking. It is not necessary to set these options manually. sp_indexoption is provided for expert users who know with certainty that a particular type of lock is always appropriate.





Brett

8-)
Go to Top of Page

Sarat
Constraint Violating Yak Guru

265 Posts
Posted - 2003-12-23 : 16:20:34
I really didn't want to mess with it but this recommendation came from a PSoft consultant who is trying to help us with performance issues we have been having for about 4 months now!!!
What I want to know is if I have to set this option just once even if I rebuild indexes every morning for these tables.
The other biggie, i am doing is to add hint (OPTION (MERGE JOIN)) at the end of some SQL Statements to force optimizer to use merge join instead of nested loop.
Thanks,
Sarat.

**To be intoxicated is to feel sophisticated, but not be able to say it.**
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2003-12-23 : 16:29:24
Did the "consultant" set up a trace?



Brett

8-)

EDIT: I wonder what would happen if SQL server decided to escalate to a page lock and it couldn't....

I would think (and damn it, it's about time to start) that would be a performance hit....
Go to Top of Page

Sarat
Constraint Violating Yak Guru

265 Posts
Posted - 2003-12-23 : 16:42:56
We have been tracing from day 1. The trace files shows bunch of sql scripts taking longer to run. i was indexing almost after every process (that runs the sql script) - so he suggested hints. the reason he said was that that sql server 2000 sp3 contains optimization feature which creates query plan just once for a process. This is an issue for peoplesoft's certain processes because within a process there are pay groups which could vary in size. so if one pay group is really large compared to others, the query plan built for smaller paygroup is not right for larger paygroup so either we split paygroup (which is a dead-no from customer) or we add hints. in addition to this he recommended page locks. I am trying all this but there is not much improvement. i don't know how to identify if page locking could cause issues.

**To be intoxicated is to feel sophisticated, but not be able to say it.**
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2003-12-29 : 10:11:38
Did you get any farther on this?

quote:

the reason he said was that that sql server 2000 sp3 contains optimization feature which creates query plan just once for a process.


Yes but that's a good thing

quote:

This is an issue for peoplesoft's certain processes because within a process there are pay groups which could vary in size. so if one pay group is really large compared to others, the query plan built for smaller paygroup is not right for larger paygroup


ummmm, I wouldn't think so...have you updated statitics?

How are you going after the data? What columns are they using?

What do you mean by Large?

You don't mention what the traces says when you go after a large paygroup....

Also you mention a "process" what is it? a load, and update and insert? What?

Let us know...

PS Consultant rates what they are and he offers Lock hints....

wow



Brett

8-)
Go to Top of Page

Sarat
Constraint Violating Yak Guru

265 Posts
Posted - 2003-12-29 : 14:04:09
1. updated statitics?
--
Yes, I update stats every morning for all tables used in the cobol (payroll) process. In addition I update stats for entire database once a week.

2. How are you going after the data? What columns are they using?
--
It is a COBOL process which calls bunch of subroutines/data mover scripts. Each script contains multiple SQL statements querying 50-100s of columns from at least 25 tables of which atleast 10 have more than a million rows.
Ex: There is a Pay Check table that consists of a paycheck an employee receives every week (we have weekly payroll) + any reversals, bonus etc from the day I joined the company. There is tremendous amount of history. We currently do not have data archiving.

3. What do you mean by Large?
--
Employees are grouped by departments. Each Paygroup (full time vs part time, corporate vs store etc) has multiple departments.
There are atleast five paygroups. Typically a paygroup consists of 100-300 employees except for one paygroup which has about 6000 employees - this is a large + our problem paygroup! We cannot split this (one of the recommended thing by PSoft) due to business reasons.

4. You don't mention what the traces says when you go after a large paygroup....
--
I have a peoplesoft tracing technique which tells the execute time for each script within a cobol process including the variables passed. We took each sql from the script, put in query analyzer, used variables (like paygroup name) and saw the execution plan. The smaller paygroups took less than a second but the larger paygroup took a long time doing bunch of nested loops. When we forced Merge join in certain sql(s), it cut down the run time for larger paygroup by more than half and didn't affect the smaller paygroups. So, we felt that the execution plan used for smaller group of population is not suitable for larger paygroup.

5. Also you mention a "process" what is it? a load, and update and insert? What?
--
A cobol process with select, insert, update and delete statements.

--
Now there has been some improvement due to adding of hints (merge joins) but we want to bring down the time more. So I tried indexing/defrag just before they run process - no change.
The next recommendation was to do page-locks which is what I am doing but I couldn't tell if it is helping. I want to know if I have to remove page locks just once.
--
Thanks.

**To be intoxicated is to feel sophisticated, but not be able to say it.**
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2003-12-29 : 14:15:59
Can you post the query, the DDL of the Table and Indexes?

I'm sure the query is a multiple table join....

AND, it sounds like you're not properly indexed...

for the big pay group, how much data is returned?

Is it a batch process where you're dragging a ton of stuff across the network..

If I remeber correcly, PS deployments used a lot of Cursors. IS that's what's happening here?

And do you SQR?


Had a guy who wrote a 7 hour process....got it to run under 30 seconds

open cursor
fetch
open cursor
fetch
open cursor
fetch
open cursor
fetch
open cursor
fetch
open cursor
fetch
open cursor
fetch
Close Cursor
Close Cursor
Close Cursor
Close Cursor
Close Cursor
Close Cursor
Close Cursor
Close Cursor
WRITE Record

sheeeeeeeeesh

Really, I'd be curious to see the DML and DDL




Brett

8-)
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2003-12-29 : 14:18:06
Oh, and just as an aside...you could tell hime you could use partitioned views call it the name of the table (renaming the tables ofcourse), and NO ONe would know any difference....

Ohhh, and you could set up your own test I would imagine, and probably see s/hes just blowing smoke.....



Brett

8-)
Go to Top of Page

Sarat
Constraint Violating Yak Guru

265 Posts
Posted - 2003-12-29 : 14:35:08
How to attach file here? the sqls are big. It will take me a long time to format!

**To be intoxicated is to feel sophisticated, but not be able to say it.**
Go to Top of Page

Sarat
Constraint Violating Yak Guru

265 Posts
Posted - 2003-12-29 : 15:44:01
Well, here is one of the sql and indexes on the tables:

--sql#1 and Indexes#1
<I will e-mail the sql if you are interested?>
Thanks,
Sarat


**To be intoxicated is to feel sophisticated, but not be able to say it.**
Go to Top of Page

Sarat
Constraint Violating Yak Guru

265 Posts
Posted - 2003-12-29 : 15:45:38
Also, Pl ignore those bind variables :7, :8 etc in the above sql. I plug in actual values, i forgot to put before sending the sql.

**To be intoxicated is to feel sophisticated, but not be able to say it.**
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2003-12-29 : 16:28:01
First...
WOW

Next Create a non composite index for eaxh table for EFFDT and also EFFSEQ...lets us know what the plan look like now...

If you script the DDL and indexes from EM, I can build here and test it...but I like your chances with that...


SELECT A.BENEFIT_RCD_NBR ,A.EMPL_RCD ,C.PAY_SYSTEM_FLG ,
	C.COMPANY ,C.PAYGROUP ,B.PRIMARY_JOB_IND ,B.PRIMARY_FLAG2 ,A.EMPLID 
  FROM PS_PRIMARY_JOBS B ,PS_JOB C ,PS_EMPLOYMENT A 
 WHERE A.EMPLID='000007' 
   AND A.EMPLID = B.EMPLID 
   AND A.EMPL_RCD = B.EMPL_RCD 
   AND B.PRIMARY_JOB_APP = 'BN' 
   AND B.EFFDT = (SELECT MAX(B1.EFFDT) 
		    FROM PS_PRIMARY_JOBS B1 
		   WHERE B1.EMPLID = B.EMPLID 
		     AND B1.EMPL_RCD = B.EMPL_RCD 
		     AND B1.PRIMARY_JOB_APP = B.PRIMARY_JOB_APP 
		     AND B1.EFFDT<=GETDATE()) 
   AND C.EMPLID = A.EMPLID 
   AND C.EMPL_RCD = A.EMPL_RCD 
   AND C.EFFDT = (SELECT MAX(C1.EFFDT) 
		    FROM PS_JOB C1 
		   WHERE C1.EMPLID   = C.EMPLID 
		     AND C1.EMPL_RCD = C.EMPL_RCD 
		     AND C1.EFFDT   <= GETDATE()) 
   AND C.EFFSEQ = (SELECT MAX(C2.EFFSEQ) 
		     FROM PS_JOB C2 
	            WHERE C2.EMPLID   = C.EMPLID 
	              AND C2.EMPL_RCD = C.EMPL_RCD 
		      AND C2.EFFDT    = C.EFFDT) 
		      AND EXISTS (SELECT 'X' 
				    FROM PS_PAY_EARNINGS 
				   WHERE COMPANY='DTC' 
				     AND PAYGROUP='ASH'
				     AND PAY_END_DT='12/13/03'
				     AND PAGE_NUM=:7 
				     AND LINE_NUM=:8 
				     AND SEPCHK=:9 
				     AND OK_TO_PAY='Y' 
				     AND EMPLID=A.EMPLID 
				     AND EMPL_RCD=A.EMPL_RCD) 
ORDER BY A.EMPLID ASC ,A.BENEFIT_RCD_NBR ASC ,A.EMPL_RCD ASC


And use [ code ] [ /code ] tags w/out the space...

Also it doesn't look like they know what index intersection is...

Brett

8-)
Go to Top of Page

Sarat
Constraint Violating Yak Guru

265 Posts
Posted - 2003-12-30 : 16:19:34
Anyway, I did build 2 indexes on EFFDT and EFFSEQ columns. I am running the processes now to see if it helps.

I don't know what you mean by:
And use [ code ] [ /code ] tags w/out the space...
I did click an icon above for code but didn't know how to do tags w/out space.

If you paste the execution plan into multi-edit or textpad, i think it will be readable.

Here is the DDL script for the tables in the SQL followed by the execution plan.
--
< i deleted the code, it is huge!! please let me know if you want it, i will e-mail it to you!! >
-- EOM


**To be intoxicated is to feel sophisticated, but not be able to say it.**
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2003-12-30 : 16:42:25
quote:
Originally posted by Sarat

I don't know what you mean by:
And use [ code ] [ /code ] tags w/out the space...


I'll take a look

as far as the code tags edit this note...just below this line is how you use them
T  H  I   S    A   L     L   O   W  S  Whitespace




Brett

8-)
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

3246 Posts
Posted - 2003-12-30 : 17:18:11
Have you done any testing by rewriting the query with the joins in the FROM clause instead of in the WHERE clause. I know it's supposed to be equivalent, but I've heard rumors that it's not, and that SQL Server prefers the join in the FROM clause. So it would look something like:

FROM PS_PRIMARY_JOBS B 
JOIN PS_JOB C ON ...
JOIN PS_EMPLOYMENT A ON A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD
 WHERE A.EMPLID='000007' 
   AND B.PRIMARY_JOB_APP = 'BN' 
   ...


--------------------------------------------------------
Visit the SQLTeam Weblogs at [url]http://weblogs.sqlteam.com[/url]
Go to Top of Page

Sarat
Constraint Violating Yak Guru

265 Posts
Posted - 2003-12-30 : 19:00:12
I get your point! Actually the script comes from PSoft. I don't want to rewrite the sqls coz PSoft changes these with every tax update/patch/fix - almost every month - it is a maintenance nightmare!!
adding hints is not that bad but i am really aiming at something which i can do at my end until Psoft does a better job on optimizing their sqls. most of the customers seems to be on oracle db. they are very few ppl like us who are having performance issues after applying sql server sp3!
-Sarat.

**To be intoxicated is to feel sophisticated, but not be able to say it.**
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts
Posted - 2003-12-31 : 04:06:40
can you re-run your query with the following options set and paste the results?

SET STATISTICS IO ON
SET STATISTICS TIME ON



-ec
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2003-12-31 : 11:23:32
Did you try the indexes?

And edit the post and remove the code and mail it to me....

Boy...PS has sure got a racket going on..

They talked my shop in to an OS/390 DB2 installation....what a mess...



Brett

8-)
Go to Top of Page

Sarat
Constraint Violating Yak Guru

265 Posts
Posted - 2003-12-31 : 15:55:29
Bret,
i sent you 2 e-mails (one with code and other with ddl + execution plan). I removed these from the previous posts.
Also, I am running the process now with new indexes. It takes time because i can't run this until i do initial steps (which is a 1/2 day deal). Will let you know asap.
What can i say about PS. We bought it coz they said they will support AS400 (db2) then they dropped support exactly after 1 yr!!
;-).

**To be intoxicated is to feel sophisticated, but not be able to say it.**
Go to Top of Page

Sarat
Constraint Violating Yak Guru

265 Posts
Posted - 2003-12-31 : 15:59:22
ec,
This is the result. Unfortunately, this sql is a part of a huge script so there is no records returned if run individually. Are you looking afor anything specific?
--
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'PS_PAY_EARNINGS'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.
Table 'PS_EMPLOYMENT'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.
Table 'PS_JOB'. Scan count 12, logical reads 37, physical reads 0, read-ahead reads 0.
Table 'PS_PRIMARY_JOBS'. Scan count 10, logical reads 30, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 2 ms.

BENEFIT_RCD_NBR EMPL_RCD PAY_SYSTEM_FLG COMPANY PAYGROUP PRIMARY_JOB_IND PRIMARY_FLAG2 EMPLID
--------------- -------- -------------- ------- -------- --------------- ------------- -----------

(0 row(s) affected)





**To be intoxicated is to feel sophisticated, but not be able to say it.**
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site