How to List Size of Tables

Even though disk space is cheap it continues to the be the first resource to run out on a server.  And once it runs out it’s a constant battle to keep the database size in check.

For sure there are things that can be done to keep database growth in control:

  • Archive.  I’ve seen databases where we have 6 years worth of data in it.  Yet our contract with our client only required 4 years.  That means we are storing 50 percent more data than we need to.  Why don’t we set up an automatic archival process?  Probably because we don’t have a framework that we can drop in place so we have to build it from scratch.  With all of the day to day operational demands who has the time to build archive systems?  That’s fodder for another post…
  • Sliding Window Partitions.  This is similar to archiving with a twist.  It requires us to have a fairly sophisticated partitioning process in place where when new data comes rolling in we automatically roll the oldest data out of the table.  Not only to maintain a consistent table size but in theory, that data that just “slid out of the table” is now going to archive storage (ie., slow and cheaper mediums).  There would be more sliding windows deployed if there was a wizard driven process to create the partitions and all of the supporting functions.  Since that is not the case, there will probably continue to be a bunch of opportunities for the consultant specializing in partitioning to speed up ETL and report queries.
  • Junk/temp tables.  We all do it so fess up.  We’re on the production server and we create a “temp” table, except it’s not in tempDB.  It’s a permanent table.  Temp tables are usually easy to spot.  The look like tmp_data_im_loading_toady, dgt_new_customer_fulfillments, temp_20120215, etc.  You need to be a little careful deleting these tables because I have seen ETL process that actually rely on temp tables… so they’re really not temp are they.  Identifying junk tables has saved us GIGs of space.
  • Too many indexes.  I’ve seen tables where the sum of the non-clustered indexes was three times the size of the data itself.  If you come across this, chances are there’s something wrong.  Not only are you wasting space to maintain these indexes, but ETL loads will need to update these indexes slowing down loads.  The optimizer for basic query selections will have a ton of indexes to consider (and probably never use) making query plan generation that much more complex and costly.

Today’s query will list an instances tables for all user databases.  Included in the result set are the size of the heap or clustered index (which is essentially the size of the data, since the index component of a clustered index is on the order of 1-2 percent of the index size).  It also lists the number and aggregate size of non-clustered indexes.

Using this data you will be able to review your table structures, identify junk tables, and identify tables that probably have unnecessary non-clustered indexes.  What you do with this information…  All depends if you are swamped with operational fires or can actually take the time to clean house.

I’m including the code in this post but will also make it available in the downloads section in the sidebar of this post.

List of Tables by Storage
EXEC sp_MSforeachdb
'    USE ?;
    IF db_id() > 4    
        WITH nci AS (    SELECT    i.[object_id]
                                , COUNT(i.[object_id]) AS [NumIndexes]
                                , CAST((SUM(au.total_pages) * (8.0 * 1024.0)) / 1000000000.0 AS NUMERIC(12, 4)) AS GBytes
                        FROM        sys.indexes i
                                INNER JOIN sys.partitions p
                                    ON    p.[object_id] = i.[object_id]
                                    AND    p.index_id = i.index_id
                                INNER JOIN sys.allocation_units au
                                    ON    au.container_id = p.partition_id
                        WHERE    i.index_id >= 2
                        AND        i.[object_id] > 100
                        GROUP BY i.[object_id] )

        SELECT    db_name() AS DBName
                , i.object_id
                , OBJECT_NAME(i.object_id) AS [TableName]
                , AS [ClusteredIndexName]
                , i.index_id
                , i.type_desc AS index_type
                , SUM(p.[rows]) AS row_cnt
                , CAST((SUM(au.total_pages) * (8.0 * 1024.0)) / 1000000000.0 AS NUMERIC(12, 4)) AS CI_GBytes
                , nci.[NumIndexes]
                , nci.GBytes AS [NCI_IndexGBytes]
                , CAST((SUM(au.total_pages) * (8.0 * 1024.0)) / 1000000000.0 AS NUMERIC(12, 4)) + nci.GBytes AS [TotalTableSize]
        FROM        sys.indexes i
                INNER JOIN sys.partitions p
                    ON    p.[object_id] = i.[object_id]
                    AND    p.index_id = i.index_id
                INNER JOIN sys.allocation_units au
                    ON    au.container_id = p.partition_id
                INNER JOIN nci nci
                    ON    nci.[object_id] = i.[object_id]
        WHERE    i.index_id <= 1
        AND        i.[object_id] > 100
        GROUP BY i.[object_id]
                , i.index_id
                , i.type_desc
                , nci.NumIndexes
                , nci.GBytes
        ORDER BY CAST((SUM(au.total_pages) * (8.0 * 1024.0)) / 1000000000.0 AS NUMERIC(12, 4)) + nci.GBytes DESC

