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:
- Reorganize Index
- Rebuild Index
- Update Statistics
- 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)
