SQL Optimization of Navision SQL DB

RobertMo

Customer runs SQL 3.60, the DB size is about 12GB (with >1GB growth/month).

They have scheduled SQL Optimization every Saturday. This procedure does a lot of reorganizations in DB and makes log file also as big as 12GB. Then the backup of log is made to truncate and this procedure need additional 12GB of space. Totally 24GB (twice teh size of DB) of free space is needed for SQL's manipulations. At the end this is of course freed up.

Don't tell me, but I have already suggested buying a bigger disks, but they want to check also other options. Condidering the DB growth rate, the total disk space requirement then grows 3GB/month, so new disk is not long term solution.

1) Is it possible to do SQL optimization somehow ohter way that you don't need twice the size of DB of free space.

2) Is it really necessary to run SQL optimization ? Does this really speeds up Navision ? How often shoud be run ?

3) Is it better to use optimization within Navison (DB Info / Tables / Optimize) ?

awarn

I can tell you what the optimization through Navision does.

When you create a key within Navision, and associate a SumIndex field with it, SQL creates a new table in the format:

CompanyName$(table number)$(key)

so the customer table with one of said keys creates a table named (from SQL Server):
Company$18$1

Of course you cannot view this table from within Navision, only the results by viewing flowfields.

When you insert / modify records from within Navision for the given table, it inserts / modifies record(s) in these tables in order to be able to calculate the flowfields. This is why adding SumIndex fields considerably slows down the database (and makes it grow so greatly).

When you delete a record from the given table, SQL does NOT delete the record from the Company$18$1 table!

When you optimize this table however Navision / SQL review the records in the Company$18$1 table and determine which ones can be deleted.


So this type of optimization could actually make the database smaller! The effects will be more present in unposted / journal tables with SumIndex fields where there is lots of deletion. Running this routine on ledger tables will not have as great an effect because of course these tables do not get records deleted.


It may be a good idea to run the Navision optimization before the SQL optimization - to make a smaller database before you run the optimization.


The SQL optimization (I'm not 100% sure) does, amongst other things, shrinks the log file. It make be better to shrink the log file manually first, if you look at the actual files the log file can be as big as the database!

-a

RobertMo

Well yes, thanks for detailed explanation.

I knew about $ tables that support sumindexes and keep all the "intermediate sums" to simulate SIFT, I just didn't know that these subsum-records are not deleted when no records that construct sums are found. Of course subsums are just changed to 0, but the subsum-records are left there, until the Navision optimization occurs for these table. SQL optimization does not clear these records.

I'm still wondring about purpose of SQL optimization ? Is it really helpfull to rebuild SQL indexes or not ? I understand that it is for regular SQL DB, but is it also benfit for Navision SQL DB ?

Has anyone practical experience ? Does anybody do that on companies SQL DB / his client SQL DB ?

Log is truncated to its "creation Size" (100MB) after backup it up, so there's no need to shrink it before ? Actually the log grows because of SQL optimization...

Savatage

by searching on optimizinf tables or optimizing database you can find many posts.

here's just one to start you off..

http://www.mbsonline.org/forum/topic.as ... ing,tables

AO

Check this out http://www.microsoft.com/technet/prodte ... kidbp.mspx

As far as I recall the Navision optimize does a lot of "CREATE INDEX WITH DROP_EXISTING" and then clears out the the sumindexes.

I think that if you run DBCC Indexdefrag and UPDATE STATISTICS you won't consume as much discspace but then again, it probably won't be as efficient and you won't get the sumindex optimizaton.

Savatage

There are soooo many tools out there it's almost impossible to know about them all!

http://www.microsoft.com/downloads/deta ... laylang=en

Overview
Microsoft SQL Server Best Practices Analyzer is a database management tool that lets you verify the implementation of common Best Practices. These best practices typically relate to the usage and administration aspects of SQL Server databases and ensure that your SQL Servers are managed and operated well.