Navision SQL Request Optimization

[Anni Kristensen]

0X000E
While Navision is using the same stored procedures over and over again, we
can see that the users often is locked and are waiting for a wait_event named
0X000E.

This tells us that the users are waiting for a ”COMPILE lock”. The procedure
still does exist in cache and is valid, but in the Navision application the
owner is not written before the object and therefore the SQL server will not
be sure whether the object does exist in other schema's. The SQL Server is
then forced to capture the compile lock to investigate. Therefore instead of
using ex. 'Execute sp_execute', use 'execute dbo.sp_execute'.

[Smartikat]

What version are you running? I am running 3.7

I don't see a lot of compile lock. Are you sure?

I do see a lot of other locks/blocks/deadlocks... Which I have solved one
at a time by using the profiler to locate and speed up the query by adding
new index (SQL Index, not Navision Index), and change code on CSIDE. Look
at the execution plan, duration, cpu, read and write numbers under the
profiler, you will find a lot of info there.

Post by Anni Kristensen
0X000E
While Navision is using the same stored procedures over and over again, we
can see that the users often is locked and are waiting for a wait_event named
0X000E.
This tells us that the users are waiting for a "COMPILE lock". The
procedure
still does exist in cache and is valid, but in the Navision application the
owner is not written before the object and therefore the SQL server will not
be sure whether the object does exist in other schema's. The SQL Server is
then forced to capture the compile lock to investigate. Therefore instead of
using ex. 'Execute sp_execute', use 'execute dbo.sp_execute'.

[Anni Kristensen]

I run DK3.01B.

What do you mean by "SQL index, not Navision index"? Could you give med an
example?

Post by Smartikat
What version are you running? I am running 3.7
I don't see a lot of compile lock. Are you sure?
I do see a lot of other locks/blocks/deadlocks... Which I have solved one
at a time by using the profiler to locate and speed up the query by adding
new index (SQL Index, not Navision Index), and change code on CSIDE. Look
at the execution plan, duration, cpu, read and write numbers under the
profiler, you will find a lot of info there.

Post by Anni Kristensen
0X000E
While Navision is using the same stored procedures over and over again, we
can see that the users often is locked and are waiting for a wait_event named
0X000E.
This tells us that the users are waiting for a "COMPILE lock". The
procedure
still does exist in cache and is valid, but in the Navision application the
owner is not written before the object and therefore the SQL server will not
be sure whether the object does exist in other schema's. The SQL Server is
then forced to capture the compile lock to investigate. Therefore instead of
using ex. 'Execute sp_execute', use 'execute dbo.sp_execute'.