nikoneugebauer / cisl Goto Github PK
View Code? Open in Web Editor NEWColumnstore Indexes Scripts Library
License: Apache License 2.0
Columnstore Indexes Scripts Library
License: Apache License 2.0
Do you want to request a feature or report a bug?
Bug
What is the current behavior?
Install-CISL throws errors about parameters which were not declared when run against a case sensitive SQL Server 2016 instance.
I was able to fix this by:
find: @currenttrimmedRGsPerc replace with: @currentTrimmedRGsPerc
find: @currenttrimmedRGs replace with @currentTrimmedRGs
I just did this across StoredProcs and saved all the files it changed (it was a couple), and after that was able to install A-OK.
Edit: I am no good at markdown but I think I finally got the capitalization to show up :)
Fix the Data Type of the [Min RowGroups] column in Suggested_Tables, by changing from SMALLINT to INT.
Do you want to request a feature or report a bug?
Bug, but not intentional - just need to update the scripts with the new 15 version number. I'll do a pull request for it - I'm not that bright, but it's easy work, ha ha ho ho.
What is the current behavior?
The vNext folder doesn't work on SQL Server 2019 because it's checking for 14 as the version number in lines like this:
-- Ensure that we are running SQL Server vNext
if substring(@SQLServerVersion,1,CHARINDEX('.',@SQLServerVersion)-1) <> N'14'
begin
set @errorMessage = (N'You are not running a SQL Server vNext. Your SQL Server version is ' + @SQLServerVersion);
Throw 51000, @errorMessage, 1;
end
What is the expected behavior?
Check for version number >= 15 (just aiming for future compatibility) and say 2019 rather than vNext.
Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
Just SQL Server 2019, and hasn't worked since 2019 RTM.
Do you want to request a feature or report a bug?
There are two bugs:
Note: We've tried it with enabling @PreciseSearch too.
MAXDOP: 0
Current Table: [SCHEMA_1].[TABLE_1]
Warning: The join order has been enforced because a local join hint is used.
Warning: The join order has been enforced because a local join hint is used.
Reorganize Open Delta-Stores
Location: Disk-Based
alter index CCI_TABLE_1 on [SCHEMA_1].[TABLE_1] Reorganize
+++++
Warning: The join order has been enforced because a local join hint is used.
Reason: -
Rebuild: false
Note: I've to change index, schema and table name due to confidentiality.
If the current behavior is a bug, please provide the steps to reproduce and if possible a minimal demo of the problem via http://sqlfiddle.com/
exec dbo.cstore_doMaintenance @execute = 1, @debug = 1;
exec dbo.cstore_doMaintenance @execute = 1, @debug = 1, @SchemaName = 'SCHEMA_1';
What is the expected behavior?
The order of IndexName and TableName should be in order and matched with Object_Id
Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
We've tried it with Azure managed database and SQL 2019 onPrem.
Please let me know if you need more information at [email protected]
Thank you.
Regards,
Tejas
I run the latest suggested_tables.sql on SQL 2019:
Warning: The join order has been enforced because a local join hint is used.
Msg 2627, Level 14, State 1, Line 119
Violation of PRIMARY KEY constraint 'PK__#TablesT__9A619291CF883BDB'. Cannot insert duplicate key in object 'dbo.#TablesToColumnstore'. The duplicate key value is (215723871).
The statement has been terminated.
Warning: Null value is eliminated by an aggregate or other SET operation.
I have fixed this way::
create table #TablesToColumnstore(
[ObjectId] int NOT NULL,-- PRIMARY KEY, -- <------!
[TableLocation] varchar(15) NOT NULL,
Make database snapshot (.dacpac) file available for each supported SQL Server version.
Cover all basic SQL Server Versions Columnstore Features with Tests
Do you want to request a feature or report a bug?
What is the current behavior?
If the current behavior is a bug, please provide the steps to reproduce and if possible a minimal demo of the problem via http://sqlfiddle.com/
What is the expected behavior?
Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
It's possible for the @IndexName variable to retain the previous value and therefore it will fail when trying to rebuild an index on a table where the index doesn't exist.
If the results of the following code return nothing e.g. if the table contain only open row groups then when the @IndexName variable is assigned in the next statement it would retain it's previous value.
-- Obtain Columnstore logical fragmentation information
insert into #Fragmentation
exec cstore_GetFragmentation @objectId = @objectId, @showPartitionStats = 1;
select 'objectid - ' + CAST(@objectId AS VARCHAR(50))
-- Obtain the name of the Columnstore index we are working with
select @indexName = IndexName
from #Fragmentation
where TableName = @currentTableName
This can be corrected by assigning a NULL value to the variable first
-- Obtain the name of the Columnstore index we are working with
SET @indexName = NULL
select @indexName = IndexName
from #Fragmentation
where TableName = @currentTableName
Do you want to request a feature or report a bug?
Bug
What is the current behavior?
When running cstore_doMaintenance on a system that needs to do an index rebuild, I get an error:
Msg 245, Level 16, State 1, Procedure cstore_doMaintenance, Line 142 [Batch Start Line 209]
Conversion failed when converting the nvarchar value 'OFF' to data type int.
It's related to this pull request, which fixed Azure SQL DB: #31
What is the expected behavior?
Run without erroring.
Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
It doesn't work for versions that return "OFF" for the database scoped config values like ELEVATE_ONLINE and ELEVATE_RESUMABLE.
In some orgs the usage of the [dbo] Schema for the maintenance objects is not acceptable.
Within the stored procedure there is a check to see if a database is part of an Always On availability group so that it doesn't execute on a read only secondary replica.
IF exists (select *
from sys.databases databases
INNER JOIN sys.availability_databases_cluster adc
ON databases.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups ag
ON adc.group_id = ag.group_id
WHERE databases.name = DB_NAME() )
begin
declare @replicaStatus int;
select @replicaStatus = sys.fn_hadr_is_primary_replica ( DB_NAME() );
SELECT @replicaStatus
if @replicaStatus <> 1
begin
BEGIN
SELECT 'Secondary Replica'
end
end
return;
end
This check is failing and stopping the rest of the procedure from running, even when the database is on a primary replica. I believe this is due to the IS NOT NULL check on the @replicaStatus, if this is removed it should then work.
IF exists (select *
from sys.databases databases
INNER JOIN sys.availability_databases_cluster adc
ON databases.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups ag
ON adc.group_id = ag.group_id
WHERE databases.name = DB_NAME() )
begin
declare @replicaStatus int;
select @replicaStatus = sys.fn_hadr_is_primary_replica ( DB_NAME() );
SELECT @replicaStatus
if @replicaStatus <> 1
begin
BEGIN
SELECT 'Secondary Replica'
end
end
return;
end
On hugely partitioned tables the queries might become incredibly slow.
Running CISL Stored Procs within TempDB produces duplicate information.
Automated update for the CISL through Powershell Function
Do you want to request a feature or report a bug?
Two Bugs in setup_all_extended_events.sql in the 2017 Folder (Columnstore\CISL-master\CISL-master\SQL-2017\Extended Events).
What is the current behavior?
Item 1:
States that the version of SQL Server is not correct. This is due to the following code:
-- Ensure that we are running SQL Server vNext
if substring(@SQLServerVersion1,1,CHARINDEX('.',@SQLServerVersion1)-1) <> N'13' --This should be 14 not 13 for all of the checks (looks like there are 8 in total).
begin
set @errorMessage1 = (N'You are not running a SQL Server vNext. Your SQL Server version is ' + @SQLServerVersion1);
Throw 51000, @errorMessage1, 1;
END
Once I got that fixed I ran into:
Item 2:
The event name, "sqlserver.clustered_columnstore_index_rebuild", is invalid, or the object could not be found
I looked through the events for 2017 and didn't find this one. I don't know if somehow I'm missing it or it has changed? I did find 'columnstore_index_rebuild', just no 'clustered' version in 2017.
If the current behavior is a bug, please provide the steps to reproduce and if possible a minimal demo of the problem via http://sqlfiddle.com/
Execute the setup_all_extended_events.sql from the 2017 folder (Columnstore\CISL-master\CISL-master\SQL-2017\Extended Events) on a SQL Server 2017 instance. (I'm currently running 14.0.3076.1)
What is the expected behavior?
Item 1: Pass when version 14 is installed.
Item 2: I'm not sure if there is an equivalent or not. If there is, then that would get setup as an event.
Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
Affects version 14 (SQL 2017). OS is Windows Server 2019. I haven't used it on any other versioning.
Thank you for these tools and your blog posts. They have been immensely educational. Looking forward to your book.
Allow central DB installation and management for the information extraction and Columnstore Indexes management.
There is a bug in doMaintenance
What is the current behavior?
If i start the sript i get some errors like "Incorrect syntax near '-'."
so i changed
Line 364+623: set @sqlcommand = 'alter index ' + @indexName + ' on ' + @currentTableName + ' Reorganize';
To: set @sqlcommand = 'alter index [' + @indexName + '] on ' + @currentTableName + ' Reorganize';
If the current behavior is a bug, please provide the steps to reproduce and if possible a minimal demo of the problem via http://sqlfiddle.com/
The IndexName is e.g. "ClusteredColumnStoreIndex-20190410-152738"
Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
SQL Server 2016 SP2 CU10
Do you want to request a feature or report a bug?
BUG :)
What is the current behavior?
https://github.com/NikoNeugebauer/CISL/blob/master/SQL-2016/suggested_tables.sql is reporting "Both" as "Compatible with" although there is a computed column in the table! "@showUnsupportedColumnsDetails = 1" also reports the computed column in the same table.
If the current behavior is a bug, please provide the steps to reproduce and if possible a minimal demo of the problem via http://sqlfiddle.com/
What is the expected behavior?
"Compatible with" should be "none"
Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64) Mar 18 2018 09:11:49 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: )
Thanks Niko!
Support for the Scripting of Extended Events targeting the Ring Buffer
Remove DMVs that require VIEW SERVER STATE permissions.
Most of the time, such in the case of dbo.cstore_GetRowGroups they need to removed, when user has no such access rights on the SQL Server Instance.
I run the procedure dbo.cstore_SuggestedTables on my big database:
Msg 2627, Level 14, State 1, Procedure cstore_SuggestedTables, Line 70 [Batch Start Line 0]
Violation of PRIMARY KEY constraint 'PK__#TablesT__9A619291537632C9'. Cannot insert duplicate key in object 'dbo.#TablesToColumnstore'. The duplicate key value is (215723871).
The statement has been terminated.
Release the scripts that support Azure SQLDatawarehouse, APS & PDW
The function cstore_SuggestedTables shows the total number of the computed columns within a database instead of showing the number of computed columns per specific table.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.