Udostępnij za pośrednictwem

Kronos’ tips: Improving Performance

  • Artykuł

Advisor and Reviewer:

Bryan deSilva, Managing Director and Chief Evangelist at Improvizations.com. His bio can be found here. 

Recently I had a conversation with Bryan deSilva, Chief Evangelist of Improvizations, a firm that specializes in workforce management software implementations. He is an active blogger on Kronos (here). During our short chat, we talked about two proven methods that can result in significant performance improvement which are covered below:

  • Locking SQL Server Memory
  • Using Read Committed Snapshot Isolation (RCSI)

We are planning to have more conversations and tips in the coming weeks.

Performance Improvement: Locking SQL Server Memory

Bryan’s general rule is to lock 50% of the Server Memory for SQL Server. Bryan reports a significant improvement in performance when this is set. SQL Server uses dynamic memory management by default and you want to give 50% of the available memory to SQL Server by default. To do this:

  1. Determine how much physical memory you have by going to Control Panel / System
    clip_image002

  2. Open SQL Server Management Studio (SSMS) and connect to the SQL Server running Kronos (‘eCommerce’),
    LiveJournal Tags: RCSI

    clip_image004

  3. Select the Instance (ecommerce) and do a right click and then select ‘Properties’
    clip_image006

  4. A Server Properties dialog will appear with the’General’ page selected on the left.
    clip_image008

  5. Click on ‘Memory’ on the left
    clip_image010

  6. Set the Minimum server memory to ½ of the available physical memory, on our sample machine we have 8 GB or 8000 MB, so we would change the Minimum Server Memory to 4000 (8000/2) as shown below
    clip_image012

  7. CAVAETS:

    1. The above is assuming that the machine is dedicated to KRONOS SQL Server Instance only.
    2. My own preference is Total Memory – 2GB, which would have resulted in 6000 being entered. You may wish to try both values.

Performance Improvement: Using Read Committed Snapshot Isolation (RCSI) option for SQL Server

A year ago I was involved in tuning Kronos for Costco and we found that the use of Read Committed Snapshot Isolation resulted in more than a 20% performance improvement. See my early post, Using RCSI with Kronos. The process is very simple:

  1. Open SSMS and connect to your Kronos SQL Server instance.
  2. Paste the code below:
 ALTER DATABASE Kronos
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE Kronos
SET READ_COMMITTED_SNAPSHOT ON

Click ‘Execute’ on the tool bar, or press Alt-X.

CAVAET:

  • Make sure that the drive(s) containing TempDB has lots of unused space; TempDB will grow significantly under load.
  • Make sure that you have at least one tempdb data file for every 2 cores that your machine has.
    • The ideal would be one tempdb data file per core with each tempdb data file being on a separate physical spindle.
    • See Optimizing tempdb Performance for more information