Issue with unclaimed space
in [Server Configuration]
|
From: jyao on 3 Jul 2008 14:37 Hi all, I think I need to post a result for this orginal post here. It seems I can hardly reproduce this issue in my test enviornment, but it did exist in my production due to the huge concurrencies in production which cannot be easily reproduced in a test environment. What we finanlly decide is to partition this table based on date, and there will be a partition for each day, and the each partition is on a different file, and the next day, we will auto delete the yesterday's partition and create a new partition for tomorrow, and this is what is termed as the "sliding window" mangement of the partition. The benefits are two folds here 1. We reduce the delete tractions on the table, and this may improve our system performance 2. by dropping a partition, we also dropped the file on which the partition resides, and this guarantees there is no space unclaimed. Jeff "jeff_yao" wrote: > Hi all, > > I am not sure whether someone has experienced the same issue as I do. > > I have a table which has a column with text data type and a few other > regular columns (int, datetime etc). There are inserts / deletes on this > table, but the weird thing is the table is keeping on growing, but the row > number is not (because of delete actions), for example, when the table has > 10000 rows, it has 150GB, but when it has 6500 rows, it has 179GB, actualy > when I execute sp_spaceused 'mytable' to monitor, I can see the rows are > dynamically changing, i.e. sometimes more rows, sometimes fewer rows, but > the data column in the sp_spaceused result set has the value growing > continously. > My environment is SQL2K5 EE (ver 9.0.3054). > > In my test environment, I cannot repeat this production issue. > > Does anyone encounter the same issue before? > > TIA, > Jeff_Yao > > >
From: Roy Harvey (SQL Server MVP) on 3 Jul 2008 15:49 I appreciate your providing closure on this. Thanks! Roy Harvey Beacon Falls, CT On Thu, 3 Jul 2008 11:37:02 -0700, jyao <jyao(a)discussions.microsoft.com> wrote: >Hi all, > >I think I need to post a result for this orginal post here. > >It seems I can hardly reproduce this issue in my test enviornment, but it >did exist in my production due to the huge concurrencies in production which >cannot be easily reproduced in a test environment. > >What we finanlly decide is to partition this table based on date, and there >will be a partition for each day, and the each partition is on a different >file, and the next day, we will auto delete the yesterday's partition and >create a new partition for tomorrow, and this is what is termed as the >"sliding window" mangement of the partition. >The benefits are two folds here >1. We reduce the delete tractions on the table, and this may improve our >system performance >2. by dropping a partition, we also dropped the file on which the partition >resides, and this guarantees there is no space unclaimed. > >Jeff > >"jeff_yao" wrote: > >> Hi all, >> >> I am not sure whether someone has experienced the same issue as I do. >> >> I have a table which has a column with text data type and a few other >> regular columns (int, datetime etc). There are inserts / deletes on this >> table, but the weird thing is the table is keeping on growing, but the row >> number is not (because of delete actions), for example, when the table has >> 10000 rows, it has 150GB, but when it has 6500 rows, it has 179GB, actualy >> when I execute sp_spaceused 'mytable' to monitor, I can see the rows are >> dynamically changing, i.e. sometimes more rows, sometimes fewer rows, but >> the data column in the sp_spaceused result set has the value growing >> continously. >> My environment is SQL2K5 EE (ver 9.0.3054). >> >> In my test environment, I cannot repeat this production issue. >> >> Does anyone encounter the same issue before? >> >> TIA, >> Jeff_Yao >> >> >>
|
Pages: 1 Prev: dm_exec_requests start_time not being updated? Next: Where discuss SQL 2008? |