Microsoft Dynamics NAV (Archived)

Rebuild indexes

(0) Share

Report

Posted on by Community Member

Hi there,

Is there a way to rebuild indexes per company in case there are multiple companies per one DB? Nav 2009 R2.

*This post is locked for comments

I have the same question (0)

All responses (10)

Answers (0)

Suggested answer

Roberto Stefanetti 12,998 on at

Like (0)

Report

hi,

Sql server or Native db ?

Native & Sql Server Db

From NAV 2009 R2 Classic Client

file\database\information\tables\"filter your company"\optimize > launch a full db index rebuild (DROP & CREATE) for all clustered & non clustered indexes) -> you can use an automation scripts to do this , search solution on Mibuso site.

on SQL Server; a simple Reindex script

use a scheduled sql server job

USE DatabaseName

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR

*** IF SQL 2012

SELECT table_name FROM information_schema.tables

WHERE table_type = 'base table' (ex: like 'Cronus%... you can filter tables using schema and using Company Names)

*** IF YOU HAVE OLD SQL RELEASE

SELECT * FROM sysobjects WHERE xtype='U'

SELECT * FROM sys.Tables

OR

SELECT sobjects.name

FROM sysobjects sobjects

WHERE sobjects.xtype = 'U'

*** charge cursor

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName

WHILE @@FETCH_STATUS = 0

*** reindex

BEGIN

DBCC DBREINDEX(@TableName,' ',90) --- is the same of DROP & CREATE indexes

FETCH NEXT FROM TableCursor INTO @TableName

END

CLOSE TableCursor

DEALLOCATE TableCursor

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Roberto,

Its SQL 2008R2. We have multiple companies in the same DB and the requirement is to rebuild indexes per a selected company.

In your query where we can specify the company to rebuild indexes?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

is this enough to filter per company?

SELECT sobjects.name

FROM sysobjects sobjects

WHERE sobjects.xtype = 'U'

and sobjects.name like 'CompanyName%'

Was this reply helpful? Yes No
Suggested answer

Roberto Stefanetti 12,998 on at

Like (0)

Report

hi,

sure.

Example:

USE Database

SELECT sobjects.name

FROM sysobjects sobjects

WHERE sobjects.xtype = 'U'

and sobjects.name like 'CRONUS Italia S_p_A%'

-> extraxt only "Cronus Italia Company tables"

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

what about defining the page free space %?

Was this reply helpful? Yes No
Suggested answer

Roberto Stefanetti 12,998 on at

Like (0)

Report

hi,

if you are talking about index fill factor:

The script will automatically reindex every index in every table of any database you select, and provide a fill factor of 90%. You can substitute any number appropriate for the fill factor in the above script.

So what is the ideal fill factor? It depends on the ratio of reads to writes that your application makes to your SQL Server tables. As a rule of thumb, follow these guidelines:

• Low Update Tables (100-1 read to write ratio): 100% fill factor

• High Update Tables (where writes exceed reads): 50%-70% fill factor

• Everything In-Between: 80%-90% fill factor.

look at www.sql-server-performance.com/.../rebuilding-indexes

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

DBCC DBREINDEX(@TableName,' ',90) , if not mentioning the index name, it will rebuild all indexes of a table? in this case ' ' are use after the table name?

Was this reply helpful? Yes No
Suggested answer

Roberto Stefanetti 12,998 on at

Like (0)

Report

yes, all indexes.

look at this link:

"technet.microsoft.com/.../ms181671.aspx"

A. Rebuilding an index

The following example rebuilds the Employee_EmployeeID clustered index with a fill factor of 80 on the Employee table in the AdventureWorks database.

USE AdventureWorks2012;

GO

DBCC DBREINDEX ("HumanResources.Employee", PK_Employee_BusinessEntityID,80);

GO

B. Rebuilding all indexes

The following example rebuilds all indexes on the Employee table in AdventureWorks by using a fill factor value of 70.

USE AdventureWorks2012;

GO

DBCC DBREINDEX ("HumanResources.Employee", " ", 70);

GO

...........

List of all index & index columns in SQL Server DB

stackoverflow.com/.../list-of-all-index-index-columns-in-sql-server-db

Get Information of Index of Tables and Indexed Columns

blog.sqlauthority.com/.../sql-server-get-information-of-index-of-tables-and-indexed-columns

USE AdventureWorks;

GO

EXEC sp_helpindex 'Person.Address'

GO

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Roberto,

How can we have this query as a stored procedure so that we can use in a scheduled job per each company in the DB?

Was this reply helpful? Yes No
Suggested answer

rudra 6,534 on at

Like (0)

Report

Hi ,

Use a SQL Management plan with T-Sql statement for ur company wise rebuild indexes, this will be running in a scheduled plan and rebuild indexed will be run at the NAV application downtime bcoz the tables will be locked when the transactions in progress.

Was this reply helpful? Yes No