In the continuation of the ConfigMgr\MEM series, our next session is going to be looking at Database Performance, how this impacts the system, and what you can do to optimize what you have.

The SQL Server component is easily the busiest part of MEM, and slow database performance is something that I run into on every client.

Before we get into the causes of Performance loss, we need to Talk SQL Basics

SQL Basics

Our Database is made up of Tables that store the information that is sent by the Clients, as well as Indexes\statistics

An Index is a database structure created to speed up data retrieval.
Heavily fragmented indexes can degrade query performance – index maintenance is absolutely necessary

Statistics are used by the query optimizer to determine the distribution of data in a column
Out-of-date statistics result in poor query plans and slow running queries

There are a couple of SQL Best Practices for MEM that will be mentioned below, however, I would HIGHLY recommend to have a look at the SQL Whitepaper for SCCM

Essential facts about SQL Server for MEM:

• Only dedicated instance for MEM is supported i.e. don’t share a named instance with another product like SCOM for instance
• The default SQL settings may not be optimal for MEM
• A custom maintenance plan may be helpful for Indexes and Statistics
• A highly optimized SQL Server is a must for high-load environments

Some best practices for SQL:

• Make sure the MEM database recovery model is set to “Simple” for best performance (unless using “Always On”)
• Set Auto Growth to a fixed Megabyte value for data and log files – Not a % based increase

The Problem then…

How do I know if there are issues with my SQL Performance?

Indicators of poor SQL performance:

• A sluggish Admin console – click and wait and wait…
• Collections update takes longer than expected
• DRS links degrade occasionally
• Operations timeouts (apply drivers step, updates deployment)

So let’s start looking at the Tips for Improving your SQL.

Note: we are not covering MAXDOP or any of the more advanced topics in this post, that will be handled in the Advanced performance Tips post that will follow

SQL Performance Tips

1.  Check your SQL File allocation

• The tempdb is used a lot – place it on separate drive to the Log and MDF files.
• To reduce tempdb contention, divide the database file into multiple files of equal size
•  The number of files = # of logical CPUs (up to 8)
• Pre-allocate space for tempdb (approximately 20-25% of the size of MEM’s database)
• Set the file growth increment to a reasonable size – frequent, small auto-growths affect performance. 1GB fixed size growth for MDF file is usually recommended
• Database file access is random – log this access sequentially
• If possible, try to place data and log files on different drives
• Never install your MEM\CM on the same drive as your SQL installation, nor on your OS Installation drive.

2. Check your Tables

Beware of large tables in your MEM Database. These may severely impact performance on insert and read operations, depending on the server’s resources:

These are typically your Tables that are > 10GB in size, or have > 10 million rows

Example: dbo.SoftwareInventory

Effect: slow collections or reports processing while querying that table

Example: DRSReceivedMessages

Effect: DRS links degrading, replication issues

Some ways of finding large tables:

• SQL Server Management Studio GUI
• Stored procedure sp_spaceused
• Dynamic Management View sys.sysindexes
• Report Disk Usage by Top Tables
  

  SELECT      T.name TableName,i.rows NumberOfRows

  FROM        sys.tables T

  JOIN        sys.sysindexes I ON T.OBJECT_ID = I.ID

  WHERE       indid IN (0,1)

  ORDER BY    i.Rows DESC,T.name

Now i have Large tables, what NOW?

Take actions for deleting any excess records that may exist:

Inventory tables

Only collect data that is really required – Do you need to collect everything in hardware Inventory?

• Enable maintenance task Delete Aged Inventory History and schedule it accordingly .
• Database Replication and Change Tracking tables

Check Data Retention (Don’t set this to less than 3 days. The default is 5 days. i.e. I can miss up to 5 days worth of replication data, before all the replication groups are re-initialized. Do not set it too long either, as it is more data to keep in the DB)

• Verify that maintenance tasks run with select * from SQLTaskStatus query or Status Messages. You can also have a look at smsdbmon.log, to verify if your tasks are running

3. Consider using Forced Parameterization

Forced parameterization may improve the performance of MEM databases by reducing the frequency of query compilations and recompilations.

At some installations, it is observed that DRS, for example, works faster with forced parameterization enabled.

This setting can be configured in SQL Management Studio > Site Database properties > Options > Forced Parameterization

4. Turn on “optimize for ad hoc workloads”

The optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many single-use ad-hoc batches (typically seen in DRS replication)

When this option is set to 1, the Database Engine stores a small compiled plan stub in the plan cache (as opposed to the full compiled plan) when a batch is compiled for the first time.

This helps to relieve memory pressure by not allowing the plan cache to become filled with compiled plans that are not reused.

This setting can be configured in SQL Management Studio > Server properties > Advanced 

5. It’s all about the Defrag

By far one of the biggest issues that I come across, is that the DB is not kept fragmentation free.

As mentioned above Indexes and statistics are key to fast data retrieval.

In this regard, MEM has a built in Maintenance Task, that can be enabled.

It is easy to set up, however there are some limitations

Limitations:

• Runs only if other jobs are finished and prevents new jobs from running
• Rebuilds all indexes with more than 10% fragmentation
• Does not update statistics

For MEM installations, however I would highly recommend to  disable this feature and use a custom SQL Maintenance Plan instead.

Custom SQL Maintenance plan

A custom maintenance plan is by far the most effective way, to keep the DB fragmentation free, and if run daily, keeps the DB lightning fast. (you can also include your WSUS Database as part of this maintenance plan)

The Plan must include the following tasks:

1. Reorganize Index
2. Rebuild Index
3. Update Statistics
4. Clean Up History

Use index and statistics maintenance solutions based on the index fragmentation level:

Index fragmentation in percent:   5-30% – REORGANIZE

  >30% – REBUILD. 

Conclusion

By implementing these 5 basic steps, you are well on your way to a better running MEM DB.

As mentioned above, the next post will cover some more advanced Performance topics (Queries, Collections, Inboxes, and how these affect your performance as well)