| Author |
Topic |
|
IceDread
Yak Posting Veteran
66 Posts |
 Posted - 2007-10-11 : 02:23:39
|
| Greetings.I'm currently on a company with an ms sql server 2000. I'm looking into the indexes and tables to see if there are some bottlenecks there but the LogicalFramentation is very low in the index I have searched. However, this table has a logicalFragmentation of 99,9215698242188 which I get when I do DBCC SHOWCONTIG ([TInsurance]) WITH TABLERESULTS. Is that a value to be trusted or not to be trusted since this does not check an index? If it is, how do I defrag a table? I know only how to defrag an index. (example: DBCC INDEXDEFRAG (MFSSEK,[TInsurance], PK_InsuranceID) )Tipps, suggestions, help, all is very wellcome! :-)DBCC SHOWCONTIG scanning 'TInsurance' table...Table: 'TInsurance' (2051694557); index ID: 0, database ID: 17TABLE level scan performed.- Pages Scanned................................: 1275- Extents Scanned..............................: 225- Extent Switches..............................: 224- Avg. Pages per Extent........................: 5.7- Scan Density [Best Count:Actual Count].......: 71.11% [160:225]- Extent Scan Fragmentation ...................: 74.67%- Avg. Bytes Free per Page.....................: 520.0- Avg. Page Density (full).....................: 93.58%DBCC execution completed. If DBCC printed error messages, contact your system administrator. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-11 : 03:10:17
|
You can use the tool CONTIG.EXE to defrag the physical daatbase file.
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
IceDread
Yak Posting Veteran
66 Posts |
Posted - 2007-10-11 : 04:00:22
|
| Thanks, I'm trying it out on a testserver to see if it helps (running it right now, seams to take a while). I could not relly find any info about it's effect on tables LogiacalFragmentation thou. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-11 : 04:07:01
|
| ... from Sysinternals in case you are Googling!"how do I defrag a table?"De-frag, or Re-index, on the Clustered Index. Which is usually the Primary Key.If you don;t have a Clustered Index you are out of luck. Create one (but not using the "I'll just try this" method on a production system please!), or BCP out the data, drop and recreate the table, and reimport. You'll spot that having a Clustered Index is a good idea!"Table: 'TInsurance' (2051694557); index ID: 0, database ID: 17"index ID: 0 means that the table is a heap - no clustered index.If there are NO indexes on that table then performance will be poor - every query will result in a Table Scan - check every row, rather than using an index to get a short-list of matching records.sp_HelpIndex 'TInsurance'will list the indexes (or report "The object does not have any indexes."), and the [index_description] column will tell you which one, if any, are clusteredKristen |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-11 : 04:09:18
|
| "I could not relly find any info about it's effect on tables LogiacalFragmentation thou."CONTIG.EXE will only sort out the physical fragmentation ... but that is worth doing, performance-wise, but nothing in SQL Server will report on the benefit of that (except for the elapsed time of your queries of course!)Kristen |
|
|
|
IceDread
Yak Posting Veteran
66 Posts |
Posted - 2007-10-11 : 04:21:00
|
| Thanks for the info Kirsten!I have on this table 8 non clustered indexes, the pk is a unique but none clusterd as well. I'm not the architect for this system and are fairly new to it. However, your suggesting to try clusterd index on my primary key? But dropping the table into a new table as I do so. I'll see if I can find the time to try it out on one of my testservers.The logical fragmentation on my indexes are under 1.2. Did a defraging of those indexes with only lesser changes. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-11 : 04:39:16
|
| "your suggesting to try clusterd index on my primary key? But dropping the table into a new table as I do so"No, you don't need to make a new table etc. That's only if you EXPORT the data and RE-IMPORT it.You can change your PK to Clustered, but I can't tell from here whether that is a good choice [for a clustered index] or not.However, I cannot think of any circumstances where changing an existing NON-clustered Primary Key to CLUSTERED, where there is NO other CLUSTERED index, could make anything worse!Kristen |
|
|
|
IceDread
Yak Posting Veteran
66 Posts |
Posted - 2007-10-11 : 04:43:41
|
| So, how do I reduce the logicalFragmentation of 99,9215698242188 that the table have? Or should that value not be considerd since the index I have on the table are good?Thanks for your time btw. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-11 : 06:56:41
|
| My expectation is that once you add a Clustered Index that will go awayNote that it may take a LONG time to create a clustered index on an existing table if it has lots of rows.Kristen |
|
|
|
IceDread
Yak Posting Veteran
66 Posts |
Posted - 2007-10-11 : 08:41:56
|
| Ok, thanks for clearifying and thanks a lot :-)Will try it in not too long. |
|
|
|
|
SHOWCONTIG - Need a bit of help to identify if