Sql-server – SSRS Blocking Processes

You could enabled RCSI (Read Committed Snapshot Isolation) which should allow users to access a snapshot of the tables that are being updated.

I would look into the import process as RLF mentioned. If the inserts and updates are being done from a single connection and that connection wraps everything it does in a transaction that is not committed until all of the inserts and updates are done that would cause the blocking. I would also look at where the data is coming from to perform the inserts and updates. If the data is coming from a select that is nested in the transaction that might explain the locking on a table that is not involved in an insert or update action.

This increases the usage of the tempdb so make sure it can handle the extra load:

• Implementing snapshot or read committed snapshot isolation in sql server a guide
• Monitoring snapshot isolation with perfmon in sql server video

This is what I use to monitor sleeping SPIDs causing blocking issues:

SELECT 
     s.session_id
    ,s.status
    ,s.login_time
    ,s.host_name
    ,s.program_name
    ,s.host_process_id
    ,s.original_login_name
    ,s.last_request_end_time
    ,CAST(t.text AS nvarchar(4000)) AS [text]
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_connections AS c
    ON s.session_id = c.session_id
CROSS APPLY (
    SELECT MAX(DB_NAME(dt.database_id)) AS database_name
    FROM sys.dm_tran_session_transactions AS st
    INNER JOIN sys.dm_tran_database_transactions AS dt
        ON st.transaction_id = dt.transaction_id
    WHERE is_user_transaction = 1
    GROUP BY st.session_id
    HAVING s.session_id = st.session_id 
) AS trans
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS t
WHERE s.session_id NOT IN (
        SELECT session_id
        FROM sys.dm_exec_requests
    )
    AND s.session_id IN (
        SELECT request_session_id
        FROM sys.dm_tran_locks
        WHERE request_status = 'GRANT'
    )
    AND STATUS = 'sleeping'
    AND is_user_process = 1;

Hope this helps.

To answer your question specifically, sys.dm_tran_database_transactions holds the information you're looking for.