Stay curious; keep learning…


Leave a Reply

You must be logged in to post a comment.

Using Gravatars in the comments - get your own and be recognized!

XHTML: These are some of the tags you can use: <a href=""> <b> <blockquote> <code> <em> <i> <strike> <strong>

custom admission paper ghostwriter websites for university custom essays writing website ca help with cheap descriptive essay on trump type my analysis essay online book review ghostwriters services gb help with university essay on trump best dissertation conclusion editor sites for masters esl course work ghostwriter for hire for college cheap research paper editing service au custom dissertation abstract editor site gb admission essay ghostwriting service us cheap application letter writing sites online best essay ghostwriting for hire for school best literature review ghostwriter sites us professional college essay ghostwriting services uk academic essay writing for hire ca cheap speech ghostwriters for hire gb type my professional article online top university essay editor service gb movie review proofreading services top admission essay proofreading sites au esl persuasive essay editor sites for school pay to do cheap persuasive essay on founding fathers cheap analysis essay ghostwriting for hire us help me write speech paper divorced beheaded died divorced beheaded survived best mba dissertation conclusion example buy top cover letter professional phd definition essay examples esl book review writing service for phd professional scholarship essay writing service for phd esl creative writing editor services for mba cheap movie review ghostwriting websites au cheap university dissertation proposal topic top personal statement ghostwriters site online best personal statement editing services top essays ghostwriter for hire ca cheap case study writing website ca cheap creative writing proofreading website usa help writing esl argumentative essay custom presentation editing for hire for phd pay for my nursing research proposal top creative writing writer site best critical analysis essay editing websites usa biography writing service online buy remedial math cv paper ghostwriting websites us cheap school essay proofreading for hire for university macbeth killing king duncan best dissertation conclusion writing service for school top movie review writer sites ca pay to do professional essays online best speech writers websites for masters custom academic essay ghostwriter service gb biography writers website custom movie review writers service for phd popular annotated bibliography editing websites for college cheap paper ghostwriters websites usa professional paper proofreading sites for university hamlets tragic flaw custom annotated bibliography ghostwriter services online custom critical thinking ghostwriting site for mba top literature review writing site for school pay to do esl scholarship essay on brexit essay about discrimination custom reflective essay editing website argumentative essay about smoking problem solving ghostwriting for hire online top thesis statement writers sites for university professional personal essay writers services au professional custom essay editor sites for masters best literature review editor service for mba best dissertation conclusion ghostwriting for hire uk popular report ghostwriters for hire online top report writer websites us custom expository essay editing website usa custom report ghostwriting website for masters cheap research paper proofreading for hire au custom course work editing for hire buy investments problem solving popular book review ghostwriting website us type my earth science dissertation results best curriculum vitae writing site for school professional content ghostwriting websites for university custom dissertation proofreading site online professional paper ghostwriting website do my music problem solving custom application letter ghostwriter websites us custom persuasive essay editing services au professional expository essay ghostwriters sites usa cheap custom essay editing service for mba popular blog ghostwriting service us custom presentation editing for hire ca esl cv editor websites expository ghostwriters sites ca best resume ghostwriter for hire for masters best analysis essay writers websites for university cheap cv editor sites popular article writer websites au assistance for article review cheap book review ghostwriters service for college popular homework writing sites au professional masters essay ghostwriters for hire gb pay to do application letter online letter writing site ca cheap literature review editing sites for college masters essay proofreading site online esl resume proofreading sites uk type my professional definition essay on hillary best rhetorical analysis essay writers websites popular assignment ghostwriters for hire usa write my critical essay on lincoln resume writers site us custom business plan ghostwriter websites for phd best critical essay writers services for mba custom academic essay writers services uk popular dissertation introduction ghostwriting websites online professional argumentative essay editor sites for university write u.s. history and government content pay for my custom phd essay on usa best movie review ghostwriter services au best critical thinking ghostwriter site for masters custom annotated bibliography ghostwriters websites usa esl movie review ghostwriter service au custom critical analysis essay ghostwriters site for college top paper editing site online top research proposal writing site for college rei human resources professional movie review writer service online top case study editing website uk custom custom essay editing services for university professional school research proposal ideas cheap business plan writing services for university popular admission essay editor website for masters popular dissertation abstract writer sites custom reflective essay editor sites for phd advice for literature review professional letter ghostwriting service for masters cheap university essay proofreading websites ca popular dissertation conclusion ghostwriting service uk finance writing website type my best term paper online problem solving proofreading for hire help with my nursing movie review admission editor website au rhetorical analysis essay ghostwriting sites au esl paper ghostwriting site themes from hamlet popular problem solving writing sites usa blog ghostwriters website gb professional expository essay editing site for school top custom essay ghostwriters service for school civilization vs savagery popular blog post writer service gb the lovely bones essay top business plan writing sites top cheap essay editing site us best dissertation hypothesis writer service au professional dissertation chapter ghostwriters service for college analysis essay writers for hire uk professional papers writers site for phd college writing for hire uk write custom custom essay on presidential elections esl best essay proofreading service ca popular dissertation hypothesis ghostwriters website for college pay to get business plan custom thesis proposal writer service usa custom masters creative writing topic cheap descriptive essay ghostwriter site gb esl dissertation chapter ghostwriting service au pay to do zoology research proposal popular thesis statement ghostwriters services gb esl phd essay writer site for college help with calculus dissertation chapter esl research paper writer services us top school essay proofreading for hire for college professional business plan editing site ca argumentative essay on geology cheap dissertation chapter writers sites gb help me write psychology essay assignment proofreading for hire ca titration of citric acid esl college essay editing for hire for college essay writer website custom cheap essay ghostwriter site cheap cv proofreading websites top creative writing ghostwriters service us definition writer website us popular personal essay ghostwriting websites uk esl rhetorical analysis essay ghostwriting sites for phd do my education problem solving pay to do esl analysis essay on presidential elections top assignment ghostwriter service for school custom phd essay writer services gb dissertation conclusion ghostwriters services au top creative essay writers sites for mba custom best essay editing sites professional dissertation results editing service for phd calculus essay ideas cheap reflective essay editor site for college best speech proofreading service professional papers ghostwriter websites for masters scholarship ghostwriter websites buy cheap critical analysis essay on pokemon go top academic essay ghostwriters website au professional resume ghostwriter site uk cheap personal statement ghostwriter websites usa pay to write government dissertation conclusion custom paper ghostwriter websites for phd professional problem solving writer websites us professional definition essay editing website us best college essay editor websites top biography writing site us custom biography writer service uk personal writer websites usa book review writing websites gb cheap business plan writers websites for phd professional course work proofreading for hire au professional dissertation methodology writer services for college professional personal statement proofreading websites usa best reflective essay editing services cheap school papers samples top phd cv topics popular cv proofreading services usa professional dissertation introduction writers sites for school argumentative ghostwriting service usa blog ghostwriter sites gb american dream in a raisin in the sun esl home work ghostwriter sites us algebra ghostwriter services cheap cheap essay ghostwriting service au custom homework ghostwriting websites uk cheap homework ghostwriter websites for university top dissertation abstract writing services ca top essay proofreading service online help writing marketing paper interpersonal communication reflection paper popular phd admission essay advice write my top academic essay on brexit professional school essay editor website for university esl thesis proposal editing for hire for school top critical essay writers site for university top presentation editing website gb dissertation proposal proofreading for hire online pay to do professional persuasive essay on pokemon go resume writers services ca critical analysis essay proofreading services usa custom term paper ghostwriter websites uk popular home work ghostwriters service for university cheap dissertation introduction writer sites usa professional problem solving proofreading websites top mba university essay ideas write my best article review online top cover letter editor for hire us top masters essay editor service papers ghostwriting sites us college writing service online order esl college essay on hillary clinton analysis ghostwriting sites online professional cheap essay ghostwriters services gb top papers writer service uk cheap article review proofreading site uk esl business plan ghostwriting site for university help with philosophy bibliography best essays ghostwriting website for phd pay to get professional custom essay on hacking pay to do criminal law dissertation hypothesis report ghostwriter site uk professional research paper ghostwriting services ca help with my professional rhetorical analysis essay online popular argumentative essay ghostwriting for hire for mba esl scholarship essay editing services for university best movie review ghostwriter websites for college top speech writing sites usa professional cheap essay writing website gb professional creative writing proofreading websites for mba write me earth science course work popular movie review editing services gb custom rhetorical analysis essay ghostwriter service for mba thesis proposal editor services us pay for my creative essay on brexit popular custom essay editor service gb help with my top scholarship essay on shakespeare top speech ghostwriters site order earth science dissertation methodology top dissertation methodology proofreading service ca top dissertation hypothesis editor service uk custom expository essay proofreading site for mba cheap university school essay samples cheap annotated bibliography proofreading websites ca best creative writing ghostwriters services popular thesis proposal writing sites best dissertation methodology ghostwriting site for mba top phd critical essay examples custom annotated bibliography ghostwriter for hire us accomplishments essay pay for art & architecture admission essay best business plan editor website for school pay to do popular descriptive essay on trump cheap masters essay editing site us esl dissertation conclusion ghostwriting sites for college womens roles custom admission paper ghostwriters for hire for university custom report editor for hire for phd philosophy writer website custom dissertation hypothesis proofreading for hire uk cheap dissertation chapter writer service for school top school essay ghostwriters sites ca popular essays editing sites online top paper ghostwriter service for mba esl course work editor websites us custom best essay ghostwriting site ca popular article review writer website ca esl letter ghostwriters services best cheap essay ghostwriter for hire for masters pay to get scholarship essay on trump scholarship writer services uk professional essay writers services for school popular argumentative essay writing services ca pay to write custom dissertation results online best masters essay ghostwriter for hire au help me write esl masters essay on founding fathers top personal statement proofreading for hire for school write top rhetorical analysis essay professional academic essay writing sites for mba ronald reagan first inaugural address order popular analysis essay on usa mathematics editing for hire help me write calculus admission paper cheap critical analysis essay editor sites gb best college presentation topics custom university essay writing website for mba cheap university dissertation results topic esl book review writing services custom bibliography editing site uk top phd essay editing for hire for school cheap descriptive essay proofreading site professional dissertation methodology proofreading sites for mba cheap dissertation hypothesis editing for hire gb top critical thinking proofreading services au write me u.s. history and government essay professional critical essay editor website ca esl reflective essay editor services gb dissertation conclusion writer service usa custom creative writing writers site for masters school uniforms are uncomfortable esl essays writers service for university academic essay writing service usa dissertation abstract writers website help writing cheap cheap essay on brexit popular rhetorical analysis essay writers website order cheap critical analysis essay on shakespeare book review editor website au order ancient civilizations application letter esl essay ghostwriting services for phd esl creative writing writing websites for mba popular letter ghostwriting sites us buy government papers top research paper writing website for college admission ghostwriter site au pay for my top critical essay on pokemon go catharsis in oedipus popular cover letter ghostwriters site for masters professional movie review ghostwriting site for university esl course work ghostwriter services uk assignment editing services au professional thesis proposal writers site usa professional curriculum vitae editing services for school top phd essay ghostwriting websites for university write my investments dissertation proposal professional descriptive essay writers site au esl speech editor services au professional college essay ghostwriting site au custom homework writer for hire usa custom research proposal ghostwriting website usa pay for my speech dissertation methodology professional thesis writing website au personal essay writers site online top argumentative essay ghostwriters for hire cheap biography writer sites us cheap critical essay proofreading sites gb professional cover letter ghostwriting websites home work writer sites professional dissertation conclusion ghostwriters sites for school custom cover letter writers service for college write me popular definition essay on hacking professional literature review writer services au best dissertation abstract ghostwriter website us top research proposal ghostwriting service au custom personal essay ghostwriter websites for masters popular masters essay writer services online esl term paper editor site for mba mark edmundson on the uses of a liberal education summary custom course work proofreading website us professional bibliography ghostwriting service for mba esl masters essay editor sites for college help with my cheap critical analysis essay esl essays writers sites online custom creative writing proofreading sites ca top phd cheap essay topic custom masters essay ghostwriter service for phd professional dissertation conclusion ghostwriting site au top homework writing site usa do my government article review pay to get esl university essay on brexit popular definition essay writer for hire usa walmart balanced scorecard best scholarship essay writers websites usa popular papers editor for hire ca pay to get journalism presentation dreamland sarah dessen sparknotes type my criminal law annotated bibliography professional writer sites for college professional phd cover letter samples popular assignment writers websites us ethnic notions full movie help writing economics cover letter popular dissertation hypothesis writer services for school best college essay proofreading sites usa popular book review editing sites for mba dissertation writer website popular persuasive essay ghostwriting for hire for masters best dissertation introduction ghostwriter websites for university esl creative essay writer websites uk popular assignment writers for hire cheap university application letter sample cheap dissertation proposal writer sites for masters cheap thesis writing services for phd custom admission essay editor site for masters custom critical analysis essay ghostwriters websites for phd custom dissertation introduction ghostwriting site for phd twelfth night love order family and consumer science research paper popular university essay writer website for mba help me write best reflective essay on civil war education of a wasp top article review writer services for masters professional content editor websites for college help with my custom critical analysis essay on hillary clinton algae uses custom college scholarship essay help argumentative writing site online custom dissertation methodology writer service for phd popular bibliography editor sites for university professional dissertation hypothesis editing sites for masters professional descriptive essay proofreading site for school custom blog post editor services for phd professional dissertation introduction writers website gb professional admission paper writer website for phd popular dissertation hypothesis ghostwriter service for school best critical analysis essay ghostwriters for hire us popular dissertation abstract writers service for college esl curriculum vitae ghostwriter service ca cheap movie review ghostwriter sites gb dissertation conclusion proofreading service ca top dissertation abstract ghostwriter service best mba essay editing site au esl content ghostwriters for hire us dissertation results editor websites uk best persuasive essay editing website uk buy custom expository essay on trump custom application letter writers service for school popular masters dissertation hypothesis help cheap essays editing site for phd esl dissertation ghostwriter websites for school custom course work writing services for mba popular critical analysis essay writers service for university write custom masters essay on hillary clinton professional personal essay ghostwriting website usa top bibliography writer website for phd esl cover letter ghostwriters services for mba help me write poetry movie review social studies ghostwriting service help writing popular article review custom papers editor for hire for college professional business plan ghostwriter website for mba write me professional reflective essay on hillary clinton custom thesis statement proofreading for hire for college help me write cheap paper online top research paper editing sites for university professional creative essay ghostwriter sites cheap application letter proofreading for hire for school a valediction of weeping top admission essay editing site gb once more to the lake eb white popular paper proofreading services for college custom rhetorical analysis essay writing site usa professional bibliography ghostwriting sites for school creative writing writing sites ca buy esl expository essay on lincoln cheap creative writing editor for hire for university custom descriptive essay writers websites for masters cheap dissertation results editor service for school professional argumentative essay ghostwriter site uk professional expository essay ghostwriter website usa the benefits of cloning buy popular critical essay on hillary popular report writers website uk best descriptive essay ghostwriting website usa esl essays proofreading service gb