Forum Discussion
Helper IWarehouse and Lakehouse | Optimization and Maintenance tasks
Hi,
We have lakehouses with delta tables and files and also have warehouses with views and SP on tables. Could anyone please suggest some regular maintainence tasks that is needed or recommended on Warehouse and Lakehouse to get best optimization/performance.
Thanks
Hi RajeshKapur
I apologize for any confusion caused by my earlier responses. Upon further research, it appears that Microsoft Fabric Warehouse does not currently support the creation of indexes using the "Create Index" statement. Attempts to execute such statements result in an error indicating that this operation is not supported.The CREATE INDEX statement is not supported in Fabric Data Warehouse.
Related information can be viewed here:
Tables in data warehousing - Microsoft Fabric | Microsoft Learn
As an alternative, similar indexing functionality can be implemented using primary keys, foreign keys, and unique constraints.
Related information can be viewed here:
Primary, foreign, and unique keys - Microsoft Fabric | Microsoft Learn
If the above information helps you, please give us a Kudos and marked the Accept as a solution.
Best Regards,
Community Support Team _ C Srikanth.
8 Replies
- v-csrikanth
Community Support
Hi RajeshKapur
I apologize for any confusion caused by my earlier responses. Upon further research, it appears that Microsoft Fabric Warehouse does not currently support the creation of indexes using the "Create Index" statement. Attempts to execute such statements result in an error indicating that this operation is not supported.The CREATE INDEX statement is not supported in Fabric Data Warehouse.
Related information can be viewed here:
Tables in data warehousing - Microsoft Fabric | Microsoft Learn
As an alternative, similar indexing functionality can be implemented using primary keys, foreign keys, and unique constraints.
Related information can be viewed here:
Primary, foreign, and unique keys - Microsoft Fabric | Microsoft Learn
If the above information helps you, please give us a Kudos and marked the Accept as a solution.
Best Regards,
Community Support Team _ C Srikanth. - v-csrikanth
Hi RajeshKapur
Thank you for being part of the Microsoft Fabric Community.-
Consolidate small Parquet files into larger files (128 MB–1 GB) and apply V-Order sorting/compression for faster reads across Fabric engines.
Reference link: https://learn.microsoft.com/en-us/fabric/data-engineering/delta-optimization-and-v-order?tabs=sparksql -
Remove unreferenced files older than the retention period (default 7 days). Adjust retention cautiously to avoid disrupting time travel or active operations
If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.
RajeshKapurAnd any if we have to perform something around this in warehouse?
mikesmallAdvocate I
Hi C Srikanth
This is only for Lakehouse's, isn't it? You can't set a lower retention period than 30 days on a warehouse can you? I have had a look and don't seem to be able to remove system created restore points within the last 30 days.
Thanks,
Mike
-
- v-csrikanth
Hi RajeshKapur
If you want implement is warehouse depends best approach is based on the data size, query complexity, and workload patterns.
1) Automated Index & Statistics Maintenance : Schedule a weekly job to rebuild/reorganize indexes and update statistics.
2) Query & Stored Procedure Optimization :- Identify slow queries from sys.dm_exec_query_stats.
- Rewrite queries for better indexing and execution plans.
- Reduce nested loops and cursors.
- Implement rolling data retention with partitioning or archiving.
If the above information helps you, please give us a Kudos and marked the Accept as a solution.Best Regards,
Community Support Team _ C Srikanth.
- RajeshKapur
- v-csrikanth
Hi RajeshKapur
In Microsoft Fabric's Warehouse, you can create indexes to make queries run faster, especially on large datasets. Fabric Warehouse uses Synapse SQL, which supports standard T-SQL indexing.
Types of Indexes in Fabric Warehouse:
- Clustered Index – Sorts and stores data physically based on the indexed column.
- Non-Clustered Index – Creates a separate structure to speed up searches without changing data order.
- Columnstore Index – Ideal for big data analytics, improving speed and reducing storage size.
If the above information helps you, please give us a Kudos and marked the Accept as a solution.Best Regards,
Community Support Team _ C Srikanth.
- RajeshKapur
Getting error while tryin creating Index
CREATE INDEX is not a supported statement type.