How Thingworx handle SQL queries

[JK_10565232]

Hi All,

we have problem with Thingworx solutions.

every once in a while sending data between TWX and MS SQL database slows down to such an extent that it is difficult for us to display production data.

We checked it on the server side during the failure, the TWX server and databases work without excessive loads.

The same query from MS Management Studio works lightning fast.

From the TWX level we only call Stored procedures that are in our database (separate from the TWX database)

I would like to ask how TWX performs queries to databases? Sometimes I have the impression that it asks for too much data in relation to what we want. The problem occurs for devices that have data from the last 6 years.

Thank you,

Jan

[mstarnaud]

Hi JK

Here's 3 suggestions to help debug the issue : 

- At the beginning & end of your SQL code, and ideally at some points in the middle as well, add records in a debug table with the current time & a common variable. Here's an example : 

DECLARE @guid UNIQUEIDENTIFIER
SET @guid = NEWID() -- This saves a random guid into the variable and will keep it for the rest of this execution

INSERT INTO DebugTable(TheGuid, Timestamp, Message) VALUES (@guid, GETDATE(), 'starting')

-- execute your procedure here

INSERT INTO DebugTable(TheGuid, Timestamp, Message) VALUES (@guid, GETDATE(), 'Ending')

The results will look like this : 

In this example, the 1st execution (the one starting with "7D4") took 6 seconds, and the 2nd execution (the one starting with "CA2") took 1.01 second. Using this you can find if the issue is within the SQL part of the system or outside of it. And also if you have more messages in the middle of your code then you can find if specific sections take more time. You could also add the stored procedure parameters in columns of your debug table, you could find if specific parameters (like a specific machine ID/name) take longer.

- In Composer, look on your SQL Thing, in the Configuration tab, for the "Maxium Number of Connections" setting. Too many times I have seen people set this to a very low amount (like 5). This is the number of parallel connections you can have, so if you have 8 services trying to run at the same time, then the first 5 will execute and the next 3 will wait for the first ones to finish before they can start executing. If you have multiple elements doing SQL queries based on the same timer, then this can be a big issue. I'm not an expert on this setting, all I know is most people I work with have it set to 50 and this works fine for them, but people with lower numbers often had slowness issues.

- You can look at your SP statistics by querying table sys.dm_exec_procedure_stats. The SP name can be found with : OBJECT_NAME(object_id, database_id). In particular, there's execution_count, total_elapsed_time (total time spent in the SP) and total_worker_time (total time working in the SP). Note that you can use command SP_Recompile procedure_name_here in order to reset those statistics. This is particularly good to know how long we're inside the procedure and how much time it's working (not blocked or waiting on something else).