I ran a trace while the report was refreshing and I have attached the five queries that ran against the table.

I see that people have suggested possibly adding non clustered indexes, I was under the impressions that non clustered indexes on large tables where not a good idea and would be best to use a clustered index (preferably a columnstore) which is why I was considering moving the nvarchar(max) columns to its own table. Was I wrong to think this?

** edit - it wouldn't allow me to add the .sql file, here are the queries

-- query 1
SELECT
TOP (1000001) [t1].[Week Ending],
COUNT_BIG([t0].[EventClass])
AS [a0]
FROM
((select [Id],
  [EventClass],
  [TimeId],
  [StartTime],
  [DatabaseName],
  [ApplicationName],
  [LoginName],
  [SPID],
  [ServerName],
  [Duration],
  [CPU],
  [Severity],
  [Success],
  [Reads],
  [Writes],
  [TraceType]
from [dbo].[QueryStats] as [$Table]) AS [t0]

left outer join

(select [_].[TimeId] as [TimeId],
  convert(date, [_].[Date]) as [Date],
  [_].[DateName] as [DateName],
  [_].[CalendarYear] as [CalendarYear],
  [_].[CalendarQuarter] as [CalendarQuarter],
  [_].[CalendarQuarterName] as [CalendarQuarterName],
  [_].[CalendarPeriod] as [CalendarPeriod],
  [_].[CalendarPeriodName] as [CalendarPeriodName],
  [_].[CalendarDayOfMonth] as [CalendarDayOfMonth],
  [_].[CalendarYearName] as [CalendarYearName],
  [_].[WeekEnding] as [Week Ending]
from
(
  select [TimeId],
   [Date],
   [DateName],
   [CalendarYear],
   [CalendarQuarter],
   [CalendarQuarterName],
   [CalendarPeriod],
   [CalendarPeriodName],
   [CalendarDayOfMonth],
   [CalendarYearName],
   [WeekEnding]
  from [dbo].[Time] as [$Table]
) as [_]) AS [t1] on
(
[t0].[TimeId] = [t1].[TimeId]
)
)

WHERE
(
(
([t1].[Week Ending] < CAST( '20180321 00:00:00' AS datetime))
AND
([t1].[Week Ending] >= CAST( '20180124 00:00:00' AS datetime))
)
AND
(
([t0].[EventClass] IN (N'RPC:Completed',N'SQL:BatchCompleted',N'Query End'))
)
)

GROUP BY [t1].[Week Ending]

-- query 2

SELECT
TOP (1000001) [t2].[Region],
(COUNT_BIG(DISTINCT [t0].[LoginName]) + MAX(CASE WHEN [t0].[LoginName] IS NULL THEN 1 ELSE 0 END))
AS [a0]
FROM
(
((select [Id],
  [EventClass],
  [TimeId],
  [StartTime],
  [DatabaseName],
  [ApplicationName],
  [LoginName],
  [SPID],
  [ServerName],
  [Duration],
  [CPU],
  [Severity],
  [Success],
  [Reads],
  [Writes],
  [TraceType]
from [dbo].[QueryStats] as [$Table]) AS [t0]

left outer join

(select [_].[TimeId] as [TimeId],
  convert(date, [_].[Date]) as [Date],
  [_].[DateName] as [DateName],
  [_].[CalendarYear] as [CalendarYear],
  [_].[CalendarQuarter] as [CalendarQuarter],
  [_].[CalendarQuarterName] as [CalendarQuarterName],
  [_].[CalendarPeriod] as [CalendarPeriod],
  [_].[CalendarPeriodName] as [CalendarPeriodName],
  [_].[CalendarDayOfMonth] as [CalendarDayOfMonth],
  [_].[CalendarYearName] as [CalendarYearName],
  [_].[WeekEnding] as [Week Ending]
from
(
  select [TimeId],
   [Date],
   [DateName],
   [CalendarYear],
   [CalendarQuarter],
   [CalendarQuarterName],
   [CalendarPeriod],
   [CalendarPeriodName],
   [CalendarDayOfMonth],
   [CalendarYearName],
   [WeekEnding]
  from [dbo].[Time] as [$Table]
) as [_]) AS [t1] on
(
[t0].[TimeId] = [t1].[TimeId]
)
)

left outer join

(select [$Table].[UserRegionId] as [UserRegionId],
  [$Table].[UserName] as [UserName],
  [$Table].[EmailAddress] as [EmailAddress],
  [$Table].[Region] as [Region]
from [dbo].[UserRegion] as [$Table]) AS [t2] on
(
[t0].[LoginName] = [t2].[UserName]
)
)

