SIMS user lock issue

[KingDuncan]

We have a rather complex locking issue in SIMS (outstanding since March). I want to see what "normal" behaviour looks like, so I would like to communicate with an IT manager that fits into this spec

- Uses SIMS on premise

- Uses SIMS Next Gen (and by extension has SSM running on their server)

- Is comfortable logging into their SIMS SQL Database using Microsoft SQL Server Management Studio

- Is happy to run a simple SELECT query on their database to observer the behaviour of a particular program initiated by SSM

- Not essential, but a Single Academy Trust

 

If this is you, I would be very happy to make contact.

 

The issue we have is that SSM is running a procedure at 5 min to the hour and 25 min past the hour for 20 minutes. While this procedure is running, various user activities on SIMS causes a lock, which then cascades to all users on SIMS. All locks are released 15 min after the start of the SSM procedure. I am really interested to see if the same program is being run on any other system, and how long it takes to run. At the moment the query performs over 338 million logical reads of the database which seems quite high.

 

I am working through our normal support channel and this problem has made its way all the way to the developers where we have had a patch designed and installed on our server, but we still have issues. I really want to check how long the particular query runs on another server.

 

Many thanks

Duncan

 

 

[David44]

Could you post the SQL query here?

[KingDuncan]

This is what I run at various intervals. This query will highlight the users who are locked and the locking session. It also will display the SSM user while it is running.

 

SELECT
    r.session_id,
    s.login_name,
    s.host_name,
    r.start_time,
    DATEDIFF(MINUTE, r.start_time, GETDATE()) AS elapsed_minutes,
    r.cpu_time,
    r.logical_reads,
    r.writes,
    r.wait_type,
    r.blocking_session_id,
    t.text AS running_query
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s
    ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE s.is_user_process = 1
ORDER BY elapsed_minutes DESC;

 

It is obviously just a snapshot so I have to refresh at regular intervals.

 

 

I also use this to check how long the SSM user has been running for (note - the user account defined in our SSM is SSMConnect).

 

SELECT
    s.session_id,
    s.login_name,
    at.transaction_begin_time,
    DATEDIFF(MINUTE, at.transaction_begin_time, GETDATE()) AS OpenMinutes,
    r.status,
    r.wait_type,
    st.text
FROM sys.dm_tran_active_transactions at
JOIN sys.dm_tran_session_transactions stt
    ON at.transaction_id = stt.transaction_id
JOIN sys.dm_exec_sessions s
    ON stt.session_id = s.session_id
LEFT JOIN sys.dm_exec_requests r
    ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE s.login_name LIKE '%SSMConnect%'
ORDER BY OpenMinutes DESC;

 

Thanks

[KingDuncan]

Hi David,

 

Is this something that you are able to run?

 

Thanks