September 22, 2011 at 11:03 am
Ive got a particularly large table that is purged at 65 days of retention. the purge is keeping up with new volume, and the row count is relatively steady.
Tens of millions of new rows daily, but the overall trend is very slightly up as the usage of the DB continues to grow.
Table size was relatively steady along with total daily row count until recently. then it took off.
row count remains a slow steadily increasing up-tick, but reserved size is increasing rapidly, very suddenly, at a dramatic rate. This started very clearly on ONE DAY, and I cannot correlate that to anything.
heres what I know:
1) no object changes occurred around this time. column definitions, indexes, etc are the same as they were before.
2) the columns are almost all int, datetime, money, etc. a nchar(1) col a varchar(22) (thats only sporadically used) and some numerics that are all NULL values. theres only 16 columns, so its not a wide table
3) Of the rows that have any data in the single varchar column, there is not a disproportionate number of rows daily now compared to before this spike in size
4) data size and index size are growing at equaly startling rates. of the indexes, the clustered index is growing at the fastest clip.
5) Ive triple and quadruple checked the row counts because this all sounds like an increase in that metric. over the last 3 weeks weve had a < 2% increase in overall row count, and a 30% increase in table size. this thing has gone from roughly 500gb to roughly 650gb in that time.
6) scandensity and logicalfragmentation on all indices is trending pretty flat and even through out this period.
im stumped.
any thoughts or suggestions?
this is sql2008 sp1. about 2 weeks before this started, it was migrated from a sql2005 server. I cant imagine its related, but in the interest of providing what I know, I mention it.
thanks in advance.
September 22, 2011 at 11:26 am
LAW1143 (9/22/2011)
Ive got a particularly large table that is purged at 65 days of retention. the purge is keeping up with new volume, and the row count is relatively steady.Tens of millions of new rows daily, but the overall trend is very slightly up as the usage of the DB continues to grow.
Table size was relatively steady along with total daily row count until recently. then it took off.
row count remains a slow steadily increasing up-tick, but reserved size is increasing rapidly, very suddenly, at a dramatic rate. This started very clearly on ONE DAY, and I cannot correlate that to anything.
heres what I know:
1) no object changes occurred around this time. column definitions, indexes, etc are the same as they were before.
2) the columns are almost all int, datetime, money, etc. a nchar(1) col a varchar(22) (thats only sporadically used) and some numerics that are all NULL values. theres only 16 columns, so its not a wide table
3) Of the rows that have any data in the single varchar column, there is not a disproportionate number of rows daily now compared to before this spike in size
4) data size and index size are growing at equaly startling rates. of the indexes, the clustered index is growing at the fastest clip.
5) Ive triple and quadruple checked the row counts because this all sounds like an increase in that metric. over the last 3 weeks weve had a < 2% increase in overall row count, and a 30% increase in table size. this thing has gone from roughly 500gb to roughly 650gb in that time.
6) scandensity and logicalfragmentation on all indices is trending pretty flat and even through out this period.
im stumped.
any thoughts or suggestions?
this is sql2008 sp1. about 2 weeks before this started, it was migrated from a sql2005 server. I cant imagine its related, but in the interest of providing what I know, I mention it.
thanks in advance.
This is a shot in the dark, but I am wondering if columns that used to contain null values and now contain something other than null which would take up more space.
September 22, 2011 at 11:29 am
thanks for the reply, but this was a thought I had as well and already ruled out. what was null then is null now.
its tied to a specific bit of application functionality that is not in use.
September 22, 2011 at 11:31 am
Fill factor change?
Any small change that could have affected the row size could be compounded but lots more pages being required to hold the data.
But I'm short on useful actionable ideas.
September 22, 2011 at 11:32 am
September 22, 2011 at 11:33 am
great thought, and I should have included that in the 'what I know' portion of my first post...
none of the indexes on this table have been rebuilt or reorganized lately, thus no change in fill factor.
and correct me if im wrong, but only a fillfactor change on the clustered index would impact this, right?
September 22, 2011 at 11:38 am
okbangas (9/22/2011)
Do you have a clustered index? If so, is it unique? Otherwise, updates can cause page splits. Aborter possibility, have you recently started to use row versioning (snapshot isolation)?
there is a unique clustered index.
no row versioning in place.
September 22, 2011 at 11:41 am
LAW1143 (9/22/2011)
great thought, and I should have included that in the 'what I know' portion of my first post...none of the indexes on this table have been rebuilt or reorganized lately, thus no change in fill factor.
and correct me if im wrong, but only a fillfactor change on the clustered index would impact this, right?
Lower fill factor on any index will have an impact. I was hoping it could be this because of the sudden part.
Do you have a compare tool that can compare both versions of the table? Any difference, no matter how small might guide us to a solution.
September 22, 2011 at 11:47 am
Ninja's_RGR'us (9/22/2011)
LAW1143 (9/22/2011)
great thought, and I should have included that in the 'what I know' portion of my first post...none of the indexes on this table have been rebuilt or reorganized lately, thus no change in fill factor.
and correct me if im wrong, but only a fillfactor change on the clustered index would impact this, right?
Lower fill factor on any index will have an impact. I was hoping it could be this because of the sudden part.
Do you have a compare tool that can compare both versions of the table? Any difference, no matter how small might guide us to a solution.
yeah, lower fill factor was an initial suspicion.
what do you mean by 'compare both versions of the table'?
im 99.99999% sure the table definition before and after this sudden growth increase is identical.
the only way this would have been changed is if it had been done via an upgrade (which i know isnt the case) or with a change control entry, of which there is none. I trust the others who have DBO access to have not made a change of this sort. Ive also got a refreshed non-prod copy of the database created a few days before this spike began, and they match up 1:1.
September 22, 2011 at 12:00 pm
another (probably useless) tidbit...
sys.sysdatabases and sys.databases are not the same! I just spent 5 minutes trying to figure out why I couldnt validate snapshot isolation levels in sys.sysdatabases... one of those days... :w00t:
thanks greatly for the suggestions and recomendations thus far! On one hand, I'd prefer this was a no brainer and I was overlooking something. on the other, I guess im glad Ive considered a thorough list of possibilities! 😮
September 22, 2011 at 2:03 pm
LAW1143 (9/22/2011)
another (probably useless) tidbit...sys.sysdatabases and sys.databases are not the same! I just spent 5 minutes trying to figure out why I couldnt validate snapshot isolation levels in sys.sysdatabases... one of those days... :w00t:
thanks greatly for the suggestions and recomendations thus far! On one hand, I'd prefer this was a no brainer and I was overlooking something. on the other, I guess im glad Ive considered a thorough list of possibilities! 😮
Those are not the same object so no they won't match!
I was talking about using redgate's compare tools to see if anything at all had changed between the schemas.
September 26, 2011 at 2:00 pm
The plot thickens,.
another table on the same server, different database exhibiting the EXACT same conditions.
September 26, 2011 at 2:47 pm
Are these tables located on the PRIMARY partition? Also, what is the autogrowth set at for the file? Lastly, what recovery model? And, just for S&G... Collation?
Thanks,
Jared
Jared
CE - Microsoft
September 26, 2011 at 2:55 pm
we autogrow by 10%, BUT monitor file free space and manually grow files in advance of an autogrow occuring.
We have a primary file group with one primary data file and 11 secondaries.
the collation is SQL_AltDiction_Pref_CP850_CI_AS
we have thousands of dbs on that collation though and havent ever come across this scenario.
September 26, 2011 at 2:57 pm
and I opened a case with M$ support to see if they can make heads or tails of it.
reaction after the first 90 minute call was "HMmmmmmmmmmmmmmm" :w00t:
Viewing 15 posts - 1 through 15 (of 44 total)
You must be logged in to reply to this topic. Login to reply