Changelog

SQLWATCH v2.2 beta 2.2.7275.26487

Beta can be installed via dbatools using the -PreRelease switch

  • Added Baselines to PowerBI Dashboard so we can see an overlay of performance data from past day, week and month.
  • Added more default Checks
    • Log Growths
    • Errors/sec
    • Logins/sec
    • Page life expectancy
    • Buffer cache hit ratio
    • Memory Grants Pending
    • Memory Grants Outstanding
    • Number of Deadlocks/sec
    • Readahead pages/sec
    • Lock Waits/sec
    • Lock Timeouts/sec
    • Lock Requests/sec
    • Average Wait Time (ms)
    • Page writes/sec
    • Page Lookups Rate
    • Page reads/sec
    • Lazy writes/sec
    • Free list stalls/sec
    • Page Split Rate
    • SQL Re-Compilations Rate
    • SQL Compilations Rate
    • Full Scan Rate
    • Long Running Open Transactions
    • Databases with no LOG backup
    • Databases with no DATA backup
    • Oldest DATA backup (days)
    • Oldest LOG backup (minutes)
    • Database page_verify not CHECKSUM
    • Databases not MULTI_USER
    • Databases not ONLINE
    • Databases with Auto Shrink Enabled
    • Databases with Auto Close Enabled
    • Queued actions have not been processed
    • Checks are failling
    • Check execution time is high
    • One or more disk will be full soon
    • Disk Free % is low
    • Action queue failure rate is high
    • Action queue is high
    • SQL Server Uptime is low
    • CPU Utilistaion
    • Blocking detected
    • Agent Job failure
  • Added more Action templates
  • Added more Reports
    • Long Open Transactions
    • Missing Log Backup Report
    • Missing Data Backup Report
    • Out of Date Data Backup Report
    • Out of Date Log Backup Report
    • Backup Report
    • Disk Utilisation Report
    • Blocked Processes
    • Agent Job failures
    • Indexes with high fragmentation Indexes with high fragmentation
  • Improved error logging for checks and actions
  • Bug fixes
  • Additional PBI dashboard to show Check status and results over time.
  • "Performance Issues" moved from PBI as checks to SQL.
  • Alternative dashboard for disk utilisation
  • Most of the data now exposed via dim and fact views
  • Updated wait stats categories for SQL 2016 as per tiger toolbox and Paul Randal's recommendation.
  • Excluded idle waits as per tiger toolbox and Paul Randal's recommendation
  • Added trend tables for Performance Counters with data aggregated over 1 hour and default retention of 2 years.
  • Added missing NOT NULL constraints where appropriate and removed unused columns (this will cause SqlPackage.exe to abort deployment due to potential data loss). Use with /p:BlockOnPossibleDataLoss=FALSE and take backup first.
  • Updated SSIS to reflect changes in the schema
  • Improved XES Blockers collector to reduce number of rows stored. Instead of storing every occurrence of the blocking chain it is now only storing the most recent occurrence.
  • Performance improvements.
  • Exclude DatabaseMail% Application name from long queries as it can hang around for a while every few minutes.
  • Added ability to remove all but most recent snapshot with the retention parameter = -1. This is handy for large collectors such as index stats and histograms.
  • Started capturing basic database options handy for alerting i.e. is OFFLINE, not MULTI_USER,, AUTO_CLOSE on, AUTO_SHRINK on etc
  • PowerBI throws an error when visuals that use R or Python are loaded even though they are not used. In this release unused visuals were removed to avoid such errors.

SQLWATCH v2.2 beta 2.2.7264.17906

To install beta release with dbatools, please use the -PreRelease switch.

SQLWATCH v2.1

  1. Fixed Cartesian product bug introduced in 2.0
  2. Re-worked PBI dashboard to remove custom data retrieval functions to utilise Direct Queries and benefit from Query Folding. It should now be also possible to deploy dashboard to the PBI Service and schedule it.
  3. As part of this work, PBI schema was simplified and GUIDs are no longer required. I have therefore migrated keys from GUIDs to numerical values which saves the storage utilisation.
  4. Further schema improvements reducing storage requirements by ~40-60%
  5. Started adding views that serve pre-calculated data for reporting which can be fed into any dashboarding solution.
  6. To improve reporting times, delta calculations have been pushed to the collector so all data is available in tables and no expensive calculation happen during reporting.
  7. Some code cleansing and aligning with standard
  8. Added config procedure to enable index compression, on top of the table compression introduced in 2.0
  9. Removed unused indexes
  10. Tweaked clustered keys for better performance
  11. Normalised wait types
  12. Removed calculations that are not used
  13. Improved wait stats collection by the mean of capturing snapshots in stage tables to correctly calculate deltas between snapshots where the first snapshot had 0 wait time.
  14. Started moving most of the business logic from PBI to the database tier
  15. Re-worked SSIS to address schema changes
  16. Improved central repository SSIS import as in some cases, it would try and collect data from remotes without having valid header which would cause FK violation.
  17. Post deployment configuration is now applied automatically by a mean of triggering the relevant agent job

Due to GUID migration it is not possible to directly upgrade from version 2.0

Documentation can be found at https://docs.sqlwatch.io

As always, please feedback any issues via GitHub Bug or on our Slack channel

SQLWATCH v2.0

Due to a bug creating duplicate table records, which creates a Cartesian product and causes exponential growth of downstream objects such as index stats and histogram I decided to pull this release until fixed as it could have some serious storage consequences. Apologies. Coding is hard.

Proud to announce the long overdue 2.0 release:

  • Updated Dashboard
  • Central repository with SSIS to pull data from remote instances
  • New collectors: os_schedulers, extended events and SQL Agent history
  • Unified and normalised schema
  • Bug fixes
  • UTC dates
  • New configuration procedures Documentation can be found at https://docs.sqlwatch.io/

SQLWATCH-2.0-beta6.2

A lot and not a lot of changes.

  • redesigned schema to accommodate server name as part of Primary Key for the central repository

That's it. PK changed in every table and procedure. Looks like small change but with a huge impact.

SQLWATCH-2.0-beta5

  • [fixed] handle collation match in job deployment logic
  • [fixed] address INT2 wait_stats overflow on busy systems
  • [fixed] added XES collection steps to to the performance collector job
  • [fixed] handling of empty wait_info recordset for default system_health session
  • [fixed] improved deployment of multi-step jobs
  • [fixed] added index stats collector step to the index collector job
  • [change] whoisactive job is now deployed disabled by default
  • [new feature] Added default filter on whoisactive collector to only collect sessions lasting over 15 seconds. This is configurable as a parameter in the procedure.

SQLWATCH-v1.3.3

  • Performance tweaks in [dbo].[ufn_time_intervals]

SQLWATCH-v1.3.20

  • [fixed] issue #47

  • [added] interrogate system_health extended event session to get information about long waits including query text, workers and long io requests.
  • [added] new snapshot (6) for query text with default 3-day retention
  • [added] new performance dashboard with lots of new tabs: Query statistics, memory, database performance, logical file performance, logical disk performance and generic performance counter analyser

  • [removed] dacpac from branches. these are binary files causing merge problems. dacpacs are now available in releases.

v.1.2.6841.38712 (2018-09-24)

  • [fixes] #52 hardcoded database name in one of the job steps breaking deployment via dbatools.io
  • [fixes] #53 missing snapshot_type_id = 3 causing PK violation.

v1.1.6833.36316 (2018-09-17)

  • [added] new PowerBI dashboard to monitor disk utilisation
  • [added] Win32_Volume logging via PowerShell
  • [added] [dbo].[ufn_time_intervals] to address poor performance of time interval generation
  • [removed] setup.sql as no longer supported
  • [fixed] sp_spaceused logging pre SQL2016 (the @oneresultset problem)

v1.0 Initial release