Category: Upgrade

Categories
PeopleTools SQL Server Tuning Upgrade

PSTREENODE Clustered Index Change

I noticed that in PT 8.55 (well at least in .08 and later) that the PeopleTools meta-data for the Clustered Index on PSTREENODE lost the “clustered” attribute. I’m not sure why this was changed but you should be aware that this change could impact your execution plans – especially if you use row level security.

PT 8.55 loses Clustered Flag on "key"
PT 8.55 loses Clustered Flag on “key”

 

"Key" is now Nonclustered
“Key” is now Nonclustered

 

And, more importantly what we now have is a “heap” and so data is no longer ordered as we might expect. This also forces any sequential access to refer to index allocation map pages. Overall, changing this is not something to be done lightly, and I do wonder at the thought process behind this change – or was it just a mistake?

Categories
Peoplesoft PeopleTools Performance Tuning Upgrade

PT 8.51.25 SYS DB Sizing – NCHAR() vs NVARCHAR()

Some quick statistics from a PeopleTools 8.51.25 SYS database on SQL Server:

  • Unicode with CHAR (i.e. NCHAR fields)       – DB size 1166 MB
  • Unicode with VARCHAR (i.e. NVARCHAR fields) – DB size 363 MB

That is almost a 70% reduction. Probably not that surprising given the number of character fields, but what is more interesting is the significant improvement in performance due to the increased number of rows per page for many tables.

An extreme example of this can be seen in PSAUDIT – using NCHAR this is close to 1200 characters wide due to 15 NCHAR(65) KEYn columns. Typically, the vast majority of the KEYn columns are empty since it is relatively rare for audited tables to have more than 5 key fields. Using NVARCHAR I have seen an average row width of 150 characters – meaning that a page holds 6 times as many rows on average.

Note: A value of 4 in the DATABASE_OPTIONS column of PSSTATUS will enable VARCHARs on SQL Server.

Categories
Peoplesoft PeopleTools Upgrade

PeopleTools Upgrade to 8.53.20

I have just completed a PeopleTools upgrade from 8.52.10 to 8.53.20 on a production CRM 9.1 MP5 system. Everything went very smoothly but it was a very intense weekend due to the size of the production stack and the need to ensure all the configurations were correct. The environment to be upgraded comprise:

  • 4 web-only servers on Windows 2008 R2 each with a single PIA
  • 8 application servers on Windows 2008 R2 running 7 application server domains, 3 process schedulers, 1 dedicated PUB/SUB domain and 1 shared search server.
  • 1 clustered SQL Server 2008 R2 database

Things to watch out for on the upgrade, and some techniques I used to speed/simplify the process:

Continue reading “PeopleTools Upgrade to 8.53.20”