Warehouse and Lakehouse | Optimization and Maintenance tasks

[RajeshKapur]

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 

[v-csrikanth · Accepted solution]

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 

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:

1. Clustered Index – Sorts and stores data physically based on the indexed column.
2. Non-Clustered Index – Creates a separate structure to speed up searches without changing data order.
3. 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.

[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]

Hi @v-csrikanth 

How can I build indexes in Warehouse available in Fabric. 

Thanks

[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=sparks...

• 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.

[mikesmall]

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

[RajeshKapur]

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