High Memory Exhaustion (18GB+) when auditing ~2300 SQL Permission resources via Azure Machine Configuration

[manuelgr0]

We are experiencing critical memory exhaustion (18GB+) on production servers when using Azure Machine Configuration (Audit Mode) with the SqlServerDsc module.

Our configuration generates roughly 2,300 distinct resources (SqlLogin, ServerRoles, SqlServerPermissions, SqlDatabaseUsers, SqlDatabaseRoles, SqlDatabasePermissions, SqlDatabaseObjectPermissions) to audit our security posture. When the agent processes this configuration, the memory spike starves the SQL instance.

Questions:

• Are there known scalability limits or memory leaks when processing this volume of SQL resources in a single pass?
• Does SqlServerDsc support batching permissions (handling multiple permissions in one resource) to reduce object overhead?
• Is there a recommended pattern for high-volume permission auditing to avoid OOM events?

Any advice on optimizing memory usage for this scenario would be appreciated.

[dan-hughes]

I'm assuming it's the gc_service or gc_worker that consumes the memory?

[manuelgr0]

Hi @johlju and @dan-hughes

Microsoft support told me that this issue is likely because of the SqlServerDsc-Module, which is a community module and they will not provide any support on this. :|

I did some further investigations on this:

We are currently on v17.3.0. As a reference configuration I am using a guest configuration package with these objects:

Category	Count	Type
SqlDatabases	41	Class-Based
SqlLogins	51	Script-Based
ServerRoles	10	Script-Based
SqlServerPermissions	53	Class-Based
SqlDatabaseUsers	237	Class-Based
SqlDatabaseRoles	399	Script-Based
SqlDatabasePermissions	256	Class-Based
SqlDatabaseObjectPermissions	188	Script-Based
TOTAL	1,235

On the virtual machine (test server with 8GB memory) this configuration uses all the memory. Over time the committed size of memory is ever growing until the process times out (default for guest configuration is 60min).

• The memory consumption reports show nothing unusual
• Using VMMap I can see that almost all of the 12GB committed is used in the "Private Data" category

I then created two new configurations where I split the class and script based resources:

Object Type	Count	Resource Type
SqlDatabases	41	Class-Based
SqlServerPermissions	53	Class-Based
SqlDatabasePermissions	256	Class-Based
TOTAL	350

Object Type	Count	Resource Type
SqlLogins	51	Script-Based
ServerRoles	10	Script-Based
SqlDatabaseUsers	237	Script-Based
SqlDatabaseRoles	399	Script-Based
SqlDatabaseObjectPermissions	188	Script-Based
TOTAL	885

--> Unfortunately, the gc_worker process is using full memory on both servers again with over 8GB of committed memory.

Do you guys have any other test scenarios or ideas how to fix this issue?

[johlju]

How to fix it, sorry to say, no. 🙂

The module SqlServerDsc uses sqlmanagementobjects (SMO) assemblies from one of the modules you have installed, either SQLPS module that comes with SQL Server, or SqlServer module that need to be installed (default if it is installed). So SqlServerDsc is only at fault if we use a pattern that is less optimal with SMO, otherwise it is MS products underneath.

If MS points to this module, you need to take it out of the equation and see if you can reproduce the same problem without it, maybe even without using DSC and guest configuration, like using the SMO assemblies directly on the host using the same calls the DSC resources uses.

Maybe you can dig further see which process that actually take up more and more memory over time, while it is running, like top 10 every 30 seconds. Could that help see where the memory goes?

You could also change the DSC resources to use T-SQL instead of SMO to see if that improves performance (ruling out SMO). Maybe AI could help with that, to change all SMO method calls to T-SQL queries in one or two resources that are used the most.

Bottom line, you need to try the find the root cause. 🙂

[manuelgr0]

Hi @johlju

Thank you for the further suggestions.
There is only one process: gc_worker

I will try to find a solution that works for us. Probably first checking to implement some of the DSC-Permissions-Resources with T-SQL. Hopefully that will improve things...

[dan-hughes]

Have you or are you able to test with the 17.5.0 module version?
It would be good to know if the changes to the SQL connection logic make any difference. I do not have a GC environment available to test, so any testing I would do would be using the LCM.

[johlju]

There is only one process: gc_worker

Since there is Guest Configuration's process that is the problem, not SQL Server, LCM or PowerShell process eating memory then maybe another test would be to try to just comment out all the method calls to SMO, then make sure Test-TargetResource always return false (so it always runs set on each run). That way you could see if it is just Guest Configuration eating memory by just running resources. If it is not eating memory then you know the fault is somewhere when calling SMO methods.