SQL Server – Resource Cost of a Blocked Query

blockingperformancesql serverssrs

I'm currently reviewing a case where an SSRS server is experiencing frequent blocking and deadlocking.
The reports are not business-critical, and they are frequently killed to alleviate the blocking.

However, this same SQL Server has some critical databases on it (it's sharing a sql server with a large sharepoint farm), that have been showing declining performance.
The plan from my predecessor is as follows:

  1. Migrate 4TB worth of databases from A to B
  2. Migrate Reporting server (originally A) from B to C
  3. Migrate Reporting server (originally C) from C to B

The reasoning being that we will have better hardware (B), be able to get rid of an old server (A), and remove the reporting server (ends up at C) from the critical database server (now B).

However, I'm not entirely convinced that this will impact performance much (save for the newer hardware).
I've been monitoring the blocks for the better part of a week now, and it's consistently the WriteLockSession blocking a ReadChunkSegment. Which as far as I can tell from documentation (google) is perfectly fine.

How could I determine that/if a blocked process is causing slowdown on a different database?
If I could show that the performance issues are unrelated (reporting and other databases) other than the shared hardware, I would like to set up shared datasets. As most of the reports, and all of the long-running reports, are querying the same small subset of data.

Best Answer

A request that is being blocked does no work. Here's an example:

Create a blocking query

use AdventureWorks2012;
go

begin tran;
    update HumanResources.Department
    set Name = 'testing 1 2 3'
    where DepartmentID = 7;
--rollback tran;

Run a query that will be blocked by the above session

use AdventureWorks2012;
go

select *
from HumanResources.Department;

Now look at any "work" this blocked request could be doing (query sys.dm_exec_requests for this blocked session, and then wait some time and query again to compare/contrast):

select
    start_time,
    status,
    total_elapsed_time_sec = 
        total_elapsed_time / 1000,
    cpu_time,
    reads,
    logical_reads,
    writes
from sys.dm_exec_requests
where session_id = 52;  -- my blocked session's spid

waitfor delay '00:00:10';

select
    start_time,
    status,
    total_elapsed_time_sec = 
        total_elapsed_time / 1000,
    cpu_time,
    reads,
    logical_reads,
    writes
from sys.dm_exec_requests
where session_id = 52;  -- my blocked session's spid

enter image description here

What you see here is a request that is doing no work, as it is not in the running state. When it is suspended then it is not on a processor doing any work.

Related Question