Categories
Peoplesoft PeopleTools Performance SQL Server Tools Tuning Utilities

_WA_Sys Stats or Hand Crafted?

SQL Server generates histogram statistics automatically whenever a column is referenced in a predicate and it is not already the leading column in an existing histogram statistic. By design, indexes create histogram statistics automatically so any column you have that is the leading column in an index will already have histogram statistics.

The autogenerated statistics are prefixed _WA_Sys_ followed by:

  • the column name (in older SQL Server versions) or the column number in hex (for SQL Server 2008 onwards) e.g. EMPLID or 00000001
  • the object_id of the table in hex e.g. 57490C28

separated by an underscore. So auto-generated statistics look like this:

  • _WA_Sys_DESCR_57490C28 or _WA_Sys_00000013_57490C28

The issue for me as a PeopleSoft Administrator/DBA is whether I should keep these statistics or recreate them as “user” statistics with more sensible names. Oddly enough the “old style” naming convention which included the column name was actually better than the “hex” version we have now. After all what is column 00000014 (decimal 20) in table X?

My personal preference is to process the system generated statistics and recreate them as statistic “column name” on table X. I have a perl script that generates the relevant DROP/CREATE STATISTICS commands for an entire PeopleSoft database. The output looks like this:

--- Remove statistic _WA_Sys_AUDITRECNAME_5921A398 for column AUDITRECNAME on table PSRECDEFN
 
DROP STATISTICS [dbo].[PSRECDEFN].[_WA_Sys_AUDITRECNAME_5921A398];
 
--- Create User Statistic for column AUDITRECNAME (+ 1 keys) on table PSRECDEFN
 
CREATE STATISTICS [AUDITRECNAME] ON [dbo].[PSRECDEFN] ([AUDITRECNAME],[RECNAME]) WITH FULLSCAN;

Why do I like to do this? Well there are a number of reasons:

  1. The naming convention is clearer to me – open the statistics tree within a table in object explorer and you get a list of column names plus the statistics created for any indexes.
  2. Once I have changed to “user” statistics I can query for any new _WA_Sys_ statistics (yes I leave auto-generate switched on). This shows me any “new” query activity that accesses via previously unused predicate columns. This can help in deciding if perhaps new indexes are required.
  3. I can ensure the existing statistic are recreated using a “WITH FULLSCAN” not just sampled data. For many PeopleSoft systems this is essential – data can often be very skewed and sampling can miss this on occasion.
  4. I like to add in the “key” columns of the table to the histogram to provide better density information for the optimizer.  This can be essential information when intermediate key fields have a single value e.g. EMPL_RCD in HR is typically 0 in most cases.

If you would like to discuss this in more detail, leave a comment.

Categories
HRMS Peoplesoft Performance SQL Tuning

HOLIDAY_SCHEDULE SQL

If you come across this as one of your top (if not THE top) SQLs in your HR system:

SELECT HOLIDAY_SCHEDULE, HOLIDAY, 
(CONVERT(CHAR(10),HOLIDAY,121)), DESCR 
 FROM PS_HOLIDAY_DATE 
WHERE HOLIDAY_SCHEDULE=@P1 
  AND HOLIDAY=@P2 
ORDER BY HOLIDAY_SCHEDULE, HOLIDAY

then look no further than the ABSW_WRK page which is a hidden page on most absence related components. There is a scroll showing the holiday dates. Unfortunately, some developer decided to put a related display in the grid, resulting in “n” executes of the above SQL – where “n” is the number of rows you have in PS_HOLIDAY_DATE for the given employees’s HOLIDAY_SCHEDULE.

In the case I came across there were some 200+ rows in the table and as a result after just 3 working days the above SQL had been executed 21 million times. This actually makes this more frequent than the PSVERSION check!

Personally, I’d remove the related display field from the scroll completely. It isn’t referenced nor visible so why take the overhead of 200+ SQL lookups?

Update: Further analysis revealed an issue with a cartesian product on a customized scroll based on the standard ABSW_WRK page. That was causing the majority of the 21 million lookups by filling the scroll with “n” times the 200+ rows in the holiday table. But my point is still very valid – why lookup something you never show the users? If you really need it – put it in the view used in the scroll so it comes “for free” with the population of the grid.

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
Hints and Tips Performance

“Do it in the Database” Anti-pattern

Reuven M Lerner’s article titled “Use Your Database” in the August 2015 Linux Journal re-iterates the point I make in my post about moving logic to the database to improve performance.

Don’t forget how fast your DBMS is nor how much processing power you almost certainly have sitting idle on your DB server(s). Use it!

Categories
Configuration Peoplesoft Performance SQL Server

Trace Flag 4136 and PeopleSoft

Trace flag 4136 effectively adds an OPTION(OPTIMIZE FOR UNKNOWN) to all SQL statements. This has the effect of making the optimizer ignore histogram statistics in generating execution plans.

If you have extremely skewed data distributions this can actually result in a much better execution plan – it essentially gives the impression of more “consistent” performance by ensuring that you don’t end up with an execution plan suited to only one subset of your data. But it will not necessarily be the best execution plan you could manage,

Categories
Peoplesoft Performance Tuning

SET ARITHABORT ON and PeopleSoft

My previous post focused on the specific issue of poor PS Query performance when ARITHABORT was set to OFF on the connection.

But the fix I suggested for that will also affect all other Peoplesoft connections. That means app servers, process schedulers, app engines, BI Publisher reports …pretty much every PS connection. But that really is what you want ….

So remember:

EXEC sys.sp_configure N'user options', N'64'
GO
RECONFIGURE WITH OVERRIDE
GO

is your friend 🙂

But remember to test it first …. 🙂