topic Re: Warehouse and Lakehouse | Optimization and Maintenance tasks in Data Warehouse

Warehouse and Lakehouse | Optimization and Maintenance tasks

RajeshKapur — Mon, 24 Mar 2025 14:52:32 GMT

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

Re: Warehouse and Lakehouse | Optimization and Maintenance tasks

v-csrikanth — Tue, 25 Mar 2025 07:28:02 GMT

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.

Re: Warehouse and Lakehouse | Optimization and Maintenance tasks

RajeshKapur — Tue, 25 Mar 2025 12:11:23 GMT

And any if we have to perform something around this in warehouse?

Re: Warehouse and Lakehouse | Optimization and Maintenance tasks

v-csrikanth — Wed, 26 Mar 2025 07:28:08 GMT

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.

Re: Warehouse and Lakehouse | Optimization and Maintenance tasks

RajeshKapur — Wed, 26 Mar 2025 09:31:42 GMT

Hi v-csrikanth

How can I build indexes in Warehouse available in Fabric.

Thanks

Re: Warehouse and Lakehouse | Optimization and Maintenance tasks

v-csrikanth — Wed, 26 Mar 2025 09:37:40 GMT

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.

Re: Warehouse and Lakehouse | Optimization and Maintenance tasks

RajeshKapur — Wed, 26 Mar 2025 09:50:47 GMT

Getting error while tryin creating Index

CREATE INDEX is not a supported statement type.

Re: Warehouse and Lakehouse | Optimization and Maintenance tasks

v-csrikanth — Wed, 26 Mar 2025 10:27:22 GMT

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.

Re: Warehouse and Lakehouse | Optimization and Maintenance tasks

mikesmall — Tue, 01 Apr 2025 13:51:05 GMT

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