asked
Why space more for staging Table compared to main tables
I have used below query to get space for tables. I have one doubt...
http://www.databasejournal.com/features/mssql/article.php/3733996/Find-space-Usage-by-Table--Schema-in-SQL-Server-2005-and-2008.htm I have 4 tables say...Two main tables and 2 staging tables(_TMP). Though the count in main tables is more the staging tables, I dont understand why data size is different and unused is different? In fact, main tables having lot data space and unsed space. How the system will allocate table space? There is proper indexing and primary key relationship on main tables but not on _TMP tables.Please help me out. Data getting into staging tables (_TMP) is thru SSIS and from this tables data moving to main tables thru SSIS. What is the mechanism of allocating space in ms sql? ![alt text][1] [1]: /storage/temp/673-table.jpg
You might want to consider the behavior changes introduced in SQL 2005 that might be causing your problem. If your _TMP tables are heaps, and you load the data into them using the TABLOCK hint, then they are treated as bulk inserts (for speed improvements). As a bulk insert does not use old pages you will only get new page allocations. If this is the case for you there are a couple of options...either drop and recreate the _TMP tables, remove the TABLOCK hint, add a clustered index to the _TMP tables (and test your load performance to see if it is acceptable).
+1 good point. Anyway not necessary to drop the table.. TRUNCATE is enough.
Without knowing the details of your load process, it's just guesswork. It's not uncommon for a load process to stage more rows than it will actually use, then delete the unwanted ones prior to loading the main table. Any possibility your process does something similar? Is it possible the _TMP table had been loaded several times in-a-row before finally being cleared for a new run - perhaps as the process was developed or encountered errors? This could have been space that was allocated during those previous runs. If you drop/recreate the staging tables are your sizes still uneven between stage and main?
14 People are following this question.
