After a very long waiting period, I have finally released the initial version of my Columnstore Maintenance solution for SQL Server 2014. The version 1.1.0 of the CISL (Columnstore Indexes Scrtips Library), free & open source is live and available for download.
This is an important release and this is why I am incrementing .1 minor version to it’s number instead of a .0.1 (build version).
The importance of this release is that I have finally included Columnstore Indexes maintenance solution for SQL Server 2014. While it is incomplete (I had to edit some of the things that I am not totally happy about), it is stable enough to be run on production server.
You can set up all scripts for a specific version within 1 single file and you can cleanup all CISL Columnstore objects with another script.
Another important internal change is the inclusion of the version information within stored procedures, for easier determination of the currently used version.
But let’s take an overview of the changes that this release brings:
cstore_doMaintenance (SQL Server 2014)
This is the maintenance procedure that I have been promising for quite some time. It includes some nice things like trace flag 643 automatic detection and enabling of Tuple Mover invocation, configurable Segment Clustering, avoidance of Rebuilds under Dictionary Pressure, closure of the open Delta-Stores, logging, historical information of the detected fragmentations, and control of the vast majority of all available knobs.
cstore_install_all_stored_procs.sql (all versions)
This script includes all available stored procedure for the respective SQL Server version. It allows you to install them all in a database in an easier and more comfortable way.
cstore_cleanup.sql (all versions)
This scripts does exactly the opposite of what the previous script does: it removes all tables & stored procedures, related to CISL – thus performing a cleanup of your database.
Alignment.sql: (all versions)
+ Added new parameter for filtering on the object id – @objectId (cstore_GetAlignment.sql only)
* Changed constant creation and dropping of the stored procedure to 1st time execution creation and simple alteration after that
* The description header is copied into making part of the function code that will be stored on the server. This way the CISL version can be easily determined.
Dictionary.sql (all versions)
+ Added new parameter for filtering on the object id – @objectId (cstore_GetDictionaries only)
* Changed constant creation and dropping of the stored procedure to 1st time execution creation and simple alteration after that
* The description header is copied into making part of the function code that will be stored on the server. This way the CISL version can be easily determined.
Fragmentation.sql (all supported versions: 2014, 2016, Azure)
+ Added new parameter for filtering on the object id – @objectId (cstore_GetFragmentation only)
* Changed constant creation and dropping of the stored procedure to 1st time execution creation and simple alteration after that
* The description header is copied into making part of the function code that will be stored on the server. This way the CISL version can be easily determined.
Memory.sql (all supported versions: 2012, 2014, 2016)
+ Added new parameter for filtering on the object id – @objectId (cstore_GetMemory only)
* Changed constant creation and dropping of the stored procedure to 1st time execution creation and simple alteration after that
* The description header is copied into making part of the function code that will be stored on the server. This way the CISL version can be easily determined.
RowGroups.sql (SQL Server 2012)
– Fixed error with a semicolon inside the parameters of the stored procedure
RowGroups.sql (all versions)
+ Added new parameter for filtering on the object id – @objectId (cstore_GetRowGroups only)
* Changed constant creation and dropping of the stored procedure to 1st time execution creation and simple alteration after that
* The description header is copied into making part of the function code that will be stored on the server. This way the CISL version can be easily determined.
RowGroupsDetails.sql (SQL Server 2012)
– Fixed error with a semicolon inside the parameters of the stored procedure
RowGroupsDetails.sql (all versions)
+ Added new parameter for filtering on the object id – @objectId (cstore_GetRowGroupsDetails only)
* Changed constant creation and dropping of the stored procedure to 1st time execution creation and simple alteration after that
* The description header is copied into making part of the function code that will be stored on the server. This way the CISL version can be easily determined.
SQL Server Info.sql (all versions)
* Changed constant creation and dropping of the stored procedure to 1st time execution creation and simple alteration after that
* The description header is copied into making part of the function code that will be stored on the server. This way the CISL version can be easily determined.
Suggested Tables.sql (all versions)
* Changed constant creation and dropping of the stored procedure to 1st time execution creation and simple alteration after that
* The description header is copied into making part of the function code that will be stored on the server. This way the CISL version can be easily determined.
Another important start is that I have started building basic test procedures for ensuring future quality of CISL releases and during this year I am intending to implement a full suite of tests.
I will add a separate blog post on how to use cstore_doMaintenace stored procedure, but as for now you can already download and install it on your test VMs.