- Why when we optimise is the record size different for the same table in different companies within the same database. 2. Can the record size increase following optimisation. 3. How do people schedule the optimisation process to run in batch. 4. Where are sift indexs kept? Within the actual table, or are they seperate tables within tables?
My database reached 109 GB in Native Navision (under 3.01B). by the way I replied to your previous Post . Optimization will erase blank space (records) in the table. you cannot schedule optimiztation (as much as i know) sift index are kept in the tables without names in SQL on Company name and numbers
Any feedback on any of my top 3 questions. I have found the SIFT indexes lurking in my SQL databse with the $tablename$siftindex, I think [;)] But what I am really concerns about is the increase in table size after optimisation, and I need to pin it down, as well as why record size is different. I am really looking forward to some enlighting posts from u guys [:D]
Hallo Zaph, ad 1.: please look at the help of the Tables information: Records The number of records (entries) in the table. By keeping track of how many new records have been added during a certain period, you can estimate the number of records you can expect in the next period. When you put that together with the record size in the Record Size field, you can see whether you will have enough space in your database. Record Size The average number of bytes per record in the table. This can be used as described under the Records field. i think average mens here the the nummer of blocks whith is used by the table will be multiplied with the blocksize and then devided by the number of records in that table. when there are fields in a table you fill/use in one company and in one other not the record size can differ. ad 2.: i don’t think so. because the optimasation kills only empty space. ad 3.: this is not possible with the normal Navision tools ad 4.: you the right answer ad optimasation at all: i don’t realy know how it is whith SQL-DB, but whith Native-DB the optimisation will slow down performance and the gained space will be lost step by step over the next time. br Josef Metz
Optimizing tables in SQL through Navision will slow it down. But it will save space (or at least should save space). 3. You need a seperate tool to do the optimize, such as Expand IT.
Most of the time, the index rebuilding done by optimizing on SQL will make the index more efficient (since all indexes are based on a clustered index) which will improve performance not make it worse. I say most of the time, because the SQL optimize is not perfect and screws up from time to time. Please give an argument why it will slow down performance, in general. Additionally, the records with zero sums in SIFT tables will be removed which might - depending on your data - give significantly less records to sum over in the SIFT table. The main purpose of Optimizing under SQL is to improve efficiency by removing redundancy in index pages, recreating the cardinality histograms for indexes, and removing null rows in SIFT tables. Reduced space is a side-effect of it.
During a recent optimize, I ran an SQL trace to see what it was doing behind the scenese. Most of what was going on appeared to be the re-creation of indexes on the SQL tables. To that end, I wrote a stored proc to re-create all the indexes on all the tables, and scheduled it using SQL’s job scheduler. It works like a charm. However, it only covers the indexes, not the other actions performed by optimize. But, if I read the above posts correclty, the other actions degrade performance anyway. That said, I’m satisified for now using a custom SQL proc to control regulate the quality of the indexes.