x

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

more ▼

asked Nov 02, 2009 at 05:13 PM in Default

user-452 gravatar image

user-452
21 1 1 1

Please give us an idea of what data types/number of columns that are included in the table.
Nov 02, 2009 at 03:54 PM Blackhawk-17

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
Nov 02, 2009 at 04:15 PM Barbara Rohrbacher
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...
Nov 02, 2009 at 04:24 PM Matt Whitfield ♦♦
Need to build those reputation points up so you can start commenting ;)
Nov 02, 2009 at 06:03 PM Blackhawk-17
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)
Nov 02, 2009 at 06:29 PM Håkan Winther
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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.

more ▼

answered Nov 02, 2009 at 06:35 PM

Blackhawk-17 gravatar image

Blackhawk-17
11.9k 28 31 36

Do the smaller tables not have indexes and the larger ones have multiple?
Nov 02, 2009 at 06:38 PM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Nov 02, 2009 at 06:26 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Nov 02, 2009 at 06:02 PM

Blackhawk-17 gravatar image

Blackhawk-17
11.9k 28 31 36

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1948
x473
x241
x22

asked: Nov 02, 2009 at 05:13 PM

Seen: 1700 times

Last Updated: Nov 02, 2009 at 05:13 PM