SQL 2005 Performance Issue

bbrown

Experiencing random system hangs when scrolling thru list forms.

Example:

1. Open Customer Card and press F5 to open Customer List. Scroll to buttom of list then start to scroll up. Sometimes it works fine, others it will take 30 seconds+ to move up 1 customer.

Also similar behavior in other list forms with larger record sets.

Any thoughts? :-k

2tje

SQL?
lots of flowfields in the list?

Alex_Chow

Can you tell us what version of Navision you're using? I know Navision 2.6 has some SQL performance issues.

bbrown

Yes this is SQL (See Subject).

There is one flowField (Balance). Removing it does not resolve issue.

This is Version 4.0 SP2 (Build 22979).

hedegaard

You maybe could have a filter on the Customer card on a field that is not indexed.

With a large number of customers this can make the list act like that.

/Anders

bbrown

hedegaard,

Thanks for the reply.

No filters are set and the form is using primary key ("No.").

bbrown

An Update...

Mirosoft has sent me Build 23099. This is a rolldown of SP3. They want me to test if this solves the issue.

hedegaard

I would use the client monitor and see what it going on.

/Anders

bbrown

When moving back up the list, the SQL statement is re-executed and the records retrieved again. This is where the delay is. (from Client Monitor).

frgusto

I am experiencing similar behaviour in the same version.
Did the MS rolldown solve the issue?

DenSter

Do you have clustered indeces for those slow tables?

The clustered index "fix" was a SQL script that did not update the NAV table definition. You run the SQL script, clustered index is added.... make a change to the NAV table definition, clustered index is gone again because the script did not check the box.

bbrown

Thanks for the replies..

BlackTiger:

There is no code or predefined filters/sorting on the form. With the exception of a couple of extra fields displayed it is the standard Form 23.

Frgusto:

I ran into a problem with the rolldown. As soon as I resolve that, I will test and let you know.

Denster:

Good point. I will have to double check that.

bbrown

The cluster index is okay.

bbrown

I have been able to duplicate this problem in an unmodified CRONUS database. The delay is much shorter, but it occurs in the same spot. I have sent a copy of the Client Monitor output to Microsoft for a response.

bbrown

As I said in my email, I was probably overlooking something. I will test this in the morning and let you know.

bbrown

UPDATECONTROLS does not appear to be the source of the problem. I ran the following test.

1. Using an unmodified CRONUS (3.70B NA), I get the same 16 ms delay as when using my orginal CRONUS test database (4.02 modified).

2. Removed the UPDATECONTROLS from the modified CRONUS database. No difference.

3. Removed the UPDATECONTROLS from client database. No difference.

bbrown

Increasing the Caching – Record Set size from the default of 500 to 10,000 seems to have resolved the issue.

bbrown

This is Microsoft's latest response:

_________

I have just been informed that this specific issue has been reported by a partner in the UK. The response from development was that this is 'by design'. The issue seems to stem from SQL2005 caching query plans when it fills a screen and tries to re-use the query again. The problem is that SQL2005 ends up doing a table scan instead of using the keys. We are consulting with the SQL team to try to get you a workaround if possible.

_____________

Waldo

bbrown,

what is the latest update regarding this issue?

bbrown

No solution at this point. This was escalated at Microsoft and they are working with us.

Waldo

Bummer ...

What is your opinion about the inpact of changing the value to 10000?

bbrown

In the long run, I don't think it resolved anything.

Sorcerer

seems there is no change in sp3...

the only one who could work normaly is the dbo. my user is set as the dbo and i have no perfomance issue when opening the forms hanging for the other users....

bbrown

We see this issue as DBO, and even SA.

bbrown

Navision 4.02 SP2 (Build 23099)

SQL Server 2005 SP1 (Enterprise)

Hardware:

CPU: 4 x 3.0 Ghz Dual-Core Xeon

Memory: 24 GB

Disk System:

Controller 0 (Single Channel RAID)

Drive C: (RAID 1 2x72 GB)
O/S + Programs

Drive (RAID 1 2x300 GB)
Scratch space

Controller 1 (4 Channel RAID)

Drive E: (RAID 10 4x72 GB [channels 0+1])
Base SQL databases (Master, MSDB, etc)
Navision PRIMARY filegroup

Drive F: (RAID 1 2x72 GB [channels 0+1])
Drive G: (RAID 1 2x72 GB [channels 0+1])
Navision Transaction Log (F:+G: )

Drive H: (RAID 10 6x72 GB [channels 0+1])
TempDB

Controller 2 (4 Channel RAID)

Drive I: (RAID 10 14x36 GB [channels 0+1])
Navision "Data FIlegroup 1" filegroup -File 1

Drive J: (RAID 10 14x36 GB [channels 3+4])
Navision "Data FIlegroup 1" filegroup -File 2

---

Waldo

Takes a whole room to be able to fit the hardware... 8-[

Since I'm very interested in the effect of hardware on the performance of Dynamics NAV, I ask for you opinion:

For controller 2:
Don't you tink it can make a difference to split the two RAID10s to 4 RAID10s? RAID0 of 7 disks, or two times RAID0 of 3 and 4 disks can make a big difference in performance... . Small calculation: RAID0 of 7 disks makes performance 260% better then 1 disk. Splitted in RAID0 on 4 and 3 disks makes performance 390% better. I understand this will cost you an extra controler.

bbrown

Actually, this configuration fits in just 13U of rack space.

Rule #1: There will never be a FAST ENOUGH computer

Regardless of the configuration we settle on, we must get someone to pay for it. The goal is to work the balance between performance and cost. Your suggestion to add extra RAID sets would require not only an extra controller ($1300), but also 2 additional drive enclosures ($3000 each). This would be a better than 10% cost increase in the existing system, and since the number of physical drive remains the same, I would expect the performance increase to be minimal at best.

Also spreading across uneven sized drives would waste space and is not recommended.

Waldo

The extra controller I get, but not the 2 additional drive enclosures. :-k

number of physical drive remains the same, I would expect the performance increase to be minimal at best

I think not. Striping 2 disks = 100%performance gain (writing the data 2 times faster). Striping 3 disks ads an extra 50%=150%, ... . So, you can make the calculation for 7 disks... . I was wrong the first time: should be 254% and 333%. :oops:

Also spreading across uneven sized drives would waste space and is not recommended.

True.

I was just asking for your opinion and I was definitally not saying this was a solution or a "must-do" for you. It is difficult to get people's opinions on things like this... . Thanks for yours.

bbrown

The extra controller I get, but not the 2 additional drive enclosures.

The drive enclosures are dual bus 14 drive enclosures (2x7). The two existing enclosures are connected to the 4 channels of controller 2. If you add another controller then we must add extar enclosures and move drives to support it.

I think not. Striping 2 disks = 100%performance gain (writing the data 2 times faster). Striping 3 disks ads an extra 50%=150%,

These numbers refer to the physical drives being striped within a RAID set. In your suggested configuration you have not increased the number of drives. Adding the extra controller may result in a small increase but not 300%.

Waldo

huh? :-k

Sandra

bbrown!

Please be sure write when this phenomena is resolved. We have a similar or even the same problem with our G/L Entries. No changes made, but if someone in our accounting department wishes to see the G/L Entries they take up to about 20 min until the select is done. Not always just sometimes - so any news on this problem would be great.
(we are also running 4.0 Sp 2 on SQL Server 2005).

Regards
Sandra