sp_spaceused is one of the frequently used stored procedures when it comes to view the number of rows, reserved size, data size, index size, unused space in a table. I myself utilized it many a time.
Given below are the three methods to run sp_spaceused or equivalent for all tables in a database to get number of rows, reserved size, data size, index size, unused space but this time we will measure the performance as well.
Method 1 :
Given below is one line script that can achieve the required result but it will cost you a performance issue because it will loop all the tables in the database one by one and will give you the result set.
EXECUTE sp_MSforeachtable 'EXECUTE sp_spaceused [?]';
Given below is one of the server execution times.
SQL Server Execution Times:
CPU time = 203 ms, elapsed time = 1768 ms.
If you have more tables, it will give you the error message as given below.
The query has exceeded the maximum number of result sets that can be displayed in the results grid. Only the first 100 result sets are displayed in the grid.
Method 2 :
Given below is another method to achieve it but this method will also cost you a performance issue because it will also loop all the tables in the database one by one and will give you the result set. Reference
Set statistics time on
CREATE TABLE ##TempTable
(name nvarchar(128)
,rows char(11)
,reserved varchar(18)
,data varchar(18)
,index_size varchar(18)
,unused varchar(18)
)
declare @UserTableName nvarchar(40)
declare UserTableSize cursor for
select rtrim(name) from dbo.sysobjects
where OBJECTPROPERTY(id, N'IsUserTable') = 1 order by name
open UserTableSize
fetch next from UserTableSize into @UserTableName
while @@fetch_status = 0
begin
Insert ##TempTable
exec sp_spaceused @UserTableName
fetch next from UserTableSize into @UserTableName
end
close UserTableSize
deallocate UserTableSize
Select * from ##TempTable
--Drop Table ##TempTable
Set statistics time off
GO
Given below is one of the server execution times.
SQL Server Execution Times:
CPU time = 125 ms, elapsed time = 1034 ms.
Method 3 :
This method will not loop all the tables in the database one by one and also you don’t need to insert its result set into a temporary table to utilize it further. The performance of this method is also better than the other two methods also.
Given below is the script.
Set statistics time on
declare @PageSize float
select @PageSize=v.low/1024.0 from master.dbo.spt_values v where v.number=1 and v.type='E'
Select object_Name(i.object_id) as [name]
,p.rows
,Convert(varchar(50),@PageSize * SUM(total_pages)) + ' KB' as [reserved]
,Convert(varchar(50),@PageSize * SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)) + ' KB' as [data]
,Convert(varchar(50),@PageSize * SUM(a.used_pages - CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)) + ' KB' as [index_size]
,Convert(varchar(50),@PageSize * SUM(total_pages-used_pages)) + ' KB' as [unused]
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id = p.partition_id
JOIN sys.tables t ON i.object_id=t.object_id
Where i.type<=1 and a.type=1
and
t.type='U' and is_ms_shipped=0
Group By i.object_id,p.rows
GO
Set statistics time off
Given below is the server execution times.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 6 ms.
Conclusion :
You can see that all the above methods produce the same result set but the issue is with the performance. I recommend Method 3 because it will take less effort and time to generate the same result set.
Any comments ?
Read Full Post »
SQL Server Portal 