SQL 2005 Performance Issue

bbrown

I received the hotfix from Microsoft today. We are testing with a client. Will let everyone know the results

Waldo

bbrown wrote:

I received the hotfix from Microsoft today. We are testing with a client. Will let everyone know the results

Is it again a private hotfix? Or can we download it somewhere?

bbrown

Waldo,

It is Hotfix 2196 (09.00.2196.00). It is supposed to address issues (922527, 926024, 926285, & 926335). The issue we have seen is 926024. There is no KB article on this one.

We are currently testing with a client.

Marije_Brummel

Brian,

Isn't this the same hotfix I emailed you back in October?

By the way, the workaround with the clustered index was also the solution we used at a customer of Waldo.

Waldo

I was just checking that, and actually it is.

And indeed, the clustered index workaround can make a big difference. I just don't know the "reasoning" around it ... why is this the "solution"?

bbrown

Mark,

ISometimes I have trouble remembering what I did last week, never mind 3 months ago. This is the same fix you gave me.

Marije_Brummel

SQL is using a wrong execution plan.

My guess is that somethimes changing the clustered index "fools" sql.

Sorcerer

so i've just got the hotfix from ms (sql build 9.00.2214.00, KB930775) for this issue (re-used sql plans).

the hotfix is not relased yet. we will install it at the weekend.
i will post again if there is any performance boost after this.

Waldo

Is that one for the "clustered index workaround"?

Sorcerer

i don't know what exactly is fixed in the fix...but it seems to work, after also creating and using plan guides.

ms is also working on a corrected finsql.exe. so you have not to create the plan guides. (it will add a RECOMPILE option to the sql statement. so you can define for which table a recompile will be added)

Marije_Brummel

I have heard of this solution, and doubt if I should be entousiastic.

Compiling Execution plans is important for performance, recompiling adds another workload to the database.

:-k

Sorcerer

from my point of view the plan guides work, whatever additonal workload is set to the sql server. this is the first time for more than six month that the users can work with navision smoothly. i think our server has enough performance resource to handle the additional workload.

illug

How can this hotfix be obtained?

I am seeing there performance issues on SQL 2005 but still using SP2 of MBS.

kriki

[Topic moved from Navision forum to SQL Performance forum]

Sandra

Hi there,

So we have installed the HF. And we created plan guides. And everything was find - for a week at least ... which is good considering. But today we had new Hangups. I created the Plan Guide ... and no result. ](*,) More hangups.
What is your experience? Any more ideas on the matter?

Regards
Sandra

Marije_Brummel

Cannot give any ideas as far as this problem conserns. I will be at convergence next week and discuss this with the NAV development team. I know they are working on this but I don;t know the status.

But what I can say is that we have a LOT of experience with SQL server performance and love to have a look at your system. No promisses or guarantees, sorry.

Sorcerer

So after updating to the last client (4.0 SP 3 Build 24219) and updating SQL Server we created the recompile options for our biggest tables (> 100.000 records).

After working one week it seems that the performance is better but not as it was on SQL Server 2000. There are still hang ups in entry forms that can only be solved by changing the record set in the database options.

At this time it seems that there is no chance to get a smooth running (big) system with SQL Server 2005.

JonnyD

Hi Sorcerer!

I will implement that recompile-feature for a big customer next week - so it would be very interesting to already hear about the results.

Just one question concerning your problem:

Have you set the /pae-switch in your boot.ini??
Do you use AWE memory management?
Have you set the policy "Locking pages in memory"?

Sorcerer

Hi JonnyD,

we use x64 Bit so these switches aren't used anyway...

(even though the policy is set...)

GOOD LUCK!!

Waldo

Can you please explain what you mean with that recompile feature?
:oops:

Sorcerer

You can add RECOMPILE for a specific table like this:

You need the table $ndo$dbconfig in your Navision database. If it is not already there, then create it in this way:

CREATE TABLE [$ndo$dbconfig] (config VARCHAR(512) not NULL) Then assign Select permissions for Public! Otherwise normal users won't be able to connect anymore.

In this table, you can create as many lines as you like, like this:

UseRecompileForTable="G/L Entry";Company="CRONUS International Ltd.";RecompileMode=1;

This means that for G/L Entry table in company "CRONUS International Ltd., Navision will add RECOMPILE to queries. The setting takes effect by just restarting the clients.

Please notice:
The syntax must use the Navision name for the tables (e.g "G/L Entry") and NOT the SQL naming "G_L Entry".

The RecompileMode options are from 0 to 3. I recommend that you use option 1. But you can experiment with option 3. The exact effect is impossible to say in advance. The options are:

