October 10, 2011 at 7:06 am
We have a production database that is growing alarmingly. We have tracked the growth down to a single table. When we run the command "sp_spaceused TableName", we get these results:
names rows reserved data index_size unused
mdl_sessions89475 3953456 KB3916816 KB 31904 KB 4736 KB
However, in our test system, we see very different results:
names rows reserved data index_size unused
mdl_sessions54509 474000 KB293048 KB 15336 KB165616 KB
In summary, the live system has 75% more rows, but 8x as much reserved space, and 13x as much actual data. I have checked every single property of the databases, and the tables, and can find no difference between the 2 databases.
Can anyone think of a reason why the live table should be so much bigger than the test one?
Thanks in advance,
Mark Thornton
October 10, 2011 at 8:15 am
I would run DBCC UPDATEUSAGE first.
Then I would look at results of sys.dm_db_index_physical_stats for this table and it's indexes.
I would also check default fill factor property, but you've done this already.
October 10, 2011 at 8:18 am
The DDL for the table wound be useful
Having a "blob" field in there full of xml, documents and images would make a big difference
October 10, 2011 at 9:50 am
Thanks, you make a good point, but there are no "blob" fields involved, in this case.
Best wishes
Mark
October 10, 2011 at 10:02 am
Your average row size is around 44KB, which means your table is either incredibly wide (e.g. lots of varchar/varbinary columns) or you're using LOB's in one way or another (varchar(max), varbinary(max) etc.)...
Can we see DDL?
October 10, 2011 at 10:16 am
Here is the CREATE statement for the table. I presume that most of the data is in the [sessdata]:
CREATE TABLE [dbo].[mdl_sessions](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[state] [bigint] NOT NULL,
[sid] [nvarchar](128) NOT NULL,
[userid] [bigint] NOT NULL,
[sessdata] [ntext] NULL,
[timecreated] [bigint] NOT NULL,
[timemodified] [bigint] NOT NULL,
[firstip] [nvarchar](45) NULL,
[lastip] [nvarchar](45) NULL,
CONSTRAINT [mdl_sess_id_pk] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[mdl_sessions] ADD DEFAULT ((0)) FOR [state]
GO
ALTER TABLE [dbo].[mdl_sessions] ADD DEFAULT ('') FOR [sid]
GO
October 10, 2011 at 10:21 am
What is stored in the ntext column? That will be the cause
October 10, 2011 at 10:30 am
[sessdata] [ntext] NULL,
This is a LOB. You must have more data in this column on average than you did on your test environment.
This column can store up to 2GB of data in a single row. You could check if this is just a few pieces of anomolous data first. E.g. running the following will show you the 100 biggest rows:
SELECT TOP 100 DATALENGTH(sessdata), ID FROM mdl_sessions
ORDER BY DATALENGTH(sessdata) desc
ntext is also deprecated and you should replace with varchar(max) or nvarchar(max) if you really need this (only use unicode columns if you need to support unicode data, otherwise you are needlessly doubling storage requirements)
October 10, 2011 at 10:42 am
MysteryJimbo (10/10/2011)
What is stored in the ntext column? That will be the cause
Definitely.
By default NTEXT stores the text value in the LOB structure and the table structure just holds a pointer to the location in the LOB where the text lives.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 10, 2011 at 10:42 am
HowardW (10/10/2011)
[sessdata] [ntext] NULL,
This is a LOB. You must have more data in this column on average than you did on your test environment.
This column can store up to 2GB of data in a single row. You could check if this is just a few pieces of anomolous data first. E.g. running the following will show you the 100 biggest rows:
SELECT TOP 100 DATALENGTH(sessdata), ID FROM mdl_sessionsORDER BY DATALENGTH(sessdata) desc
ntext is also deprecated and you should replace with varchar(max) or nvarchar(max) if you really need this (only use unicode columns if you need to support unicode data, otherwise you are needlessly doubling storage requirements)
That's what I get for stepping away in the middle of replying - Howard beat me to it.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply