cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Performance Issues with On Premise SQL Server

We have a powerapp with many connection to a on premise SQL Server DB (multiple tables and views). This runs fine normally. We've started to roll it out to a small group of users, but we are now getting issues with performance when users press buttons that change or delete rows, where the page takes a long time to load and their changes aren't saved. This seems to happen mostly when many users are using it at the same time. Could there perhaps be a limit on how many connections can go through the On Premise gateway?

1 REPLY 1
Jinman
Microsoft
Microsoft

Firstly, please check SQL database, especially target tables, to see if transactions get locked the tables so long or not.

Your DBA can run SQL Profiler to monitor slow performing queries too. If target tables get locked by Delete/Update/Insert, other non-transactional queries(SELECT or some SPs) would be waited by unless Transaction Isolation level in SQL set to Read-Uncommitted.

 

Secondly, your on-premise data gateway also needs to check and monitor its performance. By following steps below, you can monitor what the performance of SQL queries via on-premise data gateway looks like.

https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-performance

Once you got the log and PBI report feel free to share with me, 'r'.

 

FYI. A node of on-premise data gateway will have cap the concurrent SQL connections to SQL to 200, which is big numbers and it could be limited ONLY WHEN ten thousand users are using the app simultaneously.  I don't think this is the case for you.

 

Your explanation gives me a feeling that SQL gets locked often. 

 

Can you share your AppId? I will check PowerApps telemetry for you.

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (1,890)