WHERE
(
(
([t1].[Date] < CAST( '20180321 00:00:00' AS datetime))
AND
([t1].[Date] >= CAST( '20180219 00:00:00' AS datetime))
)
AND
(
NOT(
(
CHARINDEX(N'PBIUser\svc',
COALESCE([t2].[UserName], '')
)
= 1)
)
AND
(
([t2].[Region] IN ('Unknown','Americas','Asia Pacific','EMEA'))
)
)
)

GROUP BY [t2].[Region]

-- query 3

SELECT
TOP (1000001) [t0].[TraceType],
COUNT_BIG([t0].[EventClass])
AS [a0]
FROM
(
((select [Id],
  [EventClass],
  [TimeId],
  [StartTime],
  [DatabaseName],
  [ApplicationName],
  [LoginName],
  [SPID],
  [ServerName],
  [Duration],
  [CPU],
  [Severity],
  [Success],
  [Reads],
  [Writes],
  [TraceType]
from [dbo].[QueryStats] as [$Table]) AS [t0]

left outer join

(select [_].[TimeId] as [TimeId],
  convert(date, [_].[Date]) as [Date],
  [_].[DateName] as [DateName],
  [_].[CalendarYear] as [CalendarYear],
  [_].[CalendarQuarter] as [CalendarQuarter],
  [_].[CalendarQuarterName] as [CalendarQuarterName],
  [_].[CalendarPeriod] as [CalendarPeriod],
  [_].[CalendarPeriodName] as [CalendarPeriodName],
  [_].[CalendarDayOfMonth] as [CalendarDayOfMonth],
  [_].[CalendarYearName] as [CalendarYearName],
  [_].[WeekEnding] as [Week Ending]
from
(
  select [TimeId],
   [Date],
   [DateName],
   [CalendarYear],
   [CalendarQuarter],
   [CalendarQuarterName],
   [CalendarPeriod],
   [CalendarPeriodName],
   [CalendarDayOfMonth],
   [CalendarYearName],
   [WeekEnding]
  from [dbo].[Time] as [$Table]
) as [_]) AS [t1] on
(
[t0].[TimeId] = [t1].[TimeId]
)
)

left outer join

(select [$Table].[UserRegionId] as [UserRegionId],
  [$Table].[UserName] as [UserName],
  [$Table].[EmailAddress] as [EmailAddress],
  [$Table].[Region] as [Region]
from [dbo].[UserRegion] as [$Table]) AS [t2] on
(
[t0].[LoginName] = [t2].[UserName]
)
)

WHERE
(
(
(
(
(
(
(
(COALESCE([t2].[Region], '') = 'Americas')
OR
(COALESCE([t2].[Region], '') = 'Asia Pacific')
)
OR
(COALESCE([t2].[Region], '') = 'EMEA')
)
OR
(COALESCE([t2].[Region], '') = 'Unknown')
)
OR
(
[t2].[Region] IS NULL
)
)
AND
NOT(
(
CHARINDEX(N'PBIUser\svc',
COALESCE([t2].[UserName], '')
)
= 1)
)
)
AND
([t1].[Date] < CAST( '20180321 00:00:00' AS datetime))
)
AND
(
([t1].[Date] >= CAST( '20180219 00:00:00' AS datetime))
AND
(
([t0].[EventClass] IN (N'RPC:Completed',N'SQL:BatchCompleted',N'Query End'))
)
)
)

GROUP BY [t0].[TraceType]

