March 30, 2009 at 8:58 am
Hi to all.
1)
I read an article on the forum reference indexes / fragmentation. I ran 'DBCC SHOWCONTIG' against the Production system (extract below)
DBCC SHOWCONTIG scanning 'Products' table...
Table: 'Products' (1983710515); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 4200
- Extents Scanned..............................: 537
- Extent Switches..............................: 896
- Avg. Pages per Extent........................: 7.8
- Scan Density [Best Count:Actual Count].......: 58.53% [525:897]
- Logical Scan Fragmentation ..................: 13.62%
- Extent Scan Fragmentation ...................: 33.71%
- Avg. Bytes Free per Page.....................: 1243.4
- Avg. Page Density (full).....................: 84.64%
Table: 'Prices' (2011154210); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 4774
- Extents Scanned..............................: 609
- Extent Switches..............................: 638
- Avg. Pages per Extent........................: 7.8
- Scan Density [Best Count:Actual Count].......: 93.43% [597:639]
- Logical Scan Fragmentation ..................: 23.71%
- Extent Scan Fragmentation ...................: 67.16%
- Avg. Bytes Free per Page.....................: 75.5
- Avg. Page Density (full).....................: 99.07%
Q: How should I interpret the output?
2)
When the database was installed the initial size of the MDF was set to 2,255mb, the LDF to 1744 (each to grow by 10%).
I was going to set the initial size on the MDF to 5GB with autogrowth set to a fixed value 500MB. I was going to set autogrowth on the LDF to 100MB.
I am unable to offer any metrics on projected growth but lets say the MDF will grow by 1GB a year.
I was told it was possible to analyse the MDF / LDF files to see which portion of the file is taken up by data and which portion is free space (to help with sizing). Can anyone advise how I can do this.
Kind Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
March 30, 2009 at 9:06 am
Regards part 2 of my post I have found a standard report 'Disk Usage'.
It reports Auto Growth Event for the Log File on 18 occasions. Currently 98.5% is unused (I have recently amended maintenance plan to backup the transaction log every 2 hours as opposed every 5).
The MDF reports space reserved 2.20GB, space used 2.07GB.
87.84 Data
0.94 Unused
6.02 Unallocated
5.2 Index
What is the difference between Unused and Unalloacted space?
I have scheduled an index rebuild for the weekend (to run weekly each Sunday).
Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
March 30, 2009 at 9:20 am
To your first question, neither of those tables looks terribly fragmented. When the logical fragmentation breaks 30%, you might want to consider reorganizing the index. As it gets more fragmented you should consider rebuilding it. It really depends on your system though, there are no hard & fast numbers.
Also, since you're working 2005, I'd suggest using sys.dm_db_index_physical_stats rather than the old DBCC. You get better and easier to understand data that way.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 30, 2009 at 9:27 am
Thanks Grant.
Would you suggest I run a 'Reorganize Index' instead of a 'Rebuild' (I know a rebuild will reorganize anyhows). Small database. Not sure how large the data file will grow during a rebuild?
I have just been looking at the standard reports. Index Physical Stats for table Products returns:
Avg Fragmentation 12% / Fragments 12 / Avg Pages Per Fragment 7 / Pages 84
What factors determine the page size?
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
March 30, 2009 at 5:54 pm
What factors determine the page size?
Fillfactor of the index.
MJ
March 31, 2009 at 8:01 am
Thanks for the update.
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
March 31, 2009 at 8:09 am
to rebuild or reorg is a kind of a hard decision. You can apply a rule and see how it works and adjust to meet your needs. Say 30% to 70%, reorg and greater than 70% rebuild. But you'll need to see how it works for your system.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 1, 2009 at 11:48 am
Sounds like a plan.
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply