Progress in the week ending September 9th, 2018
Table of Contents
For the past week, I have been writing SQLWATCH documentation which is now available at https://sqlwatch.io/docs/. It is still being updated so if you do not find what you are looking for please contact the Team on the #sqlwatch channel on sqlcommunity.slack.com or raise an issue/feature request on GitHub.
The new snapshot_type concept is now live
I have been testing the new snapshot type over the past week and now made it live, available for download. Although it is not much use on its own it is a groundwork for coming up changes to be able to log different data at different times with different retention i.e. disk utilisation, index statistics and so on.
The snapshot type is in
[dbo].[sql_perf_mon_config_snapshot_type] and you can modify retention days to your liking.
A new primary key for the database (in testing)
When we drop a database and create a new one with the same name the performance metrics will continue as if it was the same database. I would not expect a lot o such scenarios but nevertheless, this is something we should account for. The new composite key now includes database_name and create_date which means we can now detect when a database is re-created and show accurate performance data for that specific database.
New naming standard
You may be aware of how this project started. I was using it as an ad-hoc data logger and would often run it in tempdb or an existing “dba” database to help to debug performance problems. This drove the object naming as I had to make sure they were unique enough not to cause problems. Now we have our own database this is no longer an issue. I can now also utilise the SQLWATCH name in jobs.
--procedures [dbo].[usp_logger_disk_utilisation] [dbo].[usp_logger_performance] [dbo].[usp_maintenance_retention] [dbo].[usp_maintenacne_configuration] --tables [dbo].[logger_disk_utilisation_database] [dbo].[logger_perf_os_memory_clerks] [dbo].[config_snapshot_type] [dbo].[config_database] --views [dbo].[vw_report_performance_counters] --jobs SQLWATCH-LOGGER-DISK-UTILISATION
I will be renaming existing objects gradually with each release.
I started DACPACs versioning which will help identify bugs and issues related to a specific version. It will also allow me to provide a coherent changelog. DACPAC version is available in
select * from msdb.dbo.sysdac_instances where database_name = 'SQLWATCH'
type_version contains the version of the DACPAC which is set in the Visual Studio project:
This will also mean small changes to the deployment mechanism as we are going to be registering DACPACs now. By default, they are not. If you do not register DACPAC it will not show up in msdb.
Disk utilisation logger (in testing)
The disk utilisation logger is currently only capturing databases and contains database size, allocated space, reserved space, index size, log space and log usage.
The storage requirements for 12 months worth of database sizing in hourly intervals is approx 5MB per database.