question

user-452 avatar image
user-452 asked

What caused leap in space used from SQL Server 2000 to SQL Server 2005?

* I reentered thequestion - as I couldnt respond to your comments in the other post I apologize greatly*

The jump in space used apparently happened on Friday night when the maintenance plan kicked off - after the checkdb portion.

Recently I moved from SQL Server 2000 to SQL Server 2005. I have several tables that have jumped from 5 gig to 45 gig. The data in these tables has not changed.

I have one table for each year... for years 2000 thru 2008 the number of rows are approx 45 mill for each table. for years 2000 thru 2004 the tables are taking up 45 gig space; for years 2005 thru 2008 the tables are taking up 5 gig space. table has the following columns: id - int not null type - char(3) not null runid - smallint not null mn1 - float not null mn2 - float not null mn3 - float not null mn4 - float not null mn5 - float not null mn6 - float not null mn7 - float not null mn8 - float not null mn9 - float not null mn10 - float not null mn11 - float not null mn12 - float not null

The following query was used to identify the space usage: CREATE TABLE #temp ( table_name sysname , row_count INT, reserved_size VARCHAR(50), data_size VARCHAR(50), index_size VARCHAR(50), unused_size VARCHAR(50)) SET NOCOUNT ON INSERT #temp EXEC sp_msforeachtable 'sp_spaceused ''?''' SELECT a.table_name, a.row_count, COUNT(*) AS col_count, a.data_size FROM #temp a INNER JOIN information_schema.columns b ON a.table_name collate database_default = b.table_name collate database_default GROUP BY a.table_name, a.row_count, a.data_size ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC DROP TABLE #temp

sql-server-2005sql-server-2000databasespace
5 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Blackhawk-17 avatar image Blackhawk-17 commented ·
Please give us an idea of what data types/number of columns that are included in the table.
0 Likes 0 ·
Barbara Rohrbacher avatar image Barbara Rohrbacher commented ·
I have one table for each year... for years 2000 thru 2008 the number of rows are approx 45 mill for each table. for years 2000 thru 2004 the tables are taking up 45 gig space; for years 2005 thru 2008 the tables are taking up 5 gig space. table has the following columns: id - int not null type - char(3) not null runid - smallint not null mn1 - float not null mn2 - float not null mn3 - float not null mn4 - float not null mn5 - float not null mn6 - float not null mn7 - float not null mn8 - float not null mn9 - float not null mn10 - float not null mn11 - float not null mn12 - float not null
0 Likes 0 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
How are you measuring the space used? It would be most helpful if you would post the contents of sys.indexes for the relevant objects...
0 Likes 0 ·
Blackhawk-17 avatar image Blackhawk-17 commented ·
Need to build those reputation points up so you can start commenting ;)
0 Likes 0 ·
Håkan Winther avatar image Håkan Winther commented ·
How did you migrate to 2005? I guess your maintenance plan includes a index rebuild. What fillfactor are you using? If fillfactor is to low, the tables and indexes will expand a lot. Maybe you have defined a default fill factor on your database (or even in the model database) and when you create your indexes and have not defined a fillfactor you will use the default. Default for a db in 2000 was 100% (that is, the data pages where filled completely 8kb)
0 Likes 0 ·

3 Answers

·
Blackhawk-17 avatar image
Blackhawk-17 answered

Based on your data types we are looking at approximately 105 bytes per row.

45,000,000 * 105 = 4725000000 bytes .../1024 = 4614257.8125 KB .../1024 = 4506.1111 MB .../1024 = 4.4 GB

Which is in line with your findings of table size without indexes applied.

The larger tables appear to be allocating 1,000 bytes or so per row.

Try running the following to see if there are weird allocations:

SELECT DISTINCT DATALENGTH(id) + DATALENGTH(runid)
+ DATALENGTH(mn1) + DATALENGTH(mn2) + DATALENGTH(mn3) 
+ DATALENGTH(mn4) + DATALENGTH(mn5) + DATALENGTH(mn6) 
+ DATALENGTH(mn7) + DATALENGTH(mn8) + DATALENGTH(mn9) 
+ DATALENGTH(mn10) + DATALENGTH(mn11) + DATALENGTH(mn12) 
FROM EachOfTheTables

It will take a bit to chug through but we may get a clue out of it.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Blackhawk-17 avatar image
Blackhawk-17 answered

Can you give us the output for your tables based on the following query:

SELECT object_name(object_id) AS [Object Name], SUM(used_page_count) * 8192. / 1024 / 1024 AS [Pages Used in MB]
FROM sys.dm_db_partition_stats
GROUP BY object_id
ORDER BY SUM(used_page_count) DESC;

Just Edit your post or comment on it.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered

As well, could you post the output of the queries

SELECT * 
  FROM sys.[indexes] 
 WHERE [object_id] = OBJECT_ID('name_of_big_table')

SELECT * 
  FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('name_of_big_table'), NULL, NULL, NULL)

Replace 'name_of_big_table' with the name of one of the tables that has grown in size, and that will give us fragmentation info, as well as the information regarding configured fill factors for the indexes.

A rough overview of your maintenance plan would also help greatly.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.