This hotfix solved our NAV 2009sp1 SQL performance problems

JamieHurst

We've been struggling with performance and I think we finally found a fix worth sharing.

Environment:
- NAV 2009 SP1, classic only (Build 31793)
- 150 GB database on SQL 2008 r2
- 6 companies
- Licensed for 116 users, average about 90 concurrent (we love ExpandIT)
- Lots of add-ons and customizations

Until now, we've achieved incremental improvement, thanks to the advice of two major US partners and the SQL Perform tool. But still, every month or two performance would grind to a halt and just going from one record to the next on a Card form would take a second or two.

Strangely, when we experienced a slow-down, only our biggest company was affected, the others were fine. And, it was EVERYTHING in this one company: Items, Vendors, Customers, Jobs, G/L Accounts, Fixed Assets, SOs, POs, TOs, WOs -- even stuff we don't use very much, like Standard Text. Meanwhile, the same things were perfectly normal in the other companies.

Have I built up the suspense long enough?

It turns out the "Record Link" table was killing us. It needed a compound index which isn't there by default. We have about 4,500 records in this table, mostly for our one "problem" company (it's a "DataPerCompany=No" table). And, just about every table read automatically checks Record Link so the client can show that little "LINKS" notification in the status bar. So an inefficient index in this single table can affect performance all over the place.

Anyway, here's the hotfix:
http://support.microsoft.com/kb/2648727

lvanvugt

Jamie, great that you share you findings with the community. Appreciated.