Table size issue

Question

Post reply

Table size issue

vsamantha35

SSChampion

Points: 11551
More actions
September 5, 2019 at 9:12 am
Go to Answer
#3677543

Hi Experts,
Need some background on this. I see some very big history tables in our database (like eg of 20 GB..)
For example, there is one history table is taking up to 26 GB in the database and still growing.
Spoke to development team to see if they can archive some records. So, they have deleted some rows but the table size remains the same. What is the reason behind it? and is there a way to reclaim free space of an object. Apart from shrink file is there anything we can do to release free space at object level?
Rows before deletion =17646067
Rows after deletion =4201104

CREATE TABLE [dbo].[CONTROL_HIST](
[PUB_PKEY] [bigint] NOT NULL,
[DUNS_NMBR] [nvarchar](15) NULL,
[WEBSITE] [nvarchar](1024) NULL,
[CMPNY_TYP] [nvarchar](50) NULL,
[SUBSDRY_IND] [nchar](1) NULL,
[LOCATION_TYP] [nvarchar](50) NULL,
[RANKING] [nvarchar](50) NULL,
[GLB_PARENT_NM] [nvarchar](250) NULL,
[GLB_DUNS_NMBR] [nvarchar](15) NULL,
[REVENUE_GROWTH] [numeric](38, 0) NULL,
[INCOME_GROWTH] [numeric](38, 0) NULL,
[MARKET_VAL_USD_MILLION] [numeric](38, 0) NULL,
[PRMRY_US_NAICS_CD] [nvarchar](500) NULL,
[LINE_OF_BUSS] [nvarchar](50) NULL,
[MINORITY_OWNED_IND] [nchar](1) NULL,
[BEMFAB_MRKTBLTY] [nvarchar](50) NULL,
[MATCH_GRADE] [nvarchar](50) NULL,
[CONF_CD] [nvarchar](50) NULL,
[DUNS_NMBR_SDMR] [nvarchar](50) NULL,
[BUSINESS_NM] [nvarchar](250) NULL,
[TRDSTYL_NM] [nvarchar](250) NULL,
[CARRIER_ROUTE_CD] [nvarchar](50) NULL,
[GEO_CD_ACCURACY] [nvarchar](50) NULL,
[CEO_FULL_NM] [nvarchar](200) NULL,
[SALES_VOL_LOCAL_CURR] [nvarchar](50) NULL,
[SALES_VOL_LOC_CURR_REL_CD] [nvarchar](50) NULL,
[CURRENCY_CD] [nvarchar](50) NULL,
[YEAR_STARTED] [bigint] NULL,
[SMB_IND] [nchar](1) NULL,
[OWNS_RENTS_CD] [nvarchar](50) NULL,
[SQ_FOOTAGE] [numeric](38, 0) NULL,
[GLB_ULT_IND] [nchar](1) NULL,
[GLB_ULT_DNB_CNTRY_CD] [nvarchar](64) NULL,
[GLB_ULT_STATE_PRVNC_ABBRV] [nvarchar](64) NULL,
[DOM_ULTI_DUNS_NMBR] [nvarchar](15) NULL,
[DOM_ULT_BUS_NM] [nvarchar](250) NULL,
[DOM_ULT_DNB_CNTRY_CD] [nvarchar](64) NULL,
[HQ_DUNS_NMBR] [nvarchar](15) NULL,
[HQ_PARENT_BUS_NM] [nvarchar](250) NULL,
[HQ_PARENT_DNB_CNTRY_CD] [nvarchar](64) NULL,
[HQ_STATE_PRVNC_ABBRV] [nvarchar](64) NULL,
[MAJOR_INDSTRY_CTGRY] [nvarchar](256) NULL,
[THREE_YR_GRWTH_PC_SALE_VOL] [numeric](38, 0) NULL,
[THREE_YR_GRWTH_PC_EMP] [numeric](38, 0) NULL,
[THREE_YR_BASE_SALES_AMT] [numeric](38, 0) NULL,
[FIVE_YR_GRWTH_PC_SALES_VOL] [numeric](38, 0) NULL,
[FIVE_YR_GRWTH_PC_EMP] [numeric](38, 0) NULL,
[FIVE_YR_BASE_SALES_AMT] [numeric](38, 0) NULL,
[FIVE_YR_BASE_EMP_CNT] [numeric](38, 0) NULL,
[FRST_EXEC_FULL_NM] [nvarchar](200) NULL,
[FRST_EXEC_MRC_CD] [nvarchar](50) NULL,
[SEC_EXEC_FULL_NM] [nvarchar](200) NULL,
[SEC_EXEC_MRC_CD] [nvarchar](50) NULL,
[THIRD_EXEC_FULL_NM] [nvarchar](200) NULL,
[THIRD_EXEC_MRC_CD] [nvarchar](50) NULL,
[PRMRY_NAICS_1_1_CD] [nvarchar](50) NULL,
[REF_ORG_ID] [nchar](14) NULL,
[PRMRY_ADDR_LN1] [nvarchar](200) NULL,
[PRMRY_ADDR_LN2] [nvarchar](200) NULL,
[PRMRY_CITY] [nvarchar](200) NULL,
[PRMRY_STATE_CD] [nvarchar](64) NULL,
[PRMRY_STATE_NM] [nvarchar](200) NULL,
[PRMRY_POSTAL_CD] [nvarchar](50) NULL,
[PRMRY_COUNTY] [nvarchar](200) NULL,
[PRMRY_COUNTRY] [nvarchar](64) NULL,
[MAILING_ADDR_LN1] [nvarchar](200) NULL,
[MAILING_ADDR_LN2] [nvarchar](200) NULL,
[MAILING_CITY] [nvarchar](200) NULL,
[MAILING_STATE_CD] [nvarchar](64) NULL,
[MAILING_COUNTY] [nvarchar](200) NULL,
[MAILING_POSTAL_CD] [nvarchar](50) NULL,
[MAILING_COUNTRY] [nvarchar](64) NULL,
[PHONE] [nvarchar](100) NULL,
[NET_INCOME_USD_MILLION] [nvarchar](50) NULL,
[EMP_GROWTH] [nvarchar](50) NULL,
[TOTAL_ASSEST_USD_MILLION] [nvarchar](50) NULL,
[LATITUDE] [nvarchar](50) NULL,
[LONGITUDE] [nvarchar](50) NULL,
[IMMEDIATE_PARENT_DUNS] [nvarchar](20) NULL,
[TOTAL_EMP_CNT] [nvarchar](50) NULL,
[EMP_SITE_CNT] [nvarchar](50) NULL,
[INTERFACE] [nvarchar](100) NULL,
[HSH_KEY] [nvarchar](255) NULL,
[SRC_PRIMARY_KEY] [nvarchar](255) NULL,
[SOURCE_SYSTEM] [nchar](14) NULL,
[TARGET_SYSTEM] [nvarchar](100) NULL,
[PUBLISH_COMMENTS] [nvarchar](100) NULL,
[PUBLISH_STATUS] [nvarchar](200) NULL,
[PUBLISH_CODE] [nvarchar](200) NULL,
[PUBLISH_MESSAGE] [nvarchar](4000) NULL,
[RETRY_COUNT] [nvarchar](100) NULL,
[BATCH_ID] [nvarchar](100) NULL,
[MDM_ID] [nchar](14) NULL,
[DATE] [datetime2](7) NULL,
[PUBLISH_EVENT_TYPE] [nvarchar](100) NULL,
[MULE_BATCH_ID] [nvarchar](100) NULL,
[ENTITY_TYPE] [nvarchar](100) NULL,
[CEO_PREFIX] [nvarchar](50) NULL,
[CEO_FRST_NM] [nvarchar](64) NULL,
[CEO_MID_NM] [nvarchar](64) NULL,
[CEO_LAST_NM] [nvarchar](64) NULL,
[FIRST_EXEC_PREFIX] [nvarchar](50) NULL,
[FIRST_EXEC_FIRST_NM] [nvarchar](64) NULL,
[FIRST_EXEC_MID_NM] [nvarchar](64) NULL,
[FIRST_EXEC_LAST_NM] [nvarchar](64) NULL,
[SEC_EXEC_PREFIX] [nvarchar](50) NULL,
[SEC_EXEC_FRST_NM] [nvarchar](64) NULL,
[SEC_EXEC_MID_NM] [nvarchar](64) NULL,
[SEC_EXEC_LAST_NM] [nvarchar](64) NULL,
[THIRD_EXEC_PREFIX] [nvarchar](50) NULL,
[THIRD_EXEC_FIRST_NM] [nvarchar](64) NULL,
[THIRD_EXEC_MID_NM] [nvarchar](64) NULL,
[THIRD_EXEC_LAST_NM] [nvarchar](64) NULL,
[DOM_ULT_STATE_PRVNC_ABBRV] [nvarchar](64) NULL,
[MAILING_STATE_NM] [nvarchar](200) NULL,
[FAM_HIERARCHY_LVL] [nvarchar](5) NULL,
[OUT_OF_BUS_IND] [nvarchar](5) NULL,
[STANDALONE_IND] [nvarchar](5) NULL,
[FAM_TREE_COUNT] [int] NULL,
[STOCK_TICKER_NAME] [nvarchar](100) NULL,
[SALES_VOLUME_US_DOLLARS] [nvarchar](60) NULL,
[CEO_AGE] [nvarchar](50) NULL,
[COMPETITOR1_ORG_NM] [nvarchar](256) NULL,
[COMPETITOR2_ORG_NM] [nvarchar](256) NULL,
[COMPETITOR3_ORG_NM] [nvarchar](256) NULL,
[COMPETITOR4_ORG_NM] [nvarchar](256) NULL,
[COMPETITOR5_ORG_NM] [nvarchar](256) NULL,
[COMPETITOR1_ORG_EMP_CNT] [nvarchar](20) NULL,
[COMPETITOR2_ORG_EMP_CNT] [nvarchar](20) NULL,
[COMPETITOR3_ORG_EMP_CNT] [nvarchar](20) NULL,
[COMPETITOR4_ORG_EMP_CNT] [nvarchar](20) NULL,
[COMPETITOR5_ORG_EMP_CNT] [nvarchar](20) NULL,
[COMPETITOR1_ORG_SHARE_CAP] [nvarchar](20) NULL,
[COMPETITOR2_ORG_SHARE_CAP] [nvarchar](20) NULL,
[COMPETITOR3_ORG_SHARE_CAP] [nvarchar](20) NULL,
[COMPETITOR4_ORG_SHARE_CAP] [nvarchar](20) NULL,
[COMPETITOR5_ORG_SHARE_CAP] [nvarchar](20) NULL,
[COMPETITOR1_ORG_REVENUE] [nvarchar](50) NULL,
[COMPETITOR2_ORG_REVENUE] [nvarchar](50) NULL,
[COMPETITOR3_ORG_REVENUE] [nvarchar](50) NULL,
[COMPETITOR4_ORG_REVENUE] [nvarchar](50) NULL,
[COMPETITOR5_ORG_REVENUE] [nvarchar](50) NULL,
[HOOVERS_IND_CD] [nvarchar](50) NULL,
[DNB_STANDARD_IND_CD] [nvarchar](50) NULL,
[DNB_STANDARD_IND_CD_DESC] [nvarchar](256) NULL,
[FEIN_TAX_IND_NUM] [nvarchar](150) NULL,
[FORMER_BUSINESS_NM] [nvarchar](256) NULL,
[INCORP_YEAR] [nvarchar](120) NULL,
[IND_CD_SALES_PERCENT] [nvarchar](10) NULL,
[IND_CD_SALES_PERCENT_2] [nvarchar](10) NULL,
[IND_CD_SALES_PERCENT_3] [nvarchar](10) NULL,
[IND_CD_SALES_PERCENT_4] [nvarchar](10) NULL,
[IND_CD_SALES_PERCENT_5] [nvarchar](10) NULL,
[IND_CD_SALES_PERCENT_6] [nvarchar](10) NULL,
[IMMEDIATE_PARENT_NM] [nvarchar](256) NULL,
[IMMEDIATE_PARENT_COUNTRY_CD] [nvarchar](50) NULL,
[IMMEDIATE_PARENT_POSTAL_CD] [nvarchar](64) NULL,
[IMMEDIATE_PARENT_ADDRLN_1] [nvarchar](240) NULL,
[IMMEDIATE_PARENT_STATE_CD] [nvarchar](64) NULL,
[IMMEDIATE_PARENT_STATE_ABBRV] [nvarchar](64) NULL,
[IMMEDIATE_PARENT_CITY] [nvarchar](64) NULL,
[US_SIC_CD] [nvarchar](50) NULL,
[US_SIC_CD_DESC] [nvarchar](256) NULL,
[PRMRY_STOCK_EXCHANGE_IND] [nchar](1) NULL,
[PROFIT_OR_LOSS_AMNT] [nvarchar](50) NULL,
[PROFIT_OR_LOSS_GROWTH_RATE] [nvarchar](20) NULL,
[SALES_GROWTH_RATE] [nvarchar](50) NULL,
[SECONDARY_SIC_CD] [nvarchar](50) NULL,
[SECONDARY_SIC_CD_2] [nvarchar](50) NULL,
[SECONDARY_SIC_CD_3] [nvarchar](50) NULL,
[SECONDARY_SIC_CD_4] [nvarchar](50) NULL,
[SECONDARY_SIC_CD_5] [nvarchar](50) NULL,
[SECONDARY_SIC_CD_DESC] [nvarchar](256) NULL,
[SECONDARY_SIC_CD_DESC_2] [nvarchar](256) NULL,
[SECONDARY_SIC_CD_DESC_3] [nvarchar](256) NULL,
[SECONDARY_SIC_CD_DESC_4] [nvarchar](256) NULL,
[SECONDARY_SIC_CD_DESC_5] [nvarchar](256) NULL,
[STOCK_EXCHANGE_CNTRY_CD] [nvarchar](50) NULL,
[STOCK_EXCHANGE_NM] [nvarchar](256) NULL,
[THRD_PARTY_ASSESMENT] [nvarchar](256) NULL,
[THIRD_PARTY_ASSESMENT_RNK] [nvarchar](20) NULL,
[TOTAL_EQUITY_AMOUNT] [nvarchar](50) NULL,
[UK_SIC_CD] [nvarchar](50) NULL,
[FEMALE_OWNED_IND] [nchar](1) NULL,
[DOM_ULT_DNB_POSTAL_CD] [nvarchar](50) NULL,
[DOM_ULT_DNB_ADDRLN_1] [nvarchar](240) NULL,
[DOM_ULT_DNB_STATE_CD] [nvarchar](64) NULL,
[DOM_ULT_DNB_CITY] [nvarchar](64) NULL,
[GLB_ULT_DNB_POSTAL_CD] [nvarchar](50) NULL,
[GLB_ULT_DNB_ADDRLN_1] [nvarchar](240) NULL,
[GLB_ULT_DNB_STATE_CD] [nvarchar](64) NULL,
[GLB_ULT_TOT_EMP_CNT] [nvarchar](10) NULL,
[GLB_ULT_CITY] [nvarchar](64) NULL,
[MARKET_SEG_CLUSTERS] [nvarchar](256) NULL,
[NON_MARTKETABLE_REASON_TXT] [nvarchar](256) NULL
)
Thanks,
Sam
Jeff Moden

