Fabric - Lakehouse Table Health Check

Microsoft have rolled out a new 'sys' stored procedure called 'sys.sp_get_table_health_metrics' for checking the health of your tables in your Lakehouse. And it outputs a lot of metrics looking at the state of your table and anomaly detection in them.

What does it check for? Well it checks to see if your Lakehouse Delta tables are physically well-optimised for SQL query performance. It returns file-level health metrics for a specified Lakehouse table, including file-size distribution, row-count distribution, deleted-row counts, and anomaly detection for common storage issues such as lots of small files or fragmentation. To many small files reads slowing your query time down? Metadata broken or need some maintenance? Well this is the one to run. If you check out the page on MS Learn, you get a lot in this stored procedure.

So run this on your SQL Endpoint:

EXEC sys.sp_get_table_health_metrics
    @table_name = 'dbo.FactSales';

The two main things you should look at in the output are:

• Small Files

  • Small files can reduce SQL endpoint query performance because the engine has more files to scan.

• Fragmentation

  • Does the table have uneven file distribution or signs of fragmentation. A table that get updated a lot could become inefficient

If you are using Power BI in direct query or direct lake, you want the read of those tables to be quick as possible. And this should give you enough output to run an optimisation pass on any problematic tables.

So you have the output, what next?

So what do you do when you have run these commands? Well, it's time to fire up a notebook, and run the following:

OPTIMIZE dbo.FactSales;

This compacts small Delta files into fewer, larger files. This should be used when 'sp_get_table_health_metrics' shows lots of small files or poor file-size distribution.

VACUUM dbo.FactSales RETAIN 168 HOURS;

Be careful with this one, as Vacuum removes old unreferenced data after the time set in the query. 168 hours is 7 days, and if you need to query table history, or have a rollback/recovery process based on table time travel, it could break it.

Recommendations

If you are worried about the health of your tables, I would set up a monitoring process that would iterate though them and store the results in a table. That is going to be tricky, as it is the SQL Endpoint and you can't do insert to a table there. So what can you do?

Well you'll need to use a pipeline to run a script activity, then you can take the output and dump it to a table or send the output to a notebook to run whatever commands you need.

So it is a bit hybrid, you have to query the endpoint to get the result, but then need a notebook to do the work.

I like it this new stored proc a lot, and has helped me identify some poor, sad tables, that needed so love and attention!