CONTENTS

  • 1 Visual Studio 2008 Solution (conversion tested to Visual Studio 2010)
  • 1 master SQL script called Dashboards.SQL
  • 19 RDLs files
  • 4 Shared Data sources
  • 1 Shared Dataset

What is getting monitored?

  • CPU
  • SQL Server Memory stats
    • PLE
    • Buffer cache ratio
    • Server Memory
    • Buffer pool settings
  • Active user sessions
  • Active Trace information
  • General database information (Data File sizes, Log File sizes, Recovery Models etc)
  • Historical wait stats
  • Historical Data file IO
  • Expensive queries
    • By CPU
    • By duration
    • By logical reads
    • By physical reads
    • By logical writes
    • CLR
  • Query plan stats
    • When Plan cached
    • Showplan XML
    • Number of executions
    • Last execution times
  • Blocking information

Quick Guide

1) Run the Dashboards.sql script on the SQL instance(s) you would like to monitor. This SQL file contains the code to execute all the main components of the solution such as Stored Procedures and functions.

2) The solution also uses embedded TSQL to provide information, mainly from sys.dm_os_performance_counters so please make sure you have all your perfmon counters installed too.

3) Confirm that the query for the shared data set is valid for your environment as this will hold your list of SQL Server Instances (from CMS) and confirm the data sources are valid.

4) When you are ready to deploy to your Reporting Server you may want to read this guide to help you: https://msdn.microsoft.com/en-us/library/ms155802.aspx

5) Further setup documentation (if needed) and project details can be found @: http://www.sqlservercentral.com/articles/SQL+Server/127992/

SQL Server versions tested and supported

SQL Server 2008 SP3 - SQL Server 2014 SP1
Express, Standard and Enterprise editions tested - both for On-premise SQL Server and Azure VMs that have SQL installed (IaaS) - (As long as you can connect to Azure).

Last edited Jan 25 at 12:27 PM by BlobEater, version 22