• 0 – off;
• 1 – use RECOMPILE hint during table browsing. This is intended for UI activity. It’s a default value if “RecompileMode” parameter doesn’t present.
• 2 – use RECOMPILE hint with AL operations.

If you want to use RECOMPILE in both cases you have to use “RecompileMode=3;”.

You can check if it works with Profiler. There should be queries which contain the RECOMPILE word, like SELECT abcdefgetc.... OPTION(Fast5,RECOMPILE)

The potential risks you will have if you get too many RECOMPILE's are:
- Each RECOMPILE means work for CPU's. CPU's are typically not stressed in a Navision system, so it is unlikely to cause a bottleneck, but please monitor CPU Usage.
- In theory, if too many RECOMPILE tasks are issued at the same time, they can get queued. Again, for Navision, this is not likely to happen since Navision queries are very simple (always just on one table for example).

JonnyD

Enable AWE anyway - it is recommended by Microsoft experts although the (online) documenatation says that it would not be necessary.

In your Navision-forms set the property "SourceTablePlacement" to "First" - especially in your Lookup-forms. Unfortunately the value "Saved" is the default-value for the property so I think your forms will all be set to SourceTablePlacement=Saved.

Sorcerer

JonnyD wrote:

Enable AWE anyway - it is recommended by Microsoft experts although the (online) documenatation says that it would not be necessary..

This one has explicit set to no because of 64 bit enviroment, recommended by MS expert, too. (But it hasn't had any effect anyway).

JonnyD wrote:

In your Navision-forms set the property "SourceTablePlacement" to "First" - especially in your Lookup-forms. Unfortunately the value "Saved" is the default-value for the property so I think your forms will all be set to SourceTablePlacement=Saved.

This one was one of our first steps to do...But we had to roll back this one because of some strange behaviour in some cases. There were wrong appliements shown for some entries afterwards. By now this tip is also discontinued by MBS.

Waldo

Thanks, Sorcerer.

I understand you need a specific build? You know which one?

Sorcerer

Yes, 4.0 SP3 Build 24219.

pdj

I guess this problem still exist - scary...
Has anyone been able to reproduce the problem with the NAV5 runtime?
We are in the process of starting a rather big migration (100gb+ and app. 200 concurrent users) from Native to SQL. Quick poll: Go for SQL2000 or SQL2005? (I would really like to hear what SQLPerform recommends, but of course also the rest of you :-))

PS: Measuring in minutes for anything but batch posting and similar will result in a lost customer or worse - it seems that disqualify SQL2005 :-(

Waldo

My migrations to SQL2005 went fine till now, but I have to say, the databases weren't that big. The biggest was 60Gb, and it really needed performance tuning (using SQLPerform tools), and even that one is running on comfortable speed. We haven't ran into "THE BUG" anymore since that latest hotfix on SQL Server.

I know Mark has got one customer that he migrated, and things went really wrong on SQL2005. May be he can ellaborate more. :-k

pdj

Waldo wrote:

...and it really needed performance tuning..

So will ours. We are currently trying to plan and estimate it, which is quite hard. But that's a different story :-)

Waldo wrote:

We haven't ran into "THE BUG" anymore since that latest hotfix on SQL Server.

Sounds great! Do you just mean SP2, or have you added additional Hotfixes and are they available to all partners directly, or is like NAV where we have to make a service request for each problem we encounter to get each hotfix?

Waldo wrote:

I know Mark has got one customer that he migrated, and things went really wrong on SQL2005. May be he can ellaborate more. :-k

Migrated from SQL2000 to SQL2005? I feared you would say that - well, looking forward to hear from him.

Waldo

Yes, Mark should give you more details on this.

futher more, I'm talking about the private hotfix earlier in this thread (I don't remember the knowledge base artikel). It should have been incorperated in SP2, but that I can't confirm.

Marije_Brummel

What would you like to hear?

First of all is was not me who initiated the SQL2005 migration. SQL2000 was performing just fine after some adsjustments. All I asked for what a 4.0 SP3 runtime upgrade to be able to change indexes from within NAV instead of doing this on the SQL box. :?

The project manager then decided to also migrate to SQL2005.

Personaly I do not want to deep dive into project I do for customers at this forum.

About the issue what this topic is about; yes I have seen the phonomenon and it is impossible to predict when it will happen. So far I have been able so solve all of the issues, that's all.

With one of Eric's customers it happened on the Req. Lines if I remember correctly. It was solved by changing the clustered index.

I have been doing this work now for 6 months and have done over 15 tuning projects until now.

Still waiting for more customers with this issue. 8)

Good luck on your desision.