SSC Guru

Points: 1004392
More actions
September 6, 2019 at 2:22 pm
Answer
#3677971
Andrey wrote:
Sam,
what will the following command return?
exec sp_spaceused 'dbo.CONTROL_HIST'
It looks like you have BLOB data types, however I don't see it in the table definition.
With all the NVARCHAR() columns and that one monster NVARCHAR(4000) column (and I assume they're all mostly being populated to some great extent), the rows are likely to have a whole lot of "Row Overflow" entries, which are stored kind of like LOBs are.
As Frederico suggests, if the table is, in fact, a heap, doing a table rebuild may fix it. I say that with the caveat that I have no such previous experience with heaps because I avoid them and so I have no idea if or how it will affect the "Row Overflow" data.
If the table actually does have a Clustered Index on it, then one of the ways to recover space that has gone "out of row" is to REORGANIZE the Clustered Index because only REORGANIZE will compress such "out of row" storage. The only experience that I have there is that 1) REORGANIZE isn't the tame little kitty that most people think and 2) in an experiment that I did with just 10 million shorter rows, I had to run a REORGANIZE 14 TIMES (!!!) to recover all of the disk space because REORGANIZE doesn't actually compress the "out-of-row" data to a point of completion on the first or even many subsequent passes.
And, don't forget, REORGANIZE is FULLY LOGGED no matter the Recovery Model and, in some of the experiments I've done, blew out the log file by about 170% of the total size of the table and that was without LOBs being present.
Since this is an audit table and such an audit table is ostensibly "temporally ever-increasing", my suggestion would be to partition the table by (say) month. For the things I have to do (especially restoring prod environments to dev environments), I prefer Partitioned VIEWs (yeah... I made the mistake of using a Partitioned TABLE for what I was doing) but, either way, it will make deletes (archives, whatever) a whole lot less painful.
Considering the width of this table and the fact that all of the columns are "NULLable", you might also want to consider the use of "Columnar" auditing rather than "Whole Row" auditing with the understanding that it will be a bit more difficult to assemble "whole rows" for the audit table.
And I've got to say this... considering the names of the columns, someone should probably spend some serious time fixing the normalization of the base table. 😉 Yeah... I know... that'll never happen for this table.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Andrey SSC Eights! Points: 840 More actions · Answer 1

Sam,

what will the following command return?

exec sp_spaceused 'dbo.CONTROL_HIST'

It looks like you have BLOB data types, however I don't see it in the table definition.

frederico_fonseca SSCoach Points: 16162 More actions · Answer 2

As it is a HEAP it may not release the space - but doing a table rebuild will deal with the issue

vsamantha35 SSChampion Points: 11551 More actions · Answer 3

Its a table created by INFORMATICA ETL TOOL. so we dont have control over it.

Heard about DBCC Cleantable cmd. will it be useful in this case?

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 4

vsamantha35 wrote:

Heard about DBCC Cleantable cmd. will it be useful in this case?

That's used if you delete a variable-length column from a table and want to release the space that was used by the removed column.

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-cleantable-transact-sql?view=sql-server-2017

Maybe you could create a clustered index on the table and rebuild the index when you want to reduce the size of the table.