How I can make this query runs faster?
Thanks
Really this query is slow because you currently have a clustered index scan that must fully traverse all 1.3 billion rows in the Weekly Activity table (over 52 GB worth of data, and requiring a 5 GB memory grant to process!), and then hash match those against three different table outputs. The key to improving query performance here is to create more strategic indexing and/or partitioning (I can't currently tell what other indexes currently exist), of course keeping in mind how those indexes will affect the rest of the workload. Partitioning won't really help eliminate I/O in this case, since it seems that your report needs to present data for all of time. But it might help with distributing that I/O over multiple threads. If this is a VM you should strongly consider allocating more cores (and potentially more memory, but I can't tell if you're brushing up against any limits there – also can't tell what else is pressuring or being pressured at the time).