 |
|
| Computers Forum Index » Computer - Databases - MS SQL Server » defrag of 101G table, What is the best way?... |
|
Page 1 of 1 |
|
| Author |
Message |
| seanD... |
 Posted: Fri Oct 02, 2009 10:24 pm |
|
|
|
Guest
|
Hi All,
Please bear with me as I'm inexperienced in DBA activities
What is the best way to defragment a very large table if both minimal
user impact and speed are concerns? I have a small maintenance
window And a 24X7 application on top of this database. It seems that
doing anything online would not make sense, but doing a rebuild
offline will have large impact for the
Application. Is indexing a temptable and copying into it and renaming
the best way to go?
Any help you can give would really be appreciated.
Some details on the table:
row_count = 113,315,915
table size = 101G with data=76G, index=25G and 200Mb free
Table has no LOB Columns
Table has 3 indexes
index 1 (Clustered) is on varchar(15)/varchar(36) - 88% fragmentation
index 2 nonunique on uniqueidentifier is 4%
index 3 is primary key index on int col is also 88% fragmented
Links](
[RowID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT
NULL,
[MessageInstanceID] [uniqueidentifier] NOT NULL,
[MessageLinkID] [nvarchar](15) NOT NULL,
[LinkID] [nvarchar](36) NOT NULL,
[Link] [nvarchar](1000) NOT NULL CONSTRAINT
[DF_MG_Links_Link] DEFAULT (''),
[InsertDate] [datetime] NULL CONSTRAINT
[DF_Message_Links_InsertDate] DEFAULT (getdate()),
CONSTRAINT [PK_Message_Links] PRIMARY KEY NONCLUSTERED
(
[RowID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99)
ON [PRIMARY]
) ON [PRIMARY] |
|
|
| Back to top |
|
|
|
| Erland Sommarskog... |
| Posted: Sat Oct 03, 2009 10:07 am |
|
|
|
Guest
|
seanD (sean.denney at (no spam) gmail.com) writes:
Quote: What is the best way to defragment a very large table if both minimal
user impact and speed are concerns? I have a small maintenance
window And a 24X7 application on top of this database. It seems that
doing anything online would not make sense, but doing a rebuild
offline will have large impact for the
Application. Is indexing a temptable and copying into it and renaming
the best way to go?
Any help you can give would really be appreciated.
What version and edition of SQL Server do you have?
If you have SQL 2005/2008 Enterprise, you can use the WITH ONLINE option
with ALTER INDEX REBUILD to make an online rebuild. The rebuild will still
not be 100% online, there will be small windows when the table is locked.
But most of the time table will be accessible.
The alternative is to use ALTER INDEX REORGANIZE which is always online.
Sometimes this is better, but it can also take a larger toll on the
application.
The alternative you suggest of copying data into a new table sounds like
the poor man's online defragmentation, which you would need to use if you
are on SQL 2000, or have Standard Edition. (But if you have a 24/7
requirement, I would suggest that this is a strong call to use Enterprise.)
Quote: index 1 (Clustered) is on varchar(15)/varchar(36) - 88% fragmentation
index 2 nonunique on uniqueidentifier is 4%
index 3 is primary key index on int col is also 88% fragmented
It's a little intriguing that an index on a IDENTITY column would be
that badly fragmented. On the other hand, I would expect a guid index
to have realy poor numbers. From where did you get these numbers? You
did not by chance confuse then with scan density?
--
Erland Sommarskog, SQL Server MVP, esquel at (no spam) 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 |
|
|
| Back to top |
|
|
|
|
|
All times are GMT
The time now is Tue Dec 15, 2009 6:50 am
|
|