Use SSMS to determine Dataverse table index size
Our salesorder and salesorderdetail tables are around 34Gb each. We're investigating using custom tables rather than the built in tables. Our custom tables are less than 1/3 the size but my boss tells me the size reported for the standard tables includes the indexes. The new custom tables haven't been used and would not include any automatically generated indexes.
I can connect to the instance using SSMS but a standard query to show the index size doesn't work. Does anyone know how to query the size of a Dataverse table's indexes?
I'm afraid you'll have to work with MS Support by creating a ticket to get a breakdown of the table size. What I can tell you is what you see in that chart includes the index size for all tables, regardless if they're custom or system ones.
Indexes are created automatically by SQL, so their size depends on many factors like how much data you have on those tables and what columns get displayed a lot (for instance in views). In the past, we've significantly reduced index storage by attacking full-text column indexation. For instance, you could review those tables and remove unnecessary columns from views, remove any ordering in full-text columns, check your searchable columns, etc.