SQL Clustered Index Fix v2

Administrator

SQL Clustered Index Fix
With this little tool, you can fix the clustered index-problem on SQL directly from Navision. As an extra, you can also do some maintenance on the indexes of SQL. It is possible to change the following properties of the indexes in Navision: “Clustered”, ”Enabled”, ”MaintainSQLIndex”, ”MaintainSIFTIndex”. Remember that the fields Date, Time or Modified fields of the object will be changed!

To be used only on a SQL-database of versions 4.0SP1, 4.0SP2. (I don’t know about future versions…)

http://www.mibuso.com/dlinfo.asp?FileID=594

Discuss this download here.

DarrenBezzant

Could you provide some info (or a link) to this SQL Clustered Index problem? I'm not aware of what you speak of.

regards

ara3n

Sp1 added a new feature to the keys that allowed you to select a differnt key as clustered. The problem is that once they added the feature. All the primary keys which were by default custlered were now unclustered. So there were no clustered keys on any table.

kriki

And what I understood about clustered indexes is that SQL puts the records together based on the clustered index. So if you read records in sequence using the clustered index, it should go faster because SQL doesn't have to search in different places of the DB.

Waldo

Ready for the stupid questions?

I have a Database with:
In Navision: every index is not clustered. If i open any key (primary or secondary), the property of the key "Clustered" = <No>
In SQL Server: the same key (index), property of the (primary) key "Create as clustered" is "Yes".

How is this possible??

kriki

Waldo wrote:

Ready for the stupid questions?

I have a Database with:
In Navision: every index is not clustered. If i open any key (primary or secondary), the property of the key "Clustered" = <No>
In SQL Server: the same key (index), property of the (primary) key "Create as clustered" is "Yes".

How is this possible??

I also noticed that.
This means Navision stores the clustered property somewhere in the table-object and doesn't read it from the SQL-table.
So I think Navision creates it in SQL (and not always) but not always in Navision.
In short: I think there is some random bug on the loose.

ara3n

The bug was created in SP1 and Sp2 tried to fix it. The fix is this. SP1 had all the keys nonclustered.
In Sp2 by default PK is clustered. Even if non of the keys in Navision have the field checked. Once you chage the Clustered property in Navision, then SQL is changed.

That's what I've found out from experience.

Waldo

Nevertheless, I executed kriki's fix, and seems to work fine. Don't experience some performance gain yet though ... .

Nice code, kriki!

kriki

Waldo wrote:

Nevertheless, I executed kriki's fix, and seems to work fine. Don't experience some performance gain yet though ... .

Nice code, kriki!

For performance gains, I think it is more important to have SQL-statistics updated every night and a SQL rebuild every week.
Sometimes another clustered key can be better.
E.g. in the entry-tables by using the index that is used for showing the entries on drilldown or in case some flowfield is working on them and you disabled the SIFTIndex.

GoMaD

Kriki,

a quick question before I deploy the weekly rebuild at one of our customers.

I was always told that you have to "warm up" a sql database by performing a database test. (Which loads the execution plan of all the indexes into the cache of the SQL Server)
At our customer this alone takes about 1 full day.

My question is: Is the execution plan still in the cach after I rebuild the indexes using the rebuild index task of SQL 2005?

Regards,

kriki

GoMaD wrote:

My question is: Is the execution plan still in the cach after I rebuild the indexes using the rebuild index task of SQL 2005?

I have to admit I don't know it.
But if we reason logically about it, I would say it is not anymore because rebuilding the indexes is like doing a reset. So the execution plans are outdated. Or it must be that SQL changes them on the fly, but I have doubts about that.
For the rest : I think that doing a DBtest for warmup doesn't load the currently used data in memory, but ALL of it (or at least it tries to as far as the SQLmemory can contain the DBdata).
So I think it is best to create a codeunit that reads some frequently used tables in memory (setup-tables,T36,T37,T15,... but NOT the entry-tables).

Administrator

SQL Clustered Index Fix v2
With this little tool, you can fix the clustered index-problem on SQL directly from Navision. As an extra, you can also do some maintenance on the indexes of SQL. It is possible to change the following properties of the indexes in Navision: “Clustered”, ”Enabled”, ”MaintainSQLIndex”, ”MaintainSIFTIndex”. Remember that the fields Date, Time or Modified fields of the object will be changed!

To be used only on a SQL-database of versions 4.0SP1, 4.0SP2. (I don’t know about future versions…)

Version 2:
- Added column "No. of records"
- Added field on top to filter

http://www.mibuso.com/dlinfo.asp?FileID=594

Discuss this download here.