2

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.

2

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.

  • So I can assume that moving the reporting server to a different server will not help. Either the reports need to be changed so that they are not taken from live data (replication or datasets come to mind) or the source databases need to be tuned. – Reaces Jan 2 '15 at 15:56
  • I would look to see what gains could be made out of analyzing and tuning the reporting queries, absolutely. Of course, there are also other ways to offload reporting workloads as well. But that's probably a different conversation. – Thomas Stringer Jan 2 '15 at 16:13
  • Regardless, if I can prove that the cause is not what they are assuming it is (which I now can). I can get time to find out the proper cause / real solutions. Thank you for your input. – Reaces Jan 2 '15 at 16:14
  • Though I guess it will still hold resources such as memory grants until it ends. – Martin Smith Jan 2 '15 at 20:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.