Move non-clustered indexes

When You create a NAV-database You end up with tables, clustered indexes and non-clustered indexes in the same filegroup. That's not optimal from a performance perspective.

You can gain performance by separating the actual data (tables/clustered indexes) from Your non-clustered indexes by moving these to a separate filegroup and a separate array of disks. If You do so SQL will use separate threads for reading the non-clustered index and the table/clustered index. 

The script below will do the following with the database "nav_db":

  • Create a filegroup called "nonclustindexes"
  • Create a database file and attach it to the filegroup
  • Move all non-clustered indexes in filegroup with id=2 (default filegroup for data in a NAV database) to the new filegroup.

You can of course use management studio to create the filegroup and the file and skip those parts of the script. You should also change names for database, file, file sizes etc. to match You needs.

Remember that when You change a key in NAV the corresponding index is rebuilt in the default filegroup. So You need to move it to Your nonclustindexes filegroup again. If You don't want to bother about that You can schedule the move-part of the script below to run periodically.

And here goes the TSQL script (which You use at Your own risk, so do Your testing and have a backup just in case):

use nav_db

go

-- Create a new file group
alter database nav_db
   add filegroup nonclustindexes

go

-- Add a file to the file group, we can now use the file group to store data
alter database nav_db
   add file (
      name = nav_db_NonClustIndexes,
      filename = 'E:\MSSQL\DEV\NonClustIndexes.ndf',
      size = 500MB,
      maxsize = 10000MB,
      filegrowth = 100mb
      )
   to filegroup nonclustindexes

go

-- Move the nc indexes
declare  @id integer
declare  @tbname nvarchar(100)
declare  @indid integer
declare  @indname nvarchar(100)
declare  @fill integer
declare  @group integer
declare  @list nvarchar(4000)
declare  @strsql nvarchar(4000)
declare  @ROWCNT integer
declare  @DEBUG integer
declare  @SourceFileGroupID integer
declare  @DestFilegroupName nvarchar(100)

set @DEBUG = 0
set @SourceFileGroupID = 2
set @DestFileGroupName = 'nonclustindexes'

declare curs1 cursor  for
select   top 100 percent dbo.sysobjects.id,
                         dbo.sysobjects.name,
                         dbo.sysindexes.indid,
                         dbo.sysindexes.name as indname,
                         dbo.sysindexes.origfillfactor,
                         dbo.sysindexes.groupid
from     dbo.sysindexes
         inner join dbo.sysobjects
           on dbo.sysindexes.id = dbo.sysobjects.id
where    (dbo.sysobjects.xtype = 'U')
         and (dbo.sysindexes.indid between 2 and 254)
         and (sysindexes.status & 64) = 0
         and (not (sysindexes.status & 0x800) = 0x800)
         and sysindexes.groupid = @SourceFileGroupID
order by dbo.sysobjects.name,
         dbo.sysindexes.indid

open curs1

fetch next from curs1
into @id,
     @tbname,
     @indid,
     @indname,
     @fill,
     @group

while @@fetch_status = 0
  begin
    set @list = ''
    
    select   @List = @List + '[' + dbo.syscolumns.name + '],'
    from     dbo.sysindexes
             inner join dbo.sysobjects
               on dbo.sysindexes.id = dbo.sysobjects.id
             inner join dbo.sysindexkeys
               on dbo.sysindexes.id = dbo.sysindexkeys.id
                  and dbo.sysindexes.indid = dbo.sysindexkeys.indid
             inner join dbo.syscolumns
               on dbo.sysindexkeys.id = dbo.syscolumns.id
                  and dbo.sysindexkeys.colid = dbo.syscolumns.colid
    where    (dbo.sysobjects.xtype = 'U')
             and (dbo.sysindexes.indid = @indid)
             and (dbo.sysobjects.id = @id)
             and (sysindexes.status & 64) = 0
             and (not (sysindexes.status & 0x800) = 0x800)
             and sysindexes.groupid = @SourceFileGroupID
    order by dbo.sysobjects.name,
             dbo.sysindexes.indid,
             dbo.sysindexkeys.keyno
    
    set @list = Left(@list,Len(@list) - 1)

    set @strsql = 'drop index [' + @tbname + '].[' + @indname + ']'
    print @strsql
    
    if @DEBUG = 0
      begin
        exec Sp_executesql
          @strsql
      end
    
    set @strsql = 'create index [' + @indname + '] on [dbo].[' + @tbname + '](' + @list + ') WITH  FILLFACTOR = 90 ON [' + @DestFileGroupName + ']'
    print @strsql

    if @DEBUG = 0
      begin
        exec Sp_executesql
          @strsql
      end
    
    fetch next from curs1
    into @id,
         @tbname,
         @indid,
         @indname,
         @fill,
         @group
  end

close curs1
deallocate curs1

Related
Recommended