Index Problem..

laurentiu_irofte

I made somme indexes on one table and these indexes have each almost the size of data of the table.. Sommeone can tall me what is wrong??
In this moment data on the table is somewhere at 50MB and Indexes are at 1GB...

kriki

Probably your indexes are tooooo big and you have toooooo many SIFT-levels for your flowfields.
But for the rest : it is not abnormal. Also on a Navision DB, I have seen this with Item Ledger Entries : size : 1GB. After disabling all secondary keys : 300MB.

laurentiu_irofte

The indexes are not created in Navision... They are created on sql, and are recomanded by Database Engine Tuning Advisor.. Performances are great but i'm not so happy with this increase of database.. Space on disk is enought but i think you have encounter whit this situation and you can offer me an answer.
Thx a lot.

Marije_Brummel

This is a short term approach I see happening a lot. Creating indexes to read data fast, but when your database is getting larger and larger those indexes will become an overhead.

Indexes is about balancing between reading and writing. If posting processes will start taking to long you might want to get rid of some of these indexes and have some more reads on the disks.

What is your clustered index? If this is a long index and get's added to all non clustered indexes this might cause your problem.

Last but not least: never add indexes on SQL Level. This is undocumented and unusable in Navision. When a developer comed into your system and look at the objects he will not see the indexes and maybe create duplicate indexes or whatever. Always add indexes in the table designer.

Good luck.

laurentiu_irofte

i don't have any clustered index on this table... only nonclustered indexes.. in this case written will not be influenced.. I think.. But i don't explain how one index have allmost the same size like the size of data in the table... nonclustered indexes should be pointers to the data and nothing else no?

kriki

laurentiu_irofte wrote:

i don't have any clustered index on this table... only nonclustered indexes.. in this case written will not be influenced.. I think.. But i don't explain how one index have allmost the same size like the size of data in the table... nonclustered indexes should be pointers to the data and nothing else no?

It also depends on the fillfactor of the index.

laurentiu_irofte

The fillfactor is 70%... They are bigest then normal...

kriki

laurentiu_irofte wrote:

The fillfactor is 70%... They are bigest then normal...

Way to big I would say.
Default is 10%. For big tables with few changes also less. Probably it is this that makes the table so big.

laurentiu_irofte

i let 30% free space in Index pages because is a table whit many inserts..
And the table it is not in the same Filegroup whit the indexes.. I create a filegroup "Indexfilegroup 1" for indexes.

kriki

How many inserts per day?
How much time passes between 2 indexrebuilds?
How many records are in the table?

Is the filegroup on another physical drive?

laurentiu_irofte

Inserts Aprox 10000
Index Rebuid Daily
Records Aprox 500000
And yes is on another drive (RAID 10)

kriki

I think you can go with a Fillfactor of 90% (There is another term in SQL for the same thing but on the contrary).
Meaning you leave 10% free space for inserting new records.
10% of 500000 records is 50000 records!
And if you do a daily rebuild, I think it is good enough.

stryk

laurentiu_irofte wrote:

i don't have any clustered index on this table... only nonclustered indexes.. in this case written will not be influenced.. I think.

Well, to make a long story short I recite from plenty of SQL Server forums, etc.: "Every table needs a Clustered Index. Period."

As NAV always queries a SELECT * (= all columns/fields), every query has to lookup the records from the data pages after seeking a Non-Clustered Index. Without a Clustered Index - so table is a "Heap" - this "Bookmark Lookup" sucks!

stryk

Mark Brummel wrote:

Last but not least: never add indexes on SQL Level. This is undocumented and unusable in Navision. When a developer comed into your system and look at the objects he will not see the indexes and maybe create duplicate indexes or whatever. Always add indexes in the table designer.

I disagree. Since SQL Server 2005 there are way better options to define an optimized index on SQL Server site; e.g. using INCLUDED columns.
And when it's about optimizing SIFT tables, you even have no chance to do anything on NAV site.

I strongly encourage those, who are able to deal with NAV and SQL, to use the best features from both sides!
But be aware: If you don't know what you're doing, you could severely screw up your system ...

kriki

stryk wrote:

But be aware: If you don't know what you're doing, you could severely screw up your system ...

Probably this is the reason Mark wrote "never add indexes on SQL Level".
This is also the reason I tell this to everyone.

laurentiu_irofte

I used Included Columns too... like i say'it database work verry good.. i don't have problems whit that.. but i don't understand why indexes are so big... so another question... Using included column the data who is in that colum is copied in index structure??? if yes that resolve my first question and i will spread Index filegroup around 2-3 raid (in this moment is alone on a raid10), not for performance but for maintain almost the same grow-up by LogicalDrive. Another solution i discover is to create another table (history table) to move old datas and to mantain the first table whit a small number of record.. On table whit old datas i don't need this indexes because is a table whit few reads..

stryk

laurentiu_irofte wrote:

Using included column the data who is in that colum is copied in index structure???

Well, that's actually the difference of INCLUDED columns to the normal INDEXED columns:

When indexing a column (maximum 16 columns), the index values are stored in all nodes of the index. Hence, the index could be pretty large.
If it's a "Covering Index" then this could be very feasible!

When including a column to an index, the values are NOT stored within all index nodes, but they are only attached to the "leaf nodes" of the index. This keeps the index B-Tree smaller, but the to fetch the data the SQL Server does not have to lookup the Clustered Index (or in your case - even worse - get the data from a Heap).
So included columns could be seen - simplified - as a light version of a Clustered Index (if you include all columns to an index, you have actually copied the table in a different physical order).

You should proceed like this:

1. Create a Clustered Index for all the tables. No discussion about that, else you'll NEVER get good performance.
This will defragment your database. Take care that you have about 20 to 25 % free space in the db when doing this, then the optimal physical order is granted during the re-indexing; else too many overflow pages will be created.

2. A Fillfactor of 70% is way too high! The optimal fillfactor is calculated on the table growth; without this measurement you should not go below 90%. This will remarkably reduce the size of your indexes!

3. When done with this, shrink your database to have abou 20 to 25% free space, again (or more precise: 1,5 times the size of the largest table).
This re-oragnization will further defragment your db.

4. Run a periodic index rebuild (ALTER INDEX REBUILD, not REORGANIZE) to continuely defragment the indexes.

Then you should investigate, if further actions - as table- or index-partitioning - are necessary ...

laurentiu_irofte

thx a lot. this resolve my question...
have a nice day!