Large table Issues. This identifies issues with
•Tables not partitioned, that should be. This assumes you are using SQL Server Enterprise edition or SQL Server 2016 SP1+ Standard Edition.
•Partitioned Tables and the percentage of partitions empty. Having lots of empty partitions can have a detrimental impact on query performance.
•Identify columns that will stop the table being converted to a columnstore index. See the last 3 columns in the grid.
Column |
Description |
|---|---|
Column Store Issues |
If there is a tick then the current table cannot be converted to a clustered columnstore index. There are certain column types incompatible with columnstore indexes. |
Column Store Column Issues |
The columns and their types in brackets that are incompatible with columnstore indexes. |
Issues |
General recommendation for partitioning |
Note if there are issues with columns you have 3 options.
1.Convert the column to another type eg nvarchar(max) to nvarchar(8000). This needs to be reviewed on a case by case basis. Verify there will be no data loss on the conversion.
2.Split the table into 2 tables. Move columns with issues to another table and create a columnstore index on the first table.
3.Don't use columnstore index. Just because there are issues with column types does not stop you partitioning the table.
Using the filter
1.Choose table types to view
All tables- partitioned and non partitioned
Partitioned Tables
Non Partitioned Tables
2.The minimum number of rows in the table. For partitioned tables you are looking at over 1 million rows to review for partitioning. With clustered columnstore at least 1 million rows is the minimum recommended partition size.
3.Show partitions where the percentage of partitions having data is less that the number specified. By default this is 75%
Notes on partitions.
1.Partitioning more about housekeeping than performance of queries. Think about a 1 billion row table having to perform an index rebuild/reorganize. This is going to take a long time. If the 1 billion row table is partitioned and each partition hold 50 million rows. When it comes to housekeeping you only need to reindex rebuild/reorganize the partitions that are fragmented.
2.The number of rows in each partition is subjective it depends on the number of rows and how wide the table is. For example a narrow table you might have each partition hold 50 million rows, with a wide table you might have each partition hold 10 million rows.
3.If you are using SSAS. It is important to have the partitions aligned.