Ken Lassesen from Microsoft's SQL Server blog adds another tip to our readers. Below is a preview of another article he's written for all the Kronites out there.
-------------------------
Recently I had a conversation with Bryan deSilva, Chief Evangelist of Improvizations, a firm that specializes in Kronos 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. He 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
2. Open SQL Server Management Studio (SSMS) and connect to the SQL Server running Kronos (‘eCommerce’)
3. Select the Instance (ecommerce) and do a right click and then select ‘Properties’
4. A Server Properties dialog will appear with the’General’ page selected on the left.
5. Click on ‘Memory’ on the left
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
7. CAVAETS: The above is assuming that the machine is dedicated to KRONOS SQL Server Instance only. And 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
-
------------
How should one design and build Kronos interfaces?
What's the best approach to interfacing Kronos and my ERP? Should one use Connect/WIM or SQL? Integration Guru Eugene Harrison has an opinion.
Download the Kronos Interface Design Strategy White Paper Now!
Comments