November 14, 2007 at 6:12 am
Hello,
We have been having some Performance issues with PeopleSoft on SQL Server 2005 SP2. The application performs tons of read I/O and has been bringing the system down to its knees. All documentation from PeopleSoft and Microsoft are pointing us to turning on RCSI. Why is this turned off by default if it seems to benefit I/O?
November 14, 2007 at 2:08 pm
That is not so "black" or "white" ... 🙂
It helps with "locking/blocking" issues but it generates a large amount of activity on tempdb.
In adition you may get some "undesired" effects with certaint kind of triggers.
It is Off by default for compatibility purposes.
* Noel
November 14, 2007 at 2:45 pm
Steve,
Can you provide me with more info about your PeopleSoft environment you are having issues with? We are experiencing intermittent performance issues with our PeopleSoft Finance system at the moment, and we are running SQL Server 2005 SP 2 with RCSI on.
Thanks,
Lynn
November 15, 2007 at 1:06 pm
The main issue is that our I/O is going through the roof. It has also been intermittent. The users use nVision as there reporting tool which is a bear in itself. The users run reports and it is OK sometimes, returning data in a matter of seconds. Other times, it will take a good 10 to 20 minutes, same report. We check current activities and there isn't much going on. We are at a loss. Most of the people we have spoken to is pointing to our SAN. I'm not so sure though.
RCSI is for something totally different from what I have heard. It will help with your batch processing, but not reporting.
November 15, 2007 at 1:19 pm
Our network people support our SAN, and when we have experienced high IO we haven't seen much activity to our SAN, the channels are not very busy. Our latest performance issues regarding PO's is temporarily cleared by (of all things) doing a DBCC freeproccache.
I ran a tuning trace yesterday and DTA identified 19 indexes which we have implemented to see if that helps. Our reporting (nVision) seems to be okay, it is with the day to day work that ther users are doing where we are having some issues. Our PeopleSoft Admin applied the latest maintenance packs this past weekend and we started having issues (again). We had issues with Finance when we upgraded to 89 to use SQL Server 2005.
November 16, 2007 at 6:24 am
Normally I'd suggest RCSI if lots of blocking is seen. It's not going to reduce IO load, and may even increase it, due to increased usage of TempDB.
If you can, run a perfmon trace over the busy times and watch the following counters
Pages/sec
avg sec/read
avg sec/write (this and above on the data file drive, the log file drive and the swap file drive)
transfers/sec
%disk idle time
also, if you can, run profiler, catch the RPC completed and SQL batch completed and look for queries with high reads or writes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 19, 2007 at 6:29 am
Thanks for the information. We have run performance monitor on the server and profiler. Unfortunately, the PeopleSoft application has tons of noise. We ended up using the following query against the dynamic view:
SELECT TOP 10
((QS.total_logical_reads + QS.total_logical_writes) /QS.execution_count) AS [AVGIO],
SUBSTRING(QT.text,QS.statement_start_offset/2,
(CASE WHEN QS.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), QT.text)) *2
ELSE QS.statement_end_offset END -QS.statement_start_offset)/2)
AS QUERY_TEXT,
QT.DBID, DBNAME=DB_NAME(QT.DBID),
QT.OBJECTID,
QS.SQL_HANDLE,
QS.PLAN_HANDLE
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text(QS.SQL_HANDLE) AS QT ORDER BY
[AVGIO] DESC
This returned some heavy I/O offenders.
However, the only drive that is getting hit hard is the data drive. I have been speaking to others and they are all pointing at the SAN and it's configurations.
November 19, 2007 at 6:47 am
It might be the SAN. What did the perf mon counter values look like?
If you've identified high IO using queries, see if there's anything you can do to reduce the IO. Especially if the query's running often. It's possible that a couple of useful indexs will reduce the IO load to acceptable levels.
I don't know how much control you have over the DB, seeing as it is a vender product.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 19, 2007 at 7:02 am
Here are some numbers:
Data drive only
Avg. Disk sec/read - 0.769
Avg. Disk sec/Write - 0.208
Avg. Disk sec/Transfer - 0.767
% Idle Time - 0.000
The most interesting numbers are the
% Disk Read Time - 28912.82
% Disk Time - 28912.820
Avg. Disk Bytes/Read - 423936.000
Disk Bytes/sec - 159399525.621
What is horrifying is that all these numbers are produced when the application support person ran a count against a large table, about 27 million records.
November 19, 2007 at 7:11 am
Ouch. Those are way out of expected.
sec/read and sec/write should be below 0.01 sec
% disk idle time should ideally be >75%
I'm curious how the %disk time managed to exceed 100%. 🙂 I've seen 104 before, but 20 000+ ????
Does that large table have any indexes on it?
Looking at what you have, personally I'd try a double approach. Get someone experienced with SANs to look a the config and setup, and at the same time see if the IO load from the DB can be reduced, by optimising queries or adding/modifying indexes.
Of course, the second depends on how much control you have over the DB.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 19, 2007 at 8:06 am
We have some control over our PeopleSoft system, and have created some custom indexes ourselves. Usually I will script the indexes and provide that to our PeopleSoft administrator who sets them up using PeopleTools. This last time, however, she had me build the indexes (19 of them) outside of PeopleTools. This way we could take advantage of the covered indexes in SQL Server 2005.
The new indexes have helped the performance of our finance system, but building them the way we did puts supporting them on us instead of allowing the PeopleSoft tools to support the new indexes.
As for what the OP was seeing, that is also what we were seeing, heavy IO in the data disk.
😎
November 19, 2007 at 9:05 am
The strange thing is, none of this performance issues occurred on SQL 2000. It had it's own issues, but nothing this major. Is there something that may have changed in SQL 2005 to enhance these issues? Same hardware, SAN, etc. The only thing that changed was the amount of memory and CPU's which basically doubled.
November 19, 2007 at 10:01 am
Between the PeopleSoft Administrator and myself, we think it has to do with PeopleSoft Finance 8.9 and SQL Server 2005. We moved to SQL Server 2005 because of our upgrade early this year to PeopleSoft HR 9.0; it required we move to SQL Server 2005.
We have not had any performance issues with our HR system under SQL Server 2005.
😎
November 26, 2007 at 10:51 am
I have same issue with peoplesoft Finiancial 8.4 with Sql server 2005 and peopletools 8.48.07
After upgrading sql server 2005 everything running very slow.
I ran the following commands
1. DBCC DROPCLEANBUFFERS
2. DBCC FREEPROCCACHE
and after that i ran UPDATE STATISTICS With Fullscan all tables in Database.
After that everything working fine.
Regards
Ram
November 26, 2007 at 1:37 pm
It's always recommended to update the stats on all tables after an upgrade to SQL 2005.
2005 keeps more detailed column stats and while the optimiser can use the SQL 2000 stats, they're less useful and usually lead to poorly running queries.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply