ניתוח ביצועי דוחות של Reporting Services

כל שרתי ה RS מבוססים על בסיס נתונים בשם: ReportServer
בבסיס הנתונים ישנה טבלה בשם ExecutionLog אשר מכילה נתונים על ריצות של דוחות.
טבלה זו יכולה לשמש כ LOG לפעילויות שנעשות על השרת
ולהלן מספר דוגמאות לשאילתות כאילו שתוכלו להשתמש בהן לטובת כתיבת דוחות בקרה:

The 20 most recently run reports

  SELECT TOP 20
    C.Path, C.Name,
    EL.UserName,
    EL.Status,
    EL.TimeStart,
    EL.[RowCount],
    EL.ByteCount,
    (EL.TimeDataRetrieval
          + EL.TimeProcessing
          + EL.TimeRendering)/1000 AS TotalSeconds,
    EL.TimeDataRetrieval,
    EL.TimeProcessing,
    EL.TimeRendering
  FROM ExecutionLog EL
    INNER JOIN Catalog C ON EL.ReportID = C.ItemID
  ORDER BY TimeStart DESC


The slowest reports (in the last 28 days)

  SELECT TOP 10
    C.Path, C.Name,
    Count(*) AS ReportsRun,
    AVG((EL.TimeDataRetrieval
        + EL.TimeProcessing
        + EL.TimeRendering)) AS AverageProcessingTime,
    Max((EL.TimeDataRetrieval
        + EL.TimeProcessing
        + EL.TimeRendering)) AS MaximumProcessingTime,
    Min((EL.TimeDataRetrieval
        + EL.TimeProcessing
        + EL.TimeRendering)) AS MinimumProcessingTime
  FROM ExecutionLog EL
    INNER JOIN Catalog C ON EL.ReportID = C.ItemID
  WHERE EL.TimeStart>Datediff(d,GetDate(),-28)
  GROUP BY C.Path,C.Name
  ORDER BY
    AVG((EL.TimeDataRetrieval
        + EL.TimeProcessing
        + EL.TimeRendering)) DESC


The most active users

  SELECT TOP 10
    EL.UserName,
    Count(*) AS ReportsRun,
    Count(DISTINCT [C.Path]) AS DistinctReportsRun
  FROM ExecutionLog EL
    INNER JOIN Catalog C ON EL.ReportID = C.ItemID
  WHERE EL.TimeStart>Datediff(d,GetDate(),-28)
  GROUP BY EL.UserName
  ORDER BY Count(*) DESC


The most popular reports

  SELECT TOP 10
    C.Path, C.Name,
    Count(*) AS ReportsRun,
    AVG((EL.TimeDataRetrieval
        + EL.TimeProcessing
        + EL.TimeRendering)) AS AverageProcessingTime,
    Max((EL.TimeDataRetrieval
        + EL.TimeProcessing
        + EL.TimeRendering)) AS MaximumProcessingTime,
    Min((EL.TimeDataRetrieval
        + EL.TimeProcessing
        + EL.TimeRendering)) AS MinimumProcessingTime
  FROM ExecutionLog EL
    INNER JOIN Catalog C ON EL.ReportID = C.ItemID
  WHERE EL.TimeStart>Datediff(d,GetDate(),-28)
  GROUP BY C.Path, C.Name
  ORDER BY Count(*) DESC


Failed Reports

  SELECT TOP 20
    C.Path, C.Name,
    EL.UserName,
    EL.Status,
    EL.TimeStart,
    EL.[RowCount],
    EL.ByteCount,
    (EL.TimeDataRetrieval
          + EL.TimeProcessing
          + EL.TimeRendering)/1000 AS TotalSeconds,
    EL.TimeDataRetrieval,
    EL.TimeProcessing,
    EL.TimeRendering
  FROM ExecutionLog EL
    INNER JOIN Catalog C ON EL.ReportID = C.ItemID
  WHERE EL.Status <> ‘rsSuccess’
  ORDER BY TimeStart DESC