Is it dangerous to put custom indexes on SQL Server?

josephdewey · 2012-07-18

We started utilizing Ignite to do SQL Server database monitoring, and it recommended that we put an index on a specific table in SQL Server. I'm not able to replicate that specific index by creating new Keys on the table, so I wanted to do it directly through SQL. I thought I've heard that you shouldn't do this, but I wanted to ask for advice from everybody.

I've put indexes on SQL server before, but never in a Navision instance, so I'm wondering if it's dangerous to do.

I've tested this out by creating the index in our test system, and it seems like everything's still working okay.

Is it okay to do this with Navision?

ara3n · 2012-07-18

NAV in the key window has a sql index column where you can specify the sql index.
I suggest to follow this process. Create indexes on sql works fine but the problem is that when you do a nav backup, those indexes changes are not copied and thus all the tuning you've done will be lost.

David_Singleton · 2012-07-18

If you add indexes, or any way what so ever touch a Navision database out side of Navision, the database will collapse, all data will be deleted, and the server and any clients connected to it will disintegrate on the spot. It will also locate and delete any and all backups ever created.

or...

So long as you have a good understanding of SQL and don't do anything stupid, of course you can add Indexes to a Navision database. Rashed points out the core issue, that there are cases where Navision will over write what you did, but so long as you save everything as scripts, and be aware of what is being done in Navision and reapply what might be lost you should not have any problems.

Your job is to understand the needs of your users and to provide them with the optimal solution for them. If doing this will help the users to be more efficient then do it, just be aware of what could happen. You key issue is to make sure it doesn't affect performance.

kine · 2012-07-19

I'm not able to replicate that specific index by creating new Keys on the table

What is the problem?

josephdewey · 2012-07-20

Hi David,

That's a great idea to save everything that I did as scripts, so if (or when) we move the database, then we can recreate all of the customs. Excellent response, destroying the world, and then recreating it before my eyes.

Hi Kine,

The titular problem of my post is that I couldn't find any documentation on how dangerous it is to put custom indexes on SQL Server.

Details on the specific fish that I'm trying to catch with this information is that I'm trying to speed up the batch posting of Item Journals, where we have a batch of about 6000 negative adjustment entries that we need to post at a certain time every day. This batch takes about 30-45 minutes to post, and I'd like to find a way to make it take less than five minutes. I already moved the inventory value entry adjustments and GL entries to a separate batch that runs at night.

I found that the SQL statement that was taking the most time was this one:

(@P1 varchar(20),@P2 varchar(10)) 
SELECT SUM("Qty_ (Base)")  
FROM "Prod_Asia"."dbo"."Unicity Thailand$Warehouse Entry"  
WITH  
   ( 
      UPDLOCK 
   )  
WHERE (("Bin Code"=@P1))  
AND (("Location Code"=@P2))

Which is very confusing why NAV is trying to sum by location and bin, but not by item, but I trust that the creators of NAV knew what they were doing with inventory posting. And my tool recommended that I make this index:

CREATE NONCLUSTERED INDEX [location_bin_qty] 
ON [Prod_db].[dbo].[Comany A$Warehouse Entry] 
([Location Code],[Bin Code]) INCLUDE ([Qty_ (Base)])

But, you can't make that exact index inside of NAV, because NAV always slaps the fields of the primary key on every index, which in this case in [Entry No_].

So, I put that index directly on SQL Server, and it stopped SQL Server from doing a full table scan when NAV posts the Item Journals, and it helped a little, but it didn't significantly speed up the posting.

So, if you have any ideas on my next step for significantly speeding up a huge batch post of negative adjustments in Item Journals, that would be awesome.

Marije_Brummel · 2012-07-22

If you read the query and try to interpret it, you see that NAV wants a total of something.

This is where you need to know NAV. NAV uses SIFT for this. This is a built in technology that will maintain these sums automatically during transactions without any coding. In the native database this was done my magic. In SQL this is done by Indexed Views.

That was the sales part.

To fix this issue you need to activate a SIFT. This has to be done from inside the NAV metadata. I would advise to get a NAV specialist explain how to do this.

It's not rocketscience. I bet if you search for SIFT you can do it yourself. Or purchase Jorgs book.

kine · 2012-07-23

From your post we can see that the problem is not "how to create custom index dierctly on SQL", but how to create index "location Code", "Bin Code" (or rather "Bin Code", "location Code" because bin code could have better selectivity) with sumindexfield "Qty. (Base)". And all this could be done from within NAV, as Mark wrote.

Is it dangerous to put custom indexes on SQL Server?

Comments

Categories