Jump to content

SQL server memory usage and application crash


Recommended Posts

I have very interesting problem with SQL server and it has probably nothing to do with uniGUI. Actually, I am sure it has nothing to do with uniGUI whatsoever. I just hope someone here can shed some light into this.

 

Our software is fairly large and both uniGUI standalone application and SQL server consume quite a lot of memory. When SQL server uses 4096Mb memory, which is most it can use, our software crashes. Only thing that is certain at this point is that when SQL Server uses maximum amount of memory, soon after our application will crash. As SQL server memory usage inevitably increases in time, application crash at some point is certain. I'm pretty sure SQL server isn't behaving badly in anyway. It has something to do with my code, FireDAC and uniGUI in combination. We currently still use SQL Server 2014 Express edition which is not ideal but that's what we got now. Also, upgrading database software would not be a real solution anyway even if it could alleviate this problem in short term because SQL server is most likely not the source of this problem.

 

Software we are using:

 

Delphi XE5 Architect

uniGUI Pro Plus 1.0.0.1410

SQL server 2014 Express edition

 

If anyone here has even a clue where to start looking, I'd be very greatful.

 

// Mika

Link to post
Share on other sites

Mika,

 

SQL Server shouldn't affect the stability of your application, desktop or web.

But if you open a query which returns a huge amount of records, you will be breaking your application and forcing SQL Server (or any other database) to use a lot of memory for creating the resultset.

If that is the case, you need to make sure that your requests are small enough. Take into account that you cannot show more than a handful of records client-side.

In FireDAC there are several ways of checking how many records a query will return.

Programs that used to work with BDE now fail with FireDAC.

It is just about handling huge amounts of data in queries.

 

Sorry if I cannot be more specific.

 

David

Link to post
Share on other sites

Possible scenarios :

 

1. I know there is a setting in SQL Sever that limits the ammount of memory used by the server.

    I don't know if it is also in the last Express but it's worth investigating.

    I used to run with 1 Gb without any problem. The rest might be used by the UniGui App.

2. Go to 64 Bit

3. Separate the SQL Server by the UniGui App. Put them on 2 different machines.

4. In your software avoid queries like : "Give me all invoices from 2000 till today"

5. Close all queries and commit all transactions when you close a form or a frame.

6. If you have no restricted requirements change the SQL Server to Firebird or Postgres.

    They are really good options and for 90% of applications there are no notisable speed differences.

Link to post
Share on other sites

Hello Mika,

 

As adragan said, separate your DB server from your UniGUI App.

Use FireBird or MariaDB or Postgres if you don't have a specific need to use SQL Server.

 

Whenever we used SQL server and application on the same machine we encountered some hidden problems like slowness and excessive memory consumption by OS, etc.

As you are using SQL Express 2014, so you are running on a 64 bits machine. SQL Server has a quite overhead load which is not required in most cases. Check if you UniGUI app is also 64bits. If UniGUI app is 32bits on 64bits platform and you run out of RAM, your app will crash.

 

We are in the process of evaluating UniGUI for a very large in-house application that might contain more than 700 forms and data modules. 300+ connections. So far we have decided to go with MariaDB second option is Postgres (MariaDB has performed better than Postgres in our case) with UniGUI. We are waiting for native support of UniGUI for Linux and application load balancing. Waiting for Mr. Farshad to provide us some good news in the regards.

 

Another factor is FireDAC. We use UniDAC or MyDAC from DevArt. FireDAC takes more memory than UniDAC for its datasets. On large datasets, it may be an issue.

 

 

Regards

Link to post
Share on other sites

Thank you all for your advices. I will go through our software and try to find places to improve based on your suggestions.

 

@Farshad, I didn't find any "Memory Overflow" entries in logs.

 

@DavidIzadaR, I know that SQL server should not affect our software like this but the fact is that our software crashes only when SQL server is using maximum amount of memory (4096Mb). Computer where all software is installed on, has 16Gb of memory. Memory usage of our software doesn't seem to correlate with crashes at all and faily normal memory usage is around 500-1000Mb.

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...