Development progress w/e 09/09/2018

Progress in the week ending September 9th, 2018

Documentation

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.

SQLWATCH snapshot type

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.

SQLWATCH database create date with status

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.

For example:

--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.

DACPAC versioning

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 msdb.dbo.sysdac_instances :

select * from msdb.dbo.sysdac_instances where database_name = 'SQLWATCH'

dbo.sysdac_instances

The type_version contains the version of the DACPAC which is set in the Visual Studio project:

Visual Studio DACPAC Version

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.

Register DACPAC

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.

SQLWATCH logger_disk_utilisation_database

The storage requirements for 12 months worth of database sizing in hourly intervals is approx 5MB per database.

This release will deploy a new job: SQLWATCH-LOGGER-DISK-UTILISATION as per the new naming standard.

This site, like most websites, uses cookies. By continuing to use this website, you agree to their use.