nikoneugebauer / cisl Goto Github PK

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!

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.

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

Fix the Data Type of the [Min RowGroups] column in Suggested_Tables, by changing from SMALLINT to INT.

Automated update for the CISL through Powershell Function

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.

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

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.

Running CISL Stored Procs within TempDB produces duplicate information.

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?

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.

Make database snapshot (.dacpac) file available for each supported SQL Server version.

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 :)

In some orgs the usage of the [dbo] Schema for the maintenance objects is not acceptable.

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.

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.

Cover all basic SQL Server Versions Columnstore Features with Tests

Do you want to request a feature or report a bug?
There are two bugs:

1. It generated 'ALTER INDEX' statements two times.
2. It takes previous index name for next table.

Note: We've tried it with enabling @PreciseSearch too.

What is the current behavior?
Execution log as below:

MAXDOP: 0

Warning: The join order has been enforced because a local join hint is used.

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

SQLCommand:alter index CCI_TABLE_1 on [SCHEMA_1].[TABLE_1] reorganize alter index CCI_TABLE_1 on [SCHEMA_1].[TABLE_1] reorganize

Current Table: [SCHEMA_1].[TABLE_2]
Reorganize Open Delta-Stores
Location: Disk-Based
alter index CCI_TABLE_2 on [SCHEMA_1].[TABLE_2] Reorganize
+++++
Reason: Trimmed RowGroup Percentage
Rebuild: true
Rebuild Trimmed RowGroup Percentage
SQLCommand:alter index CCI_TABLE_2 on [SCHEMA_1].[TABLE_2] reorganize alter index CCI_TABLE_2 on [SCHEMA_1].[TABLE_2] reorganize
Executed Successfully

Current Table: [SCHEMA_2].[TABLE_3]
Reorganize Open Delta-Stores
Location: Disk-Based
alter index CCI_TABLE_2 on [SCHEMA_2].[TABLE_3] Reorganize
+++++
Msg 2727, Level 11, State 1, Line 1
Cannot find index 'CCI_TABLE_2'.

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,

Allow central DB installation and management for the information extraction and Columnstore Indexes management.

On hugely partitioned tables the queries might become incredibly slow.

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

Support for the Scripting of Extended Events targeting the Ring Buffer

Release the scripts that support Azure SQLDatawarehouse, APS & PDW