SQL Server Performance Forum – Threads Archive

Indexed View Space usage

I’m trying to work out how much space an indexed view uses. You can use sp_spaceused on an indexed view but it doesn’t give any breakdown by index. For a regular table you can use sp_statistics which gives you the number of pages in an index and therefore calculate the space used, but this doesn’t work for views. Of course I can try to deduce it by dividing the space indicated from sp_spaceused but I’d like something more scientific. Any ideas, anyone? Tom Pullen
DBA, Oxfam GB

Check: http://www.sqlteam.com/item.asp?ItemID=1015 HTH Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Thanks Luis, essentially you have to run sp_spaceused after adding each index to get measure incrementally how large they are. Tom Pullen
DBA, Oxfam GB

One of the Technet article refers
It is relatively easy to approximate the required storage the view will consume. Evaluate the SELECT statement encapsulated by the view definition with the SQL Query Analyzer tool Display Estimated Execution Plan. This tool will yield an approximation of the number of rows returned by the query and the size of the row. By multiplying these two values together, it is possible to approximate the potential size of the view. However, this is only an approximation. The actual size of the index on the view can be accurately determined only by creating the index on the view.
HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>