Querying table index stats not working

Question

Querying table index stats not working

Jani Hämäläinen 105

I am trying to query my tables index fragmentation in Azure Synapse studio with query:

SELECT IPS.Index_type_desc, 
      IPS.avg_fragmentation_in_percent, 
      IPS.avg_fragment_size_in_pages, 
      IPS.avg_page_space_used_in_percent, 
      IPS.record_count, 
      IPS.ghost_record_count,
      IPS.fragment_count, 
      IPS.avg_fragment_size_in_pages
   FROM sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL , 'DETAILED') AS IPS

But i get error:

Parse error at line: 9, column: 40: Incorrect syntax near 'DB_ID'.

How to fix?

LiHongMSFT-4306 31,566 Reputation points

2024-06-17T02:01:09.7266667+00:00

Hi @Jani Hämäläinen

Do you have further question on this issue, could we offer more support?

If any answer is helpful to your question, you can accept it as an answer so that other users with similar questions can see it more easily. :)
Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2024-06-17T05:33:39.7033333+00:00

Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Accepted answer

0 additional answers

Your answer

LiHongMSFT-4306 31,566 Reputation points

2024-06-17T02:01:09.7266667+00:00

Hi @Jani Hämäläinen

Do you have further question on this issue, could we offer more support?

If any answer is helpful to your question, you can accept it as an answer so that other users with similar questions can see it more easily. :)
Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2024-06-17T05:33:39.7033333+00:00

Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 1

Erland Sommarskog 121.4K MVP Volunteer Moderator

If you look at the topic for sys.dm_db_index_physical_stats, you will see this:

User's image

That is, this is DMV is not available on Synapse. I don't know how you check for fragmentation on Synapse, but it is a different engine from the regular SQL Server, so I don't even know how application the concept of fragmentation is to Synapse.

Share via

Querying table index stats not working

0 additional answers

Your answer