-- query 4
SELECT
TOP (1000001) [t0].[TraceType],[t1].[MonthYear],[t1].[MonthYearSort],
(COUNT_BIG(DISTINCT [t0].[LoginName]) + MAX(CASE WHEN [t0].[LoginName] IS NULL THEN 1 ELSE 0 END))
AS [a0]
FROM
((select [Id],
  [EventClass],
  [TimeId],
  [StartTime],
  [DatabaseName],
  [ApplicationName],
  [LoginName],
  [SPID],
  [ServerName],
  [Duration],
  [CPU],
  [Severity],
  [Success],
  [Reads],
  [Writes],
  [TraceType]
from [dbo].[QueryStats] as [$Table]) AS [t0]

left outer join

(

SELECT [t1].[TimeId] AS [TimeId],(
COALESCE([t1].[CalendarPeriodName], '')
+ (N'-' +
COALESCE(
CAST([t1].[CalendarYear] AS VARCHAR(4000))
, '')
)) AS [MonthYear],
CAST(
(SELECT COALESCE(Op1 + Op2, Op1, Op2) FROM (SELECT
([t1].[CalendarYear] * 100)
AS Op1, [t1].[CalendarPeriod] AS Op2) AS AuxTable)
AS VARCHAR(4000))
AS [MonthYearSort]
FROM
(
(select [_].[TimeId] as [TimeId],
  convert(date, [_].[Date]) as [Date],
  [_].[DateName] as [DateName],
  [_].[CalendarYear] as [CalendarYear],
  [_].[CalendarQuarter] as [CalendarQuarter],
  [_].[CalendarQuarterName] as [CalendarQuarterName],
  [_].[CalendarPeriod] as [CalendarPeriod],
  [_].[CalendarPeriodName] as [CalendarPeriodName],
  [_].[CalendarDayOfMonth] as [CalendarDayOfMonth],
  [_].[CalendarYearName] as [CalendarYearName],
  [_].[WeekEnding] as [Week Ending]
from
(
  select [TimeId],
   [Date],
   [DateName],
   [CalendarYear],
   [CalendarQuarter],
   [CalendarQuarterName],
   [CalendarPeriod],
   [CalendarPeriodName],
   [CalendarDayOfMonth],
   [CalendarYearName],
   [WeekEnding]
  from [dbo].[Time] as [$Table]
) as [_])
)
AS [t1]
)
AS [t1] on
(
[t0].[TimeId] = [t1].[TimeId]
)
)

GROUP BY [t0].[TraceType],[t1].[MonthYear],[t1].[MonthYearSort]

-- query 5
    
SELECT
TOP (1000001) [t1].[MonthYear],[t1].[MonthYearSort],
(COUNT_BIG(DISTINCT [t0].[LoginName]) + MAX(CASE WHEN [t0].[LoginName] IS NULL THEN 1 ELSE 0 END))
AS [a0]
FROM
((select [Id],
  [EventClass],
  [TimeId],
  [StartTime],
  [DatabaseName],
  [ApplicationName],
  [LoginName],
  [SPID],
  [ServerName],
  [Duration],
  [CPU],
  [Severity],
  [Success],
  [Reads],
  [Writes],
  [TraceType]
from [dbo].[QueryStats] as [$Table]) AS [t0]

left outer join

(

SELECT [t1].[TimeId] AS [TimeId],(
COALESCE([t1].[CalendarPeriodName], '')
+ (N'-' +
COALESCE(
CAST([t1].[CalendarYear] AS VARCHAR(4000))
, '')
)) AS [MonthYear],
CAST(
(SELECT COALESCE(Op1 + Op2, Op1, Op2) FROM (SELECT
([t1].[CalendarYear] * 100)
AS Op1, [t1].[CalendarPeriod] AS Op2) AS AuxTable)
AS VARCHAR(4000))
AS [MonthYearSort]
FROM
(
(select [_].[TimeId] as [TimeId],
  convert(date, [_].[Date]) as [Date],
  [_].[DateName] as [DateName],
  [_].[CalendarYear] as [CalendarYear],
  [_].[CalendarQuarter] as [CalendarQuarter],
  [_].[CalendarQuarterName] as [CalendarQuarterName],
  [_].[CalendarPeriod] as [CalendarPeriod],
  [_].[CalendarPeriodName] as [CalendarPeriodName],
  [_].[CalendarDayOfMonth] as [CalendarDayOfMonth],
  [_].[CalendarYearName] as [CalendarYearName],
  [_].[WeekEnding] as [Week Ending]
from
(
  select [TimeId],
   [Date],
   [DateName],
   [CalendarYear],
   [CalendarQuarter],
   [CalendarQuarterName],
   [CalendarPeriod],
   [CalendarPeriodName],
   [CalendarDayOfMonth],
   [CalendarYearName],
   [WeekEnding]
  from [dbo].[Time] as [$Table]
) as [_])
)
AS [t1]
)
AS [t1] on
(
[t0].[TimeId] = [t1].[TimeId]
)
)

GROUP BY [t1].[MonthYear],[t1].[MonthYearSort]