Showing posts with label Performance. Show all posts
Showing posts with label Performance. Show all posts

Thursday, June 16, 2011

Windows Magic Performance Button

Who knew changing a single setting away from the default in Windows 2008 and later (as of Windows 2016) would result in an increase of 70% in performance? That's exactly what happened recently at a client experiencing JD Edwards EnterpriseOne UBE performance issues during upgrade testing. We were able to make UBE's that previously ran in 10 minutes run in less than 3 minutes simply by changing a setting on only the SQL Server.


Background

Windows 2008 includes a new ACPI (Advanced Configuration and Power Interface) setting called Processor Power Management (PPM) that allows the CPU to change its performance state to one that lowers power consumption. When looking at the big picture this is a good thing: Microsoft is doing its part by setting a default that helps the world consume less energy and limit pollution. My clients however, are generally not as concerned about world energy consumption as they are about the performance of their system.

The problem is that the default setting for PPM of 'Balanced' makes the performance state of the CPU 'Adaptive'. A CPU in this adaptive state will throttle core frequency back to save power. This comes at the cost of lower performance. When higher demands are placed on the CPU it is supposed to react quickly and increase its core frequency to address the increased load. This makes sense - when performance is not needed the CPU essentially rests and saves power, when high performance is needed the CPU switches to a higher core frequency and performs optimally but uses more electricity. Unfortunately, the CPU cannot react quickly enough under the loads we typically see in an ERP system's servers (Database, Application and Web) to suitably increase performance.


Changing the Setting

Update (6/30/2011): You should probably visit the Testing section below first to determine if the Processor Power Management setting is already being controlled by BIOS, making the change to the OS unnecessary. If PPM is already being controlled by the BIOS, go ahead and leave it that way. If testing shows that the BIOS isn't controlling, make the changes below and tell the BIOS to allow the OS to control PPM.


We need to change the power plan setting from the default Balanced to High Performance.

Open Control Panel | Hardware | Power Options. You may see the following:

Figure 1































Notice that the options are greyed out. Select 'Change settings that are currently unavailable' to enable the radio buttons.

Select 'High performance' and close Control Panel.

Figure 2































You still have one step remaining to fully enable the High Performance power plan. In most modern servers, the BIOS will have a setting that either gives the BIOS control of Processor Power Management or allows the OS to control PPM. We want to set the BIOS to allow Windows 2008 to manage PPM. The BIOS setting will be different for HP, Dell, etc. so consult your manual or just use your noggin. Then test.


Testing

To check whether your changes have taken effect is as simple as downloading the CPU-Z tool and checking the measured core speed against the known spec's of your CPU. What you are looking for is a measured core speed equal or greater than the CPU's potential speed.

Here's a system that has the High Performance setting properly enabled:

Figure 3


You can see that the core speed is greater than the OS-reported CPU speed. This is what we want.

Here is a system that does not have the Processor Power Management set to High Performance:

Figure 4


You'll notice that not only is the core speed lower than reported by the OS, CPU-Z also reports a much lower multiplier than expected. The multiplier for the tested CPU should be 23 but CPU-Z shows it to be only 12. You can find your processor's proper multiplier here: http://en.wikipedia.org/wiki/List_of_Intel_Xeon_microprocessors.  You may also be able to find your processor's multiplier on http://www.cpu-world.com/index.html or by simply Googling your processor's model number (ex: Xeon E5-2620) plus the word 'multiplier'.

This is a system not properly configured for High Performance PPM.


Conclusion

Significant performance gains can be realized by changing the Windows 2008 default setting for Processor Power Management. It is recommended that you do so.


Update (6/27/2011): Information on changing this setting for a large number of servers using Group Policy is here: http://greg.blogs.sqlsentry.net/2011/01/ensuring-maximum-cpu-performance-via.html. Note that you will still need to update each machine's BIOS to allow the OS to control PPM.

Update (8/18/2011): More information on power management for VMware is in the comments.


References

PPM in Windows Vista and Windows Server 2008
http://msdn.microsoft.com/en-us/windows/hardware/gg463252

Degraded overall performance on Windows Server 2008 R2 (Ya' think?)
http://support.microsoft.com/kb/2207548
Subscribe to Jeff Stevenson's Technology Blog - Get an email when new posts appear

Wednesday, September 2, 2009

Rebuild SQL Indexes in E1 Package Tables

With the change to table-based, XML-format metadata (formerly specs) in EnterpriseOne, first-use dynamic e-generation became a performance issue, impacting end users after a package deployment and affecting their perception of system performance. This article describes an easy method of lessening this impact.

A very short primer on auto package discovery and dynamic egeneration as it applies to the package tables:

Since 8.12/8.96, when a package is built, tables are created in the build environment's Central Objects database containing metadata for the objects in the package. The tables, named CentralObjectsTablePackageName (Ex: F98762BPDOCF001) are used by both the Enterprise servers and Web servers, guaranteeing consistency across the system.

The Enterprise servers access the package tables for objects as they are needed, bringing them across the wire to a local cache in the filesystem. The Java servers access the same package tables, bringing the objects across the wire, serializing them into the Serialized Objects tables and also placing them in cache.

Since the Enterprise and Java server now access the object metadata from the database server instead of local TAM files in the case of the Enterprise server and Serialized Objects (Java servers), there is now an additional burden associated with retrieving object metadata, an increase in the network load between the Enterprise/Java servers and the database server and an increase in the load on the database server.

A read of records in the package tables can be optimized by ensuring that the indexes are properly defragmented and the statistics are up-to-date. In SQL Server the index rebuild process does both.

Since we now have the XML metadata in tables, and we know that the dynamic e-generation is going to be accessing these tables heavily in the period immediately following a package deployment, it behooves us to rebuild the indexes on the package tables.

Execute the SQL2005/SQL2008 transact sql code below after the build completes but prior to deploying the package.


--SQL Script Begin

-- Enter appropriate variables in the SET statements for:
-- @DatabaseName
-- @SchemaOwner
-- @PackageName

declare @DatabaseName varchar (50)
declare @SchemaOwner varchar (50)
declare @PackageName varchar(50)
declare @SQL varchar(8000)

SET @DatabaseName = 'JDE_PD812'
SET @SchemaOwner = 'PD812'
SET @PackageName = 'BPDOCF001'

SET @SQL =
'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98762' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98720' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98711' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98713' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98712' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98710' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98743' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98751' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98750' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98740' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98741' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98306' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98761' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98760' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98745' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98753' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98752' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98770' + @PackageName + ' REBUILD ' + CHAR(13)

EXEC (@SQL)

--SQL Script End

Rebuilding the indexes of the package tables helps lessen the burden on the database server during the dynamic (or manual) e-generation process and improves object first-use performance.
Subscribe to Jeff Stevenson's Technology Blog - Get an email when new posts appear