Help determining possible reasons for SLOWSQL with SQL Server

Help determining possible reasons for SLOWSQL with SQL Server

  

We are seeing many SLOWSQL messages in our logs and when doing a deeper investigation as described in the following "certified" post: https://www.outsystems.com/forums/discussion/15702/tip-performance-of-query-in-outsystems-platform-slower-than-running-in-database/ I have a query that takes between 5000ms and 16000ms to run as reported by SLOWSQL, but runs instantaneously in SQL Server Management Studio from the platform server (query run as instructed in the post).

Can anybody suggest other investigative steps I can take to narrow down the issue? Is there a way to do additional tracing on SLOWSQL for example?

Thanks in advance!

Owain

Assuming that there is no Network/Application Server problem going on, could be a problem with the execution plan that is different in the application from the one is being used in the SQL Server Management studio...


You can take a look here: http://www.sommarskog.se/query-plan-mysteries.html

Jauch wrote:

Assuming that there is no Network/Application Server problem going on, could be a problem with the execution plan that is different in the application from the one is being used in the SQL Server Management studio...


You can take a look here: http://www.sommarskog.se/query-plan-mysteries.html

Unfortunately the post I reference has steps on how to retrieve the execution plan from the OutSystems queries and execute the query as OutSystems would so I do not think that is the issue.


Hum...


If the execution plan is exactly the same as it is being running from the platform, the SET options are the same, there is no parameter-sniffing problem and you are executing the query from the same place your application is, I fail to see why the times are different.

Owain,

Are you pulling a lot of data from the query? Like dozens of tables with dozens of attributes?

Owain, please check make sure it's not database/network latency, or some concurrent database backup/maintenance that is interfering with the performance.

Some query plans get optimized if you inline the parameters. For example WHERE Name LIKE @Search has very poor performance, but if you inline a value like WHERE Name LIKE 'Leonardo%', Sql Server might now use indexes because the first characters of the name are known. If possible, try to rule out this hypothesis.


Also, could you tell us if the table is consumed via integration, or was it created in OutSystems? And are you using aggregates or writing SQL queries?

To answer both Kilian's and Leonardo's posts:

  1. It is an aggregate on a OutSystems created tables with a "Max Records" setting of 1 so it is not a volume of data issue. The query ultimately is to see if there are any records found meeting the criteria. Using an aggregate it does query for a lot of fields when I don't require the values so is there a way to reduce the number of columns returned? I do not think this is the main cause of the issue, but it could offer a performance improvement.
  2. It is a join of 4 tables where all of the joins are on ID fields
  3. There are 4 filters on ID, Boolean, ID, Datetime

After creating this post I found through experimentation in SQL Managent Studio that one set of test values in the query in particular takes a long time and it is not clear to me why. I am trying to get a session with OutSystems support to review this specific case. I will update this thread with a resolution if we arrive at one.

Thanks!

Owain

Hi Owain,

The Platform optimizer will take care of the number of fields returned. In general, you should not copy the output of the query to a structure of list that has the same structures/entities, since this forces all data to be retrieved. I guess that's not what you do (you probably only check the List.Empty?), so that can't be the case here.

If it's a specific set of values that makes it slow it might be an execution plan gone awry. Especially filters on date/time can cause this we've noticed. In SQL queries we typically add hints for this.

Hi Kilian,

Yes it is only referenced using QueryResult.Empty.

The one query that is slower (and it is much slower) is simply varying one of the IDs in the filter. It is difficult to see why this would be the case, but using my knowledge the application and of the actual data it could be that one of the inner joins would come up with an empty result prior to the filters being applied. I have no idea why this would make it slower. It is difficult to explain exactly in this post, which is why I am hoping a shared session with support will more quickly review this specific scenario and might help uncover the underlying cause.

Hi Owain,

Ok, let us know whether you can get support to explain what's going on, I'm pretty curious myself :).

Hi Owain, did you get to the bottom of this? If you can share some details of how you solved this case, we would like to know, and our community would also benefit from it.

Thanks.

Hi Leonardo,

Due to timing restraints on both ends I have a call schedule to review this next Friday July 28th, I will update this thread then.

Owain

Solution

UPDATE: I was able to resolve this with OutSystems support.

We used SQL Server Profiler to view the query that was being executed on the SQL Server from OutSystems and the underlying issue was that the Aggregate query was quite inefficient.

This was not due to an issue with OutSystems, but Aggregate queries have limited options for expressing more complex queries.

When we reviewed the executed query and compared against existing indices, the OutSystems support representative noticed that one of the join clauses could break the efficiency of the index.

We resolved this by converting the Aggregate to an advanced query (manually) and using a UNION operator rather than a conditional JOIN clause.

This resulted in the same query being executed in 0.5s from 11s consistently trying various values for the parameters.


Owain

Solution

Glad you could find the problem!