SQL Server Scan in PurView is running since long time

Question

SQL Server(on-perm) scan is running since long time, not seeing any progress on activity in log.

Answer 1

If a SQL Server scan is running for an extended period with no apparent progress in the log, consider the following steps:

Check Blocking:

Look for blocking processes that might be hindering the progress of the scan. Use the sp_who2 or sys.dm_exec_requests system views to identify any blocking sessions.

Review Indexing:

Verify that indexes on the scanned tables are in good shape. Fragmented or missing indexes can significantly impact scanning performance. Rebuild or update indexes as needed.

Update Statistics:

Outdated statistics can lead to inefficient query plans. Update statistics on the relevant tables using the UPDATE STATISTICS command.

Check Server Resources:

Inspect server resources (CPU, memory, disk I/O) to ensure there are no bottlenecks. A lack of resources can cause the scan to take longer than expected.

Monitor Disk Space:

Ensure that there is sufficient disk space available on the server. Running out of disk space can cause the scan to halt.

Review Query Execution Plan:

Use SQL Server Management Studio to review the query execution plan for the scan. Identify any inefficient operators or missing indexes suggested by the execution plan.

Check for Long Transactions:

Look for long-running transactions that might be holding locks and preventing the scan from progressing. Identify and address any such transactions.

Consider Database Maintenance:

Regular database maintenance tasks such as updating statistics, rebuilding indexes, and checking for fragmentation can help optimize performance.

Check for Locks:

Check for locks on the tables being scanned. Excessive locking can lead to delays. Use the sp_lock system stored procedure to identify and address lock issues.

Review SQL Server Logs:

Examine the SQL Server error logs for any error messages or warnings that might provide insights into issues affecting the scan.

Review Query:

Review the query being executed. Optimize the query if necessary, and ensure that it is using the appropriate indexes.

If, after performing these checks, the scan is still not making progress, consider involving your database administrator or support team for a more in-depth analysis and potential resolution.