September 17, 2009 at 11:20 am
Help! I have an index that is heavely fragmented. I've rebuilt all the indexes through a maintenance plan. It didn't improve the index I'm having a problem with. The software vendor says I need to bebuild the indexes for the table, but I think the index is a clustered index on the table. Here's the results from doing a dbcc showcontig.
DBCC SHOWCONTIG scanning 'keygroupdata104' table...
Table: 'keygroupdata104' (2091154495); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 799
- Extents Scanned..............................: 217
- Extent Switches..............................: 216
- Avg. Pages per Extent........................: 3.7
- Scan Density [Best Count:Actual Count].......: 46.08% [100:217]
- Extent Scan Fragmentation ...................: 99.54%
- Avg. Bytes Free per Page.....................: 671.3
- Avg. Page Density (full).....................: 91.71%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Your help will be appreciated.
Thanks,
Dave:-)
September 17, 2009 at 11:39 am
What is a problem?
You can do it with GUI or with a script.
With GUI:
In Object Explorer, connect to an instance of the SQL Server 2005 Database Engine and then expand that instance.
Expand Databases, expand the database that contains the table with the specified index, and then expand Tables.
Expand the table in which the index belongs and then expand Indexes.
Right-click the index to rebuild and then click Rebuild.
To start the rebuild operation, click OK.
With a script:
USE yourDB_name;
GO
ALTER INDEX yourINDEX_name ON yourTABLE_name
REBUILD;
GO
Just don't do it on a production database during the office hours - the table will be not accessible for users.
September 17, 2009 at 11:39 am
drodriguez-762178 (9/17/2009)
Help! I have an index that is heavely fragmented. I've rebuilt all the indexes through a maintenance plan. It didn't improve the index I'm having a problem with. The software vendor says I need to bebuild the indexes for the table, but I think the index is a clustered index on the table. Here's the results from doing a dbcc showcontig.DBCC SHOWCONTIG scanning 'keygroupdata104' table...
Table: 'keygroupdata104' (2091154495); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 799
- Extents Scanned..............................: 217
- Extent Switches..............................: 216
- Avg. Pages per Extent........................: 3.7
- Scan Density [Best Count:Actual Count].......: 46.08% [100:217]
- Extent Scan Fragmentation ...................: 99.54%
- Avg. Bytes Free per Page.....................: 671.3
- Avg. Page Density (full).....................: 91.71%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Your help will be appreciated.
Thanks,
Dave:-)
It would be helpful to see the Index definition and the Table definition for this scenario. Please provide that information so we can get a better picture of the issue and provide better answers.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 17, 2009 at 11:45 am
I hope these are the correct definitions.
USE [itiserver]
GO
/****** Object: Table [hsi].[keygroupdata104] Script Date: 09/17/2009 10:44:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [hsi].[keygroupdata104](
[itemnum] [int] NULL,
[kg115] [datetime] NULL,
[kg133] [char](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Index [Akeygroupdata104_115] Script Date: 09/17/2009 10:45:14 ******/
CREATE NONCLUSTERED INDEX [Akeygroupdata104_115] ON [hsi].[keygroupdata104]
(
[kg115] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Index [Akeygroupdata104_133] Script Date: 09/17/2009 10:45:35 ******/
CREATE NONCLUSTERED INDEX [Akeygroupdata104_133] ON [hsi].[keygroupdata104]
(
[kg133] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Index [keygroupdata104_2] Script Date: 09/17/2009 10:45:48 ******/
CREATE UNIQUE NONCLUSTERED INDEX [keygroupdata104_2] ON [hsi].[keygroupdata104]
(
[itemnum] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
September 17, 2009 at 1:27 pm
To help the situation, you should consider a clustered index for the table. To defrag the other indexes, you will need a clustered index. The clustered index can be either permanent or temporary (used just long enough to defrag). You would want to consider on which field to create the clustered index (the itemnum field might be a good candidate) - it will depend on the data and usage.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 17, 2009 at 1:27 pm
You are never going to be able to rebuild that index - because it is not an index. It is a HEAP (Index ID: 0).
If you need to reorganize this table, then you need to add a CLUSTERED index (Index ID: 1) with the appropriate fill factor for the application. Or, you can just create a clustered index and then drop it.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
September 17, 2009 at 2:50 pm
OK, I think I got it. There reason for the fragmentation is because there is now primary key.
September 17, 2009 at 2:58 pm
drodriguez-762178 (9/17/2009)
OK, I think I got it. There reason for the fragmentation is because there is now primary key.
No. The reason for fragmentation is due to the lack of a clustered index. Primary Keys do not have to include a clustered index, but by default do when created through the GUI. Though this is the default behavior for PK creation, it is recommended to evaluate your clustered index column to make sure it is placed on the best column for that table.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 17, 2009 at 3:00 pm
BTW, thanks for being helpful to us. You providing the table and index information really helps make this process go a lot smoother.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 17, 2009 at 3:09 pm
Great, thanks for your help.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply