SQL 2005 Performance Issue

Sorcerer · 2007-10-10

Hi there,

has anyone experiences about this issue and update 6?

bbrown · 2007-10-10

Sorcerer wrote:

Hi there,

has anyone experiences about this issue and update 6?

Our customer (the one for which this thread was started) has installed update 6 for testing. I'll let you know the results when we have them.

sitra · 2007-10-10

The below link to a KB article describes the updates for SQL2005 that we have been awaiting for a long time, this fix should address some of the obstacles you have been having as regards to NAV performance in connection with SQL2005, the fix will be available beginning next week, this message is to inform you in advance.

http://support.microsoft.com/default.as ... -US;941450

Waldo · 2007-10-11

I got the exact same message as well ... .

But it is not clearly stated which problems it addresses (there is no NAV reference in the KB article). I asked my TSAM for more info on this, so I'll try to keep you updated.

Marije_Brummel · 2007-10-11

This update has noting to do with parameter sniffing. Please do not expect a miracle to happen.

Please send me an email for mor information as we should stop sweeping each other up on this subject on the forum.

colin_leversuch-roberts · 2007-10-22

I was just noticing my post concerning the permuserxxx cache - well there was a fix in release 3179 , should be included in the sp2 rollup 4, however it seems the main cause of the probs with the cache was the forced recompiles provided by microsoft and also the non parameterised ad-hoc queries - I've not yet been able to force the ad-hoc queries to parameterise - so one fix causes another problem.

Mauddib · 2007-10-30

Has anyone had any positive or negative effects of installing the rolling update? We are careful here about installing updates until we have heard of any problems.

However we are suffering greatly with the performance issues as described throughout this thread. I am only reading this thread now for the first time and dont know where to start! ](*,)

We have also got problems with going to the end of a list view and moving back up again (up to 3 mins delay time as the computer hangs). We even have up to 2 mins delay on a completely unfiltered Customer Card when changing the Key From No. to SearchName!

And as described all problems are totally random. You can do the same thing 10 times and the problem will only surface 10% of the time.

Marije_Brummel · 2007-10-30

It is difficult to find a place to start.

I would recomend downloading my whitepaper from the downloads and read this as a guideline.

2 Other questions:

What version of NAV are you on? (Version, SP & Hotfix)

What version of SQL are you on(Version, SP & Hotfix)

Mauddib · 2007-10-30

We are on Navision 5 and SQL SP2. No hotfixes applied as yet. Will look at your white paper too. Ive been reading a few things on this (after many searches of this forum) but nothing helped yet.

Only thing Ive tried that hasnt worked is this trick to force RECOMPILE. I added the table as described on page 10 above and inserted the record for my table in the proper company and database and table etc. But In the SQL analyser the word RECOMPILE is not appended to any of the statements.

Marije_Brummel · 2007-10-30

There are no updates yet for 5.

This will be release either tomorrow or end this week

Request this at Microsoft and apply to the client.

By default the index hinting will then also be enabled but just test if that works for you, if not turn it of as in my whitepaper ad proceed from that.

You might also want to install the last hotfix for SQL2005, also to be received from microsoft.

Mauddib · 2007-10-31

We did receive a hotfix for Naviion 5 in the last couple of days. All it appears to do is solve a problem with MIN and MAX in the flowfields (I think they were backwards or something, each doing wha the other should do). However we get many "Out of memory" type errors when trying to run it so we have abandoned this.

The RECOMPILE option seems like it might work for us as most of our problems are in situations where SQL might be trying to use a cached query when it shouldnt (for example one form shows all records where a status is "DONE" and the other form shows the same table where status is "<>Done"... so any stored query on this table is useless.

However having followed the instructions above to create the config table and put in an entry for this table in the correct company, i see no RECOMPILE keyword added to any of the queries (fully restarted clients etc).

Marije_Brummel · 2007-10-31

Recompile does not work with 5.0

I think you have had another hotfix that the one I meant.

Try putting an index hint in the config table for the specific query.

Waldo · 2007-10-31

Mark Brummel wrote:

Recompile does not work with 5.0

Well, you mean the recompile using the $ndo$dbconfig table ... . You can always use planguides in SQL off course... . Shortly explained in one of my blogposts: http://dynamicsuser.net/blogs/waldo/archive/2007/08/21/making-nav-use-a-certain-index-on-sql-server.aspx

Mauddib · 2007-10-31

No the index hinting doesnt appear to have made a difference. Im also not sure how to SEE it making a difference in the Profiler. How can I tell if my suggested index was actually used by Navision?

I do have a SQL2005 .trc log of my form opening when its slow and my form opening when it is fast. If anyone who can read such things wants to look over them then let me know where to send them on to you.

Waldo · 2007-10-31

You can see it when the SQL Statement clearly contains something like:
e.g.:

SELECT TOP 1 *,DATALENGTH("Picture") FROM "400SP3_NAV_TEST"."dbo"."DEFAULT COMPANY$Customer" WITH (READUNCOMMITTED, INDEX("$1")) ORDER BY "Search Name","No_"

When there is no statement with the INDEX-argument ... it doesn't hint.

Mauddib · 2007-10-31

No it appears not to be hinting. My SQL statements have not changed. The line in my $ndo$dbconfig table is:

IndexHint=Yes;Company="Test1";Table="Interaction Log Entry";Key="Table Name,Processing status,Date,Time of Interaction,Canceled";Search Method="-+$";Index=1

the first two fields in the key are non standard and are of type Option.[/quote]

Waldo · 2007-10-31

Well, it looks fine, to be honest... . :oops:

Are you sure the key is the entire key like it is in the table definition?

Mauddib · 2007-10-31

Yes pasted directly from the key setup in the table designer itself. So misspellings or typos arent even possible.

is the index hinting in the DBConfig ignored in NAV5 the same as the RECOMPILE hints are above? Maybe I cant do either

David_Singleton · 2007-10-31

Mauddib wrote:

We did receive a hotfix for Naviion 5 in the last couple of days. All it appears to do is solve a problem with MIN and MAX in the flowfields (I think they were backwards or something, each doing wha the other should do).

Actually what was happening, is that it was finding the first key that fitted the field, and then returning MIN or MAX according to that sorting. On for example in a sales line MIN would return the first line of the order, since that was the sorting. Good to see its fixed.

Mauddib · 2007-10-31

declare @p1 int
set @p1=180150165
declare @p3 int
set @p3=2
declare @p4 int
set @p4=1
declare @p5 int
set @p5=1
exec sp_cursoropen @p1 output,N'SELECT * FROM "navision"."dbo"."Test1$Interaction Log Entry" WHERE (("Table Name"=@P1)) AND (("Processing status"=@P2)) AND (("Canceled"=@P3)) AND "Table Name"=@P4 AND "Processing status"=@P5 AND "Canceled"=@P6 AND "Date"=@P7 AND "Time of Interaction"=@P8 AND "Entry No_">=@P9 ORDER BY "Table Name","Processing status","Canceled","Date","Time of Interaction","Entry No_" ',@p3 output,@p4 output,@p5 output,N'@P1 int,@P2 int,@P3 tinyint,@P4 int,@P5 int,@P6 tinyint,@P7 datetime,@P8 datetime,@P9 int',15,2,0,15,2,0,'2003-12-12 00:00:00:000','1754-01-01 13:45:28:000',7838
select @p1, @p3, @p4, @p5

thats the actual line in the Profiler that causes the trouble. Usually the "reads" on the table is around 130 and the Duraction about 130 also. When it goes wrong, randomly, the reads go to 75000 and the duration is 56700.

The ORDERBY is indeed the key that I specify on the link that opens the form. However all my links only filter on the first 3 of these fields. I do not know where it is getting the part that says:

"Entry No_">=@P9

or where it is getting the value 7838 for this parameter.

Marije_Brummel · 2007-10-31

Try:

IndexHint=Yes;Company="Test1";Table="Interaction Log Entry";Key="Table Name","Processing status","Date","Time of Interaction","Canceled";Search Method=;Index=1

How many interaction log entries do you have?

Try:

Remove all FIND('+'); Entry No := + 1;
Make the table autoincrement

Change the clustered index to Contact No.

Good luck.

Mauddib · 2007-10-31

Thanks for the help all but alas this isnt working either. Sorry Mark. It must be something Im missing (obviously) but no entry in that table is having any effect on the queries coming back from Navision into the profiler.

We have just over a million entries in this table. But most times it is very fast. Depending on which button the user uses to open the form the filters are different. Always the same three fields but different options. 3 of the forms only ever have about 30-80 records in the filter. The 4th has the rest. It is USUALLY the smaller result forms that give the trouble, but not always.

Most times it is fast but then randomly we get these several minute delays.

Waldo · 2007-10-31

A wild guess ...
Did you alread changed the SourceTablePlacement property to "First" or "Last"?
In some cases, this helps.

Marije_Brummel · 2007-10-31

Everyone:

I have seen performance improvements on SQL 2005 with the build 3200.

It concerns queries like this

SELECT  * FROM "TestHotfix"."dbo"."CRONUS Nederland BV$TestTable" WHERE (("No_" LIKE @P1)) AND  "No_"<@P2 ORDER BY "No_" DESC

It has nothing to do with the clustered index scans but it's a step.

Good luck.

Mauddib · 2007-11-06

Been working on this a couple of days.

Indexhinting hardly works at all for me. If I put in an index hint for something REALLY simple like the Customer table on "No." using:

IndexHint=Yes;Company=;Table="Customer";Key="No.";Search Method=;Index=1

Then it does hint an index the first time I open the customer card. However any other use of the customer card, new records, next records, open, close, you name it no longer has the index hint in the Query that is generated.

If I go to anything more complex however, like a compund secondary key, then it just doesnt work at all.

I did get the RECOMPILE keyword to appear when using the new Nav5 hotfix released last week. However the hotfix upgrades the database so the only way to test this on my live busy system is to upgrade accross the company. Is there any bad feedback about this hotfix yet? No known issues or problems?

Sorcerer · 2007-11-07

Hi Mark,

how did you get the cumulative update 4 for SQL?
Everytime i try to order it with the online request form/ by phone i get only a update for the sql native client...(always regarding to KB 941450)

Have you another idea to get it?

Thanks,
Sorcerer

Marije_Brummel · 2007-11-07

I got it using my connections, so not the official way.

Sorry. :?

tim6661234 · 2007-11-08

Sorcerer wrote:

Hi Mark,

how did you get the cumulative update 4 for SQL?
Everytime i try to order it with the online request form/ by phone i get only a update for the sql native client...(always regarding to KB 941450)

Have you another idea to get it?

Thanks,
Sorcerer

Currently fighting the same issue. MS in UK are helping a lot. Custromer also recieived onlt client update so NAV support sent me this

Package:

KB Article Number(s): 941450
Language: All (Global)
Platform: i386
Location: http://hotfixv4.microsoft.com/SQL%20Ser ... 86_zip.exe
Password: 0z3kX0c

Hope this helps you.

Tim

Sorcerer · 2007-11-08

Hi Tim,

yes seems a lot better!
But unfortunately i need the x64 version...

When you can get this too it would be excellent...

Thanks,
Sorcerer

EDIT: Finally got it!

danielecacchi · 2007-11-08

I did get the RECOMPILE keyword to appear when using the new Nav5 hotfix released last week. However the hotfix upgrades the database so the only way to test this on my live busy system is to upgrade accross the company. Is there any bad feedback about this hotfix yet? No known issues or problems?

Sorry , I have the same issue with NAV5.0.
Wich is the name of the hotfix NAV5.0 to ask to Microsoft ?
Thank you

SQL 2005 Performance Issue

Comments

Categories