Move Your 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 curs1color: maroon;">curs1
No comments yet.

Kommentera