PeopleSoft searches go from 4 seconds to 20 seconds

Question

Post reply

PeopleSoft searches go from 4 seconds to 20 seconds

Orange Crusher

SSC Veteran

Points: 250
More actions
August 4, 2009 at 10:22 am

#209046

At my job we use a PeopleSoft application that supports over 200 CSRs (Customer Service Repesentatives). The application sits on SQL Server 2005.
The about every month or two we get complaints from the CSRs that searchs that normally take less than 5 secounds are taking about 20 secounds.
If a restarted the database server, the problem would likely go away. Unfortunatly, we can not restart the server during bussiness hours.
The database does not have any locks or blocks. We are also doing a DBCC jobs weekly, and update statistics daily.
Is there anything that I can do with out restarting the server?
thanks
Orange Crusher
i love it!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

most likely manually updating statistics more often would resolve the issue;

statistics only get auto-updated(assuming auto update is ON) if 20% of the rows in a table have changed...

on a huge table, that's a LOT of rows, and it only takes a much smaller percentage of rows to break query and stored procedures execution plans which depend on those statistics.

change your job to update statistics more often (twice a day? three times?) and that will most likely resolve the issue.

it could also be parameter sniffing, but that would depend on the specific procedure that your users are calling; look up parameter sniffing here, see if ytou are using a procedure that might be succeptible.(spelling?)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Orange Crusher SSC Veteran Points: 250 More actions · Answer 2

thanks for the input Lowell

I just ran "sp_updatestats" and it did not make any difference.

Any other ideas?

Orange Crusher
i love it!

Lowell SSC Guru Points: 323518 More actions · Answer 3

do you know if the search call is coming from a stored procedure?

that's the next thing i'd look at, was parameter sniffing. if the procedure being called has any "default" search terms that are null, that's the indicator i'd look for for a bad execution plan.

pr_search(@name varchar(3) = NULL,

@address varchar(3) = NULL)

AS

...

As i understand it, the execution plan figures out a plan based on those default (nulls) values, rather than the statistics of the underlying tables, and can throw the performance to hell.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 4

Do you do any other maintenance? Like rebuilding fragmented indexes and the like?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MusabUmair Ten Centuries Points: 1071 More actions · Answer 5

it would be much better if you could just upload the actual execution plan so i can have a look at it and then i can give you an advice if possible.

Musab
http://www.sqlhelpline.com

Orange Crusher SSC Veteran Points: 250 More actions · Answer 6

thanks for the tips everyone.

Today it is running quickly again. I believe that it is caching issue on the peoplesoft application server side.

Orange Crusher
i love it!

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

Hopefully you have identified the problem as app-side (and you can do something to address it). Whether or not you have, I would still get the name of the sproc called and in the future do a recompile of that sproc if things slow down just in case it is a cached plan issue.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Orange Crusher SSC Veteran Points: 250 More actions · Answer 8

thanks for everyones tips.

It turned out to be an execution plan that was changing as a result of a index.

We now run this index script daily

ALTER INDEX ALL ON dbo.PS_CI_SP

REBUILD WITH (PAD_INDEX = OFF, ALLOW_ROW_LOCKS = ON,

ALLOW_PAGE_LOCKS = ON, ONLINE = ON, --IGNORE_DUP_KEY = OFF,

STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON);

Orange Crusher
i love it!