Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I check the size of an indexed view in SQL Server?

Its easy to check storage sizes for Tables and Indexes, you can right-click the table object on SSMS explorer and voila, the details appear in a nice popup.

But since Indexed Views are displayed the same as Normal Views, there is no storage information avaiable in SSMS to show me the current size taken up on disk.

enter image description here

Is there an alterate way to calculate the size (say via a system SP or similar method)?

Thanks.

like image 922
Steven de Salas Avatar asked May 18 '11 16:05

Steven de Salas


People also ask

How do I find the size of a SQL Server view?

If you need to check a single database, you can quickly find the SQL Server database sizein SQL Server Management Studio (SSMS): Right-click the database and then click Reports -> Standard Reports -> Disk Usage. Alternatively, you can use stored procedures like exec sp_spaceused to get database size.

How do you find the size of an index in SQL?

To review individual indexes size manually, right-click on a specific database, choose Reports -> Standard reports -> Disk usage by table: In this case, we ran a standard report on the AdventureWorks2014 database.

How do I check the size of a view?

If you need to know the dimensions of a View right after it is drawn you can simply call post() on that given View and send there a Runnable that executes whatever you need.

How do you find the size of an index?

Query to check index size in Oracleselect sum(bytes)/1024/1024 as "Index Size (MB)" from dba_segments where segment_name='&INDEX_NAME'; select sum(bytes)/1024/1024 as "Index Size (MB)" from user_segments where segment_name='&INDEX_NAME';


2 Answers

EXEC sys.sp_spaceused @objname = N'dbo.YourView'
like image 52
Martin Smith Avatar answered Oct 13 '22 03:10

Martin Smith


You can use this query here to find your data for any given indexed view:

SELECT 
    v.NAME AS ViewName,
    i.name AS IndexName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    SUM(a.data_pages) * 8 AS DataSpaceKB
FROM 
    sys.views v
INNER JOIN      
    sys.indexes i ON v.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    v.Name = 'YourViewNameHere' --View name only, not 'schema.viewname'
    AND
    i.index_id = 1   -- clustered index, remove this to see all indexes
GROUP BY 
    v.NAME, i.object_id, i.index_id, i.name, p.Rows

Gives an output something like

ViewName      IndexName     RowCounts  TotalSpaceKB  UsedSpaceKB  DataSpaceKB
YourViewName  IX_YourView     1771         592           552          536
like image 37
marc_s Avatar answered Oct 13 '22 03:10

marc_s