My Report takes long time to load, where should I check initially?

If you are the DBA or DBE, and reports have been deployed in production environment, question like above is very common? Usually quotes are something like "Report has been working fine, suddenly it takes long time for same data load", "My report usually takes 2-3 minutes now it takes more than 5 minutes". Now being a DBA or DBE, what should we check? CPU usage? Memory consumption? Or query related to the report?

True, we have to check all the listed area, but most of the time, if it is an unexpected and unusual slowness, I think we should checks for blocks too, may be the first thing just after CPU and memory usage. Why should we check?

Generally, if something is being updated, SQL Server places exclusive locks on objects. It can be on a row, page, extent or entire table. If the dataset related to the report is being locked by an update process, then the report query has to wait unless default timeout period has been set (then it throws an error and stop waiting) or different Isolation Level has been set. Therefore it is always better to check whether something has blocked our code or not before starting the analysis of codes.

How do we check it? Simplest way is Activity Monitor. It indicates Blocking and query that is blocking can be viewed too. If you need more info, then can try with set of TSQL statements.

Let's see how we can quickly check something like that. Open an connection and execute the following code.

USE AdventureWorks2014;
GO

BEGIN TRAN
 UPDATE Production.Product
  SET Color = ''
 WHERE ProductID = 1;

Now open a different connection and execute the code below. It tries to access the same table which is being modified by first connection, hence you should not see a result of it until the transaction of the first connection either committed or rolled back.

USE AdventureWorks2014;
GO

SELECT * FROM Production.Product;
GO

Now let's open the Activity Monitor and see. It can be opened by Right-Clicking the server instance and selecting Activity Monitor. Here is the screen.

As you see, second query is being run with SID 57 and this indicates that it is being blocked by 56. If you see something like this, what it says is, your report is blocked by some other processes. Activity Monitor allows us to see the code related to other process, in this case code related to 56. All we have to do is, right-click on 56 and click on Details.

It allows us to Kill the process but should not do it unless we are 100% sure that it does not disturb anyone or damage the dataset. But the good thing is, this shows the exact issue for the slowness and we can act on it in an appropriate manner.

SELECT r.session_id, r.status, r.blocking_session_id,
 r.command, r.wait_type, r.wait_time, t.text BlockedCode
FROM sys.dm_exec_requests  AS r
 CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id  > 1;
GO

Here is the way of seeing Blocking Code.

SELECT t.text BlockingCode
FROM sys.dm_exec_connections  AS c
 CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS t
WHERE c.session_id  = (SELECT DISTINCT blocking_session_id
   FROM sys.dm_exec_requests  AS r
   WHERE NOT EXISTS (SELECT 1
    FROM sys.dm_exec_requests r2
    WHERE r.blocking_session_id  = r2.session_id
     AND r2.blocking_session_id  > 0)
    AND r.blocking_session_id  > 0);

If need to kill a process using TSQL, kill command along with SID can be used.