Minimizing Fragmentation of table having Non-clustered Index.

Hi All,
i am working on SQL Server 2000 EE. I want to know that, is there any
way to rebuild Non clustered index. How can we minimize fragmentation
of table having non clustered index. can we defrag non clustred index.
Thanks & Regards,
Sajid C.Sure, you can defrag the indexes themselves, but (someone correct me if
I'm wrong here), you can't defrag the TABLE data with a single statement
(like DBCC DBREINDEX OR DBCC INDEXDEFRAG).
One way to do it would be to create and then drop a clustered index on
the table. That would effectively defragment the table's underlying
data. BUT... If you're doing a lot of scanning on the table (to the
extent that it really, really matters how the data is physically
ordered) you might want to consider putting a clustered index on the
table anyway...
-Dave
csajid@.gmail.com wrote:
> Hi All,
> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index. How can we minimize fragmentation
> of table having non clustered index. can we defrag non clustred index.
>
> Thanks & Regards,
> Sajid C.
>|||> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index.
You can defrag index leaf nodes using DBCC INDEXDEFRAG or rebuild the entire
index using DBCC DBREINDEX. These apply to both clustered and non-clustered
indexes. See the Books Online for usage information.

> How can we minimize fragmentation
> of table having non clustered index.
You can avoid page splits between between index reorgs by specifying a lower
FILLFACTOR. The downside is that it reduces the page density, which can
negatively affect scan performance and buffer efficiency. In my opinion,
it's usually best to use the default FILLFACTOR so that splits allocate free
space when and where needed.
Hope this helps.
Dan Guzman
SQL Server MVP
<csajid@.gmail.com> wrote in message
news:1173540813.502373.320030@.j27g2000cwj.googlegroups.com...
> Hi All,
> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index. How can we minimize fragmentation
> of table having non clustered index. can we defrag non clustred index.
>
> Thanks & Regards,
> Sajid C.
>|||On Mar 10, 8:59 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> You can defrag index leaf nodes using DBCC INDEXDEFRAG or rebuild the enti
re
> index using DBCC DBREINDEX. These apply to both clustered and non-cluster
ed
> indexes. See the Books Online for usage information.
>
> You can avoid page splits between between index reorgs by specifying a low
er
> FILLFACTOR. The downside is that it reduces the page density, which can
> negatively affect scan performance and buffer efficiency. In my opinion,
> it's usually best to use the default FILLFACTOR so that splits allocate fr
ee
> space when and where needed.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <csa...@.gmail.com> wrote in message
> news:1173540813.502373.320030@.j27g2000cwj.googlegroups.com...
>
>
>
>
> - Show quoted text -
Dear All,
Thanks for your reply.
Thanks & Regards,
Sajid C.|||(With all due respect) you're wrong - you can use DBCC INDEXDEFRAG or DBCC
DBREINDEX on the clustered index, which will remove fragmentation from the
table data. As far as INDEXDEFRAG (which I wrote) is concerned, there's no
difference between a clustered or non-clustered index. DBREINDEX only
differs in the locking it takes for the two types of index.
Thanks
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
"David Markle" <newsdm@.markleconsulting.c0m> wrote in message
news:%23F4gZ1yYHHA.2320@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
> Sure, you can defrag the indexes themselves, but (someone correct me if
> I'm wrong here), you can't defrag the TABLE data with a single statement
> (like DBCC DBREINDEX OR DBCC INDEXDEFRAG).
> One way to do it would be to create and then drop a clustered index on the
> table. That would effectively defragment the table's underlying data.
> BUT... If you're doing a lot of scanning on the table (to the extent that
> it really, really matters how the data is physically ordered) you might
> want to consider putting a clustered index on the table anyway...
> -Dave
> csajid@.gmail.com wrote:

Minimizing Fragmentation of table having Non-clustered Index.

Hi All,
i am working on SQL Server 2000 EE. I want to know that, is there any
way to rebuild Non clustered index. How can we minimize fragmentation
of table having non clustered index. can we defrag non clustred index.
Thanks & Regards,
Sajid C.
Sure, you can defrag the indexes themselves, but (someone correct me if
I'm wrong here), you can't defrag the TABLE data with a single statement
(like DBCC DBREINDEX OR DBCC INDEXDEFRAG).
One way to do it would be to create and then drop a clustered index on
the table. That would effectively defragment the table's underlying
data. BUT... If you're doing a lot of scanning on the table (to the
extent that it really, really matters how the data is physically
ordered) you might want to consider putting a clustered index on the
table anyway...
-Dave
csajid@.gmail.com wrote:
> Hi All,
> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index. How can we minimize fragmentation
> of table having non clustered index. can we defrag non clustred index.
>
> Thanks & Regards,
> Sajid C.
>
|||> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index.
You can defrag index leaf nodes using DBCC INDEXDEFRAG or rebuild the entire
index using DBCC DBREINDEX. These apply to both clustered and non-clustered
indexes. See the Books Online for usage information.

> How can we minimize fragmentation
> of table having non clustered index.
You can avoid page splits between between index reorgs by specifying a lower
FILLFACTOR. The downside is that it reduces the page density, which can
negatively affect scan performance and buffer efficiency. In my opinion,
it's usually best to use the default FILLFACTOR so that splits allocate free
space when and where needed.
Hope this helps.
Dan Guzman
SQL Server MVP
<csajid@.gmail.com> wrote in message
news:1173540813.502373.320030@.j27g2000cwj.googlegr oups.com...
> Hi All,
> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index. How can we minimize fragmentation
> of table having non clustered index. can we defrag non clustred index.
>
> Thanks & Regards,
> Sajid C.
>
|||On Mar 10, 8:59 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> You can defrag index leaf nodes using DBCC INDEXDEFRAG or rebuild the entire
> index using DBCC DBREINDEX. These apply to both clustered and non-clustered
> indexes. See the Books Online for usage information.
>
> You can avoid page splits between between index reorgs by specifying a lower
> FILLFACTOR. The downside is that it reduces the page density, which can
> negatively affect scan performance and buffer efficiency. In my opinion,
> it's usually best to use the default FILLFACTOR so that splits allocate free
> space when and where needed.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <csa...@.gmail.com> wrote in message
> news:1173540813.502373.320030@.j27g2000cwj.googlegr oups.com...
>
>
>
> - Show quoted text -
Dear All,
Thanks for your reply.
Thanks & Regards,
Sajid C.
|||(With all due respect) you're wrong - you can use DBCC INDEXDEFRAG or DBCC
DBREINDEX on the clustered index, which will remove fragmentation from the
table data. As far as INDEXDEFRAG (which I wrote) is concerned, there's no
difference between a clustered or non-clustered index. DBREINDEX only
differs in the locking it takes for the two types of index.
Thanks
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"David Markle" <newsdm@.markleconsulting.c0m> wrote in message
news:%23F4gZ1yYHHA.2320@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
> Sure, you can defrag the indexes themselves, but (someone correct me if
> I'm wrong here), you can't defrag the TABLE data with a single statement
> (like DBCC DBREINDEX OR DBCC INDEXDEFRAG).
> One way to do it would be to create and then drop a clustered index on the
> table. That would effectively defragment the table's underlying data.
> BUT... If you're doing a lot of scanning on the table (to the extent that
> it really, really matters how the data is physically ordered) you might
> want to consider putting a clustered index on the table anyway...
> -Dave
> csajid@.gmail.com wrote:

Minimizing Fragmentation of table having Non-clustered Index.

Hi All,
i am working on SQL Server 2000 EE. I want to know that, is there any
way to rebuild Non clustered index. How can we minimize fragmentation
of table having non clustered index. can we defrag non clustred index.
Thanks & Regards,
Sajid C.Sure, you can defrag the indexes themselves, but (someone correct me if
I'm wrong here), you can't defrag the TABLE data with a single statement
(like DBCC DBREINDEX OR DBCC INDEXDEFRAG).
One way to do it would be to create and then drop a clustered index on
the table. That would effectively defragment the table's underlying
data. BUT... If you're doing a lot of scanning on the table (to the
extent that it really, really matters how the data is physically
ordered) you might want to consider putting a clustered index on the
table anyway...
-Dave
csajid@.gmail.com wrote:
> Hi All,
> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index. How can we minimize fragmentation
> of table having non clustered index. can we defrag non clustred index.
>
> Thanks & Regards,
> Sajid C.
>|||> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index.
You can defrag index leaf nodes using DBCC INDEXDEFRAG or rebuild the entire
index using DBCC DBREINDEX. These apply to both clustered and non-clustered
indexes. See the Books Online for usage information.
> How can we minimize fragmentation
> of table having non clustered index.
You can avoid page splits between between index reorgs by specifying a lower
FILLFACTOR. The downside is that it reduces the page density, which can
negatively affect scan performance and buffer efficiency. In my opinion,
it's usually best to use the default FILLFACTOR so that splits allocate free
space when and where needed.
Hope this helps.
Dan Guzman
SQL Server MVP
<csajid@.gmail.com> wrote in message
news:1173540813.502373.320030@.j27g2000cwj.googlegroups.com...
> Hi All,
> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index. How can we minimize fragmentation
> of table having non clustered index. can we defrag non clustred index.
>
> Thanks & Regards,
> Sajid C.
>|||On Mar 10, 8:59 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> > i am working on SQL Server 2000 EE. I want to know that, is there any
> > way to rebuild Non clustered index.
> You can defrag index leaf nodes using DBCC INDEXDEFRAG or rebuild the entire
> index using DBCC DBREINDEX. These apply to both clustered and non-clustered
> indexes. See the Books Online for usage information.
> > How can we minimize fragmentation
> > of table having non clustered index.
> You can avoid page splits between between index reorgs by specifying a lower
> FILLFACTOR. The downside is that it reduces the page density, which can
> negatively affect scan performance and buffer efficiency. In my opinion,
> it's usually best to use the default FILLFACTOR so that splits allocate free
> space when and where needed.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <csa...@.gmail.com> wrote in message
> news:1173540813.502373.320030@.j27g2000cwj.googlegroups.com...
>
> > Hi All,
> > i am working on SQL Server 2000 EE. I want to know that, is there any
> > way to rebuild Non clustered index. How can we minimize fragmentation
> > of table having non clustered index. can we defrag non clustred index.
> > Thanks & Regards,
> > Sajid C.- Hide quoted text -
> - Show quoted text -
Dear All,
Thanks for your reply.
Thanks & Regards,
Sajid C.|||(With all due respect) you're wrong - you can use DBCC INDEXDEFRAG or DBCC
DBREINDEX on the clustered index, which will remove fragmentation from the
table data. As far as INDEXDEFRAG (which I wrote) is concerned, there's no
difference between a clustered or non-clustered index. DBREINDEX only
differs in the locking it takes for the two types of index.
Thanks
--
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"David Markle" <newsdm@.markleconsulting.c0m> wrote in message
news:%23F4gZ1yYHHA.2320@.TK2MSFTNGP03.phx.gbl...
> Sure, you can defrag the indexes themselves, but (someone correct me if
> I'm wrong here), you can't defrag the TABLE data with a single statement
> (like DBCC DBREINDEX OR DBCC INDEXDEFRAG).
> One way to do it would be to create and then drop a clustered index on the
> table. That would effectively defragment the table's underlying data.
> BUT... If you're doing a lot of scanning on the table (to the extent that
> it really, really matters how the data is physically ordered) you might
> want to consider putting a clustered index on the table anyway...
> -Dave
> csajid@.gmail.com wrote:
>> Hi All,
>> i am working on SQL Server 2000 EE. I want to know that, is there any
>> way to rebuild Non clustered index. How can we minimize fragmentation
>> of table having non clustered index. can we defrag non clustred index.
>>
>> Thanks & Regards,
>> Sajid C.

Migration ?

Hello,
I currently have a SQL 2000 SP3 STD running in my org, I want to move it's
contents to a clustered (two node) system running SQL 2005 STD edition. Is
it possible to move the entire contents of my old SQL environment to the new
environment? I would like to preserve user logins and the current instance
name of my old server. I know it might require me renaming the old SQL
server name when I get to that point. If someone could give me some tips on
how (or if it's even possible) to do this I would be highly greatful.
Thank you
Alex Anderson
You have to move the databases to the new server individually. Most of
these were written for 2000 but still apply to 2005.
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://msdn2.microsoft.com/en-us/library/ms345408(en-US,SQL.90).aspx Moving
system dbs 2005
http://www.databasejournal.com/featu...le.php/3379901 Moving
system DB's 2000
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
http://www.sqlservercentral.com/colu...rdatabases.asp
Moving Users
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after
a Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=320125 Moving a Diagram
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues 2000
http://www.sqlservercentral.com/scri...tions/1598.asp Script
Roles and Permissions
Andrew J. Kelly SQL MVP
"Alex Anderson" <AlexAnderson@.discussions.microsoft.com> wrote in message
news:2DF0B36D-5B38-4BE1-A448-91CE4F33F5B2@.microsoft.com...
> Hello,
> I currently have a SQL 2000 SP3 STD running in my org, I want to move it's
> contents to a clustered (two node) system running SQL 2005 STD edition.
> Is
> it possible to move the entire contents of my old SQL environment to the
> new
> environment? I would like to preserve user logins and the current
> instance
> name of my old server. I know it might require me renaming the old SQL
> server name when I get to that point. If someone could give me some tips
> on
> how (or if it's even possible) to do this I would be highly greatful.
> Thank you
> Alex Anderson
>
|||Actually, it can be done a bit more simply than that. You need to do the
following:
1. Configure the cluster
2. Install a SQL Server failover cluster instance
3. Backup the master database on your standalone and restore it to the
failover cluster instance
4. Backup msdb and restore it to the failover cluster instance
5. Move your databases using either a detach/attach or backup/restore
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:et0zGJSVGHA.6084@.TK2MSFTNGP14.phx.gbl...
> You have to move the databases to the new server individually. Most of
> these were written for 2000 but still apply to 2005.
> http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
> http://msdn2.microsoft.com/en-us/library/ms345408(en-US,SQL.90).aspx
> Moving system dbs 2005
> http://www.databasejournal.com/featu...le.php/3379901
> Moving system DB's 2000
> http://www.support.microsoft.com/?id=314546 Moving DB's between
> Servers
> http://www.support.microsoft.com/?id=224071 Moving SQL Server
> Databases to a New Location with Detach/Attach
> http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a
> Restore
> http://www.sqlservercentral.com/colu...rdatabases.asp
> Moving Users
> http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
> Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs
> after a Restore
> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
> users
> http://www.support.microsoft.com/?id=168001 User Logon and/or
> Permission Errors After Restoring Dump
> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
> Issues When a Database Is Moved Between SQL Servers
> http://www.sqlservercentral.com/scri...p?scriptid=599
> Restoring a .mdf
> http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
> for SQL Server
> http://www.support.microsoft.com/?id=320125 Moving a Diagram
> http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues 2000
> http://www.sqlservercentral.com/scri...tions/1598.asp Script
> Roles and Permissions
> --
> Andrew J. Kelly SQL MVP
>
> "Alex Anderson" <AlexAnderson@.discussions.microsoft.com> wrote in message
> news:2DF0B36D-5B38-4BE1-A448-91CE4F33F5B2@.microsoft.com...
>
|||Michael,
Moving the Master and MSDB database, what if the old server where those
databases are coming from are different builds? Will a restore on the SQL
2005 cluster allow this?
Thank you
Alex Anderson
"Michael Hotek" wrote:

> Actually, it can be done a bit more simply than that. You need to do the
> following:
> 1. Configure the cluster
> 2. Install a SQL Server failover cluster instance
> 3. Backup the master database on your standalone and restore it to the
> failover cluster instance
> 4. Backup msdb and restore it to the failover cluster instance
> 5. Move your databases using either a detach/attach or backup/restore
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:et0zGJSVGHA.6084@.TK2MSFTNGP14.phx.gbl...
>
>
|||During a restore, all of the metadata is upgraded. So, yes, it does work.
You will obviously want to test it before doing it in production.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Alex Anderson" <AlexAnderson@.discussions.microsoft.com> wrote in message
news:0A3A75CF-166A-4BD7-88C9-1B55B519B9B7@.microsoft.com...[vbcol=seagreen]
> Michael,
> Moving the Master and MSDB database, what if the old server where those
> databases are coming from are different builds? Will a restore on the SQL
> 2005 cluster allow this?
> Thank you
> Alex Anderson
>
> "Michael Hotek" wrote:
|||Michael,
I've tried every way I can think of trying to restore the master database.
I fail every time because it's complaining about the build numbers being
different. Is there something I can need to check to override this?
Thank you
Alex Anderson
"Michael Hotek" wrote:

> During a restore, all of the metadata is upgraded. So, yes, it does work.
> You will obviously want to test it before doing it in production.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "Alex Anderson" <AlexAnderson@.discussions.microsoft.com> wrote in message
> news:0A3A75CF-166A-4BD7-88C9-1B55B519B9B7@.microsoft.com...
>
>