Application really SLOW - SQL Server and Mendix

2
I have an interesting and quite urgent question regarding SQL Server and Mendix communication. We have a situation where the application(connecting to a SQL Server db) has become VERY slow all of a sudden – we think it has something to do with the db because it is only slow on the data side of things, it retrieves the rest of the app fine. But when data is needed, it hangs quite badly. After investigation we found that the database commissioned for the application contains a number of indexes. Now after double checking I can guarantee that we have not enabled any indexes on any domain models for this project. So my questions: Does Mendix automatically generate indexes at will? We noticed that these indexes have not been recreated in the last 6months. This is – we think – why it is so slow. This is one theory. Question 2 : If we enable indexes on the domain models in the project, do they override the ones already in the database. If so, how often will they be recreated? Aside from the interesting questions, does anyone have any other theories as to why the application is so slow all of a sudden? Your help will be greatly appreciated! [EDIT] : Okay so we rebuilt the indices that Mendix created on the database and the application is working as it should(nice and fast). BUT this is probably not a permanent fix and means that there is a Mendix issue with the indices making it slow. Between 4.1.0 and 4.3.2, was there a database management improvement? Or more specifically an indexing improvement? We need to well motivate the need to upgrade to the latest version(numerous implications).
asked
5 answers
3

I'm not sure I get your question, you don't have to recreate indexes to keep them working.. once there is an index it will be updated by the database itself.

As for your question 1 and 2, Mendix generates indexes for columns in association tables (added: and on owner/changedBy columns) and the database itself generates some additional indexes based on primary keys defined by the runtime server. ALL of these are on ID columns and are not the kind of indexes you can define yourself in the model.

So there is no possibility of overriding anything. You can define other indexes in your domain model if there is an attribute you often need to search on.

answered
2

I am not sure about 4.x versions but in previous version Mendix always created the fields owner/createddate/changedate and these fields all had an index on them. I also guess that the pk's have an index on them, so all guid fields.

A situation we encounterd in one of project is that there is a point when the database becomes to large. After that point, queries on the tables that have become to large cannot be kept in memory anymore which in turn resulted in io waits due to swapping behaviour. While IO wait does not necesairly have to be bad, in our case it dramatically decreased performance. After a clean-up of the tables we went from 40second retrieves back to 1 second.

answered
1

I'm not on the Runtime team, so I might be wrong, but I believe that the indexes that the Runtime creates by default are just on the link tables and of the id fields in the entity tables.

When you add more indexes you cannot create those on the id's, so you can not overwrite them.

Why the application might be slow: consider refreshing the indexes from the database admin console. Mendix just gave the order to create an index, not how often it should be updated.

answered
1

For bigger SQL Server databases maintenance plan are a must. These plans keep the performance on a required level. Normally responsibility of the DBA but I can imagine scenario's that Mendix creates these plans automatically.

answered
0

I am finding in one of the mendix blogs that indexes created by runtime are ordered, so if we were to rebuild indexes on the database directly will that cause an issue with the application ? 

Also it looks like indexes created by runtime were done with a fillfactor value of 0, where as it is recommended to have this value >=80 . So I am planning to issue this as well during rebuilt but not sure if the Application running on Mendix runtime will like it or not... 

Alter Index  IndexName ON dbo.ObjectName REBUILD WITH(ONLINE=ON,MAXDOP=1, Fillfactor=90)

any suggestions please ?

I am finding in one of the mendix blogs that indexes created by runtime are ordered, so if we were to rebuild indexes on the database directly will that cause an issue with the application ? 

Also it looks like indexes created by runtime were done with a fillfactor value of 0, where as it is recommended to have this value >=80 . So I am planning to issue this as well during rebuilt but not sure if the Application running on Mendix runtime will like it or not... 

Alter Index  IndexName ON dbo.ObjectName REBUILD WITH(ONLINE=ON,MAXDOP=1, Fillfactor=90)

any suggestions please ?

answered