SQL 2005 DB Space Allocation
in [Server Configuration]
|
From: Gert-Jan Strik on 23 Jul 2010 12:45 You should check the result of your reindex/defrag action. If you run DBCC SHOWCONTIG ("my_biggest_table") you will see "Avg. Bytes Free per Page". If its value is bigger than the average row size, then your fillfactor might be too low, and you are reserving / wasting space. Also, make sure that the tables you want to reindex/defrag have a clustered index. Even if only during the reorganization. Otherwise, no unused space might be reclaimed. -- Gert-Jan
From: RG on 23 Jul 2010 13:13 Yes, there is clustered index. I think, I may have found the major problem. It seems that the heap 12% utilized. I suppose this this is the base table. If so, how do you defrag the heap? Thanks again "Dan" wrote: > Ignore my other reply about the log, as I can see you're looking at only the > mdf. > > Do you have a clustered index? > > What is your page density set to? > > Dan > > > "RG" <RG(a)discussions.microsoft.com> wrote in message > news:A201210A-9A74-448A-8483-C1E1E382F73B(a)microsoft.com... > > I used .. > > > > DBCC showfilestats > > > > shows.. > > > > Fileid FileGroup TotalExtents UsedExtents Name > > > > FileName > > ----------- ----------- -------------------- -------------------- > > -------------------------------------------------------------------------------------------------------------------------------- > > ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > > 1 1 1230584 1086526 WH_Data > > > > C:\Program Files\Microsoft SQL > > Server\MSSQL.1\MSSQL\Data\wh.mdf > > > > > > "Erland Sommarskog" wrote: > > > >> RG (nobody(a)nowhere.com) writes: > >> > >> > I have a db which originally occupied 80 gig. I have deleted 80% of > >> > the > >> > rows in the db. After, I ran reindex and defrag. However, the db > >> > space > >> > usage is at 89%. I expected that the usage should be at 15% at most. > >> > What do I need to do to compact the db pages? > >> > >> It is unclear how you arrived at these numbers where your estimate of 15% > >> comes from. But now you apparently have a 16 GB database, with only 10% > >> free space. Depending how quickly the database fills up, there may be > >> reason to add a few more GB to get some breathing space. > >> > >> -- > >> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > >> > >> Links for SQL Server Books Online: > >> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > >> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > >> SQL 2000: > >> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > >> > >> . > >> > > > > . >
From: Gert-Jan Strik on 23 Jul 2010 13:49 You defrag a heap by adding a clustered index (and dropping it afterwards) -- Gert-Jan
From: RG on 23 Jul 2010 14:17 Correction, the table doesn't have clustered index and that explains the heap. "RG" wrote: > Yes, there is clustered index. > > I think, I may have found the major problem. It seems that the heap 12% > utilized. I suppose this this is the base table. If so, how do you defrag > the heap? > > Thanks again > > "Dan" wrote: > > > Ignore my other reply about the log, as I can see you're looking at only the > > mdf. > > > > Do you have a clustered index? > > > > What is your page density set to? > > > > Dan > > > > > > "RG" <RG(a)discussions.microsoft.com> wrote in message > > news:A201210A-9A74-448A-8483-C1E1E382F73B(a)microsoft.com... > > > I used .. > > > > > > DBCC showfilestats > > > > > > shows.. > > > > > > Fileid FileGroup TotalExtents UsedExtents Name > > > > > > FileName > > > ----------- ----------- -------------------- -------------------- > > > -------------------------------------------------------------------------------------------------------------------------------- > > > ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > > > 1 1 1230584 1086526 WH_Data > > > > > > C:\Program Files\Microsoft SQL > > > Server\MSSQL.1\MSSQL\Data\wh.mdf > > > > > > > > > "Erland Sommarskog" wrote: > > > > > >> RG (nobody(a)nowhere.com) writes: > > >> > > >> > I have a db which originally occupied 80 gig. I have deleted 80% of > > >> > the > > >> > rows in the db. After, I ran reindex and defrag. However, the db > > >> > space > > >> > usage is at 89%. I expected that the usage should be at 15% at most. > > >> > What do I need to do to compact the db pages? > > >> > > >> It is unclear how you arrived at these numbers where your estimate of 15% > > >> comes from. But now you apparently have a 16 GB database, with only 10% > > >> free space. Depending how quickly the database fills up, there may be > > >> reason to add a few more GB to get some breathing space. > > >> > > >> -- > > >> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > >> > > >> Links for SQL Server Books Online: > > >> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > > >> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > > >> SQL 2000: > > >> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > > >> > > >> . > > >> > > > > > > > > . > >
From: Dan on 26 Jul 2010 04:31 In which case I'd take a guess that the extent usage you're seeing is due to the data being scattered all over the place. If you add a clustered index and then drop it, as Gert suggested, that should defrag the table itself. Dan "RG" <RG(a)discussions.microsoft.com> wrote in message news:89474424-DE24-4526-A2CC-AD01883D9BA0(a)microsoft.com... > Correction, the table doesn't have clustered index and that explains the > heap. > > "RG" wrote: > >> Yes, there is clustered index. >> >> I think, I may have found the major problem. It seems that the heap 12% >> utilized. I suppose this this is the base table. If so, how do you >> defrag >> the heap? >> >> Thanks again >> >> "Dan" wrote: >> >> > Ignore my other reply about the log, as I can see you're looking at >> > only the >> > mdf. >> > >> > Do you have a clustered index? >> > >> > What is your page density set to? >> > >> > Dan >> > >> > >> > "RG" <RG(a)discussions.microsoft.com> wrote in message >> > news:A201210A-9A74-448A-8483-C1E1E382F73B(a)microsoft.com... >> > > I used .. >> > > >> > > DBCC showfilestats >> > > >> > > shows.. >> > > >> > > Fileid FileGroup TotalExtents UsedExtents >> > > Name >> > > >> > > FileName >> > > ----------- ----------- -------------------- -------------------- >> > > -------------------------------------------------------------------------------------------------------------------------------- >> > > ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- >> > > 1 1 1230584 1086526 >> > > WH_Data >> > > >> > > C:\Program Files\Microsoft SQL >> > > Server\MSSQL.1\MSSQL\Data\wh.mdf >> > > >> > > >> > > "Erland Sommarskog" wrote: >> > > >> > >> RG (nobody(a)nowhere.com) writes: >> > >> >> > >> > I have a db which originally occupied 80 gig. I have deleted 80% >> > >> > of >> > >> > the >> > >> > rows in the db. After, I ran reindex and defrag. However, the db >> > >> > space >> > >> > usage is at 89%. I expected that the usage should be at 15% at >> > >> > most. >> > >> > What do I need to do to compact the db pages? >> > >> >> > >> It is unclear how you arrived at these numbers where your estimate >> > >> of 15% >> > >> comes from. But now you apparently have a 16 GB database, with only >> > >> 10% >> > >> free space. Depending how quickly the database fills up, there may >> > >> be >> > >> reason to add a few more GB to get some breathing space. >> > >> >> > >> -- >> > >> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se >> > >> >> > >> Links for SQL Server Books Online: >> > >> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx >> > >> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx >> > >> SQL 2000: >> > >> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >> > >> >> > >> . >> > >> >> > >> > >> > >> > . >> >
First
|
Prev
|
Pages: 1 2 Prev: Too Many Statistics Objects? Next: Simple recovery database bloating? |