Question

Azure SQL regulary hits 100%

Forms

Updated November 27, 2023

asked on November 20, 2023

Good day all,

Has anyone had experience with using Azure SQL Databases with Forms and what server level do you use?

We are currently using S1 - 20 DTU's and as per the image, every 15 mins something is running which maxes out the server. The application itself seems to have no performance issues as far as I can tell.

If it wasn't for this spike, I would reduce the server to a lower spec SQL level.

Thanks

Ian

AZURE SQL.png (41.12 KB)

0 0

Replies

replied on November 20, 2023

Hi Ian,

First, an S1 - 20 DTU database is absolutely tiny resource-wise.

Per Microsoft:

The Basic, S0, S1 and S2 service objectives provide less than one vCore (CPU). For CPU-intensive workloads, a service objective of S3 or greater is recommended.

In the Basic, S0, and S1 service objectives, database files are stored in Azure Standard Storage, which uses hard disk drive (HDD)-based storage media. These service objectives are best suited for development, testing, and other infrequently accessed workloads that are less sensitive to performance variability.

Which is to say that the query equivalent of a light breeze could cause the less-than-a-single-hyperthreaded-vCore to spike.

Forms performs a variety of regularly scheduled background tasks, including but not limited to cleaning up old instance data. The resources on this database are so low almost any of those could cause the "spike".

In any event, two recommendations:

Use an Elastic Pool to efficiently host a set of databases on a shared set of resources rather than allocating each individual database its own lower dedicated resources. E.g., 6 databases on an 100 DTU Elastic Pool instead of 6 single 20 DTU databases. This would give you a higher performance ceiling across all those databases.
Use Query Performance Insight for Azure SQL Database to see what query/queries are resource intensive and coincide with the utilizations spikes you're seeing. Looking at the query may provide some insight into what's happening, for example a hypothetical stored procedure named "sp_forms_old_instance_data_cleanup".

Hope that helps!

Sam

0 0

replied on November 21, 2023

Thank you for the explanation Sam.

We have moved our Forms to a Elastic Pool - 100 DTU. It still spikes every 6 mins to 100%.

I could analyze the query but really I can do nothing about it if its an application needed stored procedure. Just seems like LF is running a really inefficient script.

0 0

replied on November 22, 2023

Right, so it's important to note here that 100 DTUs is still quite small. Microsoft doesn't have a direct DTU-to-vCore model conversion chart due to how DTUs are calculated, but pricing is a pretty good proxy. 100 DTUs cost about the same as a 2 vCore/10.2 GB RAM instance.

Still, I suspect at 100 DTUs vs 20 the spikes don't last as long.

Ignoring storage for a moment, SQL query duration is measured in "total compute time (ms)", which represents the total/cumulative amount of time CPUs spent processing the query. Say you have a query that takes 100 ms of compute time and is highly parallelizable. With 1 vCPU/thread, it will take 100ms of real time to complete. With 2 vCPU/threads, it will take 50ms of real time to complete, with each vCPU/thread performing half the work.

To quote something from a SQL Server monitoring tool's docs page that's still broadly relevant here:

Ideally, Machine: processor time should average less than 50% for best SQL Server performance. If Machine: processor time exceeds an average 80% for a sustained time (five minutes or more), then a CPU bottleneck exists during this time, and you should investigate its cause. Keep in mind that short spikes in Machine: processor time above 80% are normal and expected, and only sustained high CPU utilization is a problem. If a spike indicates a plateau, not a sharp peak, this may indicate a slow running query is causing the CPU problem.

The graph pattern in your original post shows those problematic "plateaus". If you can't do anything about the application activity, you want adjust the SQL resources so those plateaus turn into short spikes. Maybe 100 DTUs is sufficient for that, maybe not.

Re:

I could analyze the query but really I can do nothing about it if its an application needed stored procedure. Just seems like LF is running a really inefficient script.

Part of why I suggested this is because we don't currently know and seeing the query will provide insight. It doesn't strike me as fair to say that you "could analyze the query" without having done so and then jump to a conclusion that "LF is running a really inefficient script". Maybe, maybe not, but there's definitely not enough information here to say.

Much of Forms' behavior is tunable, both in time intervals and with toggles for alternative operating modes that have better performance in some scenarios. For example, there may be an advanced configuration option called MaintenanceStoredProcedureIntervalMinutes.

Or it could be a heavy report accidentally scheduled to run every ten minutes! The point is that we don't know right now and it's easy to sanity check whether the query is:

Expected built-in application behavior that cannot be modified
1. Subcase: The query really is inefficient, in which case we're happy to look into optimizing it for a future Forms release.
Expected built-in application behavior that can be modified
Unexpected application behavior that should be investigated
User configured application behavior (e.g., scheduled reports) that can be evaluated for appropriateness

Feel free to post the query here after making sure to redact any customer-specific or sensitive information that may appear in it.

1 0

replied on November 27, 2023

Good day Sam,

Thank you for the feedback.

When looking at Query Analyzer, the main culprit seems to be this query -

INSERT INTO @allTasks

SELECT H.[history_id],

P.[bp_id],

H.[process_id],

H.[step_id],

H.[finish_date],

NULL AS [user_id],--C.[approver_id] AS [user_id],

H.[due_date]

FROM [dbo].[cf_bp_worker_instance_history] H

INNER JOIN [dbo].[cf_bp_steps] S

ON S.[step_id] = H.[step_id]

AND S.[process_id] = H.[process_id]

INNER JOIN [dbo].[cf_bp_processes] P

ON H.[process_id] = P.[process_id]

WHERE [status] = 'complete'

AND [step_type] = 'userTask'

AND [due_date] IS NOT NULL

OPTION (MAXDOP 1)

0 0

You are not allowed to follow up in this post.

Question

Question

Azure SQL regulary hits 100%

Replies

Sign in to reply to this post.