search

olahallengren / sql-server-maintenance-solution

SQL Server Maintenance Solution
https://ola.hallengren.com
MIT License
2.88k stars 743 forks source link

Columnstore Index Maintenance Not Working #399

Open imyourdba opened 4 years ago

imyourdba commented 4 years ago

Description of the issue We built new Non-Clustered Columnstore indexes on the database and ran the following to check if they are being pulled into the index maintenance and no not see them. I sanitized the database name in the code below.

SQL Server version and edition SQL 2019 - Version 15.0.4033.1 - Developer

Version of the script 2020-01-26 14:06:53

What command are you executing? EXECUTE dbo.IndexOptimize @Databases = 'Database' , @Indexes = 'Database.dbo.Orders.NCCI_Orders' , @FragmentationLow = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE' , @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE' , @FragmentationHigh = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE' , @FragmentationLevel1 = 10 , @FragmentationLevel2 = 30 , @MinNumberOfPages = 1 , @LogToTable = 'Y' , @Execute = 'N'

What output are you getting? Date and time: 2020-06-19 16:05:10 Server: Server Version: 15.0.4033.1 Edition: Developer Edition (64-bit) Platform: Windows Procedure: [Database].[dbo].[IndexOptimize] Parameters: @Databases = 'Database', @FragmentationLow = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 10, @FragmentationLevel2 = 30, @MinNumberOfPages = 1, @MaxNumberOfPages = NULL, @SortInTempdb = 'N', @MaxDOP = NULL, @FillFactor = NULL, @PadIndex = NULL, @LOBCompaction = 'Y', @UpdateStatistics = NULL, @OnlyModifiedStatistics = 'N', @StatisticsModificationLevel = NULL, @StatisticsSample = NULL, @StatisticsResample = 'N', @PartitionLevel = 'Y', @MSShippedObjects = 'N', @Indexes = 'Database.dbo.Orders.NCCI_Orders', @TimeLimit = NULL, @Delay = NULL, @WaitAtLowPriorityMaxDuration = NULL, @WaitAtLowPriorityAbortAfterWait = NULL, @Resumable = 'N', @AvailabilityGroups = NULL, @LockTimeout = NULL, @LockMessageSeverity = 16, @StringDelimiter = ',', @DatabaseOrder = NULL, @DatabasesInParallel = 'N', @LogToTable = 'Y', @Execute = 'N' Version: 2020-01-26 14:06:53 Source: https://ola.hallengren.com

Date and time: 2020-06-19 16:05:10 Database: [Database] State: ONLINE Standby: No Updateability: READ_WRITE User access: MULTI_USER Recovery model: SIMPLE Is accessible: Yes

Date and time: 2020-06-19 16:05:15

Completion time: 2020-06-19T16:05:15.0511110-05:00

imyourdba commented 3 years ago

OMG, I dug deep into the code and found that on line 1971 there is a reference to @MinNumberOfPages = 0 and it turns out that Columnstores show zero pages used! So everything is working as it should, but we don't have any documentation anywhere that says to use @MinNumberOfPages = 0 in order to maintain columnstores in a separate job (still need to use INDEX_REORGANIZE in order for them to be maintained too).

imyourdba commented 3 years ago

Actually, the Clustered Columnstore work fine as described above, but nothing I do can make the non-clustered columnstore indexes get maintained by the solution. Those are definitely broken.

PhilM-IT commented 3 years ago

don't suppose there is any plan to update the code to address this scenario. We just came across the same problem. Luckly we're using clustered so we can try this workaround but ideally we'd not need to do this for making that work.

christillman commented 2 years ago

The premise is wrong: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-column-store-row-group-physical-stats-transact-sql?view=sql-server-ver15

johnfinlay commented 2 years ago

I have the same problem, I have dozens of indexes showing 50-98% fragmentation, and all are columnstore indexes. I have tried tweaking parameters down to 1% fragmentation level and 1 minimum number of pages. I cannot get the script to touch these indexes. Our application performance is awful.

PhilM-IT commented 2 years ago

The detail @christillman references is correct. It's a little confusing as this SQL leads to different percentages than what SSMS shows users in the UI.

A while back I built out a new proc using the fragmentation calculation from that documentation link. We now have 2 jobs scheduled, the original indexoptimize and my new proc which I tied into the same logging and execution process this solution uses. For us it's enough but you have to handle columnstore outside of this. At least for now. I'm not sure they are even planning to address this given the previous comments.

johnfinlay commented 2 years ago

Thanks, I have a lot of questions but I'll take it to Stack Exchange.

PhilM-IT commented 2 years ago

This was also updated recently... https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15

"Starting with SQL Server 2019 (15.x), Azure SQL Database, and Azure SQL Managed Instance, the tuple-mover is helped by a background merge task that automatically compresses smaller open delta rowgroups that have existed for some time as determined by an internal threshold, or merges compressed rowgroups from where a large number of rows has been deleted. This improves the columnstore index quality over time. For most cases this dismisses the need for issuing ALTER INDEX ... REORGANIZE commands."