Imagine that you are faced with two different database situations. Would your decision on what to do be influenced by the size of the objects involved?
- You need to update all the rows for a column in a table, would the size of the table matter in what method you would choose to use? Would you try to update the whole table at once? Or would you do it in batches? (https://sqlbadboy.wordpress.com/2012/11/20/attack-it-in-small-bits-or-chunks/)
- You need to rebuild an index offline, does the size of the index matter as to what time you can perform the task?
In both cases, definitely the size of the object would influence the decision made. And these are not the only cases where size could play a key role in the decision-making process.
So, in this post, I am going to show an easy way to determine sizes for tables and indexes using T-SQL with only catalog views (http://technet.microsoft.com/en-us/library/ms174365.aspx).
Here are the catalog views that are used in the examples:
- Sys.schemas (http://technet.microsoft.com/en-us/library/ms176011.aspx)
- Sys.objects (http://technet.microsoft.com/en-us/library/ms190324.aspx)
- Sys.indexes (http://technet.microsoft.com/en-us/library/ms173760.aspx)
- Sys.partitions (http://technet.microsoft.com/en-us/library/ms175012.aspx)
- Sys.allocation_units (http://technet.microsoft.com/en-us/library/ms189792.aspx)
NOTE: Please remember that there are functions and DMV’s that can return similar information to catalog views, but I wanted to illustrate the use of ONLY the catalog views. Here are some examples of what I mean:
- The sys.schema catalog view could be replaced by the function, OBJECT_SCHEMA_NAME (http://technet.microsoft.com/en-us/library/bb326599.aspx), which returns schema name from an object_id.
- The sys.allocation_units catalog view could be replaced by the DMV, sys.dm_db_partition_stats (http://technet.microsoft.com/en-us/library/ms187737.aspx), which returns page and row-count information for every partition in the current database.
As you may know, the size of the data in a table really corresponds to the size of the clustered index or heap. So, notice the following T-SQL code which finds the size of the clustered index or heap for each table using those catalog views.
SELECT s.name AS [schema_name], o.name AS [table_name], i.name AS [index_name], i.type_desc AS [index_type], i.is_unique, i.is_primary_key, i.fill_factor, i.is_disabled, p.partition_number, p.[rows] AS table_rows, p.data_compression_desc, SUM(a.total_pages) AS total_pages, SUM(a.used_pages) AS used_pages, SUM(a.data_pages) AS data_pages, -- Value returned excludes internal index pages and allocation-management pages (SUM(a.total_pages) * 8) / 1024. AS total_space_mb, (SUM(a.used_pages) * 8) / 1024. AS used_space_mb, (SUM(a.data_pages) * 8) / 1024. AS data_space_mb FROM sys.schemas s INNER JOIN sys.objects o ON s.schema_id = o.schema_id INNER JOIN sys.indexes i ON o.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 o.type = 'U' AND i.type IN (0, 1) -- 0 = heap, 1 = clustered index GROUP BY s.name, o.name, i.name, i.type_desc, i.is_unique, i.is_primary_key, i.fill_factor, i.is_disabled, p.partition_number, p.[rows], p.data_compression_desc ORDER BY [schema_name], [table_name], [index_type], [index_name]
Here’s how we can easily find the sizes of all the nonclustered indexes. Take the query that we had for finding table sizes and change the where clause expression to “i.type NOT IN (0, 1)”, and it will return all the nonclustered index sizes. It is just that simple …
WHERE o.type = 'U'
AND i.type NOT IN (0, 1) -- 0 = heap, 1 = clustered index
So, size does matter! Depending on the size of a table and/or index, it may influence your decision on how to proceed in a certain situation.