Should Size Matter in Database Decisions?

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?

For example:

  • 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:

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:

Table sizes

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]

Index Sizes

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.

Advertisements
This entry was posted in DBA, TSQL and tagged , , . Bookmark the permalink.

One Response to Should Size Matter in Database Decisions?

  1. Pingback: Exploding Data Sizes … Better Try Data Compression | SQL Bad Boy – Michael Lowery

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s