Question Status

Suggested Answer
jagesh maharjan asked a question on 28 Feb 2013 10:25 PM

My database is getting too slow, and i want to optimize it using SHIFT Optimization. Please someone post how to perform SHIFT Optimization.

Reply
Suggested Answer
Neville Foyn responded on 1 Mar 2013 10:28 PM

Hi Jagesh

Not sure what you mean by SHIFT optimization, could you perhaps mean SIFT optimization?

SIFT optimization was a lot more relevant with the non SQL version of the database, you will get a lot more performance from other areas such as SQL optimizations and looking at possibly more SQL memory, faster HDD's setups in the server

If you want to run the optimizations however it may slightly improve performance however i suspect this change may be so small as to be unnoticeable

Open the classic NAV client, with the database opened click on "File" -> "Database" -> "Information"

On the screen that opens click the tables button at the bottom

Select a range of tables and hit the optimize button,

Be aware that this may take a long time to run on big tables so you can do ranges of small tables but only a single table whee there are a lot of records

Hope this helps

Cheers

Nev

Neville Foyn
NAV Developer, Consultant & Trainer
MCTS NAV, CRM, Sharepoint, SQL
MCSA SQL 2012
MCITP NAV
MCT 

Reply
brian.db responded on 16 Oct 2013 2:50 AM

Hi Nev

Any possible way of scheduling the optimization process? Either NAV application services or SQL Agent?

Best regards

Brian

Reply
Suggested Answer
Roberto Stefanetti responded on 16 Oct 2013 11:31 AM

Hi,

from NAV 5.0 SP1 SIFT Tables are indexed views , from NAV 2009 optimized with the introduction of the RoleTailored architecture.

An indexed view is similar to a SQL Server view except that the contents have been materialized (computed and stored) to speed up the retrieval of data.

Because is an "SQL Server Object", you can optimize Indexed Views using sql agent and a scheduled maintenance plan (scheduiling reorganixe tables, defrag or rebuild indexes and Indexed views)

The NAV Optimization told (look at Naville Post) is similar to SQL Rebuild (drop & recreate indexes, you can select some table, indexes views etc.) but is obsolete from NAV 2009 & NAV 2013,

my suggest is: on NAV 5.0 Sp1 or greaters use SQL Server optimization insteaf of NAV Optimization.

Some NAV 2009 links:

SIFT and Microsoft Dynamics NAV with Microsoft SQL Server

msdn.microsoft.com/.../dd355336.aspx

Tuning and Tracing

using SQL Profiler to tracing Sift performance

msdn.microsoft.com/.../dd301142.aspx

SIFT and Performance

msdn.microsoft.com/.../dd338941.aspx

------------------------------------------------------------------------------

Roberto Stefanetti,  Dynamics Community Moderator-NAV

My Blog http://blogs.dotnethell.it/DynamicsNAV 

Twitter @robstefane

All my posts are provided "AS IS" with no warranties and confer no rights

Reply
Suggested Answer
Neville Foyn responded on 1 Mar 2013 10:28 PM

Hi Jagesh

Not sure what you mean by SHIFT optimization, could you perhaps mean SIFT optimization?

SIFT optimization was a lot more relevant with the non SQL version of the database, you will get a lot more performance from other areas such as SQL optimizations and looking at possibly more SQL memory, faster HDD's setups in the server

If you want to run the optimizations however it may slightly improve performance however i suspect this change may be so small as to be unnoticeable

Open the classic NAV client, with the database opened click on "File" -> "Database" -> "Information"

On the screen that opens click the tables button at the bottom

Select a range of tables and hit the optimize button,

Be aware that this may take a long time to run on big tables so you can do ranges of small tables but only a single table whee there are a lot of records

Hope this helps

Cheers

Nev

Neville Foyn
NAV Developer, Consultant & Trainer
MCTS NAV, CRM, Sharepoint, SQL
MCSA SQL 2012
MCITP NAV
MCT 

Reply
Suggested Answer
Roberto Stefanetti responded on 16 Oct 2013 11:31 AM

Hi,

from NAV 5.0 SP1 SIFT Tables are indexed views , from NAV 2009 optimized with the introduction of the RoleTailored architecture.

An indexed view is similar to a SQL Server view except that the contents have been materialized (computed and stored) to speed up the retrieval of data.

Because is an "SQL Server Object", you can optimize Indexed Views using sql agent and a scheduled maintenance plan (scheduiling reorganixe tables, defrag or rebuild indexes and Indexed views)

The NAV Optimization told (look at Naville Post) is similar to SQL Rebuild (drop & recreate indexes, you can select some table, indexes views etc.) but is obsolete from NAV 2009 & NAV 2013,

my suggest is: on NAV 5.0 Sp1 or greaters use SQL Server optimization insteaf of NAV Optimization.

Some NAV 2009 links:

SIFT and Microsoft Dynamics NAV with Microsoft SQL Server

msdn.microsoft.com/.../dd355336.aspx

Tuning and Tracing

using SQL Profiler to tracing Sift performance

msdn.microsoft.com/.../dd301142.aspx

SIFT and Performance

msdn.microsoft.com/.../dd338941.aspx

------------------------------------------------------------------------------

Roberto Stefanetti,  Dynamics Community Moderator-NAV

My Blog http://blogs.dotnethell.it/DynamicsNAV 

Twitter @robstefane

All my posts are provided "AS IS" with no warranties and confer no rights

Reply