As the development is underway and the project is getting more and more interest, I thought it may be a good idea to document the process and produce a short weekly (or bi-weekly, depending on the number of changes) summary of key achievements for those of you who do not follow us on GitHub. The project started about a month ago and this is the first email of this type. Call it a weekly stand-up.
Table of Contents
Migrated to Visual Studio Projects
This will bring many benefits to the project, it will make development easier and more importantly, it will streamline deployments. Thanks to the SQL Server Database Deployment
SqlPackage.exe tool we do not have to cater for different versions people may have deployed. The desired state like approach will always bring no matter how old version to the most recent. I have recently written a post on my personal blog about the benefits of using Visual Studio for Database Development
Implemented CI/CD pipeline in GitHub
With the help of AppVeyor integration, every Pull Request will now trigger an automated build and automated test on SQL Server 2008, 2012, 2014, 2016 and 2017.
AppVeyor will spin up given SQL Server instance on-demand, execute build, deployment and a test. If any of these steps are not successful the entire process will fail and Pull Request will be rejected.
The test is running our collector and retention procedures with some delays to simulate the real-life behavior
sqlcmd -Q "use [SQLWATCH]; /* we have to wait a minute after start for performance counters to come up otherwise it will try to insert NULL and fail */ waitfor delay '00:01:00'; exec [dbo].[sp_sql_perf_mon_logger]; /* simulate second run with a bit of delay */ waitfor delay '00:00:10'; exec [dbo].[sp_sql_perf_mon_logger]; /* now run retention job with some delay again */ waitfor delay '00:00:05'; exec [dbo].[sp_sql_perf_mon_retention] @retention_period_days = -1" -b -I
Below is an output from the AppVeyor pipeline:
Implemented auto-adding new databases
I originally created this project to run on an ad-hoc basis. The initial configuration, such as table creation and reference data load only happened once – during the installation. This also included adding databases to
[dbo].[sql_perf_mon_database] . This means that any new databases created after the SQLWATCH has been deployed would not show up on the dashboard. I have addressed with a new procedure:
[dbo].[sp_sql_perf_mon_add_database] scheduled to run every hour to pick up any new databases. This procedure also runs during the installation so we have it all in one place
Introduced snapshot type (under test)
The project started as a performance data logger with a PowerBI dashboard. It was a relatively simple concept with a simple header table with foreign keys to data tables.
The retention job was deleting snapshots from the header table which, with the help of foreign keys and cascade deletes, was catering for data removal in the child tables.
This however made it impossible to have different retention for different collectors i.e. 7 days for frequent performance collectors and 6 or 12 months for database growth. We needed a way to log different snapshots.
I have introduced a snapshot type table and composite keys including the original PK
snapshot_time and the new
snapshot_type_id to account for a possibility for different loggers to run at the same time.
We can now define retention for a specific snapshot type separately:
This has made the retention procedure slightly more complex but also universal:
delete top (@batch_size)
where exists (
select sh.[snapshot_time], sh.snapshot_type_id, st.snapshot_retention_days
from dbo.sql_perf_mon_snapshot_header sh
inner join [dbo].[sql_perf_mon_config_snapshot_type] st
on sh.[snapshot_type_id] = st.[snapshot_type_id]
where datediff(day,sh.snapshot_time,getdate()) > st.snapshot_retention_days
and dbo.sql_perf_mon_snapshot_header.[snapshot_time] = sh.[snapshot_time]
and dbo.sql_perf_mon_snapshot_header.snapshot_type_id = sh.snapshot_type_id
set @row_count = @@ROWCOUNT
Adding snapshot type allows me to focus on the upcoming features: Logging the growth of databases and tables.