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:
- Migrate 4TB worth of databases from A to B
- Migrate Reporting server (originally A) from B to C
- 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.