Poor Performance on Service Manager 2012?

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

SQLIndexFill

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.

SQLServerSettings

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.

LockPagesInMemory

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.

PVolumeMaint

 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.

GeneralDBSettings

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/

ODBPresize

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.

Retention

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!

 

 

Finally some update to this site!

Hi folks,

I finally have time to update this site. I plan to be more frequent with updates now, and use this blog as my personal wiki. The reasons for this is pretty simple:

I have started in a new job as Senior Premier Field Engineer (PFE) @ Microsoft!! I am so excited:) Guess it is time to start working, and posting again.

Cheers, and happy new year!

Installing SQL with a config file

When I am installing SQL for use as DB for System Center I usually use a standard to ensure that it is configured correctly. By following the simple steps described here ( http://technet.microsoft.com/en-us/library/dd239405.aspx ) you can easy fetch Your configuration file. TAKE CARE of this file, and store it securely.

If you need to do a DR With a reinstall of the SQL, you can get this moving pretty fast by the use of this configfile. Simply type “Setup.exe /ConfigurationFile=MyConfigurationFile.INI” and the SQL should be up an running in minutes:)

Note! You will have to take of the DBs after this, but the SQL server is back in business in no time.

Installing System Center prereqs with Powershell

Hi guys.

Beeing a lazy admin I tend to reuse my material in new projects. The other day I was implementing another SCOM-solution, and thought I should share my lazy tip:

1. When you install the Windows Server Roles and Features needed for the server, EXPORT the configuration it to XML.

2. Save the XML a smart Place, and be sure to take note of the servers and Versions running.

3. Install the New server by using the XML to ensure that all roles and features are configured identically (if that is what you need). Like this:

Open powershell, and run this command* Install-WindowsFeature –ConfigurationFilePath “C:\Scripts\Software\Microsoft\SCOM Roles\SCOMWindowsServerRoles.xml”

Just be sure to choose the correct folder and file, and you are good to go. Grab a coffee and let the server do the job. Great for DR-situations as well^^

Some tips about how to do the export can be found here: http://blogs.dirteam.com/blogs/sanderberkouwer/archive/2012/09/12/reusing-a-role-installation-xml-file-in-windows-server-2012-to-install-the-active-directory-domain-services-role.aspx

SC Warehouse syncronization – Queries to fix Management Pack PK problems

Recently i discovered that alot of my MPs in Service Manager vere failing and that the DW were not updated with any data. I filed an SR with MS premier support, and they helped me find this post on technet.

http://blogs.technet.com/b/thomase/archive/2013/05/13/data-warehouse-problems-after-upgrading-to-sp1.aspx

It is a very good post and the solution works however having hundres upon hundreds of events which you have to go through to create the SQL queries is extremely time consuming and tedious. Please read the blog article i have pasted above before continuing.

Here is a powershell script which searches for the events in Operations Manager event log and creates the SQL queries in one go. All you do is to change the time and date in the script before running it. Then run the queries manually on the DWRepository DB and restart the deployment of the management pack that failes. Repete this process untill all your MP’s have correct primary keys.

Please be smart and backup these three databases before you begin. DWDataMart, DWStagingAndConfig, and DWRepository.

Heres is what it does:

The script creates a three text files. events.txt, sortedEvents.txt and sqlQueries.txt in the current user folder.

1) Collects all relevant events and puts them in events.txt.

2) Searches through event.txt for primary keys eg. ‘ConfigItemServicedByUserFact_2013_Jan’ and puts these in sortedEvents.txt.

3) For each primary key in the sortedEvents.txt it creates the sql queries and puts these in sqlQueries.txt.

Be sure to run this script on the Service Manager DW Mangement server.

Download the script from here

I want to thank Thomas Ellermann for posting this on technet and Andrew Barton for finding the workaround. Thanks to Thomas who let me post my script on his blog.

 

Simplify IT Operations

Modern Service Management alters how we execute IT Operations

Litware

Almost married to Microsoft System Center 2012

Microsoft System Center Suite

Almost married to Microsoft System Center 2012

The Official System Center Service Manager Blog

Almost married to Microsoft System Center 2012

www.contoso.se

Almost married to Microsoft System Center 2012

Follow

Get every new post delivered to your Inbox.