Puzzling database growth
-
Almost two weeks ago (beginning of July), we started experiencing some very slow response times with some of our queries. One of the tables these queries access has a fairly high volume of inserts and updates daily.
I thought there may have been an issue w/the execution plan the optimzer was using for this table, so I manually ran an update statistics for this table (even though auto update statistics is turned on for this database). I also later ran a DBCC DBREINDEX on the clustered index for this table (with fillfactor of 90), and later ran another update statistics.
Query performance has gotten better; however, I noticed that the database has tripled in size. And I'm not sure if the update stats or reindex that I executed was the culprit or not. It appears this large increase occurred within the span of 24 hours, after years of fairly stable db growth.
Below is the current sp_spaceused for the table in question, and the sp_spaceused back on July 1st:
Current:
rows reserved data index_size unused
----------- ------------------ ------------------ ------------------ ------------------
8445961 17152960 KB 16628744 KB 524248 KB -32 KB
From July 1:
rows reserved data index_size unused
----------- ------------------ ------------------ ------------------ ------------------
8042797 2131008 KB 1663112 KB 467848 KB 48 KB
If you look at the at space used from July 1, you see that 8+ million rows accounted for approx 2GB of reserved space. Now, 8.4+ million rows account for approx 17GB of reserved space. I don't understand why the big jump.
I would expect some increase in size when rebuilding indexes, but as you see, indexes now only account for approx 500MB of the space. Almost all of this increase was attributed to data. We have had a higher volume of data inserted lately, but I certainly wouldn't expect that 400,000 more rows added to an 8+ million record table to make it increase in size by more than 8x.
Could the reindex or update stats I ran caused this? Or is this likely something else? I have been doing some research on this and so far, have not come across anything similar. Any input on this would be greatly appreciated. Thanks.
-
Please disregard post. Think I found the issue. Thanks.
-
I had a similar situation regarding my database growing to large and it was due to dropping and creating indexes on our nightly process that does quite a lot of inserting data. The create and dbreindex does make the database grow large and what I had to do was backup the database and shrink it down.
-
Help the next guy out, always post the solution you found, even if it was just a stupid mistake. It's likely someone else will make the same mistake.
James.
-
I did find an interesting note in BOL about "dropping and rebuilding large objects" which begs the question: was your operation complete when you ran the "current" sp_spaceused? I am a little interested by the -32KB of unused space indicated by the procedure (I took a look at a few of my tables and at your example, and it seems reserved = data + index + unused, which in your case indicates that the table is using more than has been allocated to it). It seems to me like the information for "current" is either misleading or inaccurate, maybe re-run and compare your new results with the old? Perhaps the server was in the middle of an operation?
-- http://dbachman.blogspot.com
-
Yep, it was a dumb mistake. I intended to run the reindex w/fillfactor of 90, but believe I ran it with 10 instead. If you envoke re-orgs via maintenance plans, there is an option for "change free space per page percentage to". So, I had in my mind that I wanted 10% free space. However, when you run manually w/fillfactor, you actually need the reverse - the percentage filled per page. Yep, I feel pretty dumb right now.
Thanks everyone for your input. -
SO hope you will never mistake with the fillfactor again. These things are normal each one does a mistake but are major considering the effects that they would do to demolish the performance.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
Heh... I think lots of folks, including me, have made that error at least once
Just a bit of an edge for performance reasons... if you have definition/lookup tables with static or nearly static data, consider setting Fill Factors for those tables to 100%... especially the PK and Clustered Index (usually the same but sometimes not).
--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) -
One unanticipated effect of a dbcc reindex is that you can expect that all of the tables in your database with a clustered index will have to be sorted, you can expect the database to grow by at least the size of the largest table in the database.
Joe
-
yes, When given as 10 the pages are filled with 10% and more the pages get created for that db. Obviously the space gets increased when there is more pages.
-
Can anyone help me. i am trying the reindex myself and my database has grown by 15 gig. I am new to this and am trying a script i found online that seemed to work fine on a test db.
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply