Development progress w/e 02/09/2018

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.

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

SQLWATCH schema before snapshot_type

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.

SQLWATCH Schema Snapshot Type
SQLWATCH schema with snapshot_type

We can now define retention for a specific snapshot type separately:

SQLWATCH snapshot type

This has made the retention procedure slightly more complex but also universal:

delete top (@batch_size) 
from dbo.sql_perf_mon_snapshot_header
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.

Table of Contents
Marcin Gminski

Marcin Gminski

Following his degree in Telecommunications Engineering and Information Technology, Marcin has gained over 20 years’ experience and knowledge of the SQL Server administration, E2E performance testing and tuning, database development life cycle covering technical architecture, data analysis, logical and physical design including disaster recovery, specification, coding, implementation and support. Marcin has delivered projects in banking, retail, automotive and medical industries.
This site, like most websites, uses cookies. By continuing to use this website, you agree to their use.