September 26, 2011 at 3:03 pm
Hehehe... Good luck! You'll get faster and more accurate help here 🙂
Jared
Jared
CE - Microsoft
September 26, 2011 at 3:04 pm
I've yet to have a positive experience come out of talking with M$ support, typically I've experienced they go through everything you've already tried only to arrive and the same lack of solution. I've always gotten better responses/advice from this forum.
Just a random thought: in your environment...in the table/db that's experiencing the excessive growth...do you have BLOB/TEXT columns? In a prior job I noticed DB growth because a web application was storing massive amounts of data in the app and them wrote them to these fields...and it grew and grew and grew until the web service was restarted, causing quite a bit of growth (like 1.2GB XML strings being written to the dolumn in the table). Again just a crazy thought...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
September 26, 2011 at 3:04 pm
With tens of millions of rows... What is the data type for the column of your clustered index? Is it identity insert?
Thanks,
Jared
Jared
CE - Microsoft
September 26, 2011 at 3:08 pm
Ive gotten some REALLY good help from them in the past. On occasion though, it takes a few rounds of engineers to get the help I need... Ive been on a roll lately, averaging a case a week, after being on a once a year basis for a while...
anyways, of these tables, the original one is mostly ints, with nothing of the blob/varchar type thats appreciable.
the second table I just discovered today is not that way. its a guid, an int and two varchar(max) columns... the type that I WOULD expect this kinda thing to happen with. I havent had a chance to do any row level analysis on this one yet.
but, it can NOT be a coincidence that two completely unrelated tables experienced a spid in reserved size (in both data AND index size) on the same date.
between two tables with no appreciable change in row count, Ive had over 500gb of growth in the last 30 days.
September 26, 2011 at 3:10 pm
jared-709193 (9/26/2011)
With tens of millions of rows... What is the data type for the column of your clustered index? Is it identity insert?Thanks,
Jared
Jared, we're well beyond 10s of millions, as its about 1.2Billion rows.
no identity columns. the clustered index is based on a date and 3 int columns.
September 26, 2011 at 3:13 pm
Ok, and I assume that this is partitioned on month with the date field? (you mentioned a total of 12 file groups) Also, for my knowledge, where exactly are you getting the reserved space from for these tables?
Jared
Jared
CE - Microsoft
September 26, 2011 at 3:17 pm
no partitioning, just a single file group with a bunch of secondary files.
I know its not optimal, but I'm on the implementation end NOT the architecting end of this database, and thats out of my hands.
im getting the space metrics from sp_spaceused, and the index metrics (which tell me the clustered AND nonclustered indexes are growing) from good ole dbcc showcontig (which I know is due for deprication in the next version).
September 26, 2011 at 3:21 pm
Ok, can you post the results of sp_spaceused for this table? And... Are you setting updateusage = TRUE?
Thanks,
Jared
Jared
CE - Microsoft
September 27, 2011 at 12:18 am
By the way have you checked avg_page_space_used_in_percent with sys.dm_db_index_physical_stats dmv.
September 27, 2011 at 5:55 am
suresh.guglani (9/27/2011)
By the way have you checked avg_page_space_used_in_percent with sys.dm_db_index_physical_stats dmv.
NULL?
September 27, 2011 at 5:58 am
jared-709193 (9/26/2011)
Ok, can you post the results of sp_spaceused for this table? And... Are you setting updateusage = TRUE?Thanks,
Jared
shouldnt be necessary with sql 2005 +.
or is there something im not aware of that makes it necessary in these later versions?
also, I know the old update usage run at the db level could cause some blocking. would the sp_spaceused with update usage = true do the same?
with billion + rows purged to 65 days, its obviously got a ton of activity volume against it...
September 27, 2011 at 7:44 am
Another thought is your clustered index. As the values in the composite columns get greater, so does the storage space required by the index. Also, as I understand it, every non-clustered index will contain the key value making them larger as well. Is this a possibility in your design?
Also, @updateusage is still important in 2008 R2 http://msdn.microsoft.com/en-us/library/ms188776.aspx
Have any of your data columns grown in size that would require data to be pushed to another page in the ROW_OVERFLOW_DATA allocation unit? http://msdn.microsoft.com/en-us/library/ms186981.aspx This would essentially increase your size without actual ddl changes.
Thanks,
Jared
Jared
CE - Microsoft
September 28, 2011 at 2:37 am
Hi,
It seems to me that you passed the threshold where GHOST CLEANUP process can keep up with your deletion rate. Can this be the case here?
September 28, 2011 at 8:15 am
seregak76 (9/28/2011)
Hi,It seems to me that you passed the threshold where GHOST CLEANUP process can keep up with your deletion rate. Can this be the case here?
Great thought! Try running this query:
SELECT ghost_record_count, record_count, * FROM sys.dm_db_index_physical_stats
(DB_ID(N'DatabaseName'), OBJECT_ID(N'dbo.TableName'), NULL, NULL , 'DETAILED');
Thanks,
Jared
Jared
CE - Microsoft
September 28, 2011 at 8:21 am
you guys rule. running that query now.
I'll also try the sp_spaceused with update usage = true on a non-prod copy to get a feel for how long and what kinda impact that might have before running it on the prod table.
thanks for sticking with me and keeping my conundrum in mind! 🙂
Viewing 15 posts - 16 through 30 (of 44 total)
You must be logged in to reply to this topic. Login to reply