A lot of customers are having a poor performance on their Microsoft System Center Service Manager environment. I have been investigating endless of these, and it all seems to boil down to some basic mis-configurations as well as lack of resources. In this post I will try to give you some tips that have helped a lot of customers with improving their environment, as well as some other areas to investigate. I am currently running my demo environment on a Laptop with no hassle, so improvements can be easily achieved. Some of these configurations might help out with performance issues on other applications as well, but I would strongly recommend you to consult your vendor or DBA before implementing these.
NOTE! All these configurations might reduce performance during the configuration execution. I would recommend to do those outside business hours, and make sure to have valid backups of your systems and databases prior to commit the changes.
1. Starting with the SQL Backend for the operational database (ServiceManager)
We are starting with the Default Index Fill Factor. Set this to a value of “70”. More information about this can be found here
Make sure that the SQL server has suffient memory assigned. Be aware that the Windows Operating System, as well as other applications running on the same server, should have some RAM left. In general I would allow SQL to use all RAM except 4GB that will be reserved for the system. Also make sure that the SQL server has a minimum assigned amount of memory that correlates with the load on the databases. Karthick has a pretty good post around memory handling in SQL here.
It is a good idea to reserve the ability to lock pages in memory to the service account running SQL Server. This ensures that SQL will (Close to) always have priority on memory intensive tasks. The most important effect is that it prevents the operating system to cache the SQL memory Blocks to disk. You will want these to stay in the RAM! Again, Karthick has a great post about this as well.
As we all know SQL can be pretty hard on IO. It is therefore a good idea to reserve priority on Volume Maintenance Tasks to the SQL Service account. This ensures that SQL will (Close to) always have priority when there is need for organizing, and reorganizing, data on the disk system. “Basically what this enables SQL to do is not have to zero out bit by bit when it is grabbing disk during a DATA FILE autogrow or a create database statement.” The last phrase is taken from this blogpost.
2. Continuing with the database itself
Even if the SQL backend is tuned and optimized there are still multiple steps recommended to further enhance performance. Remember that unresponsive SCSM console might be caused by a poor/bad configured backend as well as a misconfigured database. It is a couple of neat settings you should put on the actual actual database:
a) Set the database in “Simple Recovery Model”. Please make sure to have a backup regime that support this setting. Consult your DBA!
b) Set Auto Shrink to “False”! I repeat; Set Auto Shrink to “False”!! Auto Shrink is the worst enemy for your SCSM database. Whatever you do, NEVER use auto shrink on the SCSM database (or any other DB unless it is required). Please read Paul Randal’s post about the topic. It is still the golden rule to follow.
Splitting your database file is also recommended. What I have found to work best is to use an amount Equals half amount of assigned cores. Example: Having 8 cores on the SQL server would mean that I would split the database into 4 files. The screenshot below is taken from a SQL with 2 cores; hence only 1 file. Consult your DBA to have the files splitted safely. You would might want to look on the findings documented here as a guidance for direction: http://blog.idera.com/sql-server/performance-and-monitoring/increase-sql-server-performance-using-multiple-files/
3. What about Service Manager?
It’s all about the big picture when it comes to performance. Service Manager is no exception to that. There are a few basics you really want to be aware of:
a) Never use a longer retention period in the operational database than you actually need. It dramatically reduces performance. The DB should have around 40-50% free Space to perform well, so get rid of the old data. You can report on those old data from the Datawarehouse if you need that, even after the retention period. In short terms retention only deletes data from the operational DB that is stored in the DW if the DW integration is working properly. In the example below a few datasets are reduced in terms of retention period.
b) Do you really need customizations to the console? If so… keep in mind that they are often implemented improperly, and do affect performance in a negative way.
c) Console users… All about the management servers. It is not recommended to have more than 50 concurrent users per management server. If you have that many you should assign at least 4 CPU cores and 32GB RAM to Ensure a good performance.
d) Running the SCSM as Virtual servers is no problem at all. You just have to keep in mind that the physical hosts actually need to have suffient resources available. Over-provisioning RAM and CPU can be very bad for intensive Applications such as Service Manager. Ensure that you don’t (ever) provision more than 85% RAM (have at least 4 GB free for the hypervisor) on the hosts. The guest OSes need to wait for available physical resources, so it will simply perform even worse if the hosts cannot handle the load.
Same is valid for Virtual CPUs as well. If you assign more cores than you have available the guest OS will have to wait for free cores. In general it can be better with 1 core clocked up to 6 GH+ than 6 cores at 1 GHz since the guest will only have to wait for that single core. For intensive Applications you might actually need more cores, but Ensure that your hypervisor is able to deliver.
Networking.. How does that perform in general? Check your VLANs, and separation of iSCSi, Hypervisors, Management and data traffic. It can be quite benefitial to configure your networking smart. I’ll leave that to your network team as I am only to be considered as a “user”.
e) Storage. Think about SCSM as a Heavy user of IO. Your Storage system must be able to deliver a decent amount of IOPS. Splitting SQL Database, Log files and DW Files on separate volumes (or SANs) can be very useful when it comes to performance tuning. Just Ensure that your Storage can handle it. I am running SCSM on my desktop on 4 SSDs with a decent 400K IOPS, and it does perform well. But neglecting the previous Chapters is a really good way of screwing Things up.
4. SQL Enterprise for DW and Reporting
If you are using the datawarehouse you should really consider using SQL Enterprise for your DW and Reporting. Really! I have never seen it perform well on a SQL standard. This is related to the SQL server itself, as there are some differences:
* SQL Enterprise does incremental Processing on the cubes, while SQL standard does not. This effectively mean that if you are running SQL Standard for the DW it will very often be to busy to accept new data from the operational database as the resources are assigned to cube Processing. You can hack and slack all you want, but please let me know if you manage to run it well on SQL Standard. I really want to know how. If the operational DB is trying to move data to an unresponsive DW the overall performance will drop for the entire system. And pretty much as well…
To make a clear statement: SQL Standard is supported for SCSM DW, but go for SQL Enterprise. Period!
Disclosure: These are my personal opinions based on endless hours of troubleshooting. Feel free to use them in your environment, but no warranties are given as your environment can be different. I have yet to see a system not benefitting from these settings, but be aware.. It is your system, and I don’t guarantee that it will work as well for you. As always: HAVE YOUR BACKUPS